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
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: