Plan Cache Spelunking

Ed Pollack digs into the plan cache:

The data in the plan cache is not static, and will change over time. Execution plans, along with their associated query and resource metrics will remain in memory for as long as they are deemed relevant. Plans can be removed from cache when there is memory pressure, when they age out (they get stale), or when a new plan is created, rendering the old one obsolete. Keep this in mind as we are looking around: The info we find in the plan cache is transient and indicative of a server’s current and recent activity and does not reflect a long-term history. As a result, be sure to do thorough research on the plan cache prior to making any significant decisions based on that data.

The plan cache is one of the best ways of figuring out what’s going on in your SQL Server instances, but there’s a little bit of complexity to it.

Related Posts

Risks Of Using Resource Governor To Set Max DOP

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance: I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get […]

Read More

When Table Variables Have Realistic Estimates, Unrealistic Results May Occur

Milos Radivojevic wraps up a series on deferred compilation for table variables by looking at a hack which used to work but no longer does: With this change, the query is executed very fast, with the appropriate execution plan: SQL Server Execution Times: CPU time = 31 ms,  elapsed time = 197 ms. However, the LOOP hint […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29