Post reply

Plan Caching in Sql Server 2005

  • September 4, 2012 at 2:10 pm

    #143878

    So I just read an article on Plan Caching that was extremely informative.

    http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

    I am trying to determine what the potential size of our plan cache is but I don't quite understand the formula. We have 180 gb of memory on each of our servers. And the article sayS

    75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB

    Not sure what this even means. We have been having some intermittent performance issues with queries randomly taking 23secs, 35 secs, etc when normally they may only take 2 seconds or lesst.

    Just trying to get a good understanding of the plan cache and how you know what is too many single use plans versus your overall plans.

    Any thoughts?

  • September 4, 2012 at 2:14 pm

    #1533506

    "75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB"

    So you have 180 GB of memory...

    75% of the memory between 0 and 4GB is 3GB. 10% of the visible memory between 4 and 64 GB is 6GB (10% of 60), 5% of memory above 64 GB, you have 180 GB so that's 116GB, 5% of that is 5.8 GB (say 6GB for ease) and your plan cache max is 3+6+6 = 15 GB.

    As for your badly performing queries, have you checked for blocking or other wait types?

    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

  • September 4, 2012 at 2:22 pm

    #1533509

    thank You very much for the explanation. Totally makes sense. We have checked for blocking and waits. And have not seen much. We have just found some issues with deadlocks so that may be the cause.

    Is there a general base line of what percentage of the cache is too much for single use plans?

    So for example , we have 20 instances across approximately 18 servers. On one server there are 150 databases.

    Across all of those databases we have Plans cached22290size of the cached plans 8744MB

    Single-Use Plans Cached12755Size of the single use is 4662. That seems like an awful lot of single use plans relative to the overall cache(50%)

Viewing 3 posts - 1 through 2 (of 2 total)

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