sp_BlitzCache is a fantastic tool for finding the worst queries in your SQL Server. Did you know that it has a number of advanced options available to you? These options can help you dig into your worst queries, slice and dice the data, and get back exactly what you need, without wading through hundreds of results.
Only/Ignore Query Hashes
Version 2.3 of
sp_BlitzCache introduced two parameters so you can ignore individual query hashes. Reader Brian Baker had the idea of filtering out a single stored procedure by supplying all of the query hashes for a stored procedure. There’s one problem – there’s no easy way to filter an entire procedure (well, not without making
sp_BlitzCache really complex).
Here’s how we’d go about answering Brian’s question:
Step 1: sp_BlitzCache in expert mode.
EXEC dbo.sp_BlitzCache @ExpertMode = 1;
Find the stored procedure that you want in the plan cache and then scroll all the way to the right until you get just past the
Query Plan column. There you’ll find the
Plan Handle. This lets us find a stored procedure. Copy it to your clipboard and then past it into the following script:
/* Step 1: Run sp_BlitzCache @ExpertMode = 1 */
EXEC dbo.sp_BlitzCache @ExpertMode = 1 ;
/* Step 2: Grab the plan_handle of the plan you want to examine. */
DECLARE @plan_handle AS varbinary(64) ;
SET @plan_handle = 0xEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE ;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
FROM ( SELECT n.value('@QueryHash', 'nvarchar(max)') AS query_hash
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
OUTER APPLY deqp.query_plan.nodes('//p:StmtSimple') AS q(n)
WHERE deps.plan_handle = @plan_handle
) AS x
WHERE query_hash IS NOT NULL ;
Alright, now that we have our query hash(es), you can plug them into
EXEC sp_BlitzCache @OnlyQueryHashes = '0x6A26E5C5E3825653' ;
Or, if you feel like ignoring that particular set of statements:
EXEC sp_BlitzCache @IgnoreQueryHashes = '0x6A26E5C5E3825653' ;
And, there you have it – how to zoom in on a single procedure, or completely ignore it.
Only Look at Queries or Procedures
This is much simpler, to narrow down the scope of
sp_BlitzCache, you can use the
@query_filter parameter. This parameter can be one of three values: procedures, statements, all. The default is all and, by default,
sp_BlitzCache will look at stored procedures, statements, and triggers. By specifying either “procedures” or “statements”, you can force
sp_BlitzCache to narrow down its scope.
This can be particularly helpful if you have encapsulated your business logic into stored procedures and you want to know where to focus your tuning, rather than worrying about individual statements.
Rapidly Re-Running sp_BlitzCache
Have you ever run
sp_BlitzCache and noticed that it takes a while on production systems? And then you realized, after you wandered off and got coffee, that you forgot to use the
I’ve done that, too.
sp_BlitzCache now ships with a
@reanalyze feature. By default,
sp_BlitzCache will analyze your plan cache fresh every time the query is executed. By using
@reanalyze = 1, you’re telling
sp_BlitzCache to skip collecting data and immediately report on the information that it already has. This is great when you want to export to excel, send the report to your co-workers, and then immediately get to work on the results. It also lets you re-sort the already collected data in a different ways.
There You Have It!
Three fancy features in
sp_BlitzCache that you can use to make your life easier and more fun.
What are you waiting for? Download it right now!