How to Tune Queries Fast

Added: 2026-05-27

[00:00:00]Good morning party people and welcome to spring training, SQL Server spring training week.

[00:00:07]>> [snorts] >> Today we're going to be talking about how to tune queries fast or how to tune queries quickly, depending on how you want to look at it. I have to do this all the time. I'm Brent Ozar. I do a lot of emergency turnaround work for Microsoft SQL Server, Azure SQL DB, Amazon RDS. I got my start as a developer back in the late 1990s and I have so much respect for developers.

[00:00:32]It's a truly hard job. And whenever people are shipping brand new applications, I know a lot of database administrators out there are like, "You should ship perfectly tuned SQL. You should craft everything by hand and put it into stored procedures."

[00:00:47]But the reality of when we're shipping code is that we don't know what queries users are going to actually end up running. We may put in all kinds of screens in our application.

[00:00:56]Half of them the people never actually go into.

[00:00:59]So it's hard to predict in advance what we're going to have to tune and you can waste a lot of time performance tuning stuff. I would rather people just ship things that are going to work and compile and then we'll come back and tune that stuff later over time. By we, I mean a lot of you as well as me.

[00:01:16]Over in chat, tell me a little bit about yourself. Tell me what part of the world that you're in, what your job title is and how many years you've been working with databases. So where you're at, what you do and how many years you've been working with it. Now let's go take over to look over here and see what y'all have been putting in.

[00:01:36]>> [snorts] >> Richie says the best reindeer pizza I've ever had was in Finland. I've actually been to to have reindeer pizza all over the place, Denmark.

[00:01:47]I like a sexy orange is from the Pacific Northwest is a database administrator for 13 years. Rachel, a database engineer in Wisconsin, 12 years. Joseph, a senior developer for 5 years.

[00:02:02]You know, it's funny. Um when you work with databases, you might have a decade worth of experience and easily still feel like you're learning.

[00:02:11]Uh Itzik Ben-Gan was this brilliant T-SQL coder and tuner uh who's been who was active in our industry for like 30 years. He has since gone on and retired. I love keeping up with him on Facebook. And >> [snorts] >> Itzik hasn't put out a new book in several years, and yet still whenever I read Itzik's books, I've been doing T-SQL for like 25 years, but whenever I read one of his books again, and I still reread them from time to time, I still learn something every time I read his books.

[00:02:40]Database languages really haven't changed that much in the last couple of decades. Small additional techniques have come out, but it's amazing how powerful T-SQL can be just by itself and how long you can spend learning. It's absolutely bananas.

[00:02:54]So, in during today's class, I am I move forward says Itzik's books are on my shelf. They're on my iPad. I use them all the time.

[00:03:02]Um so, in today's class, I'm going to be using for the demo SQL Server 2025 in 2025 compat level. I just always want to show you that the things that you're learning here um [snorts] have not been fixed for you automatically inside the database engine. We still struggle with some basic concepts trying to get things to work in T-SQL.

[00:03:21]Using a SQL Server with 16 cores, 96 gigs of RAM and local solid state so that I can run queries and show you the before and after.

[00:03:28]Management Studio 22.5.

[00:03:31]Actually, that one might be 22.6. That just came out a day or two ago, and I believe I've upgraded that one.

[00:03:37]Um I'm using the largest version of the Stack Overflow database available right now, about 200 gigs in size, and it's open source, and you can get more information about that there.

[00:03:47]Today, we're going to be discussing the first place that you go and do when you're going to do performance tuning on a SQL Server, you're going to go check its wait stats to understand what kind of queries you want to tune. Cuz after all, you're always going to have a 10 worst queries. You're going to have [snorts] 10 worst by CPU, 10 worst by reads, 10 worst by rights, and so forth.

[00:04:08]But which one should you tune? You have thousands of queries running on the server, and you only want to tune the ones that are actually going to make a significant difference on the server. That's where we're going to start with wait stats.

[00:04:20]Then, after we know what our top wait type is, we're going to use SP_BlitzCache to slice and dice the data based on whatever our top wait type is.

[00:04:28]Once we've found the queries that we want to tune, I'm going to give you some general query tuning advice. Obviously, I can't teach you everything about query tuning the span of an hour-long webcast, but I'm going to teach you the places where I would go to look first when I'm tuning a query.

[00:04:43]Then, we're going to look at how you can use SP_BlitzCache to gather some of this data in with AI and send that into the chat provider of your choice, ChatGPT, Claude, Gemini, whatever, Copilot, and see if it can make a difference inside those queries. Then, I'm going to show you how to gather the data even faster with the Consultant Toolkit.

[00:05:03]So, let's start out with the first thing that we're going to discuss. Let's figure out whether or not our SQL Server even needs query tuning and the kinds of queries that we need to tune.

[00:05:14]The first place we're going to start is sys.dm_os_wait_stats.

[00:05:17]This is where SQL Server tracks how long it's been waiting for stuff, and I need to tune my server so that it spends less time waiting on things.

[00:05:28]This DMV is core for every monitoring tool out there. Every monitoring tool out there that shows you any kind of column chart uh that shows how heavily loaded your SQL Server is, it's always grabbing data from this DMV.

[00:05:44]Now, the problem with this DMV is that it starts at zero and just tracks up cumulatively over time. So, if you query it at any given time, it'll say that you've waited an hour on storage, 10 hours on locks, and so forth, but you don't know when that was. You don't know if it was Monday at 9:00 a.m., if it was Friday overnight while you're doing your backups.

[00:06:06]In a perfect world, you would trend this data over time, and you'd look at your wait stats to see what the biggest wait types were during your the hours that you care about, like when your end users are running queries.

[00:06:19]When you're just [snorts] walking in cold to a SQL Server for the first time, as I'm going to assume that a lot of y'all are, I want to be able to just look at what the wait servers wait types have been since startup. And the tool that I use to do that is SP_BlitzFirst since startup equals 1.

[00:06:36]SP_BlitzFirst is part of our open source First Responder Kit. It is completely free. You download it, you can install it in the master database. If you're using Azure SQL DB, this is this is completely compliant as well. You can put it in your user database if you like there.

[00:06:52]And this will show you how long your SQL Server's been up or your Azure SQL DB, and what it's been waiting on in that time. Let's go hop over into my lab and go do it, and I'm going to show you how I diagnose that data.

[00:07:07]So, here I've got SQL Server Management Studio. Switch over to it here.

[00:07:12]And I'm going to start by saying SP_BlitzFirst since startup equals 1.

[00:07:19]And you'll notice that it runs pretty much instantaneously, cuz it's not like it has to go track anything over time.

[00:07:25]All it's doing is asking SQL Server, "Hey, what have you been waiting on since you started up?" So, it's totally safe to run in production. It'll run nearly instantaneously.

[00:07:35]And in here, I'm going to focus on the first section inside here. In the first section inside here, it says that here's when the data was gathered.

[00:07:45]It's got the sample ended time.

[00:07:47]And then the [snorts] hours of sample hours of sample is how long this SQL Server or Azure SQL DB has been up.

[00:07:57]In a perfect world, I want at least a few days worth of up time before I make any kinds of management decisions. Uh because [snorts] if I just uh sample this data on a Monday morning, for example, uh and the server [snorts] was restarted over the weekend, well, all I've got is the weekend loads, which probably weren't much of anything, or they were stuff like backups and corruption checking. I want to have at least a few days worth of sample in sample time inside here.

[00:08:28]But, I also probably don't want to have more than like 60 days.

[00:08:32]If I have thousands of hours of up time, then maybe what I'm looking at here is blocking problems that happened 6 months ago on the last version of our application. Maybe I'm looking at problems that were happening when we were doing a big data import or trying to restore stuff from backups and so forth.

[00:08:53]Um uh Sirly Dev says, "I want to install SP_BlitzFirst and the other scripts on the database at my new job. Am I likely to keep that job if I do?" Frankly, Sirly Dev, you are unlikely to keep the job regardless of what you have done at all.

