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

Example 2 — Stored Procedure X Is Slow
This example is a very different kind of problem. In this example, the problem is very well-defined.
Rather than the whole application slowing down as in the previous example, in this case, a single function
is reported with the problem. In this case you can trace the feature to a single stored procedure, or a small
set of stored procedures. In this example we will deal with a single stored procedure.
ProblemStatement
Unlike the previous example, in this example you know where the problem is, but you don't yet know
what is causing it. There could be many reasons for a stored procedure slowing down. The goal of this
performance tuning operation will be to determine why the stored procedure is slow. In essence, this
means finding the bottleneck to be able to remove that bottleneck.
In this case, you can write a much more focused problem statement.
Problem Statement:
Stored procedure X is running slowly. In previous executions it takes on average 10 msec, with a min of
2 msec and a max of 15 msec. Since this morning it has been taking on average 600 msec. The goal is to
identify the root cause of this slow-down and tune the stored procedure to improve performance back to
the original execution speeds.
PlanofAttack
With a much more focused problem statement, the plan of attack will be correspondingly more focused.
You shouldn't need to look at the server as a whole, and you don't need to find one particular long
running query. A lot of the work you had to do in the previous example isn't needed here. A good plan
of attack for this example is listed below.
Plan of Attack:
Review the execution of Stored Procedure X. Capture an execution plan of the stored procedure. Review
the plan and tune the stored procedure to optimize execution time.
DataCollection
The data collection in this example is much easier, although there can be challenges with capturing a
plan for the stored procedure. In some cases this might be as easy as running the query in SQL Server
Management Studio with the relevant options enabled to show a graphical plan or to return a text plan.
In other cases the data passed to the stored procedure is critical. In these cases you might need to capture
real parameters using a Profiler Trace so that you can execute using the real data in Management Studio.
In other cases you might need to use SQL Server profiler to capture the actual execution plan of a live
running instance of the stored procedure being called by the application.
DataAnalysis
The data analysis stage in this example is all about interpreting the execution plan and tuning it. This is
covered in Chapter 9.
The iterations here are around the data collection and analysis pieces. As the problem has already been
narrowed down to a particular stored procedure and the goal is to improve the performance of this