Welcome to sp_BlitzCache™ v2.4. This release brings a few changes and bug fixes.
- Fixed a logical error in detecting the output table. Thanks to Michael Bluett for pointing that out.
- Sorting by executions per second finally works. Thanks to Andrew Notarian and Calvin Jones for submitting this week.
- Added a
@query_filter
parameter – this allows you to only look at “statements” or “procedures” in the plan cache. - A check was added to identify trivial execution plans that have been cached. If you’re seeing a lot of these, you need to fix it.
- The
@reanalyze
parameter was added. When set to 0,sp_BlitzCache™
will pull fresh data from the plan cache. When set to 1, though,sp_BlitzCache™
will re-read the results temporary table. This is helpful if you want to save off results in Excel and display results so you can tune queries. - Added the ability to see a query’s SET options. This is hidden just to the right of the plan in the results grid.
- Moved the
#procs
temp table to a global temp table named##procs
. This shouldn’t be a problem because you’d probably get angry if two DBAs were running this stored procedure at the same time any way.
Update: Denis Gobo noticed that sp_BlitzCache™ could potential clobber global temp tables. Global temp table names have been updated in sp_BlitzCache™ to avoid this in the future. Make sure you’re using v2.4.1.
7 Comments. Leave new
One suggestion and this happened to me as well, when using global temp tables make them specific to the proc that you are in..instead of ##procs make it ##BlitzCacheProcs… you just never know if someone else decides to name a global temp table ##procs inside another proc or just testing something in SSMS and this then could create a problem
Good call. I’ll push out a hotfix in about an hour.
Thanks! A couple typos in the output messages:
Long running queries have beend found. (beend)
otherwise reduce the plan count/ (should be a period?)
Looks like line 1640 has a trailing comma that doesn’t belong. Removed that and it ran ok.
exec [dbo].[sp_BlitzCache] @export_to_excel =1
COALESCE(SetOptions, ””) AS [SET Options],
FROM ##bou_BlitzCacheProcs
Setting up temporary tables for sp_BlitzCache
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Adding SQL to collect trigger stats.
Collecting execution plan information.
Computing CPU, duration, read, and write metrics
Warning: Null value is eliminated by an aggregate or other SET operation.
Checking for query level SQL Server issues.
Scanning individual plan nodes for query issues.
Checking for plan compilation timeouts.
Checking for forced parameterization and cursors.
Populating Warnings column
Displaying results with Excel formatting (no plans).
Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword ‘FROM’.
Nope, that’s just your punishment for using Excel.
I’ll fix it, though, since some of you seem to use it. 😉
Thanks. Nothing like nit-picking what took somebody a long time to develop. It is greatly appreciated.
Pfft, it’s not nitpicking, you’re making it better.