sp_BlitzCache®: Find Your Worst-Performing Queries
You have a SQL Server and you’re not sure which queries are causing your biggest performance problems. You don’t know where to start or if there are hidden queries that are making your main queries slower.
You want a fast tool to find the worst queries in the SQL Server plan cache, tell you why they’re bad, and even tell you what you can do about them.
sp_BlitzCache® provides the in-depth analysis that you’re looking for. Here’s how it works:
Download sp_BlitzCache and the First Responder Kit
If Microsoft supports it, so do we – unfortunately, that means SQL 2000 and 2005 are out of luck. We use a lot of cool DMVs and techniques that only work in current versions of SQL Server.
Common sp_BlitzCache Parameters
@SortOrder – find the worst queries sorted by reads, CPU, duration, executions, memory grant, or recent compilations. Just use sp_BlitzCache @SortOrder = ‘reads’ for example.
@Top – by default, we only look at the top 10 queries, but you can use a larger number here like @Top = 50. Just know that the more queries you analyze, the slower it goes.
@ExpertMode = 1 – turns on the more-detailed analysis of things like memory grants. (Some of this information is only available in current SP/CUs of SQL Server 2012/2014, and all 2016.)
@ExportToExcel = 1 – excludes result set columns that would make Excel blow chunks when you copy/paste the results into Excel, like the execution plans. Good for sharing the plan cache metrics with other folks on your team.
@Help = 1 – explains the rest of sp_BlitzCache’s parameters, plus the output columns as well.
If Bad Things Happen to Good Stored Procedures
If sp_BlitzCache doesn’t compile or if it throws an error when it runs, head on over to DBA.StackExchange.com and post a question. Include the version of sp_BlitzCache, your SQL Server version number (including the build #), and tag it with sp_BlitzCache.
If you’d like to suggest changes or contribute code, visit the Github repository for the First Responder Kit. You can get more instructions, file issues, and read how to contribute to the community.
More Tools for Slow SQL Servers
sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.
Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.
SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy process that gets to the root cause of your database health and performance pains.