You may have experienced something similar where the query or a stored procedure performs bad when deployed in the production and the same query / stored procedure works perfectly well in the test or development environment.

Behind the scenes (Query Internals)

It is possible for the query or the stored procedure to behave differently in lower environments than Production if you have not cleaned up the query execution plan cache when performing tests. Internally, when any SQL statement is executed, SQL Server first looks through the procedure cache (by querying SYS.DM_EXEC_CACHED_PLANS) to verify if an existing execution plan for that SQL statement exists or not. If yes, SQL Server re-uses existing plan it finds, which saves the overhead of recompiling the SQL statement. If it cannot find an execution plan, it generates a new execution plan for that query.

What is the 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 environment by removing cached query plans so 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.

Please use the below commands on the test/development environment to clear out the cached plans for queries/stored procedures.

Let’s now obtain the plan handle for the above query

Let’s now taken the plan handle and clear the cache

How to query all cached plans from local SQL Server instance

How to clear all cached plans from SQL Server instance: