Query Tuning Archives | Page 83 of 147

Changing The Default Output Of sp_QuickieStore

Posted on May 17, 2023January 21, 2026 by Erik Darling

Changing The Default Output Of sp_QuickieStore

Video Summary

In this video, I delve into the customizable output of SP Quickie Store, a powerful tool for SQL Server database professionals. I demonstrate how to leverage the `help` parameter to uncover detailed information about the stored procedure’s capabilities and parameters, making it easier to tailor your queries according to specific needs. Additionally, I explore ways to modify the default query results by adjusting the number of returned queries with the `top` parameter and changing the sort order using various metrics like executions, memory usage, and duration. By sharing these tips, I aim to help you more effectively analyze and optimize your SQL Server performance.

Full Transcript

Erik Darling here, all by myself. We’re going to talk a little bit about how you can change some of the default output of SP Quickie Store. Along the way, I’m sure that we’ll see lots of fabulous, fantastic things. So the first thing that I want to show you is how you, as a data professional, can learn more about or learn the full capabilities of SP Quickie Store. And most of it is going to be by using the help parameter. I know that a lot of us in the tech world have trouble asking for help, unless it’s Google. But I put this in here for a reason. It’s because we hold these truths to be self-documenting. Just like PowerShell code is self-documenting. And if you hit the help parameter, those P’s are really going to tell me how far off the audio sync is on this thing. You’ll get a little bit of information, a little greeting up here. Tells you a little bit about what Quickie Store is, what it can do. And then the second output is all of the parameters that you can put into SP the data type of the data type of parameters, a description, valid inputs, and what the default value for them all is. So if there’s stuff that you want to filter on specifically, you can do that. We’re going to use some of them today. We’re not going to use all of them today. Some of them will be in a different video. Specifically, I think the ones about looking for a procedure and specific plan IDs and stuff will be the next video. But this one here is just a little bit about how you can change the default output. So obviously, passing in a database name, pretty critical step since query store is on database by database. I recently added a get all databases parameter to a quickie store. So if you want to scavenge all of your query store databases, you can do that. So the first thing I’m going to show you is how you can change the number of queries that you can change the number of queries that you can change the number of queries that get sent back to you. And that is by using the top parameter. By default, top will just give you the top 10. I try to keep it to a reasonable number because often I find that if you start bringing back 20, 30, 40, 50 lines, the further down the results are, sort of the less valuable they get. It’s a little bit overwhelming. There are only so many store procedures you can tune at a time.

Anyway, but if you want to get more back and this can actually be helpful if you have sort of like longer stored procedures where you feel like there might be like if you get the top 10 back and you’re like, well, there are like 30 queries in the store procedure looking at the top 10 is good, but we kind of want to see all of them, you can get a more expansive result set for that. But I’ll show you about this store procedure thing in the next video. But we get the top 10 or sorry, we changed top to 20. We will now get 20 results back. So I’m going to go back here and you can see that I’ve really been pounding on kind of the same query over and over again. Another thing that you can change is the sort order. Now I’m going to go back to the help parameter real quick. Because I want to paste in and show you all of the different sort orders that you can actually use when you’re searching through a query store.

So you can use CPU, logical reads, physical reads, writes, duration, memory, tempDB, and executions. These will all sort by the average, not the total. Only because what I find in my day to day consulting is that if you sort by totals, you tend to get stuff that executes a lot, but doesn’t take a very long time to run. And usually I want to find the stuff that does the worst in the average execution. You might find some outliers where they had like one thing where it did like a billion reads or something or used a billion CPU ticks.

And then the rest of the time it’s pretty quick. But, you know, these are good things to sort of figure out. So these are all the possible sort orders you can use. Again, that’s all available. If you use the help parameter, you can see all the valid inputs for everything.

But this time we’re just going to use executions as an example. Remember, by default, it uses average CPU. And if we look by executions, this might actually prove my point a little bit where we have a lot of executions for some of these things. Well, not even like a lot for what we’re looking at, but the average duration and the average CPU time is pretty high.

What I find the executions parameter is good at showing you is stuff like scalar UDFs, because scalar UDFs don’t execute once per query unless they’re in line 2019, 2022, Freud, fun stuff. If they’re in line, then they don’t execute technically as often.

But if they are not in line or not in lineable, then scalar UDFs will execute once per row. You know, you put it in a select list with the returns like top 10,000, that UDF is going to have to execute 10,000 times. If you put it in like a where clause or something, and let’s say your table has a million rows in it, and all million rows come out, that UDF is going to have to run a million times once per row, produce a result, and then have the predicate applied to it.

So those can really rack up executions very quickly, and that’s usually what I use the executions sort order for. Probably the other sort order that I use the most day-to-day is memory, because I think I said in the last video that finding queries that use a lot of memory is often a good way to find queries that are ripe for tuning.

Usually you’ll find like a big sort or something in there, and you can, you know, index your data more appropriately to make the sort not require memory. You know, index order, match sort order, sometimes you can fix that.

So that’s another good thing to look at. Another thing that actually has been kind of cropping up a bit in consulting work is if you have a query that has a lot of like derived joins, subqueries, other things like that, sometimes you’ll find that like SQL Server has a cap for the memory grant, right?

Typically like a query can ask for, unless you have resource governor enabled or using the min or max grant percent hints, SQL Server has a hard cap of like 20, 25% of your max server memory setting for a single query. And so if your query has a lot of subqueries in it, whether it’s, you know, multiple CTE, multiple, you know, derived joins, subqueries, stuff like that, things where, you know, you could break up those parts of the query, you can often get the memory grant as a whole to be smaller because you have a bunch of small queries asking for memory grants rather than contributing to one giant memory grant in one big query.

I’ve always found myself quite against big monolithic queries, but this is another good reason to do that because if query operators in a big plan are unable to share memory from, you know, one operator to another, sometimes that happens if you don’t have any, you know, sort of stopping points between them like sorts, hashes, other things like that, then queries will ask for very, very large memory grants to do things where if you break them up, you can get the memory grant reduced quite a bit.

So those are the words of wisdom I have for you. Thanks for watching. Another thing that you can do to limit or to change the default results is to use the start and end date parameters. That’s right down here.

So let’s say that you have some other monitoring that showed a CPU spike or, you know, getting a bunch of errors and other stuff on like a weekend and you want to just look at a weekend, then you can use the start and end date parameters to set caps on which portion of data you want.

Like I said in the last video, by default, you get the last week of data, the last seven days. But if you want to change that to focus in on a particular day, particular event, you can absolutely do that too and just hope to, hope to whatever, whatever you have faith in that you, you, you query store captured what you want for that.

So if you, if you wanted to see what I was up to last weekend, it was not a whole hell of a lot. This is all just background system queries. I apparently, apparently I wasn’t home much.

I wasn’t doing much last weekend. I think, I think I was out and about quite a bit working on my tan, you know, my bar tan. Uh, and so I was not, uh, hammering SQL Server with, uh, all sorts of, um, you know, bad queries.

Anyway, uh, that’s sort of a few different ways that you can, uh, look at, uh, change the default, uh, output ordering, what we’re focusing on, uh, and the, uh, default span of time that we are analyzing when we’re looking at query store. Uh, that’s about it for this one.

Next video, we’re going to talk about filtering to specific procedures, hashes, plan IDs, query IDs, stuff like that. Um, and so I hope you’ll join me there. Uh, remember, like, subscribe, love me, please love me.

I mean, or just kind of, I mean, just like me enough to watch the video, I guess. That’s all I really need. View, views, I guess, right?

Big views, big view, big view energy. Anyway, uh, thanks for watching. I’m going to get working on the, the scripts for the next one, and I’ll see you over there. Goodbye.

Hopefully not forever.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Filtering To Specific Queries Using sp_QuickieStore

Posted on May 16, 2023January 21, 2026 by Erik Darling

Filtering To Specific Queries Using sp_QuickieStore

Video Summary

In this video, I delve into the advanced customization options available in my free open-source tool, SP Quickie Store, which enhances query store results for SQL Server tuning and troubleshooting. I walk you through various parameters that allow you to filter down to specific queries and execution plans based on criteria such as execution type (successful, failed, timed out), procedure schema, plan IDs, and query text. By demonstrating these features, I show how SP Quickie Store can significantly streamline the process of identifying and analyzing problematic queries, especially in scenarios where traditional methods fall short.

Full Transcript

Oh boy. Hot on the heels of that last video. I think it’s maybe two minutes later. We’re going to look at how you can further customize some of the results that you get back from a query store using my free open source. I mean, it’s free to everyone but me. I charge myself a very, very high consulting rate to use it. How you can use that to filter down to specific queries, plans, stuff like that. Now, again, we’re going to start off with the help parameter because the help parameter will give you information about what all the parameters can do.

If I add parameters, change parameters, anything like that, this will get updated so you can stay on top of exactly what’s going on and what you might care about. But getting down a little bit further in here, this is, there’s a whole section of parameters that help, they can help you filter your results down to a specific set of things that you might care about. So if we look at the full list here, I’m going to walk you through these and then we’ll, I’ll show you some examples of them.

We have execution type, which will tell you, which allows you to filter on queries that are either, you know, successful, failed for some reason, either, you know, different reasons why queries might fail. You can find ones that ran or you can find ones that like timed out, errored out, stuff like that. If you want to look for a specific store procedure in query store, you can use the procedure schema and procedure name parameters.

Procedure schema will default to DBO. So if you have procedures in different schemas, you will need to provide that schema in order to successfully find them. If you want to include a list of plan IDs or query IDs or just a single one, well, I’ll split any comma delimited value in here out to a list and pass along and pass that into a table for searching.

Likewise, you can also choose to ignore some plan and query IDs. By default, SP quickie store will filter things out like creating, altering indexes, altering tables, stats updates, whether they’re, you know, manual or whether they happen as part of a query so that you don’t get some of that noise in there that might happen from those. Like you don’t need to know that creating or rebuilding an index took an hour because you can’t tune that.

If it’s useful for you to find, let me know. I can, you know, change the default on that. But otherwise, it’s like, what are you going to do with that with that information?

Like what? Also, there’s a section in here. So what would happen to me quite a bit in my consulting efforts is I would find queries from other sources where query plans were not necessarily part of the the data collection. If you ever look at the blocked process report or the XML deadlock report, what you’ll see is that SQL handles for the queries involved will be logged in there along with the query text, but not the query plan, which is understandable because that’s it will be a concerted effort to include a query plan and all that.

And staging XML inside of XML sounds a bit, this is a bit nightmarish, even to me, a person who spends a lot of time in XML. So, yeah, I get that. I totally get that.

