Database Reference
In-Depth Information
Figure 27-4.
Wait Statistics Analysis troubleshooting cycle
As a first step, you look at the wait statistics, which are detecting the top waits in the system. This narrows the area of
concern for further analysis. After that, you confirm the problem using other tools, such as DMV, Windows Performance
Monitor, SQL Traces and Extended Events, and detect the root-cause of the problem. When the root-cause is confirmed,
you fix it and analyze the wait statistics again, choosing a new target for analysis and improvement.
This is a never-ending process. Waits always exist in systems, and there is always space for improvements.
However, a generic 80/20 Pareto principle can be applied to almost any troubleshooting and optimization process. You
achieve an 80 percent effect or improvement by spending 20 percent of your time. At some point, further optimization
does not provide a sufficient return on investment and it is better to spend your time and resources elsewhere.
Even though wait statistics can help you detect problematic areas in a system, it is not always easy to find the
root-cause of a problem. Different issues affect and often mask each other.
Figure
27-5
illustrates such a situation. Bad system performance due to a slow and unresponsive I/O subsystem
often occurs due to missing indexes and non-optimized queries that overloaded it. Those queries require SQL Server
to scan a large amount of data, which flushes the content of the buffer pool and contributes to CPU load. Moreover,
missing indexes introduce locking and blocking in the system.
Figure 27-5.
Everything is related
