Contact us

Madeira Data Solutions

Your Data, Our Solutions

Things You Can Find in the Plan Cache at SQLBits

Written By: Matan Yungman 19/07/2014

On Saturday, I had the pleasure to present my session, “Things You Can Find in the Plan Cache”, at SQLBits!

Thanks to everyone who attended my session.

The scripts and presentation can be downloaded here.

 

5 responses to “Things You Can Find in the Plan Cache at SQLBits”

  1. Oleg says:

    Very usefull post!

  2. Bibash Chandra says:

    Thank you very much for such nice session..
    Appreciate it!!

  3. Gonzalo Bissio says:

    hi!
    my name is Gonzalo Bissio I’m a DBA from Argentina, I attended today the meeting and I have a pair of questions:
    1) how do I know the size of the plan cached? can I modified it? what will be the problem if I have a big plan cached and the same query has many plans?
    2) when does sql server take the decision to reuse a plan for a query or create a new one?

    thanks!

    • Guy Glantser says:

      Hi Gonzalo,

      1) You can’t control the size of the plan cache directly. SQL Server calculates the size dynamically based on various characteristics, such as the version of SQL Server and the amount of physical RAM. If you have a single query with many plans, it’s most probably because each instance of the query uses different predicate values as literals. In this case, it is not considered the same query, and this is why each instance has a separate plan. If this is the case, then you should replace the literals with parameters, and then all instances of the query will share the same plan.

      2) Each time SQL Server needs to execute a query, it first searches for it in the plan cache. If there is already a plan for it in the plan cache, it needs to check that it’s still valid. If it’s valid, then it will execute it right away. If it doesn’t exist in the cache or it exists but not valid, then it will create a new plan. A plan can become invalid due to all kinds of reasons, such as outdated statistics, schema changes or memory pressure.

      I hope it helps…

  4. mike good says:

    just watched webinar. good job. thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *