Announcing sp_BlitzCache™ v2.4

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.

Download it right now!

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.

Previous Post
What Changed Our Career Trajectories?
Next Post
SQL Server Query Store

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

    Reply
  • Thanks! A couple typos in the output messages:

    Long running queries have beend found. (beend)

    otherwise reduce the plan count/ (should be a period?)

    Reply
  • Randall Petty
    November 4, 2014 3:46 pm

    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’.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}