When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers:
- Which tables it needs to join together
- What subqueries it needs to execute
- Whether it can reverse-engineer your intent to achieve the same results faster
- What indexes exist for the tables/views you’re trying to join
- If it can do partition elimination to make things go faster
- And much, much more
Much like you, SQL Server doesn’t like doing much work. SQL Server put a lot of work into building your execution plan (which may also be a work of art itself), and wants to avoid reinventing that wheel again, so it caches the execution plan in memory. If the same query comes in again, SQL Server can just check the cache, find your beautiful snowflake, and reuse the same plan.
This is the plan cache, and it stores more than just plans. We can get metrics about how many times the query was called and how much resources it used (min/max/avg/total).
It’s not perfect – there’s a lot of things that can cause the plan cache to flush completely or in part:
- Service restarts
- Database restores
- Statistics changing on an object
- Server comes under memory pressure
- People running DBCC FREEPROCCACHE
I still love the plan cache anyway. It’s not perfect (just like many of the things I love) but it’s a fast, easy-to-access way to discover some of the queries that have been using a lot of resources lately.
Making the Plan Cache Easier to Analyze
I’ve built up a set of queries to slice and dice my way through the plan cache, but I wanted to make it easier for people tackling their first performance tuning project.
At the PASS Summit this month, I unveiled the latest version of sp_Blitz®, which makes all this easier. I’ve added a few new parameters:
@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues. We’re looking for missing indexes, implicit conversions, user-defined functions, and more. This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.
@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed. You can do your own analysis on these queries too looking for more problems.
@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null. If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.) If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count). Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.
To learn more about how the plan cache works and how I analyze it, here’s a 30-minute video:
Or you can take a shortcut and just grab sp_Blitz® now. Enjoy!