But a lot of the times when you’re trying to figure out a blocking or deadlocking scenario, the query plan becomes really, really important because the shape of the query plan, you know, lookups, you know, missing indexes, things like that can really play into if like the reason why there was significant blocking or deadlocking. There is also a query text search parameter. So if you know, like if you’re looking for like some entity framework query or something where, you know, maybe something that was generated by dynamic SQL, something like that, you can search for the specific text.

On its own, SP Quickie Store will, if you don’t have wildcard searches on either end of your query, it’ll add them in for you so that we, you know, search the text for everything that you might find. But if you have weird spacing inside of the query, like if it’s like select, like carriage return, like big tabs and stuff like that, other weird white space issues, you might need to add your own parentheses inside of the string to search for stuff. So let’s just demo some things real quick here.

Let’s run this. And we’ll find that there’s a store procedure in here called vote sniffing. Let’s let’s I’ll type in a demo.

Screw it. Rules be damned. So let’s look for it. So since that is in the DBO schema, we don’t need to worry about supplying the procedure schema. If this were in a different schema, we would need to tell it.

But since this is sniffing, I think I spelled that right. Since this is just in the DBO schema, we can run this and we can search for a specific procedure name. Now, one thing that I talked about in the first video was how the all plan IDs parameter can be really useful.

And that is because if we use this, let’s just say that this was not a store procedure. Let’s say that this was a regular, a regular old, you know, query dynamic SQL, you know, entity framework, something like that query where we had no idea how else to identify. But we wanted to look for specific plans.

What we could do, stick that in there and we could get back all the plan IDs that we cared about from this list. Right. Pretty neat.

At least I think so. If you wanted to ignore any of this stuff, do any of that, we totally could. You can do the same thing with query IDs. Right. So if we come back up here, we grab query ID 312. We can say include query IDs.

And we can look for 312 and we can just focus in on anything that query ID 312 might have given us. Other things that can be useful. You know, if you want, like, let’s say that you had a bunch of query timeouts over the weekend.

Right. Let’s say or at night or sometime, some other time when you weren’t looking at things. Excuse me.

We could look at the execution type. And we could see that valid inputs are regular, aborted, and exception. I don’t know if I have any aborted or accepted queries in here.

But let’s take a look. And let’s see. Do we have anything aborted?

I have no idea. It’s possible. It’s entirely possible. No, we do. Look at that. I have two aborted queries in here. I don’t think I have any queries that would have failed.

But, you know, that’s because my queries never fail. I only quit on them. It’s never the query’s fault. It’s just me running out of patience. So anyway, this is different ways you can narrow down.

You can narrow your search down to what you care about from the results that you see here. I’m going to soapbox a little bit and say this is one of the big reasons why I wrote this store procedure. Because right now, the query store GUI doesn’t give you a way to search through any of these things or filter down to any of these things.

You can track certain query IDs, but you have to find that query ID and right-click it and track it and all this other stuff. You can’t, like, manipulate what query store brings back in a meaningful way to search through things for any of this stuff. And this is all important stuff when you’re doing query tuning work, when you’re doing consulting work, where you’re walking into a server that you may have never seen before, where you’re walking into a server where you might have to be able to put together pieces of a situation really quickly.

You know, again, coming back to, like, block process or XML deadlock report, like, you have to find those queries, you have to find those plans, you have to get in there and do stuff really quickly. So this is all sort of invaluable stuff for me in my day-to-day consulting work, and that’s why I put it in here. You know, if the query store GUI were written by someone who, or designed by someone who cares about humanity, we would be able to search and filter to these things a bit more easily.

But, alas, we do not have those options currently. I would love to film a video where I say, SPQuickieStore is no longer necessary because the query store GUI is completely up to snuff and offers you a commensurate experience. But for me, I still need all this stuff when I’m working on things in my day-to-day life.

So I made it easy for me. I made it easy for you. You know, again, please love me.

So anyway, that’s about the end of this one. You know, the usual rigmarole. Like, subscribe, join my email list, hire me to do work for you. No, whatever.

Or don’t. Maybe just go, maybe buy me a coffee someday so I can pour whiskey in it and have a good morning. Anyway, thanks for watching. Hope you learned something.

Hope you enjoyed yourselves. And I will see you in the next video where we will talk about something else entirely with SPQuickieStore. All right. Cool.

Feel good about this one. I only coughed once. It’s almost a new record for me. All right. Goodbye. Goodbye.

Going Further

An Introduction To sp_QuickieStore

Posted on May 15, 2023January 21, 2026 by Erik Darling

An Introduction To sp_QuickieStore

Video Summary

In this video, I dive into SP_quickie_store, a handy stored procedure that provides insights into query store without much setup. I walk through the default output, explaining how it surfaces poorly performing queries and offers detailed execution metrics over the past week. I highlight key columns like query ID, plan ID, and various performance metrics such as CPU usage, reads, writes, and physical reads. While the procedure is straightforward to use out of the box, I also touch on its limitations, particularly in terms of memory grants, which aren’t fully visible within query store. Throughout the video, I share my frustrations with the query store GUI, finding it cumbersome and sometimes counterintuitive, and I promise more detailed explorations of SP_quickie_store’s capabilities in future videos.

Full Transcript

Erik Darling here with Darling Data, currently the only person with Darling Data. It’s a little lonely sometimes. Maybe I should talk to ChatGBT more, have some conversations, figure out what’s going on in the world around me, what’s in the news, headlines and all that, good stuff. Erik Darling here with me. So we finished talking with, well, we’ve talked about SP underscore human events about as much as I can talk about SP underscore human events without getting into my deep and profound frustrations with extended events. Erik Darling here with me. So we’re going to switch over to SP underscore quickie store so I can tell you about my deep frustrations with query store.

Erik Darling here with me. I don’t know. It’s not even query store. It’s the GUI. I think the GUI was designed by someone who hates people. Erik Darling here with me. That’s about it. Anyway, let’s talk about what you get from SP underscore quickie store right out of the box. Erik Darling here with me. So you don’t have to do much to get data back. We’ll talk about some things you can do to get data back a little bit differently in other videos.

But just off the bat, if you run SP underscore quickie store and you give it a database name that has query store enabled, you will get some very helpful results back. If you are using the database that has query store enabled, like if I just change the context to stack overflow 2013 and I run quickie store, it will default to the database that it’s it’ll check the database that it’s we’re currently using and see if query store is enabled. If not, you have to give it a database name. So let’s go on back to the master database and let’s hit F5 here and let’s talk a little bit about what you get back from the default results in SP quickie store.

So I’m actually going to go a little bit backwards here. I’m going to start with the bottom results because I want just to show you where you can get a little bit of information about what you got back. Mainly it’s this column right here. By default, SP quickie store goes back seven days to look for poorly performing queries. I used to have a default of 24 hours, but I found that the previous 24 hours would often not give me everything that I need.

There are some notes about support about how to get help using the procedure, how to debug and troubleshoot performance issues. And of course, version dates and all that good stuff, blah, blah, blah, blah, blah, happy times. Now let’s go talk about the top set of results, which by default will give you the top 10 queries that we found in query store over the past week ordered by average CPU.

Now, when I order by average CPU, I’m going to walk over here a little bit. Now, since I’m on SQL Server 2022, I have a lot of additional columns that you might not see in older versions of SQL Server. But when I go and look at what happens by average CPU, that’s this column right here, we find queries that used a lot of CPU, of course, on average.

These queries might not have very high execution counts. You might see a single execution. You might see, you know, just a low number of executions.

That’s sort of to be expected. There are different ways to filter the results that we’re going to talk about in another video. You can totally filter, but like set a lower bound for the number of executions for a query you care about.

But generally what I find is the higher you set that number, the lower this average CPU gets and the harder it is to find queries that you can make a meaningful difference tuning. Now, the count executions thing can be a little bit misleading. If you have queries that aren’t parameterized, if you have queries that I believe for recompile as well, they’ll still show up in query store.

But I think you’ll get different plan or different plan entry for them when they run. So you just might not have a completely accurate gauge of just how many executions a query has looking at this. So starting a little bit more close to home, you get the query ID.

So if you want to use any, if you want to use the query store GUI to track a query, or if you want to, you know, use it to force a plan or something, you have the query ID column and the plan ID column here. If your query is responsible for inserting multiple plans, so if one query ID has multiple plans attached to it, you’ll get all of the plan IDs in this column. This comes in handy, again, something we’ll cover in another video, because you can filter down to just the query IDs or just the plan IDs that you care about using different parameters in here.

This column will tell you if the query completed executing or if it failed for some reason. There are different outcomes for this here. If your query came from a stored procedure or if it came from an ad hoc source, like an application, any framework, something like that, then you will see either the object name that the query came from or ad hoc.

You also get back the query text as a handy clickable XML column. Alright, isn’t that lovely? Isn’t she pretty and pink? You’ll get the query plan back, alright, so you can get all that good information.

What’s the point of QueryStore if we’re not getting query plans? You get the current compatibility level that the query executed in, so you can figure out if you’re using an older or newer compatibility level. This is kind of good for tracking like the regressions if you change compatibility levels.

These columns are new to SQL Server 2022. If you have hints in QueryStore, if you have, if QueryStore is giving your, your query feedback, or if you have plan variants, meaning if your query is, is trying to be enhanced by the parameter sensitive plan optimization feature in SQL Server 2022, then this will tell you if there are any variants for the plan that you need to be concerned about.

If you’re forcing the plan, you’ll get that here. You’ll get the top weights for the query execution here. This is 2017 plus only, and only if you turn it on in QueryStore.

QueryStore by default will track weight stats in 2017 and up, but some folks turn it off if things get too noisy in there. Some other good columns in here to look at. First execution time, last execution time, so you know the window of time that this count executions comes from.

If your query is a frequently executing query, then we’ll track how many executions per second your query is responsible for. Again, stuff we’ll cover in other videos. I don’t, I don’t, I don’t like to make these too, too, too long.

Mainly because the nice camera that I have attached up there that makes me look so crystal clear and flawless. Overheats sometimes and turns off on its own. So I have about 30 minutes before that happens.

So now we have some metrics about the query execution. We have average duration, total duration, CPU. We go into reads, we go into writes, we go into physical reads, we go into memory.

Now, one aggravation that I have with query store, excuse me, is that the plan cache tells you how much memory the query asked for and how much it used. Query store only tells you how much it used. It does not tell you what the total memory grant for the query was.

This kind of sucks because one thing that I love trying to find is queries that asked for a lot of memory and didn’t use much of that memory. So we can start figuring out why these queries think they need as much memory as they do. But we don’t get that in query store.

There is a parameter that we’ll talk about in a later video for SP Quickie Store called expert mode, which will go to the plan cache and look for SQL handles of queries to get some in front, some plan cache information that is not available in query store. But again, we’ll talk about that down the line here. Some row count stuff, physical reads, tempdb usage, and context settings.

