Here’s what we covered this week:

• 00:00 Start
• 01:37 DBAInAction: Hi Brent, could you please suggest an archival/purging strategy for multi-TB tables to free up disk space? Also, is there a recommended method for designing archiving/purging process for new databases before they become too large? Thank you!
• 03:47 MyTeaGotCold: I was surprised that Mastering Server Tuning never mentioned what you can do with files and filegroups in user databases. Do you find that they solve problems for you?
• 06:52 Forgetful: Do you ever recommend putting the tempdb log file on a different drive to the tempdb data files?
• 07:42 Adrian: Hi, would you enable tempdb ADR by default for new SQL 2025 servers? Any risks or negatives to consider, especially in combination with Availability Groups?
• 11:12 Dopinder: For SQL Server multi tenancy, one database per account seems optimal in limiting potential future corruption. However, doesn’t SQL AG tip over in the one database per account model due to AG database count limits?
• 13:40 FrugalShaun: What’s your go-to stat for measuring SQL Server throughput? When I want a quick comparison with past performance I check Batch Requests/sec, but that shows requests received, not necessarily work actually completed. What do you rely on instead?
• 15:02 Micen1: I have a customer running MDS on SQL 2019, they are looking to be able to run it on Azure SQL db but it seems that the MDS app still needs IIS on a vm? Do you the future of MDS or could you give your thoughts abouts its future and what to expect?

• sp_BlitzIndex adds an @AI parameter to get index consolidation advice for a single table.
• A breaking change in the AI config tables for sp_BlitzCache (and now, sp_BlitzIndex as well.) We used to have both the AI providers and prompts in the same table, but I needed to normalize that out into two tables now that we’re adding AI capabilities to more procs. If you’ve started playing around with sp_BlitzCache’s AI config table, run this script to migrate your configs to the new table structure before running the new version of sp_BlitzCache.
• Added a new SQL Server Setup Checklist markdown file in the Documentation folder. This replaces the old PDF doc that we used to distribute, but it hadn’t been updated in years. I did a quick pass with some basic updates, and folks are welcome to add their own guidance. Just create a Github issue first describing the kinds of changes you’d like to make so that you can have a community discussion about the guidance – I wanna make sure the right stuff ends up in there.
• There’s a new sp_kill stored procedure, driven by your design ideas. I’ll be blogging more about this in the coming weeks, but wanted to get the first draft out there for a client who needed it ASAP. You’re welcome to use it now, and log any issues you find with it.
• Starting with this release, we only support SQL Server 2016 SP2 & newer. Remember, even SQL Server 2016 drops out of support in 3 months, and 2014’s been out of support for 2 years. If you need to tune your Antiques Roadshow servers, use the scripts in the Deprecated folder. Why SP2 and not RTM or SP1? Because SP2 added a ton of DMVs and columns in existing DMVs.

To see how these work, read the documentation. In the coming weeks, I’ll be updating my (still free!) class, How I Use the First Responder Kit, with details on the AI calls and sp_kill.

To get the new version:

• Download the latest FirstResponderKit.zip
• Download the latest Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet, or import into a database
• PowerShell users: run Install-DbaFirstResponderKit from dbatools

sp_Blitz Changes