[00:09:06]Where's my button for laughter? There it is.

[00:09:08]>> [laughter] >> There you go. Get my little laugh track inside here for my studio audience.

[00:09:14]So, I want at least a a couple we a week, 2 weeks worth of up time, but probably not thousands of hours worth of time. If you got thousands of hours worth of time in here, I don't put too much faith inside this data. It's probably time for you to patch that SQL Server.

[00:09:27]The second column, thread time, well, the second column that I'm holding my hand over here. Thread time is how much time the SQL Server has spent running queries.

[00:09:39]And it's normal to see this higher than uptime because your SQL Server can run multiple queries at the same time across multiple cores at the same time. These numbers include stuff like parallelism.

[00:09:55]So, the higher these numbers are, the more of a workout your SQL Server's getting. Then, this [clears throat] over here is how much time out [snorts] of that total spent running queries, how much time have we spent waiting on stuff? And what was that stuff?

[00:10:16]In this example here, my I've run over 100 hours worth of queries on the server. Out of that, you can see like 50, 60, 70 hours worth of the time was actually spent waiting.

[00:10:29]Generally speaking, you want to work from the top down. Generally speaking, you want to work on the top wait types.

[00:10:37]And whatever the top wait types are, if I can cut out the majority of time that we're spending waiting, that's what my users are going to notice most.

[00:10:47]Now, I do have to drop a little bit of a hint here that in as you get deeper into performance tuning, you will find out that there are some wait types, for example, thread pool, that SQL Server doesn't track very well. And the numbers over here may not be entirely accurate.

[00:11:02]So, there are some wait types that we call poison waits, where if you see them at all in your result sets, then you probably need to focus on those first.

[00:11:12]We talk about those in some of our other training classes. But inside a query tuning training class, I'm going to tell you to start from the top and work bottom.

[00:11:21]You can also go run this now on your production server. And I say the reason why you might want to run it on your production server it's going to be worthless over in a a environment cuz development doesn't see the same workloads that you see over in production. For example, blocking.

[00:11:37]You may be having all kinds of blocking problems in production, but you won't see those in development cuz you're the only person hammering the bejesus out of that poor SQL Server.

[00:11:48]So, [snorts] in production, you want to work from the top down on whatever your top wait types are. And if you know what your top wait types are, then the next few minutes of the webcast will be useful for you, cuz I'm going to tell you what we're going to do once we have this information.

[00:12:04]Now, there are all kinds of wait types that you'll see out there. There are hundreds of them. I'm only going [snorts] to cover a handful of them inside this webcast, but if I don't cover yours, don't despair. Slide over to the far right-hand side of the SP_BlitzFirst results, and there are links over to SQLSkills wait type library, where it defines every known wait type. You can go copy-paste that, read that into your browser. Anytime that I give you any kind of health check report, I always take great pains to give you a URL for anything in the result set, so that you can see more information about it if you don't have it handy. And I always try to make it a free result, uh just cuz I know a lot of y'all are broke.

[00:12:51]Okay.

[00:12:52]>> [snorts] >> So, what do these top wait types mean?

[00:12:54]Let's go switch back over to the slide deck for a second.

[00:12:59]So, back over here on the slide deck.

[00:13:01]The most common wait types, CX, you're going to ignore and move past for the purposes of this class.

[00:13:11]It's really a deceiving issue. It involves parallelism, and people think when they read the Google for those wait types, that they're just going to turn a dial or something, and all of a sudden, uh they're going to have less of these CX waits. That's a totally different issue, and we do cover that in our mastering query tuning and mastering server tuning classes. You can't fix this just by changing settings. It's going to involve some pretty extensive query rewrites, and we talk about that inside those classes.

[00:13:42]For this class, we're going to focus on page IO latch, SOS scheduler yield, and latch EX.

[00:13:49]If you have SOS scheduler yield, that means that the SQL operating system is waiting on a CPU scheduler, which is like a CPU processor, waiting on a CPU scheduler to free up time so that it can slide back in and start doing work again.

[00:14:08]You see this when queries are burning a whole lot of CPU time, when queries are really doing something that's, for example, running row by row. If people are using scalar functions, if they're using cursors to loop through options.

[00:14:25]If they're doing CPU intensive calculations, if they're hashing, for example, if they're trying to validate JSON, if they're trying to do string concatenation, things that aren't really designed for a relational database.

[00:14:38]Or [snorts] if they're repeatedly joining, grouping, and sorting. Like because SQL Server doesn't cache joined results, doesn't cache grouped results, we talk about that in my how to think like the engine class. Totally free, if you haven't seen that, I would go check it out. SQL Server caches raw data from the tables and indexes, but it does not cache stuff that has been joined together, which is painful for those of us who repeatedly join the same tables together over and over again.

[00:15:11]These may not be long queries. This can also be a situation where you're having death by a thousand cuts. And let me explain what I mean by that.

[00:15:24]When I say death by a thousand cuts, I mean, I'll give you an example.

[00:15:29]I had a client recently where on their website, at the top of the website, you know how sometimes you have at the top of the website, there's like a little thing with your initials and your avatar, and then you click that, you can drop down and see your profile or whatever.

[00:15:46]Well, this client had a little box up at the top of their website where they showed the user's initials and their picture.

[00:15:54]And on every page render, the web app was actually passing, "Here is the user's name. Please give me back their initials."

[00:16:07]And so, tens of thousands of times per second, SQL Server was being asked to do the string comparison work, find the first uppercase letter, then go through, find the space, find the first letter after the space. These are their initials. And that's hard for SQL Server from a CPU perspective. Now, that query wasn't slow. That query ran in less than a millisecond every single time that it ran.

[00:16:34]But the problem was is it was running thousands of times per second, so it was using up several CPU cores just by itself. This is the death by a thousand cuts scenario where people may not be complaining about the individual query speed results, but overall, that is kicking the server in the behind.

[00:16:56]So, the next top wait type that we talk about is page IO latch. This means where SQL Server is waiting to read a data page from a data file because that data page was not cached up in memory.

[00:17:10]This is a very common wait type, especially up in the cloud these days where the amount of memory that you get is relatively low to the amount compared to the amount of CPU cores that you have. In this, I'm typically looking for queries that read a lot of data. Maybe it's because we have non-sargable where clauses, maybe it's because the queries need index tuning, or they're doing implicit conversions, or asking for data shredded across years worth of history.

[00:17:35]This is typically not death by a thousand cuts. These tend to be longer running queries.

[00:17:43]Cuz after all, if you're just darting in and getting very tiny amounts of data each time, odds are that's going to be up in the cache, or we're not going to have to wait very long for storage in order to pull back. Storage is these days is relatively easy to get small numbers of pages. It's the large number of pages that tend to kill us.

[00:18:02]And then there finally there's latch EX.

[00:18:04]Latch EX is when SQL Server's trying to read a page that's up in memory, and it's checking to see if someone else has a lock on that page. It's like a light weight lock. It's not really blocking. Anybody reading this page? No, I'm good. Okay, anybody reading this page? No, now I'm good. The page is up in memory, as opposed to page IO latch.

[00:18:27]And it kind of sucks how these wait type names are so similar. I always feel like they were written by somebody a long time ago, like 25 years ago, and they were never thinking about human readability. What's the difference between page IO latch and latch? They never really thought it was going to be that important. And these days, of course, now we have to explain.

[00:18:43]Here's what this one means. Here's what that one means.

[00:18:46]Latch is a really lightweight lock when we're checking to see if anybody else is accessing the table. This is very frequently death by a thousand cuts, because the data is up in memory, and we're having to check a whole lot of pages in order to get it.

[00:19:02]So, for this, we're going to be looking specifically for that death by a thousand cuts scenario.