Context settings can be pretty useful because you might find that your applications do not use expected context settings. You may find that that inhibits the use of computed columns, filtered indexes, index views, things like that. So always good to have this here since I do most of my demo testing either with SSMS or with SQL query stress, maintained by the lovely and talented Eric EJ.

I get a pretty standard set of context settings for my queries. So that is the default out of the box what you get with SP Quickie Store. In other videos, we’ll talk about different things, other ways to use it, different ways to manipulate the output, the results, how to filter things, stuff like that.

So we’ll get to all that stuff. And you’ll love it. You’re going to love every minute of it because you love SQL Server.

You love query store. You love free stored procedures that people spend hundreds of hours writing in hopes that you’ll use them and love them. Right?

Oops. Everything except pay you to use them. Anyway, that’s SP Quickie Store. In a nutshell, we’re going to do some more videos and talk about other things that you can do with it. But this is good enough for now.

Thank you for watching. Like and subscribe if that’s your fetish. I mean, it’s my fetish when people like and subscribe to me. So you’d be doing us both a solid if you do the like and subscribe thing. And yeah, I’ll see you in the next video.

Thank you for watching. Have the best day. Thank you. you

Going Further

Capturing Query Wait Stats With sp_HumanEvents

Posted on May 12, 2023January 21, 2026 by Erik Darling

Capturing Query Wait Stats With sp_HumanEvents

Video Summary

In this video, I delve into using `sp_whoisactive` to track wait stats through a unique approach by leveraging the `SP_HumanEvents` stored procedure in a different way than usual. Instead of setting up a permanent extended event session, I demonstrate how to sample wait stats for a specific duration on the server. This method is particularly useful for quick diagnostics and performance analysis without the overhead of maintaining an ongoing session. I also take this opportunity to pay tribute to Eric EJ, highlighting his contributions to the SQL Server community, including his work with SQL Query Stress and Entity Framework improvements. The video covers various aspects of `SP_HumanEvents`, such as its output format, how it creates views for easier querying, and practical insights into interpreting wait statistics by database and query level.

Full Transcript

Erik Darling here with Darling Data. Limited. I don’t have the gall to call myself unlimited because I have some very strict limitations on things. California wine, most beer, pork chops, what else? I don’t know, some other stuff. Anyway, now that we’ve been on a dinner date together, let’s talk about how we can use SP underscore human events to track wait stats. We’re going to use the store procedure in a little bit different of a way than we have in the past few videos where we set up a more permanent extended event session and sort of looked at the data that comes into there from different things happening. This time we’re going to use it to sample the wait stats for a duration of time on the server. And since I was, so there’s a little bit of a funny, just sort of bit of coincidence in this video is a while back, the lovely, wonderful, talented Eric EJ on Twitter, who took over maintaining SQL query stress from Adam mechanic when Adam, Adam sort of open source that thing, had posted a blog post about inserts. Well, it wasn’t his post. Somebody reposted it.

Someone else wrote it and did all this stuff. But a post was about inserts and goods and integers. And there was a lot of talk about index fragmentation. And I’ve been I’ve been working up some demos to show that it’s not the index fragmentation that is a problem. And this is a sort of a precursor to those demos. This is just a test sort of proc that I have to do a bunch of singleton inserts with different data types as the clustered primary key of the table. So let’s all give a round of applause to Eric EJ. He spelled also for two reasons. One, spells his name correctly. It’s Eric with a K. And two, for doing a lot of great community work. I mean, aside from SQL query stress, he does a lot of stuff with entity framework and whatnot, trying to make that better for, well, I guess for all sorts of database folks. But you know, I mostly work with the SQL Server folks who end up using entity framework. So round of applause there. We appreciate you, Eric EJ. You are, you are, you are, you you are truly an MVP in my heart, not just a Microsoft. Anyway, let’s look at this. And let’s actually kick this off running. And while that kicks off running, we’re going to start this. And this is just going to do a bunch of inserts. And it’s going to take around a minute. Now, well, that runs. I’m going to go tell you about a couple things with SP underscore human events. One is that, when you run it for a sample like this, there are a bunch of sort of analysis queries that it spits out at the end. If you use SP human events to make more permanent extended event sessions and do stuff with that, it will also create a bunch of views. This is all in the lovely documentation on GitHub. You can take a look at that if you’re, if you’re interested in more information there. But it will also create a bunch of views that mimic the output of the session data that you see when the store procedure finishes running for a duration of time. I do that because I don’t want you to have to figure out how to query things on your own to get sort of, you know, commensurate results in different ways that you might use the store procedure.

You know, I create the thing, I wrote the thing, and I want you to be able to use the thing however you feel, however you feel comfortable and try to try to give you as many ways to make it as easy as possible to get in and access things. So when you sample weight stats and you have the extended event run, it runs for however long you put in the second sample there for, and then it kills and drops the extended event session at the end. Good, good, good.

Now, let’s look at what the results give us back. We have total weights for everything across all of the time that the session ran for, right? So this is total weights.

There, well, look at, there’s a column called total weights. So we see how many instances of the weight occurred, the duration of time that accumulated for the weights, the signal duration, and then the average milliseconds per weight. So we know, we can get a feeling for a few different things here.

One is how many times the weight happened, right? Is it a lot or is it little? How much total time that weight was responsible for in the window of time that we measured? And how long, on average, that weight lasted when it occurred, right?

Like, we don’t want, like, if a weight happened, like, two or three times, or there was a long, like, say it was a lock weight or something like that, we would want to know how long queries were waiting on locks on average during that time to figure out if we have a locking problem. If the average milliseconds per weight for the locks was fairly low, then we know that it’s probably not a locking issue.

I mean, it’s something we could dig into, but it’s probably not going to be the first thing that I look at. The second one we have down here is total weights by database. So if you have, I mean, a lot, most everyone has multiple databases on their server, so you can figure out which databases had the most weights happen.

Sometimes there is a null in the database name. That’s not my fault. That’s not me not being able to resolve a database name or something. That’s in the extended event data.

That’s me pulling directly from the XML. So if database name in there is null, then there’s really nothing I can do about that for you. So whatever.

Sorry about that. Maybe you could file a bug report with Microsoft. Maybe you’ll have better luck filing bug reports with Microsoft than I do. But you’ll get just about the same information here.

Total weights, duration, signal duration, and average milliseconds per weight. That doesn’t change a whole lot from the total weights here because I don’t have a bunch of very active databases. So everything’s going to look pretty much down here like it does up here.

But then the part that I like the most, the part that I think is the coolest, is this bottom section, which is total weights by query in database. So this is where what I do.

So what I do when I can do it is find query plans and statement text for the queries that ran and accrued the weights that happened during the window of time that you measure. So again, the total weights and stuff in here isn’t going to look terribly different from what it looks like up here just because there’s not a lot of activity on my server, just me pounding away with these inserts.

So this is all going to be the same query text and plan. It is a little bit repetitive. I try to get the SQL handles in the extended event and then go to the plan cache to look for those SQL handles in the plan cache so that I can figure out the text and the query plan for them.

So I get that additional information from the plan cache. If you’re measuring something on site like this, like for a duration of time, there’s a pretty good chance that the plans will still be in the cache by the time this thing finishes running. But I can make no guarantees that it absolutely will be.

So if we look at the statement text here, this is SP human events doing a whole thing. We don’t really need to look at that. It’s quite a bit of code in there.

We have the statement text of what ran. So this is the insert that was happening. This is all the singleton insert stuff that was going on. This is the very unimpressive query plan for all the inserts that were happening.

We don’t need to save that. But then, you know, we can see in here, let’s just go for these last bottom three rows because those are the ones that we have the weights for. We can see that these queries hit some write log weights.

The average duration was pretty quick there. We were able to push those writes through pretty quick. We hit some page latch and page latch EX weights.

Those are also fairly quick here. We did accumulate a bit of time on them, but we also did a lot of them. For this run that I did here of the insert test, we did 4,000 iterations with 100 threads running for a total of 400,000 iterations there.

So 400,000 executions of those queries produced fairly minimal weights. So our insert test, I think, was, you know, pretty efficient, right? We were able to bang a lot of inserts into that table in about a minute.

400,000 inserts a minute, pretty good, right? I wish I did that kind of business. Maybe someday, maybe someday. Although I’m not sure that there are 400,000 SQL Server customers per minute that I could get contracts signed for.

I’d be very busy. I’d probably at that point be the CEO of a large corporation with many minions doing all sorts of my biddings. Ah, man.

Got to get some more minions. Anyway, this is one way to use SP underscore human events to track weight stats. Again, you can also create a persistent extended event session to grab them. And SP human events will create views for you when you do that to grab the results that look like this as much as possible.

You know, the plan cache is a volatile place. And I can’t guarantee that the plans and everything will always be in there for you to have attached to the weights that happened. But there’s not really much I can do about to fix that.

I did think about involving query store here. But, you know, in this day and age, I still don’t run into a lot of folks who have query store turned on. And there would also be some additional complications in the code to, you know, make sure that the database has query store to go look in query store for the SQL handle.

It still might not even be in there depending on query store settings. So, you know, it’s a lot of complication for what is potentially very little reward. But maybe sometime down the line if, you know, Microsoft ever decides to have query store replace the plan cache and it is, you know, on by default for everyone in the world, which it is in SQL Server 2022.

But, you know, hard time running into a lot of people with that in production just yet. Maybe then it will be worth the code review. But, gosh, I hope I’m doing something else by then.

I hope I’m retired by the time that happens. So, anyway, that’s about all I had to say here. So, thanks for watching.

Like and subscribe if that’s the kind of thing you’re into. If not, then, I don’t know, just keep hitting F5 until a new video shows up, I guess. That’s a good use of your time.

All right. DBA is a switch from hitting F5 and SSMS on SP who is active to hitting F5 on my YouTube video playlist to see if a new video showed up. But maybe you could write some sort of program that just hits when you hit F5 in one place, it hits F5 everywhere.

It just refreshes everything. Wouldn’t that be nice? Like, yeah.

Anyway. There I go again. Anyway. Yeah. Thanks for watching. Like and subscribe. And I will be recording more videos about how I use other SQL Server community tools after this. I’m going to go through a few more of mine.

SP Quickie Store and SP Pressure Detector. And then I’ll do who is active and probably the Blitz scripts as well. Because there are a couple of few of those that just do not get enough love, affection, and attention. Anyway.

That’s enough for now. Goodbye. Have a nice day. It’s not. It’s still not Friday. I’m told. Which is very depressing. All right. Well. That’s enough for me.

That’s enough of me. Thank you.

Going Further

Capturing Query Recompilations With sp_HumanEvents

Posted on May 11, 2023January 21, 2026 by Erik Darling

Capturing Query Recompilations With sp_HumanEvents

Video Summary

