Not an advance theory, everyone knows that first execution of the stored procedure creates the best plan for the execution and keeps it in the memory for the next execution. The plan is created based on the values passed to parameters with its first execution. This behavior is called as Parameter Sniffing and it is considered as a good thing as long as the cached plan is still good for parameter values passed with the consecutive execution. This behavior becomes bad when a completely odd value is passed for the parameter or the value of the parameter is vary with initial value. In a situation like this, we consider that the cached plan is not the best for this execution, hence we force SQL Server engine to recompile the plan with new values.
Forcing is done with RECOMPILE option. This option can be added to the stored procedure at its creation, resulting recompilation with all executions or can be added to the execute statement, resulting recompilation only with the current execution. Now the question is, when we force to recompile the stored procedure and generate a new plan by adding RECOMPILE option, does it discard the previously cached plan? Does it keep the new plan for next execution?
Let's test this. Following code adds a stored procedure and executes it three times. Then it checks cache.
-- Clearing the cache DBCC FREEPROCCACHE GO USE AdventureWorks2014; GO -- Drop if it is exist IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL DROP PROC dbo.GetOrderDetails; GO -- Create the procedure CREATE PROC dbo.GetOrderDetails @Number int AS SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID <= @Number GO -- Execute three times EXEC dbo.GetOrderDetails 1; GO 3 -- Seeing the cache SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t WHERE p.usecounts > 0 AND TEXT LIKE '%Sales.SalesOrderDetail%' AND TEXT NOT LIKE '%dm_exec_cached_plans%' ORDER BY p.usecounts DESC;
As you see, plan has been created with its first execution and it has been used three times. Now let's try to use a different plan for executing the procedure. Let's force engine to recompile and check the cache again.
-- Execute with RECOMPILE EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE; GO -- Checking the cache SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t WHERE p.usecounts > 0 AND TEXT LIKE '%Sales.SalesOrderDetail%' AND TEXT NOT LIKE '%dm_exec_cached_plans%' ORDER BY p.usecounts DESC;
This shows the same plan and no new plan. What does it mean? If a new plan is created, why cannot we see it? If no new plan is created, has the old plan been used? If so, why we do not see an increase on usercounts for the plan? Answer is simple. RECOMPILE creates a new plan based on passed values but it does not keep it and it does not remove the old one from the cache. The newly created plan is only for this execution.
No comments:
Post a Comment