[Video] Updated First Responder Kit and Consultant Toolkit for May 2020

We have quarantined all of the bugs. Many of the bugs. Okay, 7 of the bugs. Hey, we actually added more improvements than we fixed bugs this month! My favorite new things are that sp_BlitzIndex @Mode = 2 now makes it even easier to do disconnected index tuning just purely inside a spreadsheet, and sp_BlitzCache runs faster on big servers.

The “Deprecated” folder now has versions of sp_Blitz, sp_BlitzCache, and sp_BlitzIndex that work on SQL Server 2005. This isn’t new code: it just so happened that during one of my classes, a student asked for a 2005 version, and Oddvar Eikli mentioned that he happened to have all of ’em going back years. He graciously shared the versions that still worked with 2005, so I added ’em in. They aren’t updated by any means – they’re just here for historical purposes for folks who don’t have any other choice. Thanks, Oddvar!

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month’s script updates, plus:

  • The Indexes M2 tab adds a new Forwarded Fetches column. This is useful if you wanna sort by that descending to find the heaps that need to be rebuilt the most urgently.
  • The Indexes M2 tab’s Drop TSql and Create TSql columns now use fully qualified database names, making it even easier to copy/paste commands into a client prescription to tell them what indexes to drop, and give them quick undo scripts.

sp_DatabaseRestore: Fixes Awaiting Your Testing

Users have contributed 3 changes/improvements that need testing before we can get ’em into the dev branch. If you can test these by downloading the code and seeing if they work in your environment, please leave a note on the issue with your thoughts on it, and if a user or two agrees that they’re production-worthy, I’ll merge ’em in:

sp_Blitz Changes

sp_BlitzCache Changes

  • Improvement: when a plan isn’t found in cache, include a link to this DBA.StackExchange.com question so folks understand what could have caused it. (#2347, thanks Ajay Patel.)
  • Improvement: faster memory analysis using sys.dm_os_memory_clerks instead of sys.dm_os_buffer_descriptors. (#2345, thanks Daniel Mrowiec.)
  • Improvement: default output now adds the Remove Plan Handle from Cache column that used to require @ExpertMode = 1. Makes it easier to teach how to fix parameter sniffing issues. (#2346)
  • Fix: allows installation on Azure SQL Managed Instances. Remember, though, we don’t support anything in Azure SQL DB or Managed Instances just because MS can/has changed the DMV contents w/o warning and w/o documentation. If it works, great, if it doesn’t, getting compatibility is left as an exercise for the reader. (#2353, thanks agentKnipe and Erik Darling.)

sp_BlitzFirst Changes

  • Improvement: @OutputType = ‘Top10’ now outputs the top 10 wait types since startup in a screenshot-friendly way, so when I’m doing Twitch streams analyzing your wait stats, I don’t have to give you as many instructions. (#2342, thanks Nick Kirby.)
  • Fix: if the SQL Server was renamed without doing it right, the global variable @@SERVERNAME wouldn’t be correct, so sp_BlitzFirst wasn’t deleting history in the sp_BlitzCache table for this server. We now use SERVERPROPERTY(‘ServerName’) instead. (#2320, thanks Sixten Otto and Adrian Buckman.)
  • Fix: wait stats “per core per hour” number was rounding down to small due to extra division, and it wasn’t Joy Division. (#2336)
  • Fix: looks like fileproperty() was showing up as DBCC in sys.dm_exec_requests, causing a false positive that CHECKDB was running. Not too sure about this one since I can’t test it easily, but if you find an issue with this, holla at ya boy by creating a new issue with what you’ve discovered. (#2350)

sp_BlitzIndex Changes

  • Improvement: @Mode = 2 now includes a column with Forwarded Fetches. The output to table doesn’t, though, because we’re not currently doing change detection to see if inserting a new column would break the table. (#2314)
  • Improvement: @Mode = 2 now includes fully qualified database names for the drop & create T-SQL so that you can do easier index tuning with the Consultant Toolkit, fully disconnected, and send clients recommendations that they can execute faster. (#2357)
  • Improvement: Obsessive Constraintive: Serial Forcer warning about UDFs now points to an explanation page. (#2340, thanks C.Hambrick.)
  • Fix: now handles identity seed values bigger than bigint, like DECIMAL(38,0). (#2268, thanks jbarnard84.)

sp_BlitzWho Changes

  • Improvement: for Agent jobs, the Program Name column now shows the Agent job name. (#2328, thanks Jerry Hung.)

Live Stream from the Coding Session

Bored? Want to make fun of my code? Here’s the recording of the 3-hour live stream when I worked on a few of this month’s fixes:

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

Previous Post
No, You Can’t Calculate the Tipping Point with Simple Percentages.
Next Post
Free Upcoming Live Classes

3 Comments. Leave new

  • Thanks for the replay of the stream for those of us who missed the original.

    That forward fetches column will be nice. I had been running a separate query for that.

  • Brent, I tried to install ‘Install-All-Scripts.sql’ and received an error. I found this block of code in the sp_Blitz section of the script at line 8677:
    FROM master.sys.master_files f
    WHERE (f.name = N’master’)

    I removed the ‘Â’ char references and was able to install.


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.