[00:19:09]There are lots of other wait types inside of SQL Server. In these other wait types, query tuning tends to be less effective. For example, if you're writing a ton of data over to the transaction log, it's probably not because the query is written poorly.

[00:19:25]It's probably just because we're writing too much data and we need to step back and take architectural looks. So, we're not going to address these inside the span of this class.

[00:19:36]One drawback, wait stats are at the system level and in a modern complex application, you're probably going to have several big bottlenecks. You're going to have some queries that need CPU tuning. You're going to have some queries that need read tuning. You're going to have some where it's death by a thousand cuts. And it can be overwhelming. Just work through your top wait type down. Work through the How do you eat an elephant? One bite at a time.

[00:20:00]I don't know why that people even say that. Who wants to eat elephants? That can't be good meat. Those things are tough. They just look dusty and dry.

[00:20:08]It's probably terrible. But besides, what would you We wouldn't kill an elephant for food. Those things are ginormous. It would feed 10,000 people all for a Look.

[00:20:17]Back onto the training.

[00:20:20]So, if your wait stats say that query tuning is going to help, like if you have SOS scheduler or yield page IO latch or latch EX, now it's time for us to go find what queries are causing that problem. And the places where we start is sys.dm_exec_query_stats.

[00:20:35]sys.dm_exec_query_stats will tell you which queries SQL Server has seen the most, which ones have used the most CPU, which ones have spilled the most to tempdb. I love this DMV. It comes in so handy for all kinds of situations. And it seems like every other versions, Microsoft adds more columns to it to help more with diagnostics. This is also, just as a side note, why I I don't get scared at all about people saying AI will take our jobs. I actually love using AI. I use it all the time. But more than that, I love that Microsoft is trying to use robots, too.

[00:21:15]And the robots can't do their job because they don't have enough document or diagnostics inside the database engine. So, Microsoft has to keep adding diagnostics into the database engine to help the robots do their job. And when they do that, they also help me do my job, which is kind of fantastic.

[00:21:32]Uh so, the way that I like to query that is with SP Blitz Cache. SP Blitz Cache will tell you what are the top 10 or different numbers of the queries that have been using the most resources lately. And one of the big keys here is that you can sort it by anything that you want. You can sort by CPU. You can sort by reads. You can sort sort by duration, average duration. There's all kinds of cool things. And you can use the help parameter that to see more ways that you can slice and dice your plan cache. Um uh memory grants, unused memory grants, all kinds of stuff.

[00:22:08]So, the sort order is where you're going to figure out how your wait stats tie into this. One of these days, I am going to make it so that you can put in a wait type inside here for the sort order.

[00:22:19]Technically, SQL Server does not store wait stats in the plan cache. But you can kind of reverse engineer it. You can say, for example, page IO latch. If that's your biggest wait, then that means that we're looking for the queries that do the most reads.

[00:22:37]If you're do facing SOS scheduler yield weights, that's CPU, that means we're looking for the queries that use the most CPU.

[00:22:46]Latch EX, we're going to sort by reads and by executions and look it for the things that they have in common.

[00:22:54]I talked to you more about this in my classes, in my Fundamentals of Query Tuning class, in my Mastering Query Tuning class, where we also go into much more detail about how we tune those queries, and you get hands-on exercises where I give you a running workload. You have to figure out which queries are causing the problem, and then fix those queries and see that the workload gets better.

[00:23:17]Let's go and jump in and go take a look at my own server. Let's take a look over here in the lab.

[00:23:23]So, over here in the lab, back in my server, so what were our top wait types?

[00:23:28]Our top wait types on this I said we were going to ignore So, I'm going to skip past that one, and my next biggest one is page IO latch. And I said that means the queries that are reading the most data. So, let's go use our friend SP Blitz Cache. Whoopsie daisy.

[00:23:51]Blitz Cache sort order equals reads.

[00:23:55]And then execute.

[00:23:57]So, what this is going to do, and I should say this can take some time to run. It's not going to interfere with anybody else. It's not blocking or anything like that, but [snorts] the more complex your query plans are, the longer it's going to take SP Blitz Cache to run. And the less powered your SQL Server is, the less CPU power that you have, cuz one of the cool things that SP Blitz Cache is doing is it's going through your query plan, and it's examining it for all kinds of rules. It's saying, "Do you have a problem with table variables in here? Do you have a problem with parameter sniffing? Are there a number of missing indexes? Do you have expensive key lookups and unused memory grant?" There are all kinds of things that it's going through and analyzing, and shredding this complex XML can take some time.

[00:24:52]Now, I know you as a human being, the first place that you want to look is you immediately are drawn to this query text. You're like, "Ooh, uh select top 50. Ooh, that's a problem." Hold that thought. We want to zoom out first, take a big look at the big picture.

[00:25:08]When I say zoom out and take a big look at the big picture, if we're looking to fix the queries that are doing the most reads, what I like to do, and I'm running this class, so you're going to do as I say, is I like to scroll over to the reads section. If I'm trying to find see fix CPU problems, then I scroll over the CPU section. If I'm trying to fix write problems, I scroll over the write section.

[00:25:34]But here you'll see we have, if I'm fixing reads, we have total reads here.

[00:25:40]And look at how fast, or how quickly, I suppose I should say, those decimal places drop down. Let me move this over just a little bit. Uh do do do do do do, so that I can put my hand over inside here. There we go.

[00:25:56]So, look at how quickly those decimal places drop down. If I can just solve this handful of top queries here, it's going to make a dramatic difference on how the SQL Server behaves overall. Odds are your server looks like this as well.

[00:26:13]If you can just make a big difference on the top five or six queries, then you want to go back and take a fresh look at wait stats after that, after you fix those queries, uh so that you can see what the new bottleneck is, cuz the bottleneck will change all the time.

[00:26:29]Just as a side note, I I can see why people who are just starting out have a problem with being on camera. The longer you you're on camera and the more that you hear your own voice, the more comfortable you get with that. And as I'm doing, I do wild hand motions all the time, and I also have to watch myself on a TV monitor here to see what's going on. And at the moment, I strike myself as one of the Muppets.

[00:26:52]Like I've got little metal things coming out of my hand here and I'm doing all this.

[00:26:56]>> [snorts] >> "As you can see here," says Kermit doing query tuning.

[00:27:01]So, all I have to do is fix the top handful up here. Overall, John Doe says, "Brent, you rock." Thanks, John. You rule.

[00:27:10]So, then I can come back over here and I can say, "All right, I just need to focus on these top few inside here." And you see the top few inside here. It says proc statement, proc statement.

[00:27:23]So, this is a little tricky.

[00:27:25]When you look at this screen, it makes it seem like the number one most read intensive thing is this stored procedure. And the most number one read intensive thing inside there is this one line. It makes it look like it's some kind of parent-child, parent-child kind of thing.

[00:27:47]That is not what it is. Because if you look a little further down, for example, there's another We'll use this line in here. 7521.

[00:27:58]There's this stored procedure, and then there's the next thing that's involved with that stored procedure is way down here.

[00:28:06]What this list is is it's the top 10 things that are doing the most reads.

[00:28:13]The number one thing doing the most reads is this stored procedure.

[00:28:17]This line in the stored procedure. Just this one stored Maybe the stored procedure only has this one line. Maybe this one line is the only thing that sucks.

[00:28:28]But if I'm going to tune something, this one line, this stored procedure, this line, is worse than everything else in this list. I don't know about you, I like low-hanging fruit. I like to be able to make a big difference as quickly as possible.

[00:28:42]With some of your apps, you're going to see a stored procedure line up here, a stored procedure, and then there's going to be nothing in it in the top 10, cuz that stored procedure is made up of a thousand crappy lines that all add up to be really crappy, but individually they're not that crappy all together.

[00:29:01]So, I want to focus on the statements that are going to make the biggest bang for the buck inside here. So, when I go to look at this thing, before I go to open the query plan, I want to look at what SP Blitz Cache has as its warning over here. SP Blitz Cache is telling me, "Yo, dog, when you open this thing up, I want you to look at non-sargable things in the query."