In this video, I delve into the intricacies of query recompilations in SQL Server using SP_HumanEvents extended events to track them effectively. I explore why recompilations occur and how they can impact performance, especially when a query that was previously humming along suddenly starts using a poor execution plan. By understanding these recompilations, you can better diagnose issues and optimize your queries, ensuring smoother operations even in high-volume workloads. I also discuss common causes of recompilation such as schema changes, statistics updates, option recompile hints, and temporary table modifications, providing practical advice on how to address each scenario.

Full Transcript

Erik Darling here with Darling Data. And I think this will probably be my last video of the day because what I have discovered is the natural light that comes in through my window has a profound effect on the effectiveness of the green screen behind me. Contrary to popular belief, I do not live in SQL Server Management Studio land. I live in SQL Server Management Studio land. live in my office. And I just have SQL Server Management Studio as a backdrop for everything. I know there’s a green screen behind me and the later in the day it gets, the more distortion there is when I move around a little bit. And I don’t want you to have a bad watching experience on account of it being later in the day. So we’re going to make this the last one. And we’re going to talk about using SP underscore human events to track queries that are in the day. So we’re going to talk about using SP underscore human events to track queries that are recompiling on your server. Are query recompilations the biggest deal in the world? Maybe not exactly. But what I’ll tell you here is that if your query is humming along, reusing an execution plan, everything is doing great, and then something happens, you swear to me nothing has changed, but something has happened.

And all of a sudden, and all of a sudden, and all of a sudden, your query starts using a poor execution plan. Well, guess what? You might want to know why it recompiled. Because what you’re going to do is you’re going to send me an email, or you’re going to ask a question on stack exchange or stack space overflow. You’re going to say, hey, nothing changed, but all of a sudden, this query got slow. And everyone’s going to say, well, something changed, because you stopped using a query plan. It didn’t, and the recompilation could be a lot of things, right? Like a plan could get evicted from the plan cache because of memory pressure or something like that. Someone could clear out the plan cache. There’s a lot of reasons why you might find things happen. But those things would fall in our query compilations.

Recompilations, probably the most common reasons you’ll see would be from this list. Schema changing, statistics changing, you know, automatic statistics updates, or manual statistics updates during the course of your high volume, four batch requests a second volume workload. So, you know, temp tables changing. So, you know, temp tables changing. This doesn’t mean that, like, the definition of your temp tables change. What it means is that you have hit a threshold and modifications to the cached temp table that have call a SQL Server to recompile, which is an interesting one.

And then, of course, the ever-present option recompile requested, which, because I’m a bit of a lazy bones, is what I have asked queries to do, is request an option recompile via the option recompile hint. And that’s what I’m going to show you over in the extended event data. There are a lot of other reasons why you might see a recompile happen, depending on the, depending on local factors, the way that you use SQL Server, the way that the vendor has written queries to use SQL Server.

Query store stuff, obviously, that’s, you know, there’s a lot of, well, there’s a few query store things in here. Not a recompile is a funny one. But, anyway, there are a lot of things in here that you might see, but, you know, these are the most common, I think, that I see.

Probably statistics changed, temp table changed, option recompile. There are not a lot of people who are adding and dropping indexes or columns or other things like that in the middle of the day. So, maybe this one is not as common as I think.

But, anyway, this query setup will look pretty close to the query setup that I used for the compilations event that we looked at in the last video. Except this one is properly written dynamic SQL. It is parameterized.

Here, we have used sp execute SQL to pass a parameter value in on each execution. But, in this case, we have asked SQL Server quite nicely to recompile this query every single time it shows up. And, what we will see in the extended event that SP Human Events has so graciously set up for us is this data.

We see the name of the event. Very helpful. Thank you.

Extended events. We see when it happened. Already dating this video. We see the statement. And, now, since there’s an option recompile on here, it’s going to be fairly obvious what happened. But, in other cases, maybe where there’s not an option recompile, either at the statement level or at the store procedure level, it might not be so obvious.

And, then we will have the recompile cause over here. Option recompile requested. So, we’ve gotten a wealth of useful data out of this.

How we go about addressing the recompilation stuff kind of depends on the situation. There might be some queries where the option recompile is a good idea. Might be some queries where it’s not and you can get rid of it.

If the recompile cause is something that is not the fault of the query, something like schema changed or statistics changed, you might look at looking at what processes are changing tables in the middle of a workload. I imagine there might be some blocking involved.

Sorry, tables are indexes, columns, indexes, something like that. That might cause some significant blocking depending on other local factors. The statistics update thing, we might look at choosing to manually update statistics at some other interval if it’s causing a problem.

We might look at, if the temp table is changing, we might look at the keep plan or keep fixed plan query hint. And if it’s option recompile, we would just have to figure out if the recompile is a necessary hint for the query in order for it to run well. A lot of the times you’ll see the option recompile hint perhaps on reporting queries where you don’t care.

But if the option recompile hint is attached to a query just to fix parameter sniffing, like you decided you like to be like the one person who doesn’t use the local variable thing incorrectly to fix parameters. Like fix parameter sniffing, local variable, do this to fix parameter. Again, babies.

Then that might be another way around it. There are lots of things in here. If you run into one of the weirder ones, like four brows or set options changing or cursor options changing, well, you’ve got a really strange set of problems that you have to deal with. Boy, oh boy.

Don’t envy that. Anyway, before this green screen artifacting gets any worse, I’m going to end this video. Say thank you for watching. I hope you enjoyed yourself.

And I hope if you’re able to, if you have the constitution and the will and the means that you’re able to start drinking like I’m about to do. So if not, I don’t know, have a cup of tea. Eat a cookie, whatever you’re into.

Make sure it’s healthy and you have the consent of those around you. Consent and support of those around you. Anyway, have a good night.

Listen, you have to know if I can talk about something. I’ll show you. You have to see that dish half minhawhat. 공vakia now. There’s three minutes where I can think of this60нос around you. So let’s get started.

Going Further

Capturing Query Compilations With sp_HumanEvents

Posted on May 10, 2023January 21, 2026 by Erik Darling

Capturing Query Compilations With sp_HumanEvents

Video Summary

In this video, I delve into the issue of frequent query compilations using my go-to stored procedure, `SP_human_events`, to help troubleshoot various SQL Server problems. We explore how to identify queries that are compiled frequently and discuss why this can be a significant issue, even if it might not seem like the biggest problem at first glance. I walk through setting up an extended event session with specific parameters to track query compilations in real-time, demonstrating how to interpret the results to pinpoint problematic queries. This video is part of a series where we cover different tools and techniques for diagnosing SQL Server issues, including recompiles and wait stats, ensuring you have a comprehensive toolkit for optimizing your database performance.

Full Transcript

Erik Darling here with Darling Data and I have tried to start recording this video no fewer than five times before being hit with a sneezing fit. So if I get through this one, it’ll be nothing short of a miracle. This video is a continuation of this series of videos where I’m talking about how you can use my amazing, the most rockinest, shockinest, hip hopinest store procedure on the planet. So we’re going to be looking at the planet. SP underscore human events. To troubleshoot various SQL Server problems, we’ve talked about using it to troubleshoot blocking, we’ve talked about using it to troubleshoot query performance issues. This is going to be a short one to look at finding queries that are compiled frequently. Now, query compilations may not be the biggest problem on your system, but a lot of the systems that I look at, they are not going to be the biggest problem on your system, but a lot of the systems that I look at, they are not going to be the biggest problem on your system. more than a few times they’ve ended up being a pretty big issue.

You look at a query performance counter like batch requests a second, and then another query performance counter like compilations a second, and you might find that the number of compilations a second pretty closely follows batch requests a second.

That means that SQL Server is just constantly sitting there coming up with brand new query plans for queries that could be parameterized. Now, I know a lot of you out here are going to hear parameterized queries and have a mild freakout because you’re afraid of parameter sniffing, and God, I wish you’d stop being such babies about it.

But here we are, you being big babies, afraid of a sniff parameter, like there’s no way to fix parameter sniffing in the world, and not solving systemic workload problems.

like frequently compiling queries, not compilating, that’s my mistake. Frequently compiling queries when you could, because you don’t want SQL Server just sitting there coming up with query plans constantly every time a batch compiles.

It’s not a good feeling. So we’ve talked about a few other ways that SP underscore, human events, can help you troubleshoot other issues.

We looked at blocking, we looked at query performance. We’re going to look at compilations now. We will most likely look at recompiles next, and we will look at wait stats for the final installment in this video, where I’m going to be talking about different ways that I utilize SQL Server community tools when I am helping clients with their problems.

So I’ve already kind of done some of the legwork here, so that you don’t have to sit there and watch me hit F5 and wait for stuff to happen. So I’ve set up this extended event, and I’ve used this extended event with the keep alive parameter just to have a persistent session going.

And over in this window, I have set up a query to execute. And when this query executes, every single time, it will receive a new literal value. And this new literal value, because we are not getting simple parameterization for this query, every time this thing executes, it will compile a new query plan, which for this query is not that big a deal, because it’s not very big, it’s not very complex.

But at the same time, it’s an easy demo. And you’re going to have to suspend disbelief a little bit and live with an easy demo that shows you how this thing works and what you might want to do to fix it.

All right. So thanks. Thanks for helping me with that. So this just runs in a simple loop. Doing this little bit of…

I mean, this isn’t dynamic SQL exactly. This is like the bad kind of dynamic SQL that you don’t want to be doing, and this will definitely cause frequent query compilation.

Because every time this query runs, SQL Server will see a different literal value and say, Ooh, a brand new query. Have a brand new query plan.

Even though probably every query plan is exactly the same, because we’re just going to seek to a specific value in the clustered index. But nevertheless, SQL Server is just coming up with new plans. All the darn time for this thing.

So what does our extended event show us? Well, great question, because I’m about to show you. So some newer versions of SQL Server have an event called query parameterization data, which will give you some additional feedback about if the query has literal values, if the query could be parameterized via force parameterization, and if it’s a recompile event.

Now, I know I’m skipping ahead a tiny little bit here. Again, you’ll have to forgive me. And we will see that this query…

Let’s zoom in here. Let’s have a lot of fun. This query has literal values, is parameterizable, and is not recompiled. So this is not a recompilation event.

This is a compilation event. This is SQL Server thinking that it has discovered a brand new query and giving us a brand new query plan to go along with it, which is, I’m going to be honest with you, quite strange, because we really don’t need a brand new query plan for this, do we?

We’re just seeking to a single value in a clustered primary key. Why on earth would we need a new query plan for that? That seems weird there, SQL Server.

But here we are trying to troubleshoot this issue. The stock and standard extended event that comes along with this one on all versions of SQL Server, newer versions of SQL Server, I think 2016 or something plus, have a query parameterization data event in there.

The SQL statement post-compile event is in all of them. And that will just say, is a recompile false, and recompile cause not a recompile.

So very helpful stuff there. I’m glad I chose to show those two columns in the output. That’s smart of me. But this is one way that we can track down queries that are compiling rather than using an existing plan.

