sp_BlitzCache®: Free SQL Server Plan Cache Analysis Script

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 it in our free First Responder Kit.

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.

How to learn more and get support

In our spare time, between ogling tasting menus, we build more stuff to help:

Don’t have time to learn the hard way? I’m here to help with a quick, easy process that gets to the root cause of your database health and performance pains. My SQL Critical Care® gets to the root of your SQL Server pains and teaches you how to make your databases faster and more reliable. Learn more, see sample reports, and book a free call with me, Brent.

sp_BlitzCache Blog Posts

sp_BlitzCache: Eventual Compatibility With Azure

Better than nothing Yesterday it was announced that global temp tables were available in public preview for Azure. That means that unmodified versions of sp_BlitzCache (which uses a global temp table to hold a whole bunch of information) will be compatible up there. This is a nice addition for Azure, and an even nicer addition…
Read More

Analyzing Temporary Stored Procedures with sp_BlitzCache

All the cool kids Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog’s “Slow in the Application, Fast in SSMS“, because it’s an excellent…
Read More

Learn to Use sp_Blitz, sp_BlitzCache, sp_BlitzFirst, and sp_BlitzIndex with These Tutorial Videos

There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use ’em: How to Use sp_Blitz for a Health Check How to Use sp_BlitzCache to Find Slow Queries How to Use…
Read More

Memory Grants Added to sp_BlitzCache and sp_BlitzFirst

Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…
Read More