Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Chris Di LulloSr. IT Pro Marketing Manager Twitter | LinkedIn Pierre Roman Twitter | LinkedIn Mitch Garvis Twitter | LinkedIn Anthony Bartolo Twitter | LinkedIn
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.
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.
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.
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.
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.
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:
EXECUTE THE QUERY YOU WOULD LIKE TO CLEAR THE CACHE FOR.
SELECT * FROM CUSTOMERS
OBTAIN THE PLAN_HANDLE FOR THE ABOVE QUERY
SELECT PLAN_HANDLE, ST.TEXT
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST
WHERE TEXT LIKE 'SELECT * FROM CUSTOMERS%'
TAKE THE PLAN HANDLE FROM THE ABOVE QUERY AND CLEAR THE CACHE
DBCC FREEPROCCACHE (<PLAN_HANDLE>)
CLEARING ALL CACHED PLANS
CLEARNING THE CACHE AND LOGGING THE MESSAGE IN ERRORLOG
CLEARNING THE CACHE WITHOUT LOGGING THE MESSAGE IN ERRORLOG
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
HOW TO QUERY ALL CACHED PLANS FROM LOCAL INSTANCE OF SQL SERVER?
QUERY TO LIST ALL CACHED PLANS
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS
Thanks heaps for the brilliant tip!
This helped me big time... :)
Glad you found it useful!