Well, we can track down queries that are compiling and sort of figure out if they’re ones that could be using an existing plan. It would be helpful if I put this statement for these things in here, wouldn’t it?

Oh, hey, look. We found some other things that are showing up in here. That’s fun. There we go.

Those are the ones I wanted to show you. This is stuff that happened afterwards because I was a ding-dong and I didn’t stop the session. So these ones in here, please, once again, suspend disbelief.

Ignore these ones a little bit. There we go. We can just cut those off. I feel better about that now. So every time this query came in, it gave us a brand new query plan.

It compiled as a brand new query. All 10 of these things, SQL Server said, oh, this number. What could I possibly do with this number? Brand new query plan for all of you.

And that’s not great. Now, the query parameterization data event also has this SQL text field. But for the query that I wrote to generate this, it is probably a bit less useful than you would find it to be either in the batch or store procedure where the compilation event is happening.

You would probably see a lot of dynamic SQL being generated, the bad kind of dynamic SQL that’s not parameterized doing this stuff. You might catch queries from that third-party vendor, which you might be that third-party vendor, so welcome to the party, pal, that are not parameterized in the application or wherever your queries may originate.

I try not to think too hard about that because my brain would turn to a puddle of mush and drip out my ears.

That just wouldn’t be fun for anyone. So this is a good way to track that stuff down. The forced parameterization setting can help in some cases where it wouldn’t help where a query is partially parameterized and there are some literal values.

You’re kind of hosed there. Forced parameterization won’t fix those. But if you’re lucky enough to have a bunch of queries that have no parameters attached to them and forced parameterization is not turned on for your database, and this could be a good way to fix it.

Otherwise, it would either be you as the, well, would be first or second part. I’m still not clear on what a second-party vendor would be. First party is you made it.

Third party is someone else made it. Second party, I don’t know. Someone made it for you maybe? I don’t know how to interpret that. And God help us if we ever figure out that there’s a fourth party involved.

So, man, there’s a lot going on here. Anyway, if you look at your SQL Server and you see that compilations are a, compilations per second is a significant portion of batch requests per second, then you may want to think about running this to see what kind of queries are causing those compiles and then come up with a reasonable solution either with the database level forced parameterization setting, yelling at developers either first, second, third, maybe even fourth or fifth party to properly parameterize their queries or, um, I don’t know.

Just shut SQL Server down. See what happens. Maybe no one will complain. Maybe everyone will say, cool, the server’s down. I’m going to go outside and enjoy myself.

I’m going to touch some grass, as my friend Arthur likes to say. Anyway, thanks for watching. Again, next video, we’ll look at recompiles or recompilations, whatever you’re into.

Video after that, we’ll look at weight stats and, um, I don’t know. From there, we’ll go on and look at a different, a different tool that I think is good to use for troubleshooting SQL Server stuff.

Thanks for watching. I appreciate you.

Going Further

Capturing Query Performance Data With sp_HumanEvents

Posted on May 9, 2023January 21, 2026 by Erik Darling

Capturing Query Performance Data With sp_HumanEvents

Video Summary

In this video, I delve into using SP_HumanEvents to capture extended event information in SQL Server, specifically focusing on query performance data. I walk through setting up an extended event to track queries that run for over five seconds and demonstrate how to filter these events based on session IDs or specific stored procedures. The goal is to help you identify and tune problematic queries without overwhelming your system with unnecessary details. I also discuss the limitations of SP_HumanEvents, such as the lack of filtering in the actual execution plan view within SSMS, and highlight its usefulness for performance troubleshooting by providing detailed query plans post-execution.

Full Transcript

Erik Darling here with Darling Data. And this is going to be another video where I talk about how to capture different extended event information using SP underscore human events. I want to get a cool echo going, but I don’t quite have those audio engineering skills. So in the last video I talked a little bit about what SP human events is. is four, which is basically to make capturing performance related human events stuff easier. So that focuses on things like query performance, weight stats, blocking, compiles and recompiles, right? All sort of good things to be able to capture for your workload. I apologize for the fuzz over here. For some reason, my setup is acting a little weird today and I can’t quite figure out why the lights are being awkward on me. So anyway, there are all sorts of different ways to capture query performance data, including actual execution plans. So again, don’t run this thing for too, too long. Or if you’re going to run it for a while, make sure it’s in like a development, on a development server or stuff like that. But yeah, there are all sorts of different ways to do it. So for example, this execution would track query performance, it would capture any query that runs for over five seconds, and it would run for 20 seconds. If you’re on a really busy server, and you don’t want to capture absolutely everything, you could do that same setup, but also add this sample divisor here, which sort of acts like a little bit of a fizzbuzz thing. So you would only capture queries for with spids that are divisible by being modulo 5 equals zero. Another thing that you can do is if you’re interested in particularly interested in a single store procedure, you can do this, and you can track any execution of a particular store procedure. Now, what I find myself doing most often is sitting on a dev server, like doing client work, I find myself most often sitting on a dev server, with you know, just an SSMS tab open. And I want to find a particular I want to monitor my session and my session only.

for long running queries from, you know, whatever, right. So I find myself doing this is particularly useful for long store procedures where a lot of tiny little noisy things happen. And like, if you collect the actual execution plan for everything, that that sort of procedure executes, you’re gonna like crash SSMS, right? It’s like, it’s cool that you can turn on actual execution plans for a whole store procedure. But like, there’s no way to filter that like, it would be great if you could, like, when you clicked on, collect actual execution plan, there was like one of those little arrow drop downs, where you could like filter that stuff out and be like, look, I don’t care about things that run in like under five seconds, just show me the bad stuff, right? Because a lot of store procedures do a lot of tiny little work. If there’s like loops and stuff like that, you end up capturing just a bunch of nonsense that you’re never going to care about. And then you end up sifting through it all trying to figure out what you what’s good and what’s not. Um, uh, see, uh, the, well, God, I wish I wish it was still the SQL century plan explorer, not say the name of the company who owns plan explorer now.

Uh, but, uh, that does make, uh, you know, dealing with a long, big long store procedure execution plans easier, it breaks it down statement by statement. So it’s a bit easier to navigate in there. But, um, you know, uh, there’s also no filtering in there. So you can be a lot of scrolling through stuff looking for things that have high CPU and duration. Uh, there are a number of downsides to the product at this point. Um, uh, they haven’t yet added, uh, the per operator timing that SSMS shows you.

So it’s a little bit more work to, uh, get anything useful out of it. Now, um, I had this query run over here, uh, ran for about 13 seconds. Uh, let’s see if I can see if I can do this zoom it justice here. Uh, which way am I going this way? Oh, a little bit. There we go. You can see the 13 seconds right there.

You can also see exactly what time it is. So that’s a little freaky. But anyway, uh, this window did not capture an actual execution plan. Uh, just pretend this is a big store procedure. All right. Do me, do me a favor and go along with my fantasy where this is a big long store procedure, but this is the only query in it that stinks. If we, uh, come over to, uh, this window, which is where, uh, I have, uh, pulled up the data that my extended event collected. That’s this one here.

I turned it off just so I don’t forget to turn it off later and, you know, bog my own server down. Uh, but it was capturing from, uh, I’m looking at live data from this extended event. Uh, this is what, um, I had, what columns I’ve decided to show, uh, from the actual, from the extended event.

Uh, you get the query, uh, post execution plan, the SQL statement, timestamp, uh, duration, CPU time, uh, granted memory. It’s, it’s a little tough with extended events because not every event captures all of the same query metrics, which is a little, a little disappointing. Like, you know, you capture memory grants with this one, but not with this one.

Uh, also all the times, like you are in like microseconds. So like you have to do a lot of math in your head, you know, KB to GB is not like exactly my strongest suit as far as, you know, um, figuring out, um, uh, you know, like doing that conversion mentally. So anyway, uh, you get the point.

So there are, there are two things here. Uh, there’s the statement that finished, um, if this were coming from a store procedure, you’d have a lot of other information in here. But again, I mostly use this just to monitor a single speed for whatever it’s getting up to and like filtering that date, that data down to, uh, execution times that I care about. Right.

So like, you know, you might start at like, you know, 20 seconds or 10 seconds and then, you know, tune those queries, then move down to like five seconds or two seconds and tune those queries if you can. Uh, so all that. But the cool thing is for the, uh, post execution plan event, uh, you get a little tab over here for the query plan. And if you click on that, you’ll see what the query did, right?

Uh, we had a whole bunch of, uh, crazy stuff going on. So this, uh, just, uh, this just about confirms what SSMS told us. It ran for just a hair under 14 seconds.

And if we look through the query plan, we can see which operators, uh, had the most execution time associated with them. Uh, we have, uh, well, about a second and a half there. Uh, that adds up to a six seconds there for five seconds there.

Uh, we hit 13 seconds at this point and then another second over here. So, uh, that, that, that tells us, you know, which areas you might want to focus on. Maybe there’s some indexes we could add.

Maybe we could write the query a little bit better. Um, you know, things like that. Uh, this isn’t a query tuning video. This is just teaching you how to capture crappy queries. So you can tune them or you can pay me to tune them.

I do do that for a living. I’m told. Occasionally, I guess. So, uh, this is all the, the, the stuff that, uh, you can find from the actual execution plan. Again, newer versions of SQL Server, newer versions of SSMS.

Really handy to have these operator times in there. Uh, there are some weird caveats to the operator times thing. Uh, one of them specifically is, and this is probably a pretty good, um, probably a pretty good place to, uh, explain it a little bit. Um, each, so all of these operators in the, this section of the query plan, except for the nested loops join executed in batch mode.

So all of these three operators here, the timing that you see associated with them are individual. If this were a row mode plan, timing would be cumulative going from right to left. You would see, um, uh, all of, like, you would, like, uh, for a row mode operator, it collects, it, like, accumulates all of the time that its child operators used.

That’s a little more obvious when you get to the nested loops join that, that doesn’t support batch mode. I wish it did. Batch mode nested loops would be amazing.

But, um, this one does not support, uh, batch mode. So this runs in row mode. So this nested loops join does effectively add up all the operator times from the clustered index scan here, the clustered index scan here, and the hash match aggregate with the little spilly doodad here.

So, uh, this is usually how I use SPHumanEvents to, uh, help me with my performance troubleshooting. I find it very useful. Um, I use it quite a bit.

I hope you do, too. Um, there will be links in the video notes to the GitHub repo and all that good stuff. The readme over there has a whole bunch of information about how to execute it, how to use it, all that other stuff. But, you know, if you do use it, you hit any problems, you have any questions, anything like that, feel free to also use the GitHub repo to report those things, ask those questions, and all that good stuff.

Uh, thank you for watching. I’m going to be recording some, uh, other videos about how to use SPHumanEvents. So, uh, hope you, hope you tune into those, hope you watch those, hope you like those, and, um, I will, we’ll see you in that next video.