[00:29:24]SP Blitz Cache has all kinds of warnings. I totally don't expect you to remember what those warnings mean. That is where the second result set comes in.

[00:29:37]The first result set has the list of warnings.

[00:29:41]The second result set is like a decoder ring for those warnings.

[00:29:47]So, you can see, like number three in the list here, it says non-sargables.

[00:29:52]I give you a URL. Isn't that nice of me?

[00:29:55]I'm such a nice guy. I put I pay for those URLs out of my own pocket. It's not true, of course. URLs are free.

[00:30:03]Uh [snorts] but we've written all the stuff inside here. Sometimes they're for BrentOzar.com, sometimes they're for other places. And then we give you a short description in here, but because you have all the time in the world, I know, cuz you're watching YouTube videos, I would recommend that you go over and read the full URL to really understand what that thing means.

[00:30:20]So, then, it certainly does say you're so kind.

[00:30:24]>> [snorts] >> So, now when I open that query plan, I know that I'm going to be looking for non-sargable things. So, I click on that.

[00:30:31]And that's really helpful, because when I look at these execution plans, very often they don't have uh yellow bangs in them.

[00:30:40]Okay. So, now I'm going to start working on tuning this thing. So, how do I go about tuning this query? Let's come back over to the slide deck for a second.

[00:30:52]Just a second over here.

[00:30:54]And now come back over there.

[00:30:56]Stop button.

[00:30:58]So, now how do I actually go start tuning those queries?

[00:31:02]Um the way that you go about tuning these queries is to first think big picture.

[00:31:08]If SQL Server knows how much data is involved with what you're looking for, if SQL Server can accurately predict how many rows it's going to find in each of the tables that you're joining from, if it can make those guesses accurately, then it's going to pick the right tables to process first, it's going to pick the right indexes on those tables, it's going to pick the right ways to join those tables together, and it's going to allocate the right amount of CPU and memory in order to do it.

[00:31:44]When it can't guess those things correctly, either because your T-SQL was written by a monkey sitting in a typewriter just banging its thick bang guns book against the keys, or because there's something that's hard to understand about it, or the SQL Server didn't build good estimation rules, or because it doesn't have good statistics on those columns, there's a huge list of things that will cause bad estimates.

[00:32:07]But, when its estimates are wrong, it does all of these things poorly.

[00:32:15]When people are just getting started in their career, they'll look at an execution plan when they're reading a plan, they'll go, "Wait a minute. It should have done an index seek over here. Why didn't it do an index seek over here? I'm just going to force it.

[00:32:30]I'm going to use an index hint like force seek, or I'm going to use a a hash join hint." They've put all kinds of things hard coded inside their query trying to fix this stuff when the real thing you need to fix is helping SQL Server understand how many rows are going to come back and what those rows are going to look like cuz then your queries will be stable, unlike you. The queries will be stable over time as your data shape changes and grows, as you get different amounts of data, as your data is distributed differently across different date ranges. Your queries will be able to react because SQL Server will understand how many rows are going to come back. But when you handcuff SQL Server, I know that's your kink, but when you handcuff SQL Server with his hands behind his back and tell it, "You got to use a seek. You got to use a hash join." When you do those kinds of things and the the results are very brittle.

[00:33:22]They don't respond well over time. The the query plans can't adapt and shape as your data grows.

[00:33:29]So when performance is bad, this is a key part of it. When performance is bad, I want you to read the plan from right to left, top to bottom.

[00:33:41]And on each of these operators, I want you to compare the actual number of rows versus the estimated number of rows.

[00:33:51]When these are more than 10x off, >> [snorts] >> that's where you want to fix.

[00:33:58]Read from right to left, top to bottom looking for the very first place where estimates versus actual goes to hell in a handbasket. Let me hop over back over to the demo just to show you. Oh, I know Rachel's kink. Rachel says, "All right, Rachel, we can talk about that later."

[00:34:17]All right, so uh the way that we're going to read this is we're going to read right to left, top to bottom cuz the first thing that SQL Server did was this thing right here.

[00:34:27]>> [snorts] >> That actuals versus estimates is chef's kiss, absolutely bang on. You can see the number of rows that SQL Server actually brought back of the estimated rows is pretty close to bang on.

[00:34:43]Another way that you can think about this is that you can hover your mouse over this little arrow, and there you actually get the labels over it when you hover your mouse over that arrow. It says actual number of rows that were brought back versus an estimated number of rows brought back. But once you know that actual are on top, I'm a I'm a really big alphabetical guy. Like if something's arranged alphabetically, it's much easier for me to remember that. So this is actual of estimated.

[00:35:09]That AE makes it real easy for me to remember. I don't know about you, but I'm honest to god not very bright.

[00:35:16]Actual [snorts] number of rows of an estimated number of rows. So these are bang on, chef's kiss. We read right to left, top to bottom.

[00:35:23]Here's the next thing that SQL Server did was it did this index seek over here. And this is not quite as close.

[00:35:33]I wish that they would put commas or decimal separators in here. Hey SQL Server, how many rows did you think we're going to come back? Uh 341 million.

[00:35:43]SQL Server, how many rows actually came back?

[00:35:46]Uh less than that.

[00:35:49]Only 2% of what it expected came back.

[00:35:52]And because such a low amount came back and matched, everything else in the plan is screwed.

[00:35:59]Everything every other decision that SQL Server made isn't going to make sense for the relatively small amount of data that we thought was going to come back from here. So generally speaking, you work right to left, top to bottom looking for the first place where estimates versus actual kind of fall off the horse. Let's come back over here to the PowerPoint.

[00:36:23]So when they're more than 10x or about 10x off, stop. This is the part of the plan you want to fix first.

[00:36:30]Now, well, just a second ago, while I was doing this, I kind of cheated.

[00:36:36]I ran SPBlitzCache.

[00:36:40]And when I ran SPBlitzCache, I had both the actual and the estimated number of rows on my plans. The vast majority of you will not have that. The vast majority of you have been trained over the last 25 years that if you wanted to get the actual number of rows, you had to run the query. Well, ho ho.

[00:37:01]Since SQL [snorts] Server 2019, there's been a database level option you can turn on. And I think it's the single missed option most people miss the most.

[00:37:11]Alter database scope configuration set last query plan stats on.

[00:37:15]What this means [snorts] is that every time SQL Server runs a query, it's when it caches the execution plan, it's going to update those count numbers inside the cache with the last execution. Now, this [snorts] isn't the worst execution, it's not the best execution, it's not the average execution, it's just whoever happened to run it last. So, when you're dealing with problems like parameter sniffing issues, for example, you're going to have a totally separate set of issues there. But, this session's about query tuning.

[00:37:44]This isn't on by default. This does have CPU overhead. You can imagine in an environment where you run thousands of queries per second that this is going to have a pretty significant CPU overhead.

[00:37:57]I only turn this on in situations where I am actively query tuning. If I'm working on a server for a day or two, I will turn this option on so that I can see last actual query plans without having to run the query myself. If you're in the kind of environment where you don't mind running the query, then you don't need this switch.

[00:38:19]But, I don't know about you, but people usually task me with tuning queries that take 10, 20, 30 minutes in order to run.

[00:38:26]I ain't got 10, 20 to 30 minutes uh what to sit around and wait for the query to run. That's where this option comes in so handy.

[00:38:35]So, whether you're looking at the actual plan cuz you ran it or whether you're looking at it from SP Blitz Cache after you've turned this on, you're going to work right to left, top to bottom through that plan looking for the first place where estimates versus actual went to hell in a handbasket.

[00:38:54]There are two kinds of estimates inside queries. There's early estimates and late estimates. Early estimates are generally driven by your where clause, where reputation is over a million, where widgets sold is greater than a thousand, where customer name equals Alice, things like that.

