Here’s what we covered:

• 00:00 Start
• 02:17 Janis: Hey Brent, Deleted million of rows from Azure SQL clustered columnstore ix. Want reclaim free space in shared elastic pool without long table blocking, but SHRINKDB and usp_IncrementalShrink does not help much. Help 🙂
• 03:35 Used-to-be-DBA: When you began your career, what were the IT job functions or titles that people were worried about disappearing? Why don’t people take the mindset of “always be learning’? I work with data, but I expect my actual job functions and title to change throughout my career.
• 03:47 Used-to-be-DBA: When you began your career, what were the IT job functions or titles that people were worried about disappearing? Why don’t people take the mindset of “always be learning’? I work with data, but I expect my actual job functions and title to change throughout my career.
• 06:21 Not 2019: Have you had any bad experiences with SQL Server 2022’s adaptive memory grants? I’ve twice needed to use emergency MAX_GRANT_PERCENT hints because a query that needs megabytes of memory started taking a quarter of my RAM.
• 07:37 ContestWinner: Hey Brent, when you give away bundles in your contests, is that yearly or lifetime access?
• 07:54 johnkurt: first thanks for helping us. we see some cmemthreads and we have already ctfp=50 and maxdop=8. A microsoft page suggest maxdop up to 16 in our case. What is yours experience with maxdop greater than 8 ?
• 10:13 Used-to-be-DBA: Hey Brent, LLMs are not deterministic. If users are using natural language to ask AI questions about data, then does that mean the query results are not deterministic? If I ask for month over month earnings, then will I always receive the same query result?
• 11:36 Blommetje: Do you ever tinker with things like Arduino, ESP32, HomeAssistent or iot things? To monitor pool temperature or toggle the driveway lights.
• 14:51 gserdijn: Regarding procedure sp_spaceused. Is there anything useful to say about the value of the ratio total_index_size divided by total_data_size? Any heuristics whether a database might be overindexed or underindexed? Might be only useful as a trend metric.

I said, “Whenever I’m working with a client, I have to gather the same data over and over. I run scripts like sp_Blitz, sp_BlitzIndex, sp_BlitzFirst, and so on, copy/pasting that data into a spreadsheet for later review. Then I run sp_BlitzCache a bunch of different ways to get their most resource-intensive queries, and I have to save the query plans off one by one for later review. It’s a lot of manual labor, and that costs money. Can you build something to gather all that data automatically, put it into Excel, and save the query plans as separate files, named by the problems with them, like high CPU or high reads?”

Richie: “Yeah sure.”

That app saved us so much time that we were able to lop an entire day off my 3-day 2-day SQL Critical Care engagement, which cut costs for the client, and let us focus on the fun parts of the work instead of the drudgery.

It worked so well that whenever prospective clients would contact us even thinking about bringing us in to help, I would say, “Here’s a utility to gather a bunch of diagnostic data. Send me that, and I’ll review it for free to see whether or not I’m a good fit. Very often, I’d be able to rip through the data in minutes and say, “Oh actually, all you need to do is ___ – try that first, and that may solve the problem altogether. Let me know either way.” I didn’t have to get connected to the server at all.

I ended up giving up on the idea of trying to build a big SQL Server consulting company, so I started selling the Consultant Toolkit to other consultants and contractors. I ran a few tutorial webcasts showing folks how it worked, and consultants bought in. Today, over 100 consulting and contracting companies around the world rely on The Consultant Toolkit to deliver their services.

Over time, DBAs started buying it, too.

At first, I started replying to their purchase emails saying, “Whoa, hang on, I don’t think you really wanna buy that. That’s for consultants.”

To my surprise, they did indeed wanna buy it, and they had a few interesting usage scenarios that I hadn’t predicted:

• DBAs who worked for giant global companies, and were often called into different teams or departments to quickly troubleshoot a server
• Customer support engineers at software vendors who would get calls from clients saying there was a problem with the app, and the customer support wanted to check on the health of the SQL Server where the data was stored
• DBAs who just love the First Responder Kit, and use it for performance troubleshooting, and wanna have a way to get all of the data out quickly