Like and subscribe and all that, right? Click the little bell for me. Thanks for watching.

Going Further

Capturing and Analyzing Blocking with sp_HumanEvents

Posted on May 8, 2023January 21, 2026 by Erik Darling

Capturing and Analyzing Blocking with sp_HumanEvents

Video Summary

In this video, I delve into how to log and analyze blocking using a couple of stored procedures that I developed from scratch—sp_human_events and sp_human_events_block_viewer. These tools are designed to make it easier for SQL Server users to set up important human events for different performance issues such as query performance, weight stats, blocking, compiles, and recompiles. The focus is on capturing these events over a specified duration or using the keep alive parameter to continuously monitor potential issues without causing unnecessary overhead. I walk you through setting up the blocked process report and demonstrate how to use sp_human_events_block_viewer to view detailed results from extended events, providing insights into blocking queries, wait times, isolation levels, and more. The full code is available on my GitHub gist, along with necessary scripts, and I encourage viewers to explore and contribute to improve these tools for everyone’s benefit.

Full Transcript

Erik Darling here with Darling Data Enterprise Edition. And today I’m going to show you how I log and analyze blocking using a couple of stored procedures that I wrote all by myself from scratch. The first one is called sp underscore human events. I wrote that one to make it easier for SQL Server users to set up important human events for different sort of performance issues. It does like a query stuff, weight stats, blocking, compiles, and recompiles. Those are the sort of important ones. The most common ones that I end up troubleshooting, so that’s what I geared it towards. I don’t really have any plans to add more because I don’t really end up troubleshooting more than that. I don’t care about your AG failover. personally. I don’t care about AG’s personally. So I’m going to show you how I do that. And all of the code from this section here, which is all you need, will be available at this GitHub gist. I don’t expect you to copy this URL from the screen. I’m going to include it in the video description. And I’m also going to link to the the necessary scripts in my GitHub repo. Of course, if you find any issues, you need support, you hit errors, you have problems, you have questions, that GitHub repo is also the best place to go and ask there. Alright, so let’s get down to it. I have this, these couple commands right here that run sp configure so that we can turn on the blocked process report. The block process report is what drives information being fed into the extended event that we’re talking about. So I would, I already set this up a little bit, so I don’t have to do a bunch of goofy crap on camera. But if I run this, and this sets up the blocking event type, and it also uses a parameter that I like called keep alive. So sp human events can do one of two things. We can either capture stuff from the stuff that I mentioned before, query performance, weight stats, blocking compiles and recompiles.

For a specified duration of time. Or you can use this keep alive parameter to set up a session that just keeps on running. So if you’re not sure when to expect something happening, that can be a good idea. I wouldn’t suggest leaving a lot of these on for a long time. Because extended events, you know, they can, they can cause some observer overhead. I know that Microsoft has taken some steps to alleviate that. But especially query performance ones, I’m capturing actual execution. plans. Because a lot of the times, because a lot of the times, that’s what I need to do. So I wouldn’t suggest leaving those running forever and ever. Could be, could be detrimental. But the blocking one is pretty straightforward and easy. So I already have this set up. If you come over here, and you look at all this stupid stuff I have on my server, and we go into management, and we go into extended events, and hopefully I don’t have anything nefarious, or weird, and these, in my extended event names. But if we come on, oh boy, zoom it just like, got weird on me. Alright, there. So I have one to capture deadlocks. I actually have a duplicative one up here. I guess I could have used that instead. I didn’t look first. But I have this keeper underscore human events underscore blocking session. That’s the default name that SP human events will set up for a blocking session.

that is being kept alive. And I’ve got a couple queries over in these windows. I just did a simple update for one row to modify the age column. And I can get rid of that because I’m done with that. And I had one query over here that was just doing a select star from users looking for that same user ID, so that it would get blocked under the default isolation level for every version of SQL SQL Server, other than Azure SQL DB, I guess. You have to be good. You have to be able to say something good about the cloud in these videos, right? Well, anyway, so SP human events does use the ring buffer as a target. It does not use file targets. I decided to use that because it seemed a little bit easier to manage in code. I know that Jonathan I think the highest, the man who always has great weekends, that is not the biggest fan of the ring buffer, nor am I, but it’s just a design trade off that I decided to make.

So anyway, I let that blocking situation proceed for about five seconds. And then I ran SP human events block viewer. And I got these results. Now, some of this, some of the details that come back are sort of at the mercy of what the block process report captures. For example, we get the event time, we get the database name, and a lot of the times this contentious object will resolve to the table or whatever that the blocking was taking place on. In this case, we got a really weird object ID back. It just doesn’t make a whole lot of sense. But we see in these lines here, who did the blocking, who got blocked, the SPIDs. I know that a lot of this stuff doesn’t exactly help you troubleshoot the blocking problem. I just wanted to get as much of the information out of the block process report as I could.

Like ECID, I don’t think I’ve ever actually used this to troubleshoot a blocking problem, but it’s in there, so I get it out for you. Just in case you might need it at some point. We get the query text of the blocking query in the blockhead query. You can see those there. Those are the queries that I just showed you in the other window. We get the total wait time, the status, the isolation level, the lock mode. Note that the query that does the blocking, I don’t think I’ve ever seen lock mode get populated there.

I get out the SQL handles because I want you to be able, if you need to, to track down the query plans for the queries that were involved in the blocking. So you’ll have both the, these lines are, I mean, they’re a little bit duplicative, but we have the SQL handle for both the blocking. You see the comma there? That’s both SQL handles for the blocked and blocking query.

If there’s a procedure name in there, I do try to resolve that as well. So transaction count, the transaction name. One thing that shows up in here a lot that can be really useful is implicit transactions, where, you know, you’ll like, you know, certain tools like the JDBC driver, the Python driver, use implicit transactions by default. You have to explicitly turn those off and like, not in the connection string, but like in the connection code kind of.

We get when the transaction started, completed. We have all the client options in here. Sometimes client options can lend some insight into, you know, just like, like a, like a weird setting, but these are both coming from SQL Server Management Studio. So there’s not really any weird settings.

We get the wait resource. If there’s a priority set, if there’s log used, stuff like that. We get the client app, host name, login name, transaction ID, which again, isn’t terribly useful, but it’s in there. And then over here, I give you the full block process report.

So we look through here, like this object ID is obviously nonsense. So, you know, whatever. This index ID of 256, I’m not sure.

That’s entirely accurate, but there we have it. And then coming down through some of the other stuff. So SQL Server 2022 added a lot of these stacks in here.

These are probably not terribly useful for query blocking, at least that I’ve ever seen. They’re probably more useful if you have like, you know, like background process blocking where you could really trace some of these call stacks to something a little bit more useful. And then we have the query that got blocked.

We have the SQL handles that I was talking about and the query that was doing the blocking down here. All right. So we got all that stuff.

And that can be really useful for figuring out what was blocking, you know, what your problem, what the problem was, all that other good stuff. And the bottom section kind of gives you a breakdown of all the like all the blocking that has happened in the database that was captured that is currently in the ring buffer extended event. So a couple in here that I think are particularly useful for the scenario that we’re looking at are lines four and five right here.

And these will tell you if you have blocking involving selects. So under the default isolation level read committed, write queries can block modification queries and modification queries can block select queries. And unless you, I mean, let’s litter all your all your select queries with no lockends and whatnot, which, you know, is not a good idea.

Please don’t do it. I would much prefer that you need use a good old fashioned optimistic isolation level. So multi version concurrency control like using read committed snapshot isolation.

And the second one is that there is a sleeping query doing the blocking. If you have queries that are regularly sleeping and still blocking, there is often signals some sort of application error where you are not closing out connections when you should. This will also warn you about implicit transactions.

A lot of other stuff. There’s a ton of checks in there for various like different things that are weird. Like if you have oddball isolation levels like repeatable read or serializable getting involved, you know, it can be a good idea to warn about those because at least I want to say with entity framework, when you use the transaction scope, I don’t know, is it method or whatever? That sets a pretty strict isolation level.

I can’t remember if it’s repeatable read or serializable by default, but it’s one of those. So it’ll give you all that information. It’ll show you all that good stuff.

And this, I don’t know, I use this quite regularly with client work to try to help me, you know, be able to troubleshoot blocking issues and all that. And I don’t know, that’s about it there. You know, it’ll tell you how much blocking there is and, you know, for the entire database and then per object and all that other good stuff.

So, you know, pretty good. All right. Pretty good.

All right. Cool. So I’m going to keep recording some more of these videos. This is going to be pretty short. I’ll probably be sort of repetitive because I don’t know who’s going to watch what when. You might find one video and watch that. You might find, you might watch a whole series of videos.

I don’t know how it’s going to work. I don’t know. I don’t know what you’re going to do with your life or your day or your great weekend. But anyway, I will catch you in the next video. Again, please use this stuff.

I spent a lot of time writing it, not just for me to benefit, but for everyone to benefit. It’s open source hippie stuff. So, yeah. Enjoy. Use it.

Report errors to me on GitHub. Good stuff. All right. Thank you for watching. Goodbye.

Going Further

A Little About Memory Grants In SQL Server Query Plans

Posted on May 5, 2023January 21, 2026 by Erik Darling

A Little About Memory Grants In SQL Server Query Plans

Video Summary

In this video, I delve into memory grants and SQL Server execution plans, explaining how they work and how operators can share memory within a query plan. I also discuss how SQL Server decides on the size of a memory grant for a specific query, focusing on sorts and the impact of string data types, especially those with large maximum lengths like `VARCHAR(MAX)`. By running through various examples and queries, I highlight how memory grants are calculated based on row count and estimated data size, and how they can vary depending on join algorithms. Additionally, I touch on parallel query execution and how memory grants are adjusted for such scenarios.

Full Transcript

Erik Darling here with Darling Data, the most widely used SQL Server consultancy in four towns. What, what, you’re not impressed by that? I don’t know what else to give you. This video is going to be focusing on memory grants and SQL Server execution plans. And a little bit about how they work, a little bit how operators are able to share memory within a query plan, and how SQL Server decides on the size of a memory grant for a specific, for a given query. Now, this is not going to cover memory grant feedback or any of that other stuff. This is just going to cover how SQL Server, out of the box, decides on a memory grant. So we have query plans turned on because, believe it or not, this is not my first take recording this.

Something stupid happened, and I had to start over again. Namely, my printer, which is apparently possessed, just started making all sorts of weird noises, and I found it distracting, and I found it detracted from the, the normally very high quality of my recordings. So, let’s run this query, which is just selecting the top 1000 IDs from the users table ordered by reputation, an ascending order because we have not specifically asked for descending, and of course, every sane database in the world defaults to an ascending sort when no direction is specified. So, let’s run this query, and let’s look at the execution plan. We have a finely ordered result set. Amazing.

