Skip to content

2019-10-24: Massive Save-To-Table Improvements
Compare
Choose a tag to compare
@BrentOzar BrentOzar released this 24 Oct 17:49
20191024
fd5e1fb
This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
GPG key ID: 4AEE18F83AFDEB23
Expired
Learn about vigilant mode.

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.

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:

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

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

Assets 2
Loading