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:

1. Reuse is almost always a good thing (not always though). Try to maximize it to reduce memory consumption and CPU usage.
2. Know the way your applications work and what it can do to your Plan Cache
3. Recompile does not only mean “give me a new plan”. Know its real meaning before using it.
4. You can get great insights about your system from querying the Plan Cache.
5. 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:

Kalen Delany - Microsoft® SQL Server® 2008 Internals

Kalen Delany - Recompile or Reuse? Making the Most of Plan Cache

Brent Ozar - Tuning SQL Server with the Plan Cache

Kendra Little -The Use and Abuse of RECOMPILE in SQL Server

Bob Beauchemin - Analysing Performance Trends and Peeking into the Plan Cache with Microsoft SQL Server 2008

Jason Strate - Can You Dig It? – Plan Cache Series