Updated First Responder Kit and Consultant Toolkit for October 2019

This is a pretty doggone big release. There are a lot of improvements to sp_BlitzFirst, sp_BlitzCache, and sp_BlitzWho to prepare for a new release of the First Responder Kit Power BI Dashboard. If you’re using that today, I would strongly recommend updating to this new version of the FRK scripts – they fix a ton of bugs and start collecting data in a way that will make joins easier.

I wanted to ship the new Power BI Dashboard this month, but couldn’t quite get it working just the way I want to, so I’ll ship that one in next month’s release. Here’s a quick preview of the updated Query Details tab contents:

It has the new Power BI filters pane on the right – and yes, there’s a server filter, which means you’ll be able to centralize all of your data into a single repository, and navigate through it with easy filtering. On the left, I’ve got not only the cached plans from sp_BlitzCache, but also any live snapshots that were captured from sp_BlitzWho. Those show when the query is running, and its status along the way – really useful for those big long OPTION RECOMPILE queries that don’t show up in the plan cache, but you know they’re running and killing performance. Lots of good stuff coming there.

Breaking change: if you’re logging the data to tables, there’s a new JoinKey computed column added to all of the views, and to the BlitzCache, BlitzFirst, and BlitzWho tables to make Power BI Desktop joins easier. These columns will automatically be added by sp_BlitzFirst & friends when you upgrade, but if you’re doing any kind of ETL to put all of the Blitz% tables in one central location, you may need to add this column to your central location’s tables (and make sure you’re not doing SELECT * to ETL the data around.) For questions, feel free to hop into the #FirstResponderKit Slack channel. I’m out on vacation starting Sunday, but I’ll stick my head in there from time to time.

To get the new version:

• Download the updated FirstResponderKit.zip
• Azure Data Studio users with the First Responder Kit extension:
  ctrl/command+shift+p, First Responder Kit: Import.
• Download the updated Consultant Toolkit in your account
• EU customers: check your email for the updated version from Gumroad, our European distributor

Consultant Toolkit Changes

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

• Fix: the Plans CPU ByHash tab wasn’t populating if the server didn’t have sp_BlitzCache installed.

sp_Blitz Changes

• Fix: new @SkipBlockingChecks parameter defaults to 1. There’s a known bug in sys.dm_db_persisted_sku_features, which caused sp_Blitz to be blocked during columnstore index creation. I don’t expect Microsoft to fix that one quickly, so we just skip that check by default to be safe. (#2130)
• Fix: if you stored your database files on a UNC path (\\) and wrote your backups on one too, sp_Blitz no longer reports that you’re storing backups in the same place as your databases. (#2141)
• Fix: some heaps with 0 reads were reported as being actively used. (#2149, thanks Razvan Socol.)

sp_BlitzCache Changes

• Improvement: @SortOrder = ‘query hash’ now takes a second sort order in a comma-delimited list, like @SortOrder = ‘query hash, reads’ to find the queries with multiple cached plans, sorted by most reads. (#2156)
• Improvement: adds JoinKey computed column (ServerName + CheckDate) to output table to make Power BI Desktop joins easier. (#2162)
• Fix: when called by sp_BlitzFirst, the CheckDates in the output table now match the exact same dates/times as sp_BlitzFirst’s output tables (even though it’s not exactly when sp_BlitzCache is running.) This enables joins in the Power BI Dashboard since we’re filtering by dates/times. This used to work back when we only called sp_BlitzCache with a single sort order, based on your top wait type, but once I switched to @SortOrder = ‘all’, it stopped working. (#2159)
• Fix: when @SortOrder = ‘all’, @MinutesBack was being ignored. This is especially important for folks who use the Power BI Dashboard because with the default Agent job step, this would have resulted in way too much stuff showing up in the plan cache reports, instead of only showing things that executed in the last 15 minutes. (#2168, thanks Matt Rushton.)
• Fix: in @SortOrder = ‘all’ output, the plans with the most spills were being incorrectly shown in the ‘memory grant’ category. They were still queries you wanted to fix, but the reason WHY they were a problem was wrong. (#2170, thanks Matt Rushton.)

sp_BlitzFirst Changes

• Improvement: new @OutputTableNameBlitzWho logs sp_BlitzWho results to table. (#2159)
• Improvement: new @BlitzCacheSkipAnalysis parameter defaults to 1 to replicate existing behavior, but if you want your sp_BlitzCache output tables to have the full query analysis, you can turn this off in your Agent jobs. I’m not a fan – this makes the Agent job take >30 seconds on even my laptop – but if you want it, there it is. (#2155, thanks Matt Rushton for the idea. The idea is good though!)
• Improvement: BlitzFirst output table has 2 new columns: JoinKey computed column (ServerName + CheckDate) and QueryHash, plus added join keys to the output views to make Power BI Desktop joins easier. (#2162, #2164)
• Improvement: added SQL Server 2017 XTP Perfmon counters. (Yes, they actually hard-code the version numbers into the Perfmon counter name, and 2019 uses the 2017 names.) (#2162)
• Fix: if you passed on a @LogMessage with a @LogMessageCheckDate, we were, uh, ignoring it and just using the current time. (#2135, thanks Matt Rushton.)

sp_BlitzIndex Changes

• Fix: reducing collation errors. (#2126, thanks chaowlert.)
• Fix: more natural sorting for missing index recommendations, by impact. (#2143)

sp_BlitzLock Changes

• Fix: because the system health XE session stores deadlock times in UTC, @StartDate used to assume you knew that. We now assume @StartDate is the server’s local time, and we also convert the outputted deadlock dates from UTC to the server’s local time too. (#2147, thanks DigitalOhm for the idea.)

sp_BlitzQueryStore Changes

• Fixes: wasn’t running on Azure SQL DB, now does. (#2124 and #2136, thanks Paul McHenry.) Remember, though – our support policy on Azure SQL DB is pretty fast and loose since the DMVs change up there without warning.

sp_BlitzWho Changes

• Improvement: added a @CheckDateOverride parameter so this can be called by sp_BlitzFirst and write the exact same CheckDates in the output table. This enables joins in the Power BI Dashboard, which requires joins on exact field contents. (#2159)
• Improvement: adds JoinKey computed column (ServerName + CheckDate) to output table to make Power BI Desktop joins easier. (#2162)

sp_DatabaseRestore Changes

• Improvement: can now specify blocksize, buffercount, and maxtransfersize. (#2127, thanks Frederik Vanderhaegen.)

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 me!) 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.