MSSQL – Execution Plan Caching and Recompiling

Hi,

Have you every experience that you have a stored procedure(SP) that have plenty of parameters that when 1 set of parameter is executed for the very first time the said SPwas created, its fast. Then after you have a new set of parameter passed to the same SP you’ll end up a long execution process?

Then that is because of a so called Execution Plan Caching that MSSQL is doing on your query. In an overview MSSQL check if there is an existing execution plan for the SP. If there is none, it will create an optimized execution plan, compile, and then cache it on memory. So when the same SP is executed with the same number of parameter it will be reused.

So in cases that you have the same number of parameter every time you use the said stored procedure and you know that you have a logic that will change the query result then this optimization may not be on our side.

When does this caching is cleared? It is only cleared when either there are no memory allocation for MSSQL or you force MSSQL to recompile the SP.

How to Recompile your specific SP? Just add OPTION (RECOMPILE) at the end of your query, before the END statement, and you are ready to go. In some cases you may want to force all cache to be removed, you need to use another statement to do that and that is DBCC FREEPROCCACHE.

That’s it. Hope it helped you as it helped me on my project.

God Bless!
Thanks,
Thomie

Author: Thomie Jose San Agustin

He is a Microsoft Certified Professional whom develop different website using ASP .NET MVC and PHP. He can also create systems out of C#. A Magna Cum Laude of Adamson University whom started a Freelance company named i-CT Combinatorics Technology with his friend Calbin Montalban. He accepts both Website and System development and even can help now his colleagues in generating their logic and research.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.