In preparation for this month’s new Fundamentals of Columnstore class, sp_BlitzIndex has a few cool new tricks! I’ll show you how to use ’em for free in the upcoming How I Use the First Responder Kit class, too.

Columnstore indexes are kinda like an index on every column (called a segment), and plus they’re kinda partitioned (called a row group). When you want to visualize how the data is stored, call sp_BlitzIndex just for that one table:

The last result set gives you a row per row group, plus columns for each column of the table, showing you what range of data is stored in each row group/segment combo. It helps you better understand what parts of the table SQL Server will be able to eliminate, and what parts it’ll still need to dig through. This also affects how we load data and how we manage index rebuilds.

Next, when you want to analyze the sizes of your columnstore dictionaries, we’re now breaking those out in the Index Size column. Columnstore indexes used to just show a total LOB size, but now it’s broken out between columnstore and dictionary data:

That “Index Size” column shows in @Modes 0, 2, 4, and table-level output. And hey, speaking of sizes, you’re probably curious about which indexes & tables are the largest ones in your database. sp_BlitzIndex @Mode = 2 now accepts a @SortOrder parameter. You can now sort by rows or size, plus more stuff too.

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

sp_Blitz Changes

• Improvement: new check for >= 35 databases being VSS snapshotted at the same time. (#2581)
• Improvement: if you’re outputting the results to database, the @OutputXMLasNVARCHAR parameter now lets you save XML columns of the table output as NVARCHAR. Useful if you’re using a single table to store results over linked servers and locally. (#2601, thanks David Prestridge.)
• Improvement: added warnings for non-default settings for SQL Server 2019’s new database scoped configuration options. (#2573, thanks mtndew01.)

sp_BlitzCache Changes

• Improvement: if you use the @SortOrder parameter, rows won’t be shown unless they actually have that problem. For example, if you used @SortOrder = ‘spills’, we were ordering by spills – but if not a single query plan in cache was having any spills, we were still sorting by that column, and showing plans where Spills = 0. This sounds trivial, but it’s actually a big deal for those of us who do a ton of plan cache analysis because now we can glance quickly at the output and go, “Your plan cache has been cleared since the spills problem happened, or the plan has aged out, or it’s using a recompile hint. (#2174, thanks Erik Darling.)

sp_BlitzFirst Changes

• Fix: CHECKDB running wasn’t always shown due to a cross apply (rather than outer apply) to query plan DMFs. (#2609, thanks Erik Darling.)

sp_BlitzIndex Changes

• Improvement: when you specify the @TableName parameter, there’s a new result set that visualizes the row groups and segments. (#2584)
• Improvement: new @SortOrder parameter lets you sort the inventory output from @Mode = 2 by rows, reserved mb, forwarded fetches, lock time, reads, writes, and more. (#2619)
• Improvement: the Index Size column now separates columnstore index size vs its dictionary size. (#2585)
• Improvement: the stats histogram output now includes the modification_counter. (#2612, thanks Erik Darling.)
• Fix: the stats histogram & columnstore visualizations weren’t working when sp_BlitzIndex was installed in a database other than master. (#2616, thanks Erik Darling.)

sp_BlitzLock Changes

• Improvement: added @ExportToExcel parameter like sp_BlitzCache has. When set to 1, we don’t show the XML results in the output, making it easy to copy/paste the results to Excel. (#2613, thanks Rebecca Lewis.)

sp_BlitzWho Changes

• Improvement: added @SortOrder parameter that accepts most of the column names, so you can sort by things like session_id or memory grant. (#2603, thanks Dylan Petrusma.)

sp_DatabaseRestore Changes

• Fix: if you set @RunRecovery = 0 and also pass in a @DatabaseOwner name, we ignore you and don’t bother trying to change the database owner. (#2598, thanks Greg Dodd.)

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.

Watch Us Work on the First Responder Kit

In this 12-minute video, Greg Dodd works on sp_DatabaseRestore:

In this 1-hour video, I work on the @SortOrder parameters for sp_BlitzWho and sp_BlitzIndex: