Why Queries perform differently in Test vs Production

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.

-- Step 1 - Clear Cached Plan for a specific Query
-- Execute the query you would like to clear the cache for (ex: Customers table)
 
SELECT * FROM CUSTOMERS
Go

-- Step 2 - Obtain Query Plan Handle
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

Let’s now take the plan handle and clear the cache:

-- Step 3 - Clean the Cache
DBCC FREEPROCCACHE (<plan_handle>) -- replace this with the plan handle from the above query
Go

How to query all cached plans from local SQL Server instance

--You can use the below query to list all the cached plans from your database
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS
GO

-- You can use the below statement to clear all the cache plans from your database.
DBCC FREEPROCCACHE
GO
 
-- Clearing all cache plans without logging the messages in error log
DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO

Be careful when clearing all the cache plans as you may be removing a good cache plan as well.