“The SQL Guy” Post #14: The Myth Behind Stored Procedure Behaviour
You are a trusted database developer for your company. You developed a Stored Procedure that will perform some important functions on the database. You tested the Stored Procedure and everything looks great. The Stored Procedure runs the way you expected and within the anticipated timeframe. Everything looks great and now you are ready to deploy the code to the production environment. The production environment has same hardware as development/test environment therefore, you are confident that your procedure will run just fine.
Deployment day approaches and you or your DBA team deploy the code to the production environment. The Stored Procedure is live but it is not performing the way you had expected. You are now wondering “what went wrong?”.
You may have experienced something similar where the stored procedure/query performs bad when deployed on the production SQL Server and the same stored procedure/query works perfectly fine in the test/development environment.
POSSIBLE CAUSE
It is possible for Stored Procedure/queries to behave differently in Dev/Test and Production environments if you have not cleaned up the query execution plan cache from the dev/test environment while performing tests. Stored Procedures or queries will first try to use an existing plan cache at every execution and in the above scenario that may very well be happening.
BACKGROUND
When any SQL statement is executed, SQL Server first looks through the procedure cache (by querying SYS.DM_EXEC_CACHED_PLANS) to verify that an existing execution plan for the SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the same SQL statement. If it cannot find an execution plan, it generates a new execution plan for that query.
BEST PRACTICE
The best practice for deploying the code in your production environment is to make sure you first test the code in the test / development environment by removing cached query plans so that you know how your stored procedure or queries would perform in "Cold" cache which is almost like reproducing the cache as though SQL Server had just been started.
CAUTION
DO NOT USE THE BELOW COMMANDS IN THE PRODUCTION ENVIRONMENT, AS YOU COULD BE FLUSHING OUT SEVERAL CACHED QUERY EXECUTION PLANS THAT MAY PERFECTLY BE WORKING FINE.
HOW TO:
Please use the below commands on the test/development environment to clear out the cached plans for queries/stored procedures.
CLEARNING A SPECIFIC QUERY CACHED PLAN:
Steps |
Action |
1 |
EXECUTE THE QUERY YOU WOULD LIKE TO CLEAR THE CACHE FOR. SELECT * FROM CUSTOMERS |
2 |
OBTAIN THE PLAN_HANDLE FOR THE ABOVE QUERY SELECT PLAN_HANDLE, ST.TEXT FROM SYS.DM_EXEC_CACHED_PLANS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST WHERE TEXT LIKE 'SELECT * FROM CUSTOMERS%' GO |
3 |
TAKE THE PLAN HANDLE FROM THE ABOVE QUERY AND CLEAR THE CACHE DBCC FREEPROCCACHE (<PLAN_HANDLE>) |
CLEARING ALL CACHED PLANS
|
|
|
|
|
|
|
| |
|
|
|
HOW TO QUERY ALL CACHED PLANS FROM LOCAL INSTANCE OF SQL SERVER?
Steps |
Action |
1 |
QUERY TO LIST ALL CACHED PLANS SELECT * FROM SYS.DM_EXEC_CACHED_PLANS |