• Fix: wrap sp_Blitz, sp_BlitzIndex, sp_BlitzAnalysis, and sp_DatabaseRestore stored proc contents in a BEGIN and END to make it easier to automate updates to the Consultant Toolkit. (#3851)
• Fix: code modernization for SQL Server 2016 & newer. (#3872)

sp_BlitzCache Changes

• Enhancement: return oversized >128 node plans inline instead of asking the user to run a separate query. (#3868, thanks Erik Darling.)
• Enhancement: add per-database warnings for duplicate and single-use plans to help narrow down which apps are causing the problems. (#3878, thanks Erik Darling.)
• Fix: wrong parameter name in readme.md fixed. (#3861, thanks Ioan Podaru.)
• Fix: skip @AI = 1 calls when query plan is null, and allow @AI = 2 to build a prompt even when there’s no query plan, just a query text. (#3855 and #3854)
• Fix: invalid column reference in AI-generated code. (#3880, thanks shekarkola.)
• Fix: code modernization for SQL Server 2016 & newer. (#3870)

sp_BlitzIndex Changes

• Enhancement: AI advice available at the table detail level. When calling sp_BlitzIndex @TableName = ‘mytable’, you can use the @AI = 2 parameter to get an AI prompt for your favorite LLM, or @AI = 1 to call the LLM directly as we do with sp_BlitzCache. (#3827)
• Fix: code modernization for SQL Server 2016 & newer. (#3876)

sp_BlitzLock Changes

• Fix: corrected deadlock numbering errors. (#3859, thanks Vlad Drumea.)
• Fix: avoid duplicate rows in result sets. (#3765, thanks Tisit.)
• Fix: nicer error messages in Azure SQL DB that include instructions on creating an event session. (#3892)

sp_BlitzWho Changes

• Fix: code modernization for SQL Server 2016 & newer. (#3874)

sp_kill Changes

• First version! See the documentation for more details. (#3864)

Consultant Toolkit Changes

If you’re a consultant that does long term work, maintaining a client’s SQL Servers, then you probably want to track the health and performance of those servers over time. You want data – in a database.

We’ve got a new Loader app that watches a folder for incoming zip files, and when one shows up, it processes the data to load it into a SQL Server (or Azure SQL DB) repository for you.

This means you can set up the Consultant Toolkit at your clients on a scheduled daily task, upload the data to S3 (built in) or use your own file sync methods to get it to the location of your choosing, and then have the data automatically loaded into your database server for you.

To learn more about that, read the PDF documentation included with the Consultant Toolkit.

We’ve also added a new separate Consultant Toolkit download for SQL Server 2012-2014. Microsoft no longer supports those versions, so we’re not making changes to it anymore. It still works just the same as it always did – it just doesn’t have the cleaner code and additional diagnostics that we’re adding to the newer versions, starting this month.

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

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.

• Amy Abel – aka the Database Reliability Spotter and a senior consultant at Straight Path.
• Ásgeir Gunnarsson – consultant at Data Lab ApS, leader of the Icelandic PASS and Power BI groups.
• Kellyn Gorman – aka DBAKevlar, engineer and advocate at Redgate Software with experience across all kinds of vendors.
• Niko Neugebauer – aka NikoPort, associate director for data & analytics at Finsolutia, former Microsoftie.
• Tracy Boggiano – senior SQL developer at Abarca Health, longtime blogger and presenter, and very active in many communities.

I’m excited because the panelists have a diverse set of experiences. Across the group, they’ve seen the cloud from its infancy 20 years ago, to its current state, and their visibility across a lot of companies and industries means they’re better equipped to see what’s coming next.

I’ll be asking them questions like:

• What misunderstandings do executives or application teams still have about what cloud means for database performance and operations?
• Has cloud improved collaboration between dev, ops, and data teams, or just created new boundaries and blame patterns?
• What’s something that younger data professionals take for granted today that used to be a slow, painful process?
• If you had to point to one area where cloud delivered beyond expectations, what would it be? What about under-delivered?
• Looking ahead, which current cloud trends feel foundational, and which are just fashionable?
• What’s a belief about cloud that you held 10 years ago, and no longer believe?
• What should attendees be more skeptical about, and what should they be more optimistic about?
• What skill or mindset should a data professional build now if they still want to be effective 10 years from today?
• If you had to live with a system for 10 years, what choices would you make differently than if you were just trying to get it live in 3 months?

Wanna watch it unfold? Join us Thursday 10:00-10:50 in the Living Lounge. Here are calendar invite files for all of my sessions to make it easier:

• Wednesday 10:40-18:00: Dev-Prod Demon Hunters  – because this is an all-day workshop, you need to register for this separately. There’s space in it now because they moved the session to the auditorium, so if you wanna join and get a free Fundamentals + Mastering Bundle for a year, contact the SQLBits registration staff.
• Thursday 10-10:50: 20 Years of the Cloud Panel Discussion
• Friday 16:30-17:20: Watch Brent Tune a Query in SQL Server 2025
• Saturday 12:20-13:10: Pokémon Battle, Choose Your Index

See you in Newport!

They will offer Extended Security Updates that you can buy for 3 more years, either through Azure or your licensing partner. The price is stunning:

• Year 1, July 2026-2027: 75% of the original license price
• Year 2, July 2027-2028: 150% of the original license price
• Year 3, July 2028-2029: 300% of the original license price
• Total for 3 years of security updates: 525% of your original license price

I didn’t know numbers went that high.

This must be that “big data” I keep hearing about. In Microsoft’s credit, I understand they’re working on an ad campaign to raise awareness, and it’ll feature Roger Allen Wade’s classic song. I’m looking forward to those commercials.

If you’re SQL Server 2016 or prior, and you’re having difficulty getting management buy-in for version upgrades, and you’re at a large enough company where you have a security team, send this in an email to your security team:

Does our tech insurance, HIPAA/PCI/SOX compliance needs, or corporate policies require us to be on currently supported software, or is it okay if we’re relying on out-of-support software that no longer gets security updates?

This lets somebody else be the bad cop, and you can connect the security team with your managers & accountants to let them duke it out. If your company doesn’t have a dedicated security team, you can send that same question to your manager, but then you’re the bad cop. That’s not nearly as much fun, but you should still probably send the email just to have something in writing to cover your rear.

Ideally, the security team and/or managers recognize the risk involved in running your shop on Antiques Roadshow software, and you start picking out which version of SQL Server you should replace it with.

If your company’s security team and/or your manager reply in writing saying no, no problems, then you’ve at least done your duty. I will say, though, that if your company is willing to be on unsupported software, they’re probably doing it to save money – and that means they’re probably also cheap on stuff like salary, employee benefits, and training. Long term, you probably wanna start passively looking for work elsewhere before your skills get as out of date as your database server. (What a dark way to end a post, eh?)