“The SQL Guy” Post #14: The Myth Behind Stored Procedure Behaviour

“The SQL Guy” Post #14: The Myth Behind Stored Procedure Behaviour

  • Comments 2
  • Likes

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

 

Steps

Action

1

CLEARNING THE CACHE AND LOGGING THE MESSAGE IN ERRORLOG

DBCC FREEPROCCACHE

 

 

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?

 

Steps

Action

1

QUERY TO LIST ALL CACHED PLANS

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Thanks heaps for the brilliant tip!

    This helped me big time... :)

  • Glad you found it useful!

    Damir