SQL Server performance tuning methodology and common tools

In earlier articles, the detect methodology in performance tuning was mentioned, and the Detect methodology was briefly reviewed here.

Discover the problem: finding problems

Explore The conditions: Reasons to explore

Track down possible approaches: providing a possible solution

Execute the most likely approach: perform the best possible solution

Check of Success: Confirm success (if not successful, repeat the above steps)

Tie up Loose Ends: complete Remaining Work

First look at Discover, find the problem

Has the whole problem been succinctly described?

Where is the user's current baseline?

What does the user expect?

It's not all the problems that can be solved.

Look at explore again, explore the reasons

Obtain evidence

• SQLProfiler Trace/sql Trace
• DMV and DMF
• Showplan Execution Plan output
• Performance counters for various systems
• SQL Server-specific performance counters

Check the most obvious questions (don't go into the question first)

Track down provides a possible solution

The first stage, the establishment of a plan to prove the hypothesis

The second stage, the establishment of a problem-solving plan

Execute the most likely solution

The first stage, execute the test plan to prove your hypothesis.

Phase II, implementation of the solution to the problem

Check to confirm success or not

First Stage

Did your plan prove your hypothesis, or did you overthrow him?

Phase II

• Does your plan change the phenomenon?
• Is the bottleneck shifting?
• Does the solution match your original goal?

Remember: The process is often recursive and repeated

Tie up completes the rest of the work, ending

• Is performance tuning up to an unpredictable marginal effect?
• Did the changes really solve the problem, and will they encounter the same problem in a short time?
• What kind of tracking work do you need to do?

It said that the methodology, although very boring, but it is instructive. Here's some practical knowledge.

Bottleneck analysis

Definition of bottlenecks

Bottleneck = Rate of demand > actual throughput

Process:

• Deciding which point to be stuck on
• Determine what is waiting in the queue
• Reduce input (rate of demand) or increase simultaneous throughput
• Determining income
• Determining costs
• 
  

monitoring tasks for common bottlenecks

• Monitor memory consumption
• Monitor thread and CPU usage
• Monitor HDD IO
• Monitor low performance queries
• Monitoring stored procedures, SQL, and user activity
• Monitor current locks and user interactions

Build a performance tuning plan

Performance tuning is the iterative process, again and again, the again cycle, again and again approaching the revision, to use the text records to explain

• Make a point, highlight the problem and prove
• The approximation target of the system
• Have a consensus, know each other talking about the land
• Ability to summarize comparisons
• When the system comes up with multiple bottlenecks, find the most critical, lowest-cost first-perform tuning

When performing a performance tuning plan, determine the impact on the on-line production environment

Methodology--narrowing

Common tasks in Performance tuning

• Windows Event Viewer
• Windows System Monitor
• Current active window in SSMS
• T-SQL Tools
• SQL Profiler
• Query Analyzer
• Database Engine Tuning Advisor

Windows Event Viewer, primarily to view the following event logs

• Windows Application Log
• Windows system logs
• Windows security Log

Windows System Monitor is able to track:

• SQL Server I/O
• SQL Server memory
• SQL Server user
• SQL Server lock
• Copy activity

Activity Monitor in SSMs:

• Active user Tasks
• Resource Waits
• Data file I/O
• A resource-intensive query

T-SQL Tools:

• System stored Procedures
• Global variables
• T-SQL statements
• Dbcc
• Trace flag
• Dmf/dmf

SQL Profiler, tracking and capturing SQL Server events

• Select the events you want to track
• Select a track template
• Select the data you want to capture
• Make sense of classifying data

Query Analyzer

• Show Query Execution plan
• Show Server Traces
• Displaying server-side statistics
• displaying client statistics

Database Engine Tuning Advisor

• Analyze bottlenecks
• Give the recommended SQL statements (indexes and statistics)

SQL Server performance tuning methodology and common tools