Updated First Responder Kit and Consultant Toolkit for February 2021

Along with the usual bug fixes & improvements, this month’s release includes a brand new sp_BlitzAnalysis script from Adrian Buckman. It’s for folks who have an Agent job to log sp_BlitzFirst to tables every 15 minutes, saving wait stats, Perfmon counters, file stats, etc into tables. sp_BlitzAnalysis reads those tables and sums up activity in your chosen time period, like this:

To install it, run the sp_BlitzAnalysis.sql file included in the First Responder Kit. It’s not a complex installation or anything – I just didn’t want to include it in the main installer scripts because this is the first public release, and I haven’t tested it myself yet either. (Been a little busy with the whole Iceland move.) To learn how to use sp_BlitzAnalysis, read the documentation. For questions or support on it, hop into 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.

How I Use the First Responder Kit
Watch and learn

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Fix: the check for the most recent CHECKDB dates was running even when @CheckUserDatabaseObjects = 0. This was the only instance where we were using sp_MSforeachdb with @CheckUserDatabaseObjects = 0, so it’d slow sp_Blitz down on instances with hundreds or thousands of databases. (#2779)
  • Fix: put comments around the term xp_cmdshell to fool a firewall. (#2788, thanks TLovie.)
  • Fix: changed typo of BrenOzar.com to BrentOzar.com on one check. (#2786, thanks Michael Gowan.)

sp_BlitzAnalysis Changes

  • All new! This is the first release that includes sp_BlitzAnalysis by Adrian Buckman. If you want to install this script, install sp_BlitzAnalysis.sql – it’s not included in the normal installation scripts yet just to limit the blast radius in case something goes wrong.
  • For tips on how to use it, check out the documentation.
  • For questions or problems, see the “For Support” section of this doc.

sp_BlitzCache Changes

  • Improvement: default output now includes PlanGenerationNum from sys.dm_exec_query_stats. Helps troubleshoot scenarios where the plan is frequently recompiling due to statistics changes. (#2792, thanks Tom Lovie.)
  • Fix: a recent release broke @SortOrder = ‘recent compilations’, and that’s fixed. (#2772)

sp_BlitzFirst Changes

  • Improvement: if more than 20% of the queries are runnable, waiting on CPU power to make progress, we warn you as shown above. sp_BlitzFirst makes two passes through to check – once at the beginning of the sample defined with @Seconds (defaults to 5), and again at the end. (#2776, thanks Erik Darling.)
  • Fix: if you passed in @OutputTableRetentionDays because you wanted to store more or less history in the output tables, that change wasn’t being passed to sp_BlitzWho, which was still just storing 3 days. (#2758, thanks Emanuele Mezzo.)
  • Fix: the high compilations/sec and recompilations/sec warnings had way too high of thresholds. They were looking for 1,000 batch requests per sec, but you can see high CPU usage due to compilations with even just 10 big ugly plans per second getting generated. Lowered the threshold to either 10 compilations/sec, or more compilations than batch requests per second – which happens when a proc has multiple statements with recompile hints in them. (#2770)

sp_BlitzIndex Changes

  • Improvement: duplicate indexes are now sorted by row count descending so that the big bang-for-the-buck stuff comes first. (#2762, thanks Todd Chittenden.)
  • Improvement: Aggressive Indexes warnings are now sorted by total lock wait time descending. (#2768)
  • Fix: a few releases back, I added support for SQL Server 2019’s new missing index DMV that tells you which queries are triggering the request. I had some challenges getting it to work reliably, and Microsoft just now documented it so I’m removing support for it temporarily. It’ll be back. If you want to help with coding for it, check out #2185. (#2780)

sp_AllNightLog Changes

  • Fix: the RPO and RTO now honor overrides set in the config table. (#2775, thanks Alin Selicean.)

sp_ineachdb Changes

  • Improvement: Azure SQL DB compatibility in the sense that it’ll run on Azure SQL DB, but it can’t actually change databases since Azure SQL DB doesn’t allow that. I’m referring to Azure SQL DB “Classic Flavor” here, not Managed Instances. (#2790)

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.

Previous Post
How to Trace a Nested Stored Procedure Without Using Profiler
Next Post
New SQL ConstantCare® Feature: One Serious Query to Tune

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.