[00:39:14]SQL Server looks at how easy to understand your where how easy to understand your where clause is, and then looks at whether or not there are statistics to match what it's looking for.

[00:39:25]Then, as you join more tables on, you get later estimates, where the number of rows that SQL Server thinks are going to come back are purely dictated by averages, where you don't have a where clause filter on the join, you're just saying, "Here, I'm going to go find 10 customers that I want to go look at sales." Well, if you don't put a filter on sales, SQL Server is going to guess based on how many sales an average customer has.

[00:39:55]Let's take a look over in the SSMS at a couple of queries and see if we can fix their estimation causes. Now, I'm not going to start with this big old hairy monster. I'm going to start with something that looks a whole lot similar or a whole lot simpler.

[00:40:12]I'm going to start with a simple query out of the Stack Overflow database. Go get me the top 1,000 users where location equals India and I want to get them ordered by reputation descending.

[00:40:29]SQL Server, if you read from right to left, SQL Server found 113,000 people who live in India and it estimated it was going to find about 112,000.

[00:40:43]That's great. When SQL Server knows roughly how many people are going to come back, how many rows are going to come back, it's going to make good decisions. So, what do I mean by good decisions?

[00:40:57]There's an icon in here, parallelism.

[00:41:00]SQL Server understood that there were going to be a lot of rows that would come back, so it decided to to allocate this work across multiple cores. SQL Server got multiple workers involved, which is a good decision. If it had not done that, this query would have taken longer, and I want my queries to finish quickly.

[00:41:22]Now, there is a yellow bang all the way over there. Let's look at a yellow bang.

[00:41:26]If we hover our mouse over that yellow bang, the yellow bang's generally a warning.

[00:41:30]Yellow [snorts] bang says, "Danger, I gave you way too much memory. I gave you 683 MB worth of memory and you only used 34 MB." Okay, that's cool. SQL Server, especially recent versions of it, will automatically change that over time.

[00:41:48]Right now, it's giving us a warning cuz it granted us 684 MB.

[00:41:53]If I rerun that exact same query again and again and go look at the execution plan, the yellow bang >> Yes. is gone.

[00:42:03]If I hover my mouse over that select, there's no warning on it anymore.

[00:42:09]And look at memory grant. The memory grant has dropped.

[00:42:13]So, this is adaptive memory grants. This is wonderful because SQL Server can figure out over time how many how much data is going to come back from here.

[00:42:21]The number of rows didn't change. The estimate is still the same, but SQL Server is just estimating a more appropriate amount of memory for this particular query.

[00:42:31]But overall, this estimate was fine.

[00:42:35]Now, let's change the query.

[00:42:38]Here's the query we started with.

[00:42:41]Now, let's change the query.

[00:42:44]Instead of saying just where location equals India, let's say show me all the locations where LTRIM RTRIM of the location equals India.

[00:42:54]Now, there were no spaces around India before. The reason that I know that is look at how many rows came back.

[00:43:03]113,399.

[00:43:06]113,399.

[00:43:08]Now, let's go rerun that query, but this time we're going to do the RTRIM LTRIM and look at the execution plan.

[00:43:15]And sad trombone.com, it's like 113,400.

[00:43:20]It's not off by only but one.

[00:43:23]>> [snorts] >> But look at SQL Server's estimates.

[00:43:26]That estimate is terrible.

[00:43:30]Now, okay. So, let I feel guilty whenever I say that anything about SQL Server is bad cuz it's not necessarily bad. I'm going to be honest between the two of us.

[00:43:41]The T-SQL is bad. People shouldn't be running functions on the contents of columns.

[00:43:48]But because they did, SQL Server estimate that only 2,000 rows were going to come back instead of 113,000.

[00:43:59]So, what did that impact? Now, we have a yellow bang here. [snorts] That yellow bang, if I hover my mouse over it, has a different problem.

[00:44:12]That yellow bang says warning, I had to write thousands of pages worth of data to disk because I didn't grant enough memory.

[00:44:23]I only granted 17 megabytes worth of memory, but because you brought back so much data, I didn't have enough memory to hold that data, so I ended up spilling it to disk. Now, philosophically, what I wish would happen, 17 megabytes isn't a lot, no other queries are running on this server, this server's got 100 gigs worth of RAM.

[00:44:50]Philosophically, what I wish would happen is a SQL Server would say, "Oh, oh, oh, it turns out you brought more data.

[00:44:58]Why don't you have some more RAM to work with?"

[00:45:02]Every other application in the universe does this. If you start with one browser tab, it's not like that's all the memory you get in Chrome or Edge or whatever Brave browser, Safari that you choose to use.

[00:45:18]The more stuff you do, the more memory the application asks for. Not with SQL Server. SQL Server makes this guess of how much memory it's going to need at the start of the query's execution, and it doesn't adapt that as more data starts to come through. It doesn't adapt it until the next time the query runs.

[00:45:42]If I go back and I run that query again, and I go look at the execution plan, now the yellow bang is gone.

[00:45:52]Well, golf clap there. It means that the first execution is probably going to suck when your estimates are wrong.

[00:46:00]Now, ideally, the way that I would like to fix this is I would like my estimates to become correct so that every time we're running queries like this, we're going to get good execution plans right from the get-go because often people won't do things with the right uh parameterized queries. They'll put in something like, say, China and SQL Server's like, "Oh my god, I've never seen a query that looks like that before. That's completely and totally different. So, I better build a brand new execution plan just for China." And I do find it kind of funny that that ends up with the same 2,000-row estimate. Haha. Wah, wah.

[00:46:39]So, when when I think about when we read the query from right to left, top to bottom, looking for the first place where estimates versus actual go to hell go to hell in a handbasket, in [snorts] this case, SQL Server doesn't understand how many rows are going to come back because our query isn't easy to predict.

[00:46:56]Our query isn't easy for SQL Server to have It doesn't have any functions on their statistics on the output of functions. So, the simpler, the more understandable you can make your query, the more likely it is that your query that SQL Server's going to do a good job of estimating how many rows are going to come back. And yes, that means doing things like taking functions off the where clause, but I bet a bunch of us know that putting functions inside the where clause is bad. It's just that you have to teach all your coworkers to avoid doing stuff like that.

[00:47:30]Now, that's a relatively simple example.

[00:47:31]By simply taking off the LTRIM RTRIM, then we're going to get great estimates right from the get-go, and then SQL Server will understand how much resources to dedicate to the query, whether it's memory or whether it's CPU.

[00:47:44]That's a really simple one.

[00:47:46]And so, and and I move forward, that's a great comment. Yes, absolutely. So, and remember that cuz with what we're about to do here. So, let's do something that's a little bit more complex.

[00:47:58]Let's get a two-part query.

[00:48:01]Let's say, go find the top one location from the users table, the one that's got the most people in it.

[00:48:11]Then, go find the top 1,000 people who live in that location. I want to see them ordered by reputation descending. I want to find the biggest place on earth with the most people who using Stack Overflow, and then out of that, who's winning the fist fight for the most reputation points.

[00:48:30]So, let's execute this.

[00:48:34]And I'm going to read it from read the plan from right to left, top to bottom.

[00:48:39]And way over here on the right-hand side.

[00:48:43]How are these estimates looking?

[00:48:46]Well, first off, it says clustered index scan across the users table. SQL Server was able to estimate that there are about, 4 million people inside the users table who filled out their location. So far, so good. Things start to get less good over here.

[00:49:02]This is where SQL Server is grouping together people by location. Now, one of the things that I wish SQL Server would do, I wish that when I hovered my mouse over an operator, I wish that it would hover over the part of the query.

[00:49:20]I wish that it would light up the part of the query that it was executing.

[00:49:24]Wouldn't that be cool?

[00:49:26]Cuz otherwise, like you and I, when we look at stuff that says hash match aggregate, it's not really obvious what that thing is doing.

