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.
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* 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) SELECT query_hash 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 sp_Blitzcache:
|
1 |
EXEC sp_BlitzCache @OnlyQueryHashes = '0x6A26E5C5E3825653' ; |
Or, if you feel like ignoring that particular set of statements:
|
1 |
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 forcesp_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@export_to_excel parameter?
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!
14 Comments. Leave new
Is there a trick to tracking down a particular stored procedure? I ran sp_blitzcache expert, got the plan_handle, modified ran the code – and got no query hashes. Reran expert, got the same plan_handle again. 2008R2 SP2. Thanks.
Ah, that’s because the
@only_query_hashesparameter won’t work for a stored procedure’splan_handle. That’s a great feature to add, I’ll get that into the development queue.Okay, I’m feeling dense. I ran expert, got the plan cache for the SP I’m trying to track down.
Plugged it into your code
DECLARE @plan_handle AS varbinary(64) ;
SET @plan_handle =
[…]
But that didn’t return any hashes. Any idea? Thanks.
Well, I’m not sure what you could be running into – there’s no place in the code right now that will take
plan_handles into account, so there might be a decent amount of modification involved. If you head over to http://support.brentozar.com and submit a feature request, we can go back and forth more readily.I’m interested in monitoring the performance of a particular user-defined view. What’s the best way of doing this? It doesn’t seem possible to filter the results to a view, however I could pass @queryfilter=’statements’ and then search the results for the specific query text. Is there a better way?
Hello, I’m using and executing the sp as shown below:
exec dbo.sp_BlitzCache @ExpertMode =1
but getting an error as shown below when executing the sp:
Collecting execution plan information.
Msg 207, Level 16, State 1, Line 63
Invalid column name ‘min_spills’.
Msg 207, Level 16, State 1, Line 64
Invalid column name ‘max_spills’.
Msg 207, Level 16, State 1, Line 65
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 66
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 66
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 183
Invalid column name ‘min_spills’.
Msg 207, Level 16, State 1, Line 184
Invalid column name ‘max_spills’.
Msg 207, Level 16, State 1, Line 185
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 186
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 186
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 417
Invalid column name ‘min_spills’.
Msg 207, Level 16, State 1, Line 418
Invalid column name ‘max_spills’.
Msg 207, Level 16, State 1, Line 419
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 420
Invalid column name ‘total_spills’.
Msg 207, Level 16, State 1, Line 420
Invalid column name ‘total_spills’.
could be a bug ? or am I doing something wrong ?
Carlos – yep, it means you haven’t patched to SQL Server 2017 CU3 or newer. Time to patch – you’re on a version with bad known issues.
Hi Brent/team,
I’m currently using sp_BlitzCache as follows to log the stats for specific procedures that require close monitoring:
EXEC sp_BlitzCache @DatabaseName = ‘mydb’,
@StoredProcName = ‘spMySproc’,
@QueryFilter = ”Procedures”
However, I also need to monitor the performance of a small number of specific statements, or rather, specific VIEWS. I was doing it like this:
EXEC sp_BlitzCache @DatabaseName = @DatabaseName,
@QueryFilter = ”Statements”
…and dumping the results into a temp table. I would then query those results and add a filter e.g.
SELECT *
FROM #spBlitzCacheResults
WHERE QueryText LIKE @Filter
The is somewhat flawed, in that I will only get the stats I want if the VIEW happens to be in the results of spBlitzCache, which as you know, is limited based on the @Top and @SortOrder. The net result is that if my VIEW isn’t in the top 10 worst performing queries, it isn’t getting logged. I want to log it regardless of how bad it is.
I have a few options:
1. Pass in a ridiculously high @TOP value (yuk!!)
2. Re-write the VIEW as a stored procedure. Not really an option as I don’t have control over how it’s called
3. Pull the information direct from the DMVs (but lose all the sp_BlitzCache good-ness)
4. Ask Brent if there’s a nice way of doing this in sp_BlitzCache.
As you can guess, I’m going with Option 4, with Option 3 as the fall-back. Any advice?
Many thanks,
Shaun
Just realised I’ve already asked a much briefer version of this question above!
Hi Brent –
In an Azure SQL Database, why does [sp_BlitzCache] error without setting NUMERIC_ROUNDABORT OFF?
SET NUMERIC_ROUNDABORT OFF
EXEC [dbo].[sp_BlitzCache] @SortOrder = ‘CPU’;
If NUMERIC_ROUNDABORT is ON, the below occurs:
Msg 8115, Level 16, State 7, Procedure dba.sp_BlitzCache, Line 1246 [Batch Start Line 0]
Arithmetic overflow error converting numeric to data type numeric.
Msg 1934, Level 16, State 1, Line 3
INSERT failed because the following SET options have incorrect settings: ‘NUMERIC_ROUNDABORT’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Mike – check the readme for where to ask support questions. Thanks!
Please disregard my question from earlier today. I found the answer in the video from Brent.
@query_filter above should be @queryFilter
Is there a possibility to monitor Native Compile Stored Procedures with sp_BlitzCache?