So for y’all, here’s how it works.

When you download the Consultant Toolkit, it’s a zip file with the app ready to go. You don’t have to install it or go through any kind of setup process. It’s a command line app. To gather diagnostic data from MyServer using your Windows auth login, go to a command line prompt and run:

ConsultantToolkit.exe --datasource MyServerName

There are also parameters for SQL authentication, Azure SQL DB, and Entra ID multi-factor authentication, and those are covered in the documentation.

By default, it gathers a minimal set of data within a couple of minutes. If you’d like much more data, you can add the –deepdive parameter, and it’ll gather more on the plan cache, indexes, etc.

It packages the script outputs into an Excel file with one tab for each set of results, like sp_Blitz, sp_BlitzIndex’s different parameters, sp_BlitzCache with several different sorts (top queries by CPU, by reads, by execution, by duration, etc), sp_BlitzLock, plus other diagnostic scripts that I find helpful.

In addition to the Excel file, it also outputs:

• Each query plan from sp_BlitzCache – for example, the top 1 by CPU will be saved as TopQueryBy_CPU_1.sqlplan (and the full top 10 are saved)
• The queries running right now are saved as QueriesRunningNow_1.sqlplan (and they’re all saved, which is useful if you’re troubleshooting a live blocking issue)
• The deadlock graphs from sp_BlitzLock are stored as “Deadlock #1, Query #1 – VICTIM.xdl”
• On SQL Server 2019 and higher, queries that triggered missing index recommendations are saved as MissingIndex_1_dbname_schemaname_tablename.sqlplan

When it’s done, you get a single zip file with the server’s name and date, and instructions to send that zip file back. It’s designed to be easy enough for a non-technical person to run, but the output is powerful enough for you to be able to dig into really complex issues in a matter of minutes.

If that sounds useful, check it out.

It’s on sale for just $349 during our Anniversary Sale, and I believe it’ll pay for itself within a week. You’ll do your work faster and more thoroughly, with less time involved from everybody else in the process. I’ll guarantee it with a 14-day, no-questions-asked money-back guarantee. If it doesn’t pay for itself, email me at Help@BrentOzar.com and I’ll give you your money back, period. Enjoy!

For years, I’ve pointed folks to the SQL Server 2008 documentation on Forced Parameterization, a really useful tool for reducing plan cache bloat, getting more accurate reusable query plans, and enabling SQL Server 2019 and 2022’s Intelligent Query Plan features, many of which rely on the same query text coming in repeatedly over time in order to tune it. That documentation was pretty sparse, though.

Good news! There’s a new set of documentation on Forced Parameterization, and it goes into more details about:

• What kinds of literals are converted into parameters
• The exceptions: things you would expect to get converted, but won’t be, and will stop the whole statement from getting parameterized at all
• The partial-parameterization exceptions: things that won’t get converted, but other stuff in the query still will, leading the plan cache to continue to be a hot mess and stopping the IQP features from working on those queries

There’s also a foreboding warning:

At first blush, that sounds completely reasonable, but it carries a hidden connotation. If you’re using Query Store, and you expect the forced query to remain the same from patch to patch or version to version, it might not. I’ve never actually seen these things change, but Microsoft is saying they could, which is just another heads-up that forced query plans may not be forced anymore after a change like that. This might have even always been in the old docs – I just never noticed it before. Still, it seems reasonable to me. I’d love to see more investments in Forced Parameterization.

Also, technically, we don’t have Service Packs anymore, so I was a good community member and submitted a pull request to change that wording to Cumulative Updates. As a reminder, when you see a typo or outdated language in the docs, you can click the little three-dot icon at the top right, then click Edit, and you’ll be shoved over to Github to edit there and submit a pull request:

If you’ve never used Github before, the process is intimidating at first, but once you get used to it, it’s a piece of cake and you can submit edits in less than 30 seconds.