Bringing It All Together with SQL Nexus - SQL Server 2012 Internals and Troubleshooting - page 334

The second nugget of information that you will i nd in this report is the graphical query, which
gives you an aggregated view of the different waits observed, grouped into categories. In
Figure 12-10, note that 41% of the total waits observed on the SQL Server instance are attributed to
locks. Therefore, based on a single report, you can determine that the SQL Server instance had
a blocking bottleneck. The next step is to drill down into the data to ascertain the i ner details about
the blocking bottleneck.
FIGURE 12-10
The table shown in Figure 12-10 has a drill-through option for each wait category other than CPU.
Clicking any of these wait categories will take you to another report containing details about the
wait category recorded from the sys.dm_exec_requests output captured by the SQL 2005/2008
Perf Stats script.
The Bottleneck Analysis report provides a quick way to gauge the bottlenecks that were experienced
on the SQL Server instance from which the diagnostic data was collected. This report enables you
to ascertain whether the bottleneck is a red herring. The drill-through option helps you get granular
information about the wait category that you are interested in analyzing further. In short, you can
easily identify your top bottlenecks with a few mouse clicks!
NOTE The CPU wait category is uniquely different from the other wait
categories. It classii es the amount of time taken by requests utilizing the CPU
and requests waiting for CPU quantum, which is the smallest unit of execution
time, within the same group. Therefore, if you have a system that has a CPU
bound SQL Server workload, then you might i nd CPU to be the top wait
category even though you don't see a 100% CPU usage. This is not necessarily a
problem, and you will have to perform a certain amount of due diligence before
identifying queries with high CPU usage as the culprit(s).
Shifting our focus now to the Blocking and Wait Statistics report, this report shows a snapshot
of the top wait categories, along with a list of any blocking chains that were reported when
when the SQL Server 2005/2008 Perf Stats were collecting data. Figure 12-11 shows an example of
the report.