Now, since we don’t have an index that leads with the reputation column at the moment, if you watched the last video, which I hope you did because you like all my videos and you subscribe to my YouTube channel, the thumb and the bell button, we don’t have that index anymore. I got rid of the execution plan. But if you look at the execution plan, we have a query plan. And because we have a sort in the query plan, SQL Server needed to ask for some scratch base in order to write the results that we’re selecting down in the order that we are asking for, the presentation layer order that we are asking for in the query.

That means we got a 182 meg memory grant to sort that ID column by that reputation column. Because that’s all we did. If we look at this arrow going into the sort, the estimated row size is 15 bytes, the estimated data size is 35 bytes. And since we’re selecting a whole bunch of rows, well, since we’re, you know, trying to get the top 1000 rows out, SQL Server does some multiplication and says, all right, 182 megs, that should do it.

Should set the boat straight. It does. We don’t spill. We don’t get a warning that we use too much memory. So apparently, this memory grant was Goldilocks, right in the middle, perfect memory grant for this query. If I run this query, where I join one result set of that select top 1000 to another, the memory grant here isn’t going to double.

And it’s not going to double because SQL Server is going to choose a hash join algorithm to make this query to join those two results together. Because we have the hash join in the query, SQL Server is able to share memory from one sort operator to another. So this portion of the query plan goes, gets all the rows, inserts them into the hash table that SQL Server creates to implement the hash join.

And then when this portion of the query plan starts probing in down here to get the rows that we care about, then this portion of the query plan runs and this sort uses memory that this sort operator gave back. So if we look at the overall memory grant for this one, it only went up by about a meg.

And it only went up by about a meg because that hash, so apart from sorting data, hashes also require memory to do their thing, to create their hash table and do some work. So this only goes up by about a meg.

So we know that those two sorts shared memory because neither one spilled, neither one seems particularly starved for memory. And again, we don’t have any warnings that we asked for too much memory.

Again, this is a pretty good Goldilocks zone grant for this query. If I change the query a little bit and I force a loop join, rather than letting SQL Server’s cost-based optimizer decide on the join algorithm, then the query plan in this one will show a memory grant that just about doubles here.

Actually, I think it exactly doubles here, if I’m remembering correctly. Rather than the hash join, we have the nested loops join. And if we look at the memory grant for this thing, we have a 364 meg memory grant, which is 182 times 2.

So I think anyway, at least from… Ran out of fingers at 5, 10? I don’t know. Thumbs are fingers.

Hot dogs are sandwiches. The world’s a strange place. But because the nested loops join operator doesn’t offer what my dear friend Paul would call any phase separation in the query plan, it’s all sort of streaming, then that memory grant can’t be shared.

And we need twice as much memory for each side of the query to do its sorting. So we’ve learned so far. SQL Server, to calculate a memory grant, looks at the number of rows and the size of those rows in order to figure out how much memory to be granted.

It also has to decide the memory grant on how many memory consumer… How many memory consumerating? Wow!

What a time to be alive. How many memory consuming operators are in the plan? And if memory can be shared across different portions of the query plan based on the operators that get chosen in there.

Now, where things get more interesting, I think, is when we start looking at string data. Now, you’ll notice that I have some numbers listed over here.

This should be 182. Don’t tell anyone. Forgot to update my entire script. But if we look at this query, the memory grant for this query, Now, these numbers are cumulative.

So if I just did web display name or website URL or location, we would get different memory grants. But if we sort of just quote columns in until we get down to about me, which is a max column and varchar max, then this will all lead up to about a 9 meg memory grant.

Some of the other memory grants in compat level and 160 have been inflating a little bit on me. So these numbers might be off by a little bit, but they are going to be large enough for you to get the general idea.

And of course, this one went up by quite a bit. This went up 14 gigabyte memory grant to sort this result set. The reason why we get a 14 gig memory grant here is because of the way that results get sorted by SQL Server.

Now, the easiest way for you to visualize this is to picture an Excel file, right? And that top left-hand corner of the Excel file, you have that magic button that highlights all of the rows and columns in the sheet.

And then you have the little sort button up at the top, and you hit that, and you can choose a column to sort by. And unless you are a deranged person, and you don’t choose to sort your entire spreadsheet by that column, if you just sort one column independently, you’ve ruined your spreadsheet.

If you sort all of the columns by that column, you’re probably in okay shape. But what happens is the entire spreadsheet, all the columns and whatever, and the spreadsheet will flip to match the sort order of the column that you chose.

That’s a lot like what SQL Server has to do when you ask for ordered data without a supporting index. You have to sort, you have to write down in sorted order by whatever column or columns you’re choosing to order by all of the columns that you are selecting.

So you write down reputation in order, but you also need to write down the rest of the attached row in that order as well. So all of these columns, all of the data from these columns is being sorted along with reputation.

That’s why the memory grants for string columns, especially big max data types, can get so wild and out of control is because SQL Server, when it needs to figure out string columns, it guesses that they’re all going to be half full.

So if you have a, let’s to make it easy, let’s just say you have a VARCAR 100 column, SQL Server is going to guess that every single row in that column is 50 bytes full.

So it’s going to guess at the half full mark because some might have less or fewer bytes in them. Some might have more bytes in them. And so it does that to sort of get right in the middle and just sort of give itself some fudge room on either side of that.

So if you have like a VARCAR 1000 column and everything in there is like a state code, like M-A-N-Y-C-T-N-J, other northeastern states, SQL Server is going to guess that they are all 500 bytes full even though you never cross like the two byte mark.

So that’s real depressing. And VARCAR columns, Unicode columns, of course, since they’re double byte stored, it’s just basically going to be whatever, whatever size you choose for the column.

SQL Server is going to guess half of, like half of that. So if you have like a, like in VARCAR 200, it’s going to guess, I guess it’s still like 200, but anyway.

String columns were a mistake. We should just have numeric codes for everything and translate everything into binary and never have to deal with this string. Now, one thing that, important thing that I want to point out is that memory grants are not, for parallel queries, are not the grant times whatever the degree of parallelism for the query.

The memory grant is divided evenly amongst parallel threads. The reason for that, of course, is that every plan starts off as a serial plan. Even if the cost is, like SQL Server has a bunch of like initial costing things to figure out the initial cost of the query.

But the initial plan that you’re going to get every single time is a serial plan. SQL Server only starts exploring parallel plans if you have a cost that breaks your cost threshold for parallelism, which I’m going to have a video about that soon, and related to that soon.

And if there is nothing in your query that would inhibit parallelism in any way, non-inlineable scalar UDFs, inserts into table variables, things like that.

Those are the two most common ones. Different cursor options will do it too. But if we run this query at max.8, we will see that the memory grant for this query does not get multiplied by 8 because that would be insane.

It gets divided by 8. So what you get is a memory grant of about 197 megs. Now, this goes up a little bit from before. It was like 182, 183 before.

Just because we do have some hashing in here. We have some sorting in here. And there’s some parallelism going on. So things do go up a little bit, but they don’t get multiplied by 8.

So what SQL Server does is it takes that initial serial plan, comes up with a memory grant for that. That’s why in the query plan properties, you will see under the memory grant info, you will have the serial desired memory, which is 188.032, which just about would have been what we got for the 183 plan.

Divide by 1024. That should get you pretty close to 183. So SQL Server comes up with the serial plan, a memory grant for the serial plan.

And then if a parallel plan is chosen down the line in the optimization process, the memory grant will get divided evenly amongst the number of threads or the degree of parallelism for the plan.

That is a little bit about how SQL Server comes up with memory grants for plans based on the size of the data that is getting passed into memory consuming operators and other stuff like that.

This video mostly focused on sorts because sorts are the operators that I see responsible for gobbling up the most memory. Bad estimates for the number of rows can over inflate memory grants.

Bad estimates for the size of data. Again, this comes back to like how you designed your schema. If you, you know, again, VARCAR 1000 column for, you know, very small bytes of data, you know, like one, even if everything was VARCAR, even if like you had a VARCAR 1000 and there were only under 100 bytes in there, you’ll be overestimating that by 400 bytes a row.

So the worse your estimates are for the number of rows, the worse your sort of schema design in for how big a data type you have assigned to your string columns, sort of the worse those memory grants can get on the high side.

And of course that sucks because if you’re, you know, if your server isn’t set up from a hardware perspective to, you know, cache like, you know, your, like your, like most important data in memory and also service queries, then those big memory grants are going to steal space away from the buffer pool where SQL Server stores all those precious data pages.

And it’s going to, you know, knock those back out to disk. And, you know, it’s just going to make things worse. You know, weird variable performance issues in there because, you know, sometimes you’ll be going to disk, sometimes not.

And of course you can hit issues with, you know, query, like, like, you know, like just like you can run out of threads and hit thread pool weights, you can run out of available query memory and hit resource semaphore weights.

So we’re going to, we’re going to talk more about that when we talk about how I use SP pressure detector. I’m going to do some videos on that shortly, sometime this week. And then, you know, that’ll be fun.

Yeah, more stuff. But again, kind of calling back to the last video, there are times when you can create indexes that put data in the order you care about so that when you choose your order by columns, then you can have data physically sorted by the, or logically sorted by the index ahead of time so that you don’t need to ask for memory for separate scratch space to write all that stuff down.

But that was the last video. Go watch that one. Anyway, that’s about all I have to say about this. I forget what the next video is going to be about. We’ll figure that out in a minute. But anyway, as usual, hope you enjoyed yourselves.

Hope you learned something. And, you know, do the old like and subscribe. It’s the thumbs up and the bell. So, appreciate that. Anyway, I’m going to go figure out what’s next. Thank you for watching.

Going Further

A Little About How Indexes Store Data In SQL Server

Posted on May 4, 2023January 21, 2026 by Erik Darling

A Little About How Indexes Store Data In SQL Server

Video Summary

In this video, I delve into how indexes store and sort data in SQL Server, focusing on B-tree rowstore indexes since they are the most commonly used type. I explain that creating indexes helps speed up searches by organizing data in a way that makes locating specific records more efficient. The video walks through visualizing index storage with query results to illustrate how data is sorted within an index and why this matters for query performance. I also touch on inequality predicates, which can cause sorting issues when crossing boundaries between index values, impacting the need for sorts in query plans.

Additionally, I discuss the implications of sorts on memory grants, noting that they can lead to large memory requests or spills to disk, potentially affecting parameter sniffing and overall query execution efficiency. The video aims to provide insights into why proper indexing is crucial for optimizing SQL Server performance and sets up the next segment by previewing upcoming discussions on memory grants and their impact on query plans.

Full Transcript