[00:49:35]So, one of the best tips that I can give you when you're trying to understand what something is doing, like what part of the query it's doing and why it's estimates are so far off, hover your mouse over it.

[00:49:46]Hover your mouse over it and look down at the bottom at the output list. What's coming out of here?

[00:49:54]Now, I really wish that this expression here, for example, I wish this would say things like count star, that it's counting the number of rows that are inside here.

[00:50:03]But the more that you read about this stuff, the more that you learn about it, you'll understand that this is grouping things together by location.

[00:50:12]Let's try to run just that part of the query by itself.

[00:50:16]Let's try to run just the CTE part and hit execute.

[00:50:20]And go look at the plan. And it looks really familiar, doesn't it?

[00:50:25]It's the same shape as what we were looking at before. SQL Server's doing in this case the CTE first.

[00:50:32]So, I'm going to ask you a question, and there are lots of good answers to this.

[00:50:36]How can I make SQL Server's estimate more accurate?

[00:50:44]When it comes time to guessing how many people live here, how many locations there are in Stack Overflow, >> [sighs] >> how could I go about doing that? How could I make it easier for SQL Server to find the top one location? And Joseph says over in chat, also Joseph's last name is P chat, so maybe that's I'm going to stop there. Um so, Joseph says, "Hey, there's an index that SQL Server recommends on location here.

[00:51:15]Let's go take a look."

[00:51:17]So, if I say create index location on dbo.users.location, that does a couple of things. One of the things that it does is it creates an index on location. And so, then that way, SQL [snorts] Server's going to be able to do this work faster. Let's come back up here and we'll rerun the query again as our location index finishes creating.

[00:51:40]So, pro tip, whenever you know, we'll go off we go.

[00:51:43]Okay, so if I go look at the plan, da da da da da da da da da da da da >> [applause] >> Now our estimates are chef's kiss.

[00:51:55]>> [applause] >> But they're not chef's kiss because of the index.

[00:52:04]They're chef's kiss because of statistics.

[00:52:09]Cuz here's what happened. Let's say drop index location on dbo.users.

[00:52:18]And I go back and I run the crappy version of this part of the query again.

[00:52:21]Go back and rerun the crappy part of the query. And our estimates as we can see here estimates are back to bad.

[00:52:29]Well, why? Why are the estimates bad? Let's say DBCC show statistics dbo.users location.

[00:52:42]How [snorts] does SQL Server know how many people live in a given location?

[00:52:48]Well, this is the output of DBCC show statistics. I teach you how to use this in my how to think like the engine class. It's totally free. It's available on YouTube.

[00:52:57]And [snorts] over at the top left, you can see there's this WA_Sys statistic. SQL Server says, "Oh, you're asking questions about the location column. I don't know anything about the location column.

[00:53:11]So, I better build a statistic on the location column. There's the location column.

[00:53:18]SQL Server says, "But time is money. Watches are expensive.

[00:53:22]So, I I can't afford to ski sample all of the rows in the table. I'm in a hurry. Joseph wants his data quickly.

[00:53:30]So, I'm just going to sample a small percentage of the data.

[00:53:35]SQL Server sampled a small percentage of the data. Even though this is only a tiny 1 GB table, to SQL Server. Even 1 GB is big data. So, SQL Server decided to sample all of that data and it assumed that everyone else's locations look exactly like the sample that it retrieved.

[00:53:57]When I created an index, when I ran this create index command, what happened was that SQL Server said, "Oh, you know what?

[00:54:09]I'm looking at all of the rows on location. If I have to go build an index, I have to go build a list of everyone's location. Wouldn't that be the perfect time to update my statistics to actually sample all of them?"

[00:54:27]So, now the number of rows sampled is equal to the number of rows in the table. So, because SQL Server took a really close look at the entire table, suddenly I have accurate estimates when it comes time for me to guess how many locations are there.

[00:54:48]So, why I said technically the location doesn't fix it, or the index doesn't fix it, is cuz if I drop it and I go uh update statistics dbo.users with fullscan, this tells SQL Server I want you to read every page in the table. I want you to build a perfect set of statistics on this in order to get how many rows match through you know, all the locations, all of the display names, whatever. Now, you'll notice this is going to take some time.

[00:55:26]This takes some time because SQL Server actually reads every page in the table.

[00:55:31]And even worse, it reads it for every single uh look every single statistic that we have.

[00:55:39]Pierre says, "Love the how to think like the engine voice. Thought it would be more robotic." I always think of SQL Server as a guy cuz he's dumb and stubborn and he refuses to ask for directions. He's all, "Trust me. I got this." when he doesn't actually got it.

[00:55:53]So, if we go back and we run that top part of the query again, you'll notice that we have perfect estimates even though we don't have the index.

[00:56:06]>> [applause] >> There are lots of ways to fix these estimates. Sometimes it's about making your T-SQL more accurate. Sometimes it's about making sure that your statistics are updated with full scan.

[00:56:20]In the case of this one, I actually don't mind the index on location. The index on location can be helpful, can get us across the finish line cuz it also helps us execute the query faster.

[00:56:31]So, let's go ahead and execute that index on location. But again, I am fine with also updating the statistics with full scan is also a perfectly valid answer to this.

[00:56:41]So, let's go back and run the first part again and make sure that our estimate is correct.

[00:56:46]And voila, French chef's kiss, not French kiss. I don't like the execution plan that much. As we read from right to left, top to [snorts] bottom, SQL Server's estimates are absolutely bang on, absolutely perfect.

[00:57:01]So, we solved that part of it. Let's now come to this part.

[00:57:08]Let's go run the whole thing and if we read it top to bottom, right to left, top to bottom, as what So, here's the first thing that SQL Server did, estimates, chef's kiss, chef's kiss, fantastic, fantastic, fantastic, bravo, applause. Things are looking Oh god.

[00:57:31]Now I have a new part of the plan where SQL Server estimated poorly.

[00:57:36]Working through query tuning is working through from right to left, top to bottom, trying to figure out where your estimates go to hell in a handbasket and what you're going to do about them.

[00:57:48]Again, I wish that I could hover my mouse over like regions of the plan so that SQL Server can show me up in SSMS what parts of the query are involved.

[00:57:59]Unfortunately, it doesn't have that advanced technology.

[00:58:02]So, instead, [snorts] you're just going to have to believe me. What happens in this top part here leading up to the uh parallelism is [snorts] where SQL Server is finding what is the top one location.

[00:58:15]And then SQL Server, "Hey, how many people live in that top one location?"

[00:58:20]"14."

[00:58:23]Uh SQL Server, "How many people actually came back from the top location?" Uh "More than 14."

[00:58:31]So, now I have another problem that I have to go and solve.

[00:58:35]Now, there are several people in here asking about statistics. So, if you want to learn more about that, go check out over on YouTube. We have a totally free like 2-hour long class on statistics that goes into more detail.

[00:58:51]If you go to uh YouTube, uh search for Brent Ozar statistics, there's a whole playlist full of stuff by me and Doug Lane that talk about how statistics work in SQL Server, how you should keep them updated, what tools we recommend, how multi-column statistics work, and much more. All totally for free because I love y'all. Now, back over to this.

[00:59:15]So, working through this can be kind of a pain in the rear. There's There's all this stuff that you have to work through and understand what your different different options are and how you're going to go about solving that.

[00:59:25]So, one of the things that I love the most and I love teaching people how to do this, we'd go into detail on it on Fundamentals of query tuning and on Mastering query tuning as well.

[00:59:37]Doing this can be a lot of work, though.

[00:59:39]So, one of the cool things that we have with SP Blitz Cache is an AI parameter.

[00:59:46]And just like with yesterday's videos on or 2 days ago's videos session on how to tune indexes with AI, you also do the same thing. We build stuff for you with SP Blitz Cache as well if you do AI equals 2. Now, because I just blew the plan cache cuz I was changing so many things about the tables, I I got to go re- throw my server under load, so things are going to run for a minute.

