Some facts about Stored Procedure Compilation & Recompilation

I generally ask one question in interview “If we have one complex query which JOIN five table, what would work best from .NET application, Call of Query or Stored Procedure?” 80% of candidate used to say “SP works fast”. My next question always would be “WHY?” than out of those 80%, 60% would say “Because SP is a compiled code”. After that I fire my follow up question, I can see option of “Compile page or project in .NET but I never see that kind of option in SSMS, how do you compile your SP?”, once I EXECUTE this question, 90% candidate of those 60% candidate would like to keep silence or divert the talk.

Anyway, intention of this article is to let you know some facts about SP compilation & Recompilation. Since it is big topic and wouldn’t cover under on article, I may come up with some more articles on this topic but right now, let me explain some basic facts only.

First of all, let me tell you that you don’t need to compile Stored Procedure manually, when you execute it, SQL Server will compile your Stored Procedure for you and save the execution plan for future use so that it doesn’t need to compile again and again, this is generic understanding, it doesn’t work all time as few facts are there which cause recompilation many time or every time. If you want to recompile your Stored Procedure manually, you should use “SP_Recompile” Stored Procedure given by SQL Server.

Now, you think if recompilation is that bad than why Microsoft has given facility to recompile? Well, let me have an opportunity to break the ice, recompilation of stored procedure is not always bad. It may be beneficial or may be harmful, it is totally depends on the situation.

Actually compilation of Stored Procedure stores the execution plan first time you execute your Stored Procedure and every follow up call would use the same execution plan but recompilation of SP would be helpful if you have new statistics or new index on the table. BTW, in SQL Server 2008+ there is in-built functionality to recompile at statement level rather than recompiling whole stored procedure which is less resource centric. 

Following is the list of basic cause which forces Stored Procedure to recompile.

·          

• Change in SET option within Stored Procedure
• Execution plan is very old
• Schema change in table, index, view or temp tables which are used in Stored Procedure
•  “Deferred object resolution”, means object was not available while compiling Stored Procedure  but you have created later on, may be some temp table you have created in Stored Procedure.
•  Call of “SP_Recompile” Stored Procedure.
• Call of RECOMPILE clause in Stored Procedure.
• Statistics are old

How to avoid Stored Procedure recompilations?

• Avoid using temp table or other DDL statements as long as possible.
• Use table variable in Stored Procedure if needed
• Avoid changing SET option in Stored Procedure like ARITHABORT, Quoted_Identifier, ANSI_NULLS, ANSI_WARNINGS etc.
• Avoiding recompilation by statistics change by using “KEEPFIXEDPLAN” hint.
• Disable Auto Update statistics for your database.

Well, these are very basic understanding and each point of this article may consume separate dedicated article and I may come up with series on this very soon.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”