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:
- 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:
- 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_DatabaseRestore should still open the BackupFiles cursor when @StopAt and @OnlyLogsAfter are specified
- Restore database from multiple backup paths
- Safer string aggregation
- Improvement: new check warning about files with a logical name of ‘master’ that can break SQL Server updates. (#2352, thanks Hermholtz.)
- Fix: updated copyright dates and fixed typo. (#2354, thanks Andy Mallon.)
- Fix: on DBCC checks, add more-info link pointing to https://www.BrentOzar.com/go/dbcc so folks can start learning about the default trace. (#2338)
- 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.)
- 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)
- 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.)
- 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:
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.