[01:00:11]With SP Blitz Cache AI equals 2, now you get this additional para- or additional column here that says, and this is a prompt that you can go copy-paste right into your favorite LLM, into ChatGPT, into Claude, Gemini, whatever. We're going to show I'm going to show you what's inside here here in a second.

[01:00:30]We have a [snorts] couple different options for AI. If you put in AI equals 1, we will actually call AI for you. We will call your AI provider, ChatGPT, Gemini, Claude, whatever.

[01:00:45]There is more setup with that, and it only works on SQL Server 2025 or newer or Azure SQL DB. Because most of y'all aren't on platforms, you're probably going to want to stick with AI equals 2.

[01:01:00]Plus, AI equals 2 is instantaneous.

[01:01:04]Whereas [snorts] with AI equals 1, because we actually do go call, you know, ChatGPT, Google Gemini, those can take 30 seconds or 60 seconds per row of the result set. So, it can take 10 minutes in order to run this exact same stored procedure for going through and calling those. This way, the nice thing about this is that you can just call it for the specific rows that you care about.

[01:01:28]So, let's run it again, and we're going to have some different rows inside here for more complex queries.

[01:01:35]So, here we [snorts] have one of those stored procedures that we were looking at earlier. Um I'm going to come across and grab one of the AI prompts. So, if I click on the AI prompt, you can see here we've got a set of prompts up there at the top for the AI. Part of what I want to coach the AI on is that I want to tell it the kinds of advice I want, and I don't want it to ask me for more help.

[01:02:04]Very often, AI prompts are geared towards here's a little bit of the answer, tell me what you want next. And it's I would rather tell it, "Hey, look, I don't have time for that. Go give me everything I need right now, and I I don't have time for follow-up questions." Then, it gives me here's what it looks like in production. If I come down further, it says, "Here's the query text so that you can copy-paste that back and forth." And here's the execution plan pasted in in a way that's easier for stuff like chat GPT to understand.

[01:02:40]So, I can copy all this out. I'm going to just go over here just like you would, hit copy, go over to chat.openai.com, start [snorts] a new session, paste this in, and I I It says too long to show in in text field because this is a lot of data that's inside there, especially the larger that your execution plans become.

[01:03:02]And this is amazing. I just absolutely love the kinds of advice that you get very quickly.

[01:03:10]Where [snorts] it goes through and interprets stuff, it'll tell you what whether or not the index recommendation makes sense based on its analysis, what you need to change about existing indexes that aren't as efficient, will come on down and it the way that I really like it even gives you a rewrite.

[01:03:27]It says, here you go, avoids repeated expressions and protects against divide by zero. And then [snorts] gives you a final deployment script. And in that deployment script, it gives you a create index statement with advice tailored to your version of SQL Server and whether or not it supports things like sort in tempdb. And it tells you what to expect in terms of the answer. All this was in like 30 seconds.

[01:03:53]Now look, I know a lot of people have problems with AI. I know that a lot of people are like, it's coming for my jobs.

[01:04:01]If it's coming for your job, it's coming regardless. Why don't you make it do your job as long as you're getting powerful? This is really cool.

[01:04:13]Now I do have to say that when you're doing query tuning, it is so important to choose the right models. And choosing the right models depends on which user interface you're using. If you're using chat GPT, for example, you want the expensive models. You want the thinking, the latest model because the lighter weight module that you get, the crappier answer that you are going to receive back.

[01:04:39]Also, I'll also tell you Copilot is quite frankly not very good inside of SQL Server Management Studio.

[01:04:47]Now, I'm going to give you an an example.

[01:04:51]So right now we're tuning this USPQ57521.

[01:04:55]So I'm going to take USPQ7521.

[01:05:00]I'm going to script it out in the window. I'm going to say programmability stored procedure 7521.

[01:05:07]I'm going to put it in the window here so that chat GPT has it.

[01:05:11]Then I am going to I'm going to move things around a little, make it easier for y'all to see.

[01:05:16]I'm going to start a new chat with Copilot, and I'm going to say, um the stored proc, let's zoom in so you can see it.

[01:05:25]USP_Q_7521 isn't performing well.

[01:05:31]How should I make it go faster?

[01:05:36]Now, in fairness, I should also say, if I go look at the the drop down here for models, there are model choices that you have. Chat GPT's 5.5 is not in this list because SSMS does not have uh the ability to use Chat GPT 5.5 yet. Right now, it only has kind of relatively older models. Out of the older models that it has, the best one that it has is 3.1 Pro. So, I'm going to use the best one that we have here, 3.1 Pro.

[01:06:04]In fairness, when I hit send on here, this prompt is not as good as what came out of SP Blitz Cache. I didn't say things about the metrics, for example. I didn't say things about here's the execution plan. So, I'm relying on Copilot to do it, which is what most people will work. Um what most people will do in terms of prompting it. So, what it's going to do is it's going to go look at the object, it's going to go script it out, it's going to go take a look at what what its metrics look like, and look at how much time it takes. Look at how slow this is relative to GPT 5.5.

[01:06:43]It's just It's not even close. Now, I can paste I can take the exact same thing that we pasted over from SP Blitz Cache. So, this is This is funny. It's So, now it's putting in Now, it wants two recommended indexes instead of uh Copilot. Um let's see if it also does a rewrite for it. It's still waiting. It's still doing its thing.

[01:07:07]I think it says that. No, it's No, it just said create those two indexes. It didn't even rewrite the query for me.

[01:07:13]Whereas ChatGPT rewrote the query for me.

[01:07:15]So, let's do one other thing.

[01:07:18]Let's start a new query and let's take the whole thing that I from the prompt that I got that I pasted over to ChatGPT. Let's go to that AI prompt.

[01:07:28]I'm going to copy the whole thing in and I'm going to go paste it over into the Copilot chat window and hit send.

[01:07:36]Now, this So, this is going to take long in or take a while in fairness cuz this is an older model relative to GPT-5.5.

[01:07:44]The reason that I'm doing this is I'm just showing you the the better way to use AI isn't just to say to Copilot, "Yo, dog. Go do my job for me." You want to give it as much background information as possible. Um and sadly here, sadtrombone.com, it's still I should put it up there. It still doesn't recommend uh rewriting the query. So, there's where really you uh want to do a better job with sending it to a higher powered model.

[01:08:15]Um okay. So, all this is awesome. Let's come back over to the slide deck for a second. Come over to here.

[01:08:25]So, I said in terms of getting advice from AI, uh remember demos with public databases look artificially smart because the AI remembers what it's seen before. Uh whenever you see people doing demos with AdventureWorks, Northwind, Stack Overflow databases like that, these are public databases. So, the AI has been more deeply trained on those databases. So, they tend to give you better answers. Your real-world databases, uh you're going to have to give it that same level of background.

[01:08:51]You're going to have to give it your uh database schema, the definitions of the stored procedure, and so forth.

[01:08:59]You also need to think about what that means for AI. So, what that means is is that if you continuously send in context and information about your company's databases, that also means that your company's databases are going to become part of the training set. Um various LLM providers will say things like, "We would never use your data for training." And then you have to watch their terms of service because they later go and opt in and they say, "We are now going to use your past stuff for training." You know, so you have to make sure if you're going to be in that, you're going to opt out of that stuff.

[01:09:31]Um and the bigger the better that you use for models, the better advice you're going to get.

