Let’s give a big round of virtual germ-free applause to Adrian Buckman, Erik Darling, Garry Bargsley, Greg Dodd, Ian Manton, John McCall, Kuldar Andares, Pierre Letter, Stephen Anslow, Tom Butler, and Walden Leverich for volunteering their time to help make your job easier this month. Thanks to them, the September release is chock full of features – I’m not even going to try to sum them up here, and you should read the release notes. Kudos to them!
- Download the updated FirstResponderKit.zip
- Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
- PowerShell users: run Install-DbaFirstResponderKit from dbatools
- 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
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 the /Resources/SQLServerSetup checklist folder over your existing files to replace the SQL scripts.
- Improvement: new check looks for Query Store problems where is_query_store_on reports 1, but sys.database_query_store_options doesn’t agree. (#2525, thanks Walden Leverich.)
- Improvement: more readable info in the drive free space checks. (#2557, thanks Pierre Letter.)
- Fix: sp_Blitz was failing on Amazon RDS SQL Servers because it was trying to run sp_validatelogins, which Amazon doesn’t allow. (#2526, thanks Erik Darling.)
- Now alerts if there’s even just 1 memory dump in the last year (as opposed to 5.) (#2564, thanks Garry Bargsley.)
- Improvement: @OutputServerName parameter lets you push the sp_BlitzCache results to a central server via linked server. The linked server will need to already exist and have RPC enabled. Linked servers don’t support writing via XML, so when writing out to a linked server, the query plan column is created as NVARCHAR(MAX) rather than XML. (#2528, thanks Pierre Letter.)
- Fix: “Many Duplicate Plans” check was reporting numbers higher than 100%. I didn’t do a great fix, but if anybody wants to dig into the real underlying problem, they can take the code from issue 2480 and apply it to this same problem. I’d be glad to take a pull request for that. (#2532, thanks Tom Butler.)
- Fix: removed unqualified join to get the Microsoft data migration wizard to stop complaining. (#2558, thanks Ian Manton.)
- Improvement: new warnings for queries running with memory grants > 15% of the buffer pool. You can configure that threshold with the @MemoryGrantThresholdPct parameter, too. (#2505, thanks Adrian Buckman.)
- Fix: the recently-updated-statistics check (ID 44) was being blocked by index rebuild operations due to sys.dm_db_stats_properties ignoring isolation level hints. (#2548, thanks Adrian Buckman.)
- Improvement: missing indexes now show both the data type for the column, and have commas in between the columns. (#2522, thanks Greg Dodd.)
- Improvement: if lock escalation has been disabled on a table, add text mentioning that. (#2553, thanks Stephen Anslow.)
- Fix: if Clippy came up with a missing index request more than 4,000 characters long, we were truncating it. Now, we preserve Clippy’s insanity in all its blazing glory. (#2533, thanks Kuldar Andares for the report and Greg Dodd for the fix.)
- Fix: suggested index names are now capped at 128 characters long. This can produce duplicate index names, but frankly, that’s the price you should pay for creating column names that long, buddy. (#2552, thanks Greg Dodd.)
- Fix: @Mode = 3 (missing indexes compilation) had a few extra diagnostic columns in the output left over from working on last month’s release. Removed those. (#2539)
- Improvement: when you use the @OutputTable parameters, sp_BlitzWho now automatically creates a deltas view like the other procs. The deltas view shows the last collection of each query on a session, so you see the total CPU/reads/time impact of each query. Not collected across multiple sessions – just helps you identify long-running queries that aren’t showing up in the plan cache, like stuff with option recompile hints or servers with a lot of memory pressure. (#2315, thanks Adrian Buckman.)
- Fix: when a database has RCSI turned on, some DMV (don’t ask me which one) isn’t always reporting all transactions are in RCSI. John McCall came up with a slick workaround to report the right isolation levels. (#2534, thanks John McCall.)
- Fix: if you pass in an empty string for the @RestoreDatabaseName parameter, we use the existing database name instead. (#2536, thanks Greg Dodd.)
- Improvement: when creating the SqlServerVersions table, we now also add extended properties for documentation. (#2574, thanks John McCall.)
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.
Watch Me Working On It