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

These are all vital bits of information that help you arrive at a problem statement that starts to describe
the issue you need to investigate.
The Plan of Attack
Once you have a problem statement, you can formulate a plan of attack. This should simply state what
you think the underlying problem is, what the data is that you want to collect, and what analysis of that
data should show.
This step is short and easy, but it is also very important. It makes sure you start on the next step correctly
and have thought about what you are going to do with any data you collect.
An example of a problem statement and plan of attack is shown below.
Problem statement:
Several users of system X have reported poor performance. They are reporting this problem during peak
hours, which for them are from 1 pm through 3:30 pm on Mondays, Wednesdays, and Fridays. They are
connecting to database Y on Server Z. This application is the only application using this database and
server. The server has dedicated local storage.
Plan of attack:
Capture Perfmon counters for server resources from Server Z. Review for system bottlenecks. Capture
SQL Server wait types from database Y on Server Z. Review for long wait types. Data will be captured
during three periods when the system exhibits the performance problem.
Capture server level performance counters at 1-second intervals over three 5-minute sampling
periods between 1 and 3:30 pm on days when the problem is reported. Capture counters at 5-second
intervals between 1 and 3:30 pm on days when the problem is reported. If no baseline is available, cap-
ture server performance counters at 15-second intervals over a 24-hour period on days when the problem
occurs, and also on at least one day when it doesn't occur.
Capture SQL Server Wait stats. Sample at 30-second intervals over a 5-minute period. Repeat three
times over the period when the user reports the problem. Review for long wait times.
The plan of attack should detail what data you intend to collect, what you intend to do with it, and maybe
even mention some follow-up actions depending on the analysis of the data collected. Another important
aspect of the plan of attack regarding data collection is when you want to collect the data and for how
long. This information may come from the problem statement (as in the above example), but in a case
where the problem is either continuous or intermittent, you will need to be creative about when and for
how long to sample.
Data Collection
The plan of attack has defined what data you want to collect and what you intend to do with it. Now you
can go ahead and start collecting the data.
This step will involve setting up the data collection, then sitting back and letting the data roll in for some
period of time. This could include collecting hundreds of GB of Performance Monitor logs, running a