By: Enrique Garcia | Updated: 2007-08-08 | Comments | Related: 1 | 2 | > Query Plans
Problem
You get a call from tech support that users are complaining about a report being particularly slow. Tech support says they think the problem occurs when retrieving data from the database. After using SQL Profiler to find out what is happening, you find out that the stored procedure that pulls the data for the report is having very high reads and it is taking several seconds to complete. However, when you execute this procedure on the same database to analyze the query plan and start solving the problem, you can't reproduce the bad performance the users are complaining about, even when you use the same parameters. How can you reproduce the problem to get the same results as the users?
Solution
Query execution plans are created the first time a stored procedure gets executed, once created the plans are cached and reused unless the query optimizer decides based on several factors (schema changes, statistics changes, etc.), to create a new plan. However, different query plans can be created for different connections if they have different SET options (SET ARITHABORT ON/OFF, SET ANSI_NULLS ON/OFF, etc.), so it is possible to have several execution plans for the same stored procedure at the same time.
If the user's application has different SET options than those of the tools you are using to execute the queries (for example, SQL Server Management Studio), you will very likely get a different execution plan then the one the user is getting, and the stored procedure's performance will be different.
In order to reproduce the problem in the same database where the performance problem is happening, you have to get the SET options that the application's connection is using and then set your connection the same way. The easiest way to get this information is by using SQL Profiler and tracing the ExistingConnection event class (it is included in the Standard Template).
Once you get the application's SET options, you can setup your connection the same way and you will be able to get the same cached plans that the users are getting from the application. The following steps show you how to do this:
- Launch SQL Profiler
- In SQL Profiler, select the Standard Template.
- Start the trace with the default settings. The very first rows that will appear correspond to the ExistingConnection event class. Each row corresponds to a different database connection.
- Select the row that corresponds to the connection being used by the user's application and copy the TextData column to your query window. The text data column for the ExistingConnection event class contains the required SET commands to setup any connection the same way as the selected connection.
- Execute the user's stored procedure to reproduce the problem. Now you will get the same execution plan that the user is getting and you can start to analyze the execution plan.
Next Steps
In these types of scenarios, very likely you will have a parameter sniffing problem. To test for this possibility, force a recompilation of the stored procedure (for example by dropping the procedure and creating it again or by executing the DBCC FREEPROCCACHE command). If the performance improves with this action then you have found that the culprit is parameter sniffing.
If the performance does not improve by forcing a recompilation of the stored procedure you will have to turn to other methods like index analysis.
Stay tuned for more tips regarding options to avoid parameter sniffing and other performance related improvements.
About the author
Enrique Garcia is a Software Developer and his area of interests are database performance and .Net development.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-08-08
