Performance Tuning - Professional SQL Server 2005 Performance Tuning - page 8

PlanofAttack
When you don't have a clear target for data collection, it is necessary to start at the top (the server
resources) and work down until you find a bottleneck that is the root cause of the problem. In this case,
on the first iteration you can collect multiple sets of data. A good place to start is with collecting perfor-
mance counters of the server and SQL resources, maybe also collecting wait stats, and capturing a Profiler
trace looking for long-running queries. This can be a lot of data to collect and analyze. Although it's a
nice idea to cast a broad net like this, in practice it's better to start with just a few data sets and zoom in
on the problem from there.
Plan of Attack:
Collect Perfmon counters of the top level server and SQL resource counters at 5-second intervals for 20
minutes at some point between 09:00 and 11:00.
There are a couple of things to note about this plan of attack. It's gone into quite a lot of detail about the
frequency of counter sampling, how long the counters need to be collected for, and over what time the
counters should be collected. This is all important information to have thought about and to define at
this stage. In a larger organization or anywhere where you are dealing with a team of people who are
responsible for the servers, this is very important as it may often be that this information has to be passed
from the DBA team to the Ops team that will actually run the data collection.
Even if you don't have an environment with a separate team, it's still very useful to go into this amount
of detail as it forces you to think about how much data you really need to collect.
DataCollection
With the plan of attack defined, you can proceed with data collection. In this case it's a pretty simple set
of data to collect. This can be made even easier if you have a set of Logman scripts around that set up
counter logs. An example of using Logman to set up a counter log is given in Chapter 12.
In the case where a separate Operations team manages the server, this is where you sit back and wait
for the files to be dropped onto a shared server somewhere. If your environment is smaller and you are
responsible for all activities on the server, then you will be busy setting up and running the relevant data
collection tools.
Chapters 2 and 3 cover using Performance Monitor to capture Server and SQL resource counters.
DataAnalysis
Once the data collection is complete, the task of analyzing the data starts. In this example there will be a
single Performance Monitor log file to be analyzed. Chapters 2 and 3 cover interpreting the contents of
the Log File.
Once the analysis is complete, it will hopefully point to a resource bottleneck with a server resource like
CPU, Memory, or disk I/O. Alternatively, it might point to a SQL Server resource bottleneck.
The third option is that it doesn't indicate any resource bottleneck. In this case you will need to refine the
problem statement and plan of attack and collect more data. The next iteration should focus on looking
either at SQL Server waits or a Profiler Trace looking for long-running queries.