Erik Darling here with Darling Data, the most sought after SQL Server consultancy on the planet and known universe. And I’m re-recording this video because it was pointed out to me by the ever vigilant Randolph West that my audio sync was off quite a bit in the last one. I’m not sure why. I recorded the isolation level video right before this one. Everything was fine. Walked away for a minute, came back, let the other video upload and start processing on YouTube. And this one was a disaster area. So we’re going to do this over again because, you know, I guess if I’m going to be pitching these videos as a reason why you should either, you know, buy my training or, you know, purchase consulting from me, I should not look like a badly dubbed Kung Fu movie when I do it. So, um, that’s fun. Anyway, uh, this video is going to talk about how indexes store and sort data because of course the reason we create indexes is that they put data in an order that we care about. And of course, uh, when we create indexes and we put data in order, it is to make searches of that data faster.

And of course the fastest data to search is data that is sorted in a way that makes locating the data that we care about as efficient as possible. So, uh, let’s make sure that query plans are turned on because I have a helpful note to do that right here because sometimes I am a bonehead and I forget to do that. There’s nothing worse than running a query that takes like 30 seconds and then finding out that, uh, you didn’t turn on query plans.

I think maybe the only thing worse than that is recording a video for 15 minutes and then realizing that you looked like a badly dubbed Kung Fu movie. So we’re gonna, we’re gonna try that again. Um, and of course we care about, uh, sorted data because when we have sort operators in our query plans, bad things can happen. Uh, we can have rather large memory grants and we’re gonna talk about memory grants in the next video.

So I’m not gonna spend too much time explainifying them here. I’m just going to say that sometimes sorts ask for big memory grants because those are, uh, size of data operations. Uh, if we don’t get enough memory, we might spill to disk.

If we ask for way too much memory, we might, uh, you know, knock a bunch of data out of the buffer pool. We might clear out other memory consuming caches and memory managers in order to loan memory out to query so that they can execute. Um, memory grants and sort of things like that can, uh, make queries more sensitive to parameter sniffing.

Um, even with the memory grant feedback, uh, intelligent query processing, uh, feature in newer versions of SQL Server, we can still run into situations where, uh, it does not quite work out once you start sort of mixing, um, different performance issues with those features. It’s just, you know, parameter sniffing is sort of a classic one. I know we have the parameter sensitive plan optimization, but, um, you know, most folks not on SQL Server 2022, aren’t gonna feel a lot of, uh, uh, pain relief from that one.

And, um, even like when you have a normal parameter sniffing situation, uh, the memory grant feedback thing, um, kind of kicks back and forth a lot. Even if it settles on like a middle ground value, that middle ground value can still not be all that great. Um, you still end up with a fair amount of spilling and a fair amount of overestimating memory grants.

And, uh, you sometimes have to override that when you can with the, uh, the, the max grant percent query hint. Uh, so let’s look at the index that I have created here. Uh, it is on the users table and the columns that make up the key of the index are reputation, upvotes, downvotes, and creation date, which I just stuck in descending order for a little bit of flavor.

It’s not gonna make a whole lot of difference to our query. Uh, down in the included columns, we only have display name. Um, and, uh, the thing about included columns is that they are just window dressing for your query.

They are only stored in the data pages. Uh, they are not sorted the way that the key of the index is. And since they’re not in the key of the index, they don’t offer the storage engine, any sort of, um, uh, like optimized way of locating exactly where things are.

So if we had a query that was like where reputation equals one and display name is like capital A percent, like, like we couldn’t like seek to any values for display name. Cause it’s down to the includes the order of included columns doesn’t matter for the index definition. Key column order matters a whole lot.

Included column order does not matter one single lick. So the first thing that I want to do is kind of talk you through how, uh, you can visualize the way that indexes store data. Uh, that makes the, uh, makes finding data in them more efficient.

So I’m going to run this query and notice that I have an order by reputation upvotes downvotes and creation date descending. I have a bunch of columns in the where clause. That’s sort of less important, but what I want to do is run this.

And show you the query plan and show you that there is no sort in the query plan because we have an index that exactly matches the ordering. The present presentation level ordering of our data. Uh, so we don’t have to sort that, but what I want to show you in the results to sort of help you visualize the way that indexes store data.

When you have a multi key column, nonclustered index is by showing you the results over here. So for reputation, we only have the value one 24. Uh, of course, the reputation column is going to be ordered from low from an ascending order from a 124.

From lowest value to highest value. But just for the chunk of this index where reputation equals one 24, we can sort of get a sense for how other, other data in the index is sorted. Now reputation, I’m going to call it the primary sort of the index.

I don’t want you to get that confused with the primary key. I just want you to know that because it’s the leading key column of the index, every, it is the primary sorting of the index is by this column. All the other columns are sorted within a duplicate value chunk by it, like within that.

Um, we’re going to talk about what happens when you cross boundaries a little bit later when we talk about inequality predicates. But with it within reputation one 24, up votes are all sorted in ascending order from one down to 10. Down votes are all sorted within any duplicate values in up votes.

So we’re, we’re up votes is one down votes is one one two. And that pattern kind of carries on except for poor up vote number four that only has a one and a two. But for six, we have one two three for seven, we have two four.

So it’s ascending order within every range of duplicate values. Um, for unique, you know, if this was a unique index, you know, uh, where we had like, you know, if this is like an identity column or something, there wouldn’t be a lot of room for that.

So, um, it would look a little bit, it would look different, of course. But if, you know, if we had a unique leading key column, we wouldn’t have this nice visualization here. And then within, uh, any, uh, duplicate values for down votes, uh, creation date will be sorted in descending order for those.

All right. So like, that’s how you can visualize this index data being stored, right? Whether, wherever there’s duplicate values, uh, values for the column in the key of the index after it will be stored in ascending order within that duplicate range.

And that’ll go for any other key columns that come along. So I’m going to run these two queries back to back. And we’re going to look at the query plans in a second.

But what I want to just, you know, bring up is to point out kind of the obvious what’s on your screen. Uh, this, this query, uh, is just the top 1000 ordered by upvotes. And since upvotes isn’t the leading key column, we don’t have that.

We don’t have that column nicely ordered from a, an ascending order from smallest to largest. We have reputation ordered that way, but not upvotes. For the second query, we’re only looking for where reputation equals one.

So we’re only going to return the top 1000 rows for reputation equals one. Now that the query plans look different. And, uh, well, I guess at least half an important way.

The top query, uh, goes parallel and has a top end sort in it to put data in the order that we care about. So if we look at the details of that top end sort, uh, we’re going to see an output list and we are going to see an order by upvotes in ascending order. Right.

Cause we had to, we don’t have upvotes perfectly in, we don’t have upvotes is like the primary sorting of this index, in this index or any other index. So we need to sort that we, the SQL Server needs to get all those rows and ask for memory is scratch space, memory grant, and write down all of the columns that we’re selecting in order of the column that we’re asking to be ordered by. The second query plan where we’re only looking for reputation equals one, we don’t have any sort at all.

There’s no sort operator in this one because we have that equality predicate on reputation. We have upvotes in perfect ascending order for just that chunk of reputation equals one. Remember when we looked at that first query where we had reputation equals one 24 and upvotes was in like, like one through 10 after that.

It’s the same deal here. So we don’t have to physically sort data because we have data in, in index order for reputation equals one, right? So we use that leading key column.

We have an equality predicate. We only search that chunk of data and upvotes is in perfect order within that chunk of data. We can expand that across the entire key of the index and we can order by creation date descending. And we will still not need to sort in our query plan.

Our query plan is sort free, right? We have a seek, we have a top. And because the equality predicates preserve index order across the key of the index, we don’t need to sort data for that. Included columns are useless for that.

If we look at this query with an order by on display name. Remember display name was in the include section, include region of the index. If we look at the, we get one row back, one single solitary row, but our execution plan decides to sort that one row.

So even though we seek to all the values we want, that gets us down to a single row. SQL Server is like, ah, well, let’s make sure that row is nicely sorted for them. They asked so nicely for that order by, I guess, I guess it has to follow the rules.

Otherwise we’d have mungodb. So something that I brought up a little bit earlier, inequality predicates do not work in the same way. So if we look for where reputation is less than or equal to one or greater than or equal to 1 million.

Again, we’re only going to get one row back for this, but SQL Server is still going to choose to sort data. Well, SQL Server is still going to have to sort data for these two queries. So if this thing will cooperate and we can get these things nice and close to each other.

So when I zoom in, they’re not 10 million miles apart. It looks like a moon mission. So this top query, we have to sort data. SQL Server chooses a parallel execution plan to do it.

And for the second query, even though we get a single row back, we still need to sort that single row because we have a top and order by. So crossing boundaries presents an issue for these queries because every time you cross a boundary, so like let’s say we went from reputation 124 to 125, the upvotes column would reset the ordering.

So let’s look at how that we can visualize that. So this query is almost the same as the first query, except we’re doing in 124, 125. If we look at the results, the important thing that I want to get to is where this boundary changes.

Because now we’ve crossed from 124, we’ve hit upvotes equals 10. Now we get to 125 and the sorting for upvotes resets. So the same thing is going to happen here where we’re not going to have downvotes like perfectly in order, even after the upvotes, right?

So like that this boundary resets. If we did where reputation in 124, 125 and upvotes equals zero or one or something, then we would have a different sort of set of data there.

But you can see all the sorting resets every time you cross a boundary like that. Now that matters less as long as we are sorting by the leading key column. If we search on other columns, like on other key columns in the index, upvotes is the second key column in the index.

So we can cross boundaries in upvotes or downvotes. And as long as we’re ordering by the leading key column in the index, then sorting this data is free, right?

Looking at these two, we have two nearly identical query plans. They both scan the index because again, we’re not searching on reputation. We’re only ordering by reputation.

But so the order by is free, but the search does result in a scan because we’re not also searching on reputation. All right. So that is how a little bit about how indexes was Btree rowstore indexes in SQL Server store data.

columnstore indexes are of course a little bit different. By that, I mean a lot different. And they are not a topic in this video because they are so different.

And most of y’all are just using the Btree rowstore indexes anyway. So that’s why I chose to talk about it. Anyway, again, we care about sorts.

Sorts ask for memory grants. We’re going to talk about those next. You know, sorts can spill the disk. Sorts can also ask for way more memory. Steal a bunch of pages from the buffer pool. Clear out your plan cache.

All sorts of nasty stuff. And they just kind of generally make code a little bit more sensitive to parameter sniffing issues on account of the memory grant thing. And in parameter sniffing scenarios, the memory grant feedback loop gets thrown off a little bit. So that is all I have to say about that at the moment.

Next video will be more about memory grants. We can talk about those. And yeah, I don’t know. Thanks for watching.

Please, pretty please, like and subscribe so that you can keep seeing these. And if you like and subscribe in the next five minutes, I’m timing you, I will send you an Adidas t-shirt. Leave me your address in the YouTube comments.

All right. Good enough. Thanks for watching.