Your Data, Our Solutions
Professional Training – Academy
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.
Very usefull post!
Thank you very much for such nice session..
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?
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…
just watched webinar. good job. thank you!
Your email address will not be published. Required fields are marked *
Notify me of follow-up comments by email.
Notify me of new posts by email.