Looking into Stored Procedure recompilation in SQL Server August 4, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
In this post I will be looking into recompilation and stored procedures in SQL Server. More specifically I will show you why it happens,when it happens and the tools to identify recompilation of stored procedures.There are many reasons why a stored procedure will recompile. Some are more obvious than others.
If you use the sp_recompile system stored procedure before the actual stored procedure the SQL Server Optimiser will drop the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure will run.You can also add the WITH RECOMPILE directly to the stored procedure declaration right before the AS keyword. This tells SQL Server to throw out the query plan on every execution of the stored procedure.
In this post I will be looking into how schema changes in objects that are referenced by the stored procedure, can cause recompilation.
I will be using SQL Server 2012 Developer edition in this post but feel free to use SQL Server 2005,2008 versions.I will also use SQL Server Profiler.
We all know that SQL Server optimises the execution of a sql query using execution plans.The execution plan is what is compiled and executed by the relational engine and then placed for reuse in the plan cache.T-SQL is declarative by nature.That means that it does not provide any details on how the results will be returned.On the other hand SQL Server execution plans provide the actual (imperative-detailed) way for the relational engine to fetch the results.One way of using the plan cache in an efficient way is exactly writing you T-SQL commands in the context of a stored procedure.
Let me demonstrate recompilation by creating a small example. I am using the AdventureWorks2012 database.
I connect to the SQL Server instance.
Type (copy and paste but make sure you review it) the code below.Make sure you do not execute anything yet.
USE [AdventureWorks2012] GO IF ( SELECT OBJECT_ID('dbo.GetProductInfoByListPrice') ) IS NOT NULL DROP PROCEDURE dbo.GetProductInfoByListPrice GO CREATE PROCEDURE GetProductInfoByListPrice @LowerVal MONEY, @UpperVal MONEY AS BEGIN SELECT Name, ProductNumber, Color,Size,StandardCost FROM Production.Product WHERE [ListPrice] > @LowerVal AND [ListPrice] < @UpperVal END GO DBCC FREEPROCCACHE SELECT cpl.objtype AS PlanType, OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName, cpl.refcounts AS ReferenceCounts, cpl.usecounts AS UseCounts, stl.text AS SQLBatch, qpl.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cpl CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl; GO
Execute lines 1-3 to bring AdventureWorks2012 to scope. Then in lines 5-10 I check to see if the stored procedure already exists. Then I create a stored procedure (lines 12-23) that returns a few columns from the Production.Product table based on two input parameters based on the ListPrice column values.In line 29 I use the DBCC command to clear the plan cache.Please do not execute this command in production systems.
In lines 31-40 I use 1 DMV and 2 DMFs to get information from the procedure cache.I get information such as the Plan Type,Use counts(how many times the plan was used),the actual query and its query plan.When you execute the code you will see that there are no plans in the plan cache.
Now we need to launch SQL Server Profiler. Go to the menu Tools -> SQL Server Profiler.The SQL Server Profiler will launch and I will connect using Windows Authentication.I will create a new trace based on the Blank template.In the events tab I will use the SP:Recompile and SQL:StmtRecompile.The SQL:StmtRecompile event fires every time a statement recompilation occurs during execution.The SP:Recompile event exists but only for backwards compatibility in SQL Server 2005.Make sure you hit the Run button.
Have a look at the picture below
In the same query window type (copy-paste) but do not execute the code below.
EXEC GetProductInfoByListPrice 7,155 EXEC GetProductInfoByListPrice 24,3456 SELECT cpl.objtype AS PlanType, OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName, cpl.refcounts AS ReferenceCounts, cpl.usecounts AS UseCounts, stl.text AS SQLBatch, qpl.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cpl CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl; GO
Execute lines 1-3.We execute the stored procedure with two different values. Then have a look again in the procedure cache (lines 5-14).
Ηave a look at the picture below to see the results I have got in my case.The execution plan was created, placed in the cache and reused.There is no activity in the SQL Server Profiler.
In the same query window type the following
CREATE INDEX IX_Test ON Production.Product(ListPrice) EXEC GetProductInfoByListPrice 55,5555
I am creating a new index on the Production.Product table and then I execute the stored procedure again. SQL Server Optimiser detects the presence of the new index and does not use the current execution plan in the procedure cache.It does recompile the existing plan because it is more beneficial to spend more CPU cycles to create a better execution plan.
Have a look in the picture below
To recap we saw that the SQL Server Optimiser will drop a plan from the procedure cache when it decides that are changes in the underlying objects that are referenced from the stored procedure that result in a more optimal plan.
Bear in mind that if you have a high recompilation ratio this is one factor that will slow your whole application.You must identify the reasons behind that and eliminate them.It costs both in terms of time and resources for SQL Server to create a new plan.
Hope it helps!!!