Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DB NewsFeed

Matan Yungman is a SQL Server DBA at the Microsoft Protection Services operations team. He fell in love with T-SQL in 2003 and since then took part in various challenging projects, focusing mainly on performance tuning, database development, high availability and database design. He is passionate about SQL Server, databases and technology in general.

Things You Can Find In The Plan Cache

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:

  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

Comments

Leave a comment on the original post [www.dbnewsfeed.com, opens in a new window]

Loading comments...