Plan caching in SQL 2008 ~ Recompilations due to SET option changes

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • so the assertion that it does in the paper is incorrect or am I reading it wrong to start with ? 🙂

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 🙂

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply