Post reply

Plan Guides and plan cache

  • August 14, 2014 at 4:08 am

    #289909

    Hello.

    On my production SQL Server i have a problem with a lot of adhoc queries. They were used by third-party software, so i was unable to fix it and decided to create a template plan guide with option (parametrization forced). But now, when i look at my sys.dm_exec_cached_plans, i see that usecounts number for plan_guide query plan are growing, but i also still see all this adhoc plans. I always thought, that creating a plan guide should stop this queries from blowing away query cache and creating adhoc plans, they should use one parametrized plan. Am i wrong?

  • August 14, 2014 at 4:18 am

    #1737980

    The ad-hoc ones just shells, not actual plans. They're there so that SQL can link the original query and the parameterisd

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/68d06d07-e662-4359-b55f-1ba646e3e6b1/forced-parameterization-why-still-multiple-plans-in-cache

    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

  • August 14, 2014 at 4:36 am

    #1737983

    Thanks for your reply.

    So, althought sys.dm_exec_cached_plans shows me, that every adhoc query have, for example, 16300 size_in_bytes, in reality it doesn't occupy it?

  • August 14, 2014 at 4:37 am

    #1737984

    Offhand, no idea.

    You should be able to dig up some info on plan shells from google.

    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 4 posts - 1 through 4 (of 4 total)

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