SQL Server Performance Tuning methodology and common tools, SQL Server Tuning

SQL Server Performance Tuning methodology and common tools, SQL Server Tuning

In earlier articles, we mentioned the DETECT methodology in Performance Tuning. Here we will briefly review the DETECT methodology.

Discover the problem: found the problem

Lead e the conditions: Explore causes

Track down possible approaches: provides possible solutions

Execute the most likely approach: the best possible solution for execution

Check of success: Check whether it is successful (if not, repeat the above steps)

Tie up loose ends: Finish the remaining work

First, check Discover to find the problem.

Have you briefly described the entire problem?

Where is the current baseline of the user?

What do users expect?

Not all problems can be solved.

Let's look at caffe to find out why.

Obtain evidence

• SqlProfiler Trace/SQL Trace
• DMV and DMF
• ShowPlan execution plan output
• Performance counters of various systems
• Performance counters unique to sqlserver

Check for the most obvious problems (do not go into depth first)

 

Track down provides possible solutions

Stage 1: Establish a plan to prove assumptions

Stage 2: establish a plan to solve the problem

Execute the most likely Solution

Stage 1: execute a test plan to prove your assumptions

Stage 2: implement the solution to the problem

Check. Check whether the operation is successful.

Stage 1

Does your plan prove your assumption or overwrite him?

Stage 2

• Has your plan changed?
• Is the bottleneck transferred?
• Does the solution meet your original goal?

Remember: This process is usually performed recursively and repeatedly.

Tie up to finish the remaining work.

• Is performance tuning an unpredictable marginal effect?
• Does the modification actually solve the problem? Will it encounter the same problem in a short time?
• What else do we need to do?

Although the methodology mentioned above is boring, it still has guiding significance. Here is some practical knowledge.

Bottleneck Analysis

Bottleneck Definition

Bottleneck = desired rate> actual processing capacity

Process:

• Determines the point on which the data is stored.
• Determines the status of waiting in the queue
• Reduce input (the rate at which demand is met) or increase the simultaneous processing capacity
• Determine earnings
• Determine cost
• 
  

Monitoring tasks with common bottlenecks

• Monitor memory usage
• Monitor thread and CPU usage
• Monitor hard disk I/O
• Monitor low-performance queries
• Monitor stored procedures, SQL, and user activities
• Monitor current locks and user interactions

Create a performance tuning plan

Performance Tuning is an iterative process. The loop of one and three times, the correction that approaches again and again, should be illustrated by text records.

• Give a clue, highlight the problem and prove it
• Approaching target of the system
• Have consensus and know the benchmark of mutual discussion
• Summary and Comparison
• When multiple bottlenecks occur in the system, find the most critical and lowest-cost optimization

When executing a performance tuning plan, determine the impact on the online production environment

Methodology-narrowing down

Common work in Performance Tuning

• Windows Event Viewer
• Windows System Monitor
• Current active window in SSMS
• T-SQL tools
• SQL Profiler
• Query Analyzer
• Database Engine Optimization Consultant

Windows event viewer, mainly used to view the following Event Logs

• Windows application logs
• Windows system logs
• Windows Security Log

Windows system monitor supports tracking:

• SQL server I/O
• SQL server Memory
• SQL server user
• SQL server lock
• Copy Activity

Activity monitor in SSMS:

• Active user task
• Resource waiting
• Data File I/O
• Resource-consuming queries

T-SQL tools:

• System stored procedures
• Global Variables
• A T-SQL statement
• DBCC
• Trace tag
• DMF/DMF

SQL Profiler: tracks and captures sqlserver events

• Select the event to be tracked
• Select a Tracking template
• Select the data to capture
• Meaningful data classification

Query Analyzer

• Show query execution plan
• Show server trace
• Display Server statistics
• Display client statistics

Database Engine Optimization Consultant

• Analysis bottleneck
• Provides recommended SQL statements (index and statistics)