github BrentOzarULTD/SQL-Server-First-Responder-Kit 20200913
2020-09-13: September to Remember

latest releases: 20240222, 20231222, 20231010...
3 years ago

Let's give a big round of virtual germ-free applause to Adrian BuckmanErik DarlingGarry BargsleyGreg DoddIan MantonJohn McCallKuldar AndaresPierre LetterStephen AnslowTom 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!

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 the /Resources/SQLServerSetup checklist folder over your existing files to replace the SQL scripts.

sp_Blitz Changes

  • 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.)

sp_BlitzCache Changes

  • 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.)

sp_BlitzFirst Changes

  • 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.)

sp_BlitzIndex Changes

  • 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)

sp_BlitzWho Changes

  • 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.)

sp_DatabaseRestore Changes

  • Fix: if you pass in an empty string for the @RestoreDatabaseName parameter, we use the existing database name instead. (#2536, thanks Greg Dodd.)

SqlServerVersions Changes

  • Improvement: when creating the SqlServerVersions table, we now also add extended properties for documentation. (#2574, thanks John McCall.)

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.

Don't miss a new SQL-Server-First-Responder-Kit release

NewReleases is sending notifications on new releases.