Finding the worst queries in SQL Server is easy, right? You just query one of the
dm_exec_* DMVs, bring in the SQL text and the execution plan, and then you spend hours looking at execution plans trying to figure out what’s wrong in the query, much less how to fix it.
All that’s changing – we’re introducing sp_BlitzCache®.
Life Should Be Easy
We try to make things easy for you.
sp_BlitzIndex® make life easy – they find help you find configuration and indexing problems.
sp_BlitzFirst® helps you find problems that are hitting your SQL Server right now. We decided to take it one step further and apply our expertise to analyzing the plan cache.
There are a lot of queries that will help you get data out of the plan cache. A lot of those queries are also fairly complicated. They present a lot of data, but they don’t help you interpret the results.
Work Just Got Easier
sp_BlitzCache® analyzes the top poorly performing queries for many different problems. After finding your bad habits, sp_BlitzCache® builds a list for easy analysis. Your bad habits are outlined in a summary result set:
In addition, each of the queries in the top 50 worst performers will have their warnings called out in a separate warnings column:
This will not work on SQL Server 2005. If you want to analyze your plan cache on SQL Server 2005, you can use our legacy plan cache script (it’s in our download back of free tools – ‘sp_BlitzCache for SQL 2005.sql’)
There are a number of different options available with
sp_BlitzCache®. If you want a refresher, just run
EXEC sp_BlitzCache @GetHelp = 1. Otherwise, here’s a quick overview:
@GetHelp – Displays a summary of parameters and columns.
@Top – Determines the number of records to retrieve and analyze from
@SortOrder – CPU, reads, writes, etc. This controls collection and display.
@UseTriggersAnyway – Don’t set this unless you’re crazy. SQL Server 2008R2 and earlier has a bug where trigger count can’t be trusted. It seemed safer to ignore triggers on those versions of SQL Server than give you bad information.
@Results – There are three ways to display the detailed results grid. * Narrow will only display averages for CPU, reads, writes, and duration. * Simple, the default, displays totals and averages. * Expert displays all of the information collected by
sp_BlitzCache in addition to metrics normally displayed.
If you want to save the results to a table, you can use
@OutputTableName to persist the results to a table. If the table doesn’t exist, it’ll be created for you.