[01:09:37]Now, if you do this a lot for a living, as I do, you want to gather this data as quickly as you possibly can about which queries are performing poorly, what your wait stats uh look like, what kinds of indexes you need to focus on. I certainly do. I do performance tuning full-time as a consultant. I specialize in 2-day turnarounds where I have to turn servers around as quickly as possible, and I have limited time amount to do it. I can't imagine trying to do it without the consultant toolkit. The consultant toolkit is a tool that I built for y'all. I did not build Richie Rump on my team, Joris, who shows up in YouTube chat all the time, uh built. It goes through and runs all kinds of diagnostic queries for you. Uh you install it on your workstation, not the SQL Server itself, and then you point it at the workstation you want or the SQL Server you want to go analyze. It then goes and collects all kinds of data so that you get an Excel spreadsheet that has everything that you need, plus queries uh that you could query queries and query plans that you can then go use to tune disconnected. Let me show you how it works.

[01:10:44]So, I'm going to hop back over into my workstation.

[01:10:48]And in there, I've got Let's go pop into Windows Explorer.

[01:10:55]Not SQL query stress.

[01:10:56]Let's go pop into Windows Explorer.

[01:11:00]And then I have already put in here do do do do this PC.

[01:11:05]temp Consultant Toolkit So I have already saved in here the Consultant Toolkit. Consultant Toolkit has a bunch of files. This is one folder. You don't have to install anything. You can put this on a network share and just run it off of the network share. Really useful for big companies.

[01:11:24]So if I would then go run it, the the easy way to run it is you can simply double check double click on it. You can double click on the SQL Server Check Up application. And that connects to whatever local SQL Server you have.

[01:11:37]However, you can also run it from the command line and you can point it at servers. You can say go gather data from that SQL Server over there.

[01:11:46]So if I double click on it, it just spawns up a command window that connects up to the SQL Server and starts running all kinds of diagnostic queries on the server. It runs SP_Blitz. It runs SP_BlitzFirst, SP_BlitzCache.

[01:11:59]And saves this data out into an output folder. Let's go take a look at the output folder.

[01:12:05]So the output folder here, if I maximize this, it gives me all kinds of stuff. So for example, it gives me an Excel spreadsheet that has a tab for each of the diagnostic info. So it has a tab for SP_Blitz. It has a tab for SP_BlitzIndex and the different modes for SP_BlitzIndex. Has a tab for your wait stats, just like we saw for your top wait stats on the server that I showed you from SP_BlitzFirst. Same thing comes out of here into the Excel spreadsheet and I'll show you what that looks like.

[01:12:36]Plus you get your top queries by CPU, your top queries by reads, top queries by duration, any queries that have missing the top queries that have missing index recommendations. Let's go back over to the slide deck so you can see what this looks like. Come over to here.

[01:12:56]And come out to here.

[01:12:59]So, when I open up the spreadsheet, I get a bunch of tabs going across the bottom kind of organized by category.

[01:13:05]The first time tab gives me stuff like uptime and how large the server is cuz obviously I don't want to make any diagnostic call decisions on a SQL Server that's been up for like 24 hours or less. There's just not enough diagnostic info in there. Since SQL Server keeps everything up in memory rather writing rather than writing it to disk for diagnostic purposes.

[01:13:26]Then it's got tabs for SP Blitz, so you can make sure that the conserver has its database backups done, that you're not facing any kind of database corruption, uh that you don't have bad performance settings like a auto shrink turned on.

[01:13:39]Then there's a tab for your wait stats, so you can see where your top wait stats are. And this is how I used to figure out whether or not the clients really having a performance emergency or not.

[01:13:49]Uh the shows me their thread time and their total wait time. So, the higher that wait time is, the more of an emergency it is. And that also tells me which wait stat I need to focus on for query tuning, so I can decide whether or not to look at queries by CPU, queries by reads, and so forth.

[01:14:08]All kinds of tabs, uh information about the plan cache, what are the top 10 queries that have been uh used the most reads, the top 10 queries that have done the most average reads when they run individually, they suck really bad, and [snorts] much more. There are more tabs for high availability and disaster recovery, the error log, perfmon counters, and much more.

[01:14:30]If you're a consultant, this also can be auto set up to automatically upload data to your AWS S3 bucket, so that then your clients can just run this and it data goes straight up to you for your own analysis. Based on how you configure the bucket, you just And we give you instructions for that. The files can only be written there and not read, so other people can't see other clients' data.

[01:14:52]We can even automatically import the data into a database for you, so that you have one SQL Server database or Azure SQL DB that tracks data from all of your clients. For example, when I'm working with clients on a long-running basis, I have them just schedule this to run once a day, sends me the data, and then I can go look to see how their servers are doing without having to connect into them or run expensive per-client monitoring software.

[01:15:19]I'm surprised by how many people we have who are database administrators who run this. And the the one that I hear the most often is giant companies, think like international global companies, where the DBA has to support all kinds of other departments. They have to support the sales department, the accounting department, the inventory department. And they're not allowed to go connect to all these different servers. Whenever people create a help desk ticket, these DBAs just say, "Just run this utility, send me the Excel file that comes out of that, and I'll tell you what's wrong with your SQL Server."

[01:15:52]Me as a consultant, I cannot imagine trying to do my job without that. It is absolutely life-changing. It helps me work on more clients in less time, so that I can make less make more money with less expenses on my part. Just life-changing.

[01:16:12]So, to recap what we talked about inside here, what you learned was that you were first want to start by looking at your wait stats with SP Blitz first since startup equals one. Based [snorts] on whatever those wait types are, that's how you determine which queries you want to go focus on. The SP Blitz cache has so many sort orders, you only want to focus on the sort order that's going to make the biggest bang for the buck in terms of your server's performance.

[01:16:36]Then, go check the the query design. I put in here sort order equals mine. What I mean is that you either want to look at reads or by CPU if you're facing these weight types. You either put in sort order equals reads or sort order equals CPU and turn on AI equals two so that you can copy paste those AI prompts out and then go paste that into a chat GPT or Google Gemini or whatever it is that you'd like to use Anthropic Claude. Um keep in mind that when you're pasting into AI, that stuff if you don't have an agreement with that company, your stuff may be used as part of the training data and you use the biggest model that you can in order to get the best advice possible.

[01:17:19]We cover all of that and more in our training classes. If you go to BrentOzar.com, I've got training classes, I've got fundamentals, mastering, all that and more. Plus, I have the consultant toolkit as well where you can go buy it. It's sold either per person or for an entire company license. So that you if you're in a large company with dozens of DBAs, you can pick that up and have everybody use it really quickly and not have to worry about reinventing that wheel there. All that's available over at BrentOzar.com/go/toolkit.

[01:17:54]So let's [snorts] hit some of your questions that came up through the course of this.

[01:17:59]Mr. Dave Hubble says, "Will you be uploading these sessions so that you can rewatch it later? I missed the index one on Tuesday." So I have a live index one next week and also these will be up on YouTube as well later. We need to talk about your punctuality, but I'll leave that for another day.

[01:18:15]Um let's see here. Alex says, "Can you recommend a similar set of tools for Postgres?" No, I'm not aware of anything like this for a Postgres actually.

[01:18:23]That's one of the things that I love about SQL Server is that there's this really thriving ecosystem and bloggers and all that open source kits for first responder type stuff that are really centralized and proven over the decades, which is magical.

[01:18:38]Um let's see here. Next up we have do do do do do do do coming back a little further cuz I know there was something else.

[01:18:49]Do do do do co-pilot. I know there was something else. Oh uh yes JG says is there a different or more preferred model cuz he says he's been sticking with Claude Opus and uh Sonnet.

[01:19:01]I am with you in that I adore Claude Opus. It's a little on the expensive side compared to most of the tools that are out there, but my opinion is usually like even if it cost me 50 cents or a dollar to tune a given query, that's still cheaper than me trying to spend 20 30 minutes reverse engineering it. Now also of course the AI can give you bad advice by all means. This is something that you have to check and prove out.

[01:19:29]But for 50 cents or a dollar, it's totally worth it as at least as very minimum as a starting point.

[01:19:35]All right. Well, thanks for hanging out with me today. Hope y'all learned something and I will see you on the next live stream. Adios everybody.