Two weeks ago I delivered a presentation called “Things You Can Find In The Plan Cache” to the israeli SQL Server user group. Among the subjects we talked about were:
Why the Plan Cache is needed
The right ways to work in order to increase plan reuse
Plan Cache Dynamic Management Views and Functions
Common application problems in relate to the Plan Cache
Plan Cache memory limits and age-out algorithm
Querying the Plan Cache to identify problematic queries
The real meaning of Recompile
Querying Execution Plan XML for interesting insights
Below is the presentation itself. You might have a slight problem understanding it if you don’t know Hebrew – I hope to deliver it online in English within a few months.
Meanwhile, you are welcome to review the slides and demos.
Here are my top 5 takeaways from the presentation:
Reuse is almost always a good thing (not always though). Try to maximize it to reduce memory consumption and CPU usage.
Know the way your applications work and what it can do to your Plan Cache
Recompile does not only mean “give me a new plan”. Know its real meaning before using it.
You can get great insights about your system from querying the Plan Cache.
You can get great insights from a different angle by querying Execution Plan XML (here’s an example).
In addition, here are some of the great resources I used during my preparations: