April 11, 2011 at 8:06 am
Just reading the aforementioned whitepaper, and playing with the example for Recompilations due to SET option changes
For the example
CREATE PROCEDURE SetOptionsDemo
AS
BEGIN
SET ANSI_NULLS OFF;
SELECT p.Size AS ProductSize,
SUM(p.ListPrice) AS TotalPrice
FROM Production.Product AS p
INNER JOIN Production.ProductCategory AS pc
ON p.ProductSubcategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY p.Size
ORDER BY ProductSize, TotalPrice;
END;
GO
SET ANSI_NULLS ON;
EXEC SetOptionsDemo; -- causes a recompilation
GO
EXEC SetOptionsDemo; -- does not cause a recompilation
GO
It states
When SetOptionsDemo is executed, the "SELECT" query is compiled with "ANSI_NULLS" ON. When SetOptionsDemo begins execution, the value of that SET option changes because of "SET ANSI_NULLS OFF", and therefore the compiled query plan is no longer "valid." It is therefore recompiled with "ANSI_NULLS " OFF.
Well actually my profile trace does not show up a recompile for that example .. Am I missing something ?!
thanks
Simon
April 11, 2011 at 8:32 am
What events are you tracing for?
That shouldn't cause a recompile. It should cause a compile and a second plan in cache with the different set option. Set options don't so much cause recompiles as multiple plans in cache, for certain set options anyway.
p.s. Which whitepaper are you referring to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 8:36 am
http://msdn.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx
if you go to the section
Recompilations due to SET option changes
and Im tracing
SP:Starting
SP:StmtStarting
SP:Recompile
SP:Completed
April 11, 2011 at 8:38 am
It shouldn't cause a recompile event. That's for when the query starts executing and something (schema/stats) have changed invalidating the plan. If that set option is one that results in different plans, it'll be part of the lookup key of the plan cache, hence a change should result in a cache miss and a whole new plan.
Try tracing sp:CacheMiss and sp:CacheInsert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 8:41 am
so the assertion that it does in the paper is incorrect or am I reading it wrong to start with ? 🙂
April 11, 2011 at 8:46 am
Well.....
Test and confirm that I'm not lying to you first. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 8:53 am
I trust you... you like babylon 5 !
but yes I did run a trace with those events selected and it causes a cachemiss so guessing the whitepaper is slighlty out 🙂
April 11, 2011 at 8:59 am
Simon_L (4/11/2011)
but yes I did run a trace with those events selected and it causes a cachemiss so guessing the whitepaper is slighlty out 🙂
What it probably is, is a difference in what is meant by 'recompile'. The event traces cases where execution starts, the plan is found to be invalid and the query is thrown back to be recompiled, not the case where the query cannot use the plan existing in cache because of a difference in SET options
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply