Copy
Buckle up: time to learn more about SQL Server,
or whatever I'm obsessed with this week.

Updated First Responder Kit and Consultant Toolkit for February 2020

We spent Valentine’s Day removing the bugs from your chocolates. You’re welcome.

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month’s script updates, plus:

  • Improvement: the Databases tab now has all columns from sys.databases, making it easier to troubleshoot obscure stuff and new features.
  • Improvement: the Deadlocks tab now only has the victims, and we only save each deadlock graph as a file once (not once per query involved in the deadlock.)

sp_Blitz Changes

  • Improvement: removed spaces from documentation file names for easier publishing in an Azure DevOps Server wiki. (#2273, thanks PierreLetter.)
  • Improvement: added new priority 250 data size check. Only shows when @CheckServerInfo = 1. Gives you a quick idea of the size of the server you’re dealing with in terms of number of databases and total file size. (#2265)
  • Improvement: updated unsupported-build checks now that SQL Server 2014 SP2 is out of support. (#2293)

sp_BlitzCache Changes

  • Fix: plans will no longer trigger both the nearly-parallel warning if they’re already parallel. This would happen when you had a parallel query whose cost had dropped under the Cost Threshold for Parallelism. (#2234, thanks Erik Darling.)
  • Fix: “selects with writes” warning may fire incorrectly for stored procedures. (#2290, thanks Erik Darling.)

sp_BlitzIndex Changes

  • Improvement: suggested index names no longer start with IX_. (#2292, thanks Greg Dodd.)
  • Improvement: indexes with 0 reads and 0 writes are no longer shown by default in mode 0 and 4. Just lowering the distractions so you can focus on the indexes that are really causing problems. (#2280, thanks Erik Darling.)
  • Improvement: unused index warning now also shows the index’s number of writes. (#2266, thanks Erik Darling.)
  • Fix: some parts of the output were doubling the size of nvarchar columns, like telling you a column was nvarchar(80) when it was really nvarchar(40). (#2285)
  • Fix: now handles identity values even larger than BIGINTs, going all the way to DECIMAL(38,0). (#2268, thanks JBarnard84.)

sp_BlitzLock Changes

  • Improvement: new @VictimsOnly parameter. If you set it to 1, you only get the deadlock victims. Since all of the queries in a deadlock have the same deadlock graph, this can tighten up your result set by a lot if all you’re troubleshooting is the deadlock graph, which is what we’re focused on in the Consultant Toolkit. (#2219, thanks Erik Darling.)
  • Improvement: better support for identifying when heaps are deadlocked. (#2219, thanks Erik Darling.)
  • Fix: object names not always populating correctly. (#2282, thanks Erik Darling.)

sp_DatabaseRestore Changes

  • Improvement: removed dependency on Ola Hallengren’s maintenance scripts. Ola recently released a version with a new mandatory parameter for his CommandExecute stored proc, which broke sp_DatabaseRestore, and we weren’t depending on it for anything complex. (#2269, thanks mKarous for the bug report and Eric Straffen for the code.)

sp_ineachdb Changes

  • Improvement: added @exclude_pattern parameter so you can skip databases like the pattern. Accepts wildcards just like the SQL Server LIKE. (Pull #2272, thanks Aaron Bertrand.)

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.

This week's sponsor: Learn powerful ways to assess the performance of your queries. Join Kevin Kline for our upcoming #S1Webinar on “Intro to Query Tuning on #Microsoft #SQLServer.” Attend in North America on March 4th.


 

Agree? Disagree? Leave a comment.

 

Interesting Undocumented Tidbits in SQL Server 2019 CU2

SQL Server 2019 Cumulative Update 2 is out, and Microsoft snuck in a few things that they didn’t mention in the release notes.

Contained Availability Groups continue to get new investments. In the lead-up to SQL Server 2019’s release, we got a few presentations at various conferences where Microsoft folks said we’d get the ability to put system databases inside a Contained Availability Group. However, things got a little weird leading up to the release, and right now, the only official references I can find are dedicated to Big Data Clusters only. Microsoft published a KB article about some of the fixes in CU2 for contained AGs, but they’ve already pulled the KB articles from public view. Fortunately, Bing and Google still have it cached:

Hmm. I get a little nervous when Cumulative Update documentation pages get updated after their release. Either the CU has a fix for it, or the fix didn’t work, or Microsoft isn’t ready to talk about the feature yet.

Anyhoo, in CU2, there are a few new related CAG goodies:

  • sys.dm_exec_sessions has a new column for contained_availability_group_id
  • 2 new entries in sys.configurations:
    • 1593 – version high part of SQL Server – version high part of SQL Server that model database copied for
    • 1594 – version low part of SQL Server – version low part of SQL Server that model database copied for
  • New message 47147: Creating contained availability group ‘%.*ls’ failed. When creating contained availability group, neither master database nor msdb database can be included in the CREATE AVAILABILITY GROUP statement. They will be automatically included in the availability group. Remove master database name and msdb database name in CREATE AVAILABILITY GROUP statement and retry the operation.
  • New message 47148: Cannot join database ‘%.*ls’ to contained availability group ‘%.*ls’. Before joining other databases to contained availability group, contained availability group master database has to be joined and recovered. Make sure contained availability group master database has been joined and recovered, then retry the operation.

2 new undocumented DMVs: sys.dm_db_data_pool_nodes and sys.dm_db_storage_pool_nodes. Both are empty in my SQL Server 2019 testbeds, and I don’t know whether they refer to Big Data Clusters or something in the cloud.

1 Linux DMV now shows up in Windows: I’m not sure if this is intentional, but sys.dm_pal_net_stats is now shipping with the Windows version.

Some CU2 KB articles have been pulled. For example, there was a KB article about Accelerated Database Recovery silently corrupting data, but that KB article now returns a 404. Fortunately, Bing and Google have caches of it:

Again, I don’t know whether Microsoft pulling the KB article means that they haven’t fixed the bug after all, or that they’re embarrassed about the bug. Either way…this isn’t a great look.

This week's sponsor: Learn powerful ways to assess the performance of your queries. Join Kevin Kline for our upcoming #S1Webinar on “Intro to Query Tuning on #Microsoft #SQLServer.” Attend in North America on March 4th.


 

Agree? Disagree? Leave a comment.

 
sfs_icon_twitter.png
sfs_icon_forward.png
icon_feed.png
Copyright © 2020 Brent Ozar Unlimited®, All rights reserved.