Tag: SQL Server Training

Learn T-SQL With Erik: Nonclustered Index Interactions

Posted on by Erik Darling

Learn T-SQL With Erik: Nonclustered Index Interactions

Chapters

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we are going to continue with our little baby slices of material from my Learn T-SQL with Erik course. And today we are going to talk about nonclustered indexes and their relationship to either the heap or clustered table that they were created on. And of course, this, you know, again, this is not specifically like, here’s how to write a query, Learn T-SQL, SQL Server stores data, and learning about how those data structures relate both to each other and to the base tables that they are created on. In this case, is very, very valuable for people who want to seriously practitioners T-SQL, because without this knowledge, your queries are going to suck. They’re going to be terribly slow, and people are going to be angry at you. You can be as advanced as you want in the writing of T-SQL, but unless it, unless those queries ever finish, no one’s ever truly going to apply and appreciate them. So, that’s why we must talk about these things. So, down in the video description, you will see all sorts of very helpful links for you to give me money. You can hire me for consulting. I still need to tell people what to do with their SQL servers, which is fine with me. I don’t mind doing that. In fact, I’m rather accustomed to it. You can purchase my training materials. There is a link, actually, with a discount code attached for this very course.
Right? Bloop, bloop, bloop, bloop, bloop, bloop, bloop. You can become a supporting member of the channel to keep the wind in my sails, the gas in my engine, whatever. Does the gas go into the engine? I don’t know. I’ve never had a car. But to keep creating this high-quality SQL Server content for you or one of your friends, if you share this video with one of them after you like and subscribe, if you haven’t done either of those already. I don’t want to be repetitive here, but unless I say these things, people just sort of passively watch and then I never see them again.
If you would like to monitor your SQL servers for free, I have a very, very free $0, zero-cost monitoring tool available up on GitHub. It’s this link, but it’s down in the video description. You got to go click on something, right? But it’s totally free, totally open source, no email, no phone home, no weird telemetry data. It’s just all the stuff that I would look at if I were a SQL Server monitoring tool, which now I am. So now you know what I’d look at. Congratulations.
Weight stats, blocking, deadlocks, queries, CPU, memory, disk I.O., all that fun stuff. You know, normal SQL Server monitoring stuff, just way better. And if you are the type of robot-loving person that seems to make up the majority of the IT world today, you can turn on, optionally, if you can opt in to using MCP servers to allow your robots to talk to purely your monitoring data.
And they can make a much better sense out of what I have collected than if you were to just go allow them to run random DMV queries on their own. Because, I mean, look, I’m not trying to get on the case of the LLMs and, like, all that, but they still mess a lot of stuff up, and they still make a lot of stuff up, at least, you know, in my dealings with them.
No, I don’t know that I would just let them go do that. But the MCP tools that I built in here are all very well defined. The schema is all sort of predefined.
The results, like, the columns that they represent is all predefined. And so it’s much easier for them to manage that. There’s nothing to hallucinate there. So they could still make things up, but they can’t hallucinate things existing or not existing, which is supposedly a step in the right direction.
Anyway, if you would still, if you still value human interactions, I will be leaving the house. And when I leave the house, these are the places I’ll be going, right?
I’ll be in, well, let’s do this in order, kind of. I’m going to be at Pass on Tour. I’m going to lose my entire arm up here. I’m going to be at Pass on Tour in Chicago, May 7th and 8th, right?
And then from there I fly to Poland from May 11th to the 13th. And then from there I fly home. And after I’m home for about a month, I fly to date a Saturday, Croatia, June 12th and 13th.
And then I’m going to be home for a little, as far as I know, I’m going to be home for a little bit. And then I go to Seattle, Washington, November 9th through 11th. So get your dance and rain boots on for that.
But for now, it is April. And April is baseball and floating hot dogs. Because this gentleman database is clearly either, that thing is about to drop to the ground.
Or, I don’t know, maybe it’s like floating to his mouth. I don’t know what happened there. But everyone’s got beer and they’re happy because they won.
At least they think they did. This guy looks angry. You know, like angry eyebrows. This guy’s kind of like sad looking. This guy’s angry looking too. Champion’s guy looks happy.
But he’s, again, wearing red. So I don’t know how he got into this picture. I feel like, oh, this isn’t brown. I don’t know. Someone’s about to get shot, maybe. Anyway, let’s talk about nonclustered indexes. Now, we cannot talk about nonclustered indexes without some sort of actually type person piping up and saying.
But what about, what about, what about, I don’t know, chicken little. What if the sky does fall on your head? I don’t know what to tell you.
What, won’t they slow down inserts, updates, and deletes? I’m like, yeah, merges too. They do have an impact on these things. But we’ve got this query over here that’s running for like a minute.
And if we add an index, it’ll run really fast. So maybe reducing that query’s impact on the server sort of amortizes the cost of maintaining this index for the inserts, updates, and deletes. Maybe that’ll happen.
Maybe there are trade-offs that we have to make in life. Maybe there are things that we as professionals in the database world have to test. But make sure that they work as expected.
I know, it’s crazy. So yes, every index is a separate object on disk and memory. It is a copy of the data from either the heap or clustered table.
Every index will add some overhead to modifications because you’re going to have to write those changes to the transaction log. You’re even going to have to lock them to complete writes. All right?
My goodness. Even indexes that are unused by reads. And we hope to get rid of those. We do seek to remove indexes that are not helping our queries go faster. So don’t keep those around. Need to be maintained because the SQL Server doesn’t know when a query might show up and need to use them.
All right? So we need to read those indexes into the buffer pool. Sometimes a little teeny bit of the index if we do a seek. Sometimes a lot of an index if we do a scan.
Right? So the main points that poor indexing habits can introduce, the main problems that they can introduce to your systems, consist of typically these three points.
Buffer pool contention because you have more objects vying for space in your buffer pool. You have more transaction log activity because there are more objects you need to write information about changes to the transaction log. And you may even see increased locking and maybe even increased lock escalation attempts and sometimes even successes.
Assuming that there are no competing locks that would prevent a lock escalation from becoming a success after it gets attempted. So there are things that we do have to be aware of when we are indexing things. So all of these things being true simultaneously, we must come to an agreement, you and I.
We must come to an arrangement that suits everybody. We must compromise and we must say that we need indexes but we should enjoy them responsibly. The same way that we enjoy other leisure activities responsibly so that we don’t end up throwing up on ourselves.
All right? But we also need to query responsibly. Don’t we?
The wider your indexes, or rather the wider your queries are, the wider your indexes may have to be in order for SQL Server to actually use them. All right? Because if we are writing select star-ish queries, or if we have queries that are, you know, attempting to sort of kitchen sink, evaluate 14 different columns in a table, well, if we don’t have indexes that SQL Server considers covering for those very wide queries, if our queries have a very wide berth, we’ve got wide hips on that query, well, guess what?
SQL Server might say, ah, no, I’m just going to scan that clustered index instead. And let’s talk about why that happens. That’s a great question.
Why does that happen? I’ve created this index on the reputation column in the users table. It maxed up one so that I get a more consistent statistics histogram. Sometimes that works.
Sometimes it doesn’t. But anyway, if we, the important thing that I want to show you in this query, or rather across these three queries, is the cost of the key lookup in the plan.
Because remember, our index up here is only on the reputation column. And so when we go to get other columns from the table, and I’m telling SQL Server here for a bit of demo stability that it must use this index, or else it will be in big trouble, then SQL Server has to get this column from somewhere, and so a key lookup gets involved.
If we run this query, and we look at the cost of the key lookup, SQL Server says, I think that’s going to cost 47.3194 query bucks to fetch that one column, that one integer column, from the users table, a total of 15,656 times, right?
Because we have to do that many loops to go get that set of columns, or that column from the clustered index down here. Okay, remember that number, four, gosh darn it, you, 47.3194, alright? What if we say, I need to go get an envarchar max column, because this is a count ID column again, this is an integer, right?
And this about me column, well this is not an integer at all. If that other helpful thing will show up, we can see this, about me is indeed an envarchar maximum, alright? Max envarchar there. How much does this cost?
Well, shockingly, 47.3194 query bucks. Oh dear. So, SQL Server assigns the same cost to going to get a 4 byte integer as it does to going to get a book with a key lookup.
Isn’t that interesting? That completely leaves out that we might have to do, like, lob reads, or read off-road pages, or off-road data, or any of that other stuff. It just has the same cost, right?
Wild. And even still, if we sell SQL Server, hey, go get everything from that table. I guarantee you, my friends, this is not a metadata cost that we are concerned about here. Anyone who talks about metadata in these situations should be hit with a hammer.
We have the same plan, right? But we have, also, we have the same cost assigned here. 47.3194 query bucks.
So, getting everything from the table has the same cost, with a lookup, has the same cost as getting just one integer column from the table, right? So, you don’t even have to do a select star query.
Remember, all the folks on LinkedIn who give you the groundbreaking advice to avoid select star. My goodness, you geniuses, you. They are not quite, not quite all together, are they?
Because you don’t have to do select star. All you have to do is get one extra column, even just one integer column that is not represented in your nonclustered index, for SQL Server to need to evaluate the cost of lookup versus clustered index scan plans, and to maybe not use your index just based on that one column difference.
So, while I do agree that select star makes things a little bit harder, it also doesn’t take select star to get index choice to be a sort of painful point during query optimization.
You can ignore that number. That was a previous run. I guess, I don’t know. I installed a cumulative update, math changed on me, or something. Hard to tell.
But, that’s all. All I know is that the last time I ran through this, that’s how much it cost it did. Funny. All right. So, let’s see.
When we let SQL Server choose which index to use, right? And we’re going to look for reputation equals 16 now. SQL Server says 38,000. Nope.
Scanning the clustered index. That takes 234 milliseconds to do that. Okay. That’s fine. Keep in mind that the clustered index scan is a fixed cost, right? So, if we needed to go get, say, like, that was, how many?
38,000? I think 21 is like 58,000 or something. Yeah, 51,000. This will have the same fixed cost, right?
Because we still have to scan the clustered index and do the same amount of work either way. So, that’s 60, oh, let’s see. 68.3519 query bucks.
And that is, execution plan. 68.3519 query bucks. So, because scanning the clustered index represents the same amount of work, no matter how many rows we estimate might come out of that clustered index scan, it is a fixed cost based on pages and whatnot, right?
So, the bigger your clustered index is the more expensive it gets, at least from a CPO and IO perspective. Lookup costs are dynamic, and they are based on the number of times the lookup has to be performed. I find this to be a very sensible arrangement.
If we were to go look at the execution plan for this, we would see that the cost of this lookup is no longer 47.3819 query bucks, but for one single row, it is, oh, you know what? Oh, that’s the right place. 0.0032831 query bucks.
A very small number. So, lookup costs are dynamic based on the number that SQL Server has to get done. There is some math done around sort of like the first one is expensive, but then following ones are less expensive because SQL Server sort of figures, well, like this one might be from an empty, it’s going to be from an empty buffer pool, it assumes. And then like the next one will, like maybe the day is going to be on the same page, so I can read that from memory.
So, like the first one’s expensive, but then the next ones are, the cost is reduced for each one. So, good things to know when one is looking at query plans and whatnot. There is a fun quirk with unique versus non-unique nonclustered indexes.
In the users table, we have this account ID column, which is unique. It is entirely unique. It is a somewhat monotonically increasing number.
However, it is not exactly the same as the identity column that is the clustered primary key on the table, which is the ID column, but we can create a unique and a non-unique nonclustered index on the account ID column, right? I think I got that one. The other one’s showing me a red squiggle.
Yeah, okay, I got that one. So, when you create a non-unique nonclustered index, the clustered primary key acts as a hidden key column in the index definition. So, if we run this query and we tell SQL Server to use our non-unique index, we will get a double seek into the nonclustered index.
We can see both where ID and account ID are evaluated with the literal values that we passed in. So, we get a double seek there. In a unique nonclustered index, that clustered primary key column is more like an include in the index, where we are no longer able to double seek.
Notice that we do have a seek predicate here to account ID, but now we have a residual predicate up here on ID. So, that’s the truth about that. But when I talked about how nonclustered indexes have a relationship both to the table that they get created on and other nonclustered indexes.
Well, let’s take a look at some of that. There’s a type of plan that SQL Server can use, a type of execution plan SQL Server can use called index intersection. And hopefully, this all works out.
Where SQL Server has taken… Let’s do our magic zoom trick here. Oh, that’s too big. There we go.
SQL Server has done a seek into two non-clustered… So, what’s annoying is that it’s very… I don’t know why those ellipses kick in so soon, but if we were to look here, we would see an index seek on this nonclustered index, and then an index seek into this nonclustered index. And then SQL Server is able to merge join those two indexes together and produce the result that we care about.
And if we look at the merge join, it is a residual, well, this is a merge join, on the ID column in one index being matched to the ID column in the other index. So, when I talked about how nonclustered indexes inherit the clustered index key column in some form, depending on uniqueness, SQL Server does a merge join here because that ID column is the second key column. And so, that data is in order after we do equality predicates to the actual key column that we defined the index on.
Interesting stuff, right? If we look at index intersection, we can even get index intersection with a key lookup, right? And we’re going to hint SQL Server to use those indexes, right?
Because that’s the easiest way to get this sort of plan. But now, we have what we saw before, which is the merge join and the two nonclustered index thingies here. But now, we also have a lookup back to the clustered index to get that additional column, right?
Because now, we’re trying to get display name, but display name is not in either of our indexes. So, SQL Server can do index intersection plus a lookup. But the index hint order is significant, right?
So, up here, we have the non-unique account ID and reputation. And down here, we have reputation first and then the non-unique one, right? So, if you’re going to be the type of person who hints indexes, you may want to consider the order that you’re hinting them in because SQL Server will hit them in the order that you hint them, right? Because we hit reputation first in this one and we hit the non-unique one second in this one, right?
But up here in this query, right? If you look at this plan again, right? Execution plan, this hits non-unique first and then this one hits reputation second.
So, very interesting things when you start hinting in indexes, especially multiple indexes. We also have the concept of an index union plan. This is where SQL Server brings the results of two indexes together, like if you were doing a union or a union all type query, right?
So, this is merge join concatenation. This is not just a merge join. This is concatenating results from both of these.
And we’ll see once again that, well, we don’t really have much of a, we don’t have a join condition because this is not a merge join. It is a concatenation operator. But it still says merge join, which is a little confusing, but that’s okay.
I’m sure Microsoft is on the case, right? But we hit both of our nonclustered indexes and then we did that. And then we can also see that index union with a key lookup is also a perfectly valid plan choice, right? So, that’s all that happening in there.
So, index intersection happens most commonly with AND predicates. You can see, well, you can see there are a variety of different join types that might be chosen, sometimes even a hash join, depending on your index setup. Both indexes must be fairly selective.
The result set must be fairly small. The optimizer doesn’t choose these quite as often because usually it will do either a covering index or, you know, scan a clustered index or just do a regular old key lookup. Those don’t always require hints, but, you know, sometimes if you want it to happen, it’s not going to happen naturally.
You have to tell it to happen. Index union is more common with OR predicates. You see a concatenation operator that combines streams of data from two nonclustered indexes.
You may even see a stream aggregate to remove duplicates, which is a nice thing. Each branch has to be seekable. I guess it’s somewhat more commonly chosen than index intersection, but, you know, whatever.
They don’t get used a lot. Like, they don’t get, those plans don’t get chosen very often. The cost model assumes some overhead to, you know, doing that type of stuff that is unfriendly to them.
It prefers to hit a single index path when possible. Sometimes statistically, or rather, the statistics that you have, the histograms that you have, may not, like, present a very big benefit to the optimizer for using them. And then there’s always some risk of eliminating duplicates, adding a bunch of overhead, which, you know, for a, that’s why they have to be selective.
But they can be very beneficial if you have selective AND or OR conditions, and each predicate has a good candidate index to seek into. So, if you are sort of, you know, hoping for that type of execution plan, then you will have to sort of do some work to get one quite often, either with a foreseek hint or hinting the indexes to use in the appropriate order to use them. Anyway, that went on a lot longer than I thought it was gonna.
I’m gonna go finish my coffee now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Today is Friday. Nah, I shouldn’t have saved a long video for Friday.
No one watches the long videos on Friday. But I will see you on Monday, where we will do yet another Office Hours. And hope that someone out there cares.
Alright. Thank you for watching.

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.

Learn T-SQL With Erik: Clustered Tables, Page Splits, and Readaheads

Posted on by Erik Darling

Learn T-SQL With Erik: Clustered Tables, Page Splits, and Readaheads

Chapters

  • *00:00:00* – Introduction to Clustered and Heaps
  • *00:07:52* – Clustered Indexes and B-Trees
  • *00:13:32* – Heap Tables Explained
  • *00:21:11* – Clustered Index vs. Heap: Insert Performance
  • *00:29:25* – Clustered Index vs. Heap: Update Performance
  • *00:36:15* – Clustered Index vs. Heap: Read Performance
  • *00:45:20* – Read-Ahead Reads and Clustered Indexes
  • *00:51:54* – Read-Ahead Reads and Heaps
  • *00:58:08* – Forwarded Records and Heaps
  • *01:03:16* – Clustered Index vs. Heap: Scan Performance
  • *01:10:02* – Summary and Next Steps

The AI did not do a particularly good job on these chapters. Remember that.

Full Transcript

Erik Darling here with Darling Data. In today’s video, we are going to talk about, well, we’re going to use a snippet, a tiny little bibbidi-bobbidi-boop from my larger training corpus of material, the Learn T-SQL with Erik course. It’s available down in the video description. I’m going to say this in like two minutes again anyway, but down in the video description, there’s a link you can click on to buy this course at a date. I’m going to give you a discount because I care about you and your financial future in the world. But today we’re going to learn about clustered tables, how they differ from heap tables, which I guess was maybe fairly obvious if you watched yesterday’s video or if you’ve been around SQL Server for a bit. But there’s an interesting thing that I want to show you at the end of this that not a lot of people know about. So you’re just going to have to hold on to your diapers for that. Of course, like I said, I predicted this, right? I predicted these predictions. Erik Darling knows his stuff. Down in the video description, you will find all sorts of helpful links to hire me for consulting, buy my training, including the larger corpus of training material that makes up the Learn T-SQL with Erik course. And the links down there where the fingers are pointing. You can even do something kind of funny and be like, I’m juvenile. But you can also become a supporting member of the channel. Ask me office hours questions. And of course, if you enjoy this training content and you want to keep me going, you want to keep my batteries full, you want to keep my wind in my sails, I’m going to keep my powder dry here, then you should like, subscribe and tell a friend or many friends. Just don’t tell. I mean, you can tell the robot friends. Maybe they’ll learn a few things from me. I don’t know. They can’t subscribe. They can’t like, but you know, maybe maybe they’ll pick up on some helpful things. If you want free, absolutely free as in speech kind of SQL Server monitoring, you can you can get that from me. I have released completely open source, no charge, no email, no weird phone home telemetry. It’s available at my GitHub repo. It’s all the stuff that I would monitor if I cared about monitoring SQL Server, which I do. So I do now. It’s all in there. Wait stats, blocking, deadlocks, query stuff, you know, CPU, memory, disk, you name it, Erik Darling is on top of it.
And if you are a fan of our robot friends, if you want to have your favorite robot talk to your monitoring data, and maybe give it some like some better context on what you’re looking for, what your problems are, you can you can use the optional opt in built in MCP tools to have the robots go look at stuff for you and maybe get some faster answers than just poking through all sorts of charts and graphs on your own, because that can, that’s that can be difficult if you don’t know what you’re looking for. But the robots who have been trained on years of smart SQL Server people like me saying things for free online. Apparently, apparently, they can they can whiz bang, look at all your data and give you answers. Maybe, we’ll see. You can still hire me if they if you don’t like their answers. I’ll be your robot friend.
Of course, as long as as long as people still care, I will be bringing you human generated warmth, kindness, hugs, I don’t know, all that stuff out in the wider world. I will be at SQL day Poland, May 11 through 13. Shortly after pass on tour Chicago, May 7 and 8. Crazy, right? From Chicago, I go from New York to Chicago to Poland, and back to New York. And then like a month later, I go to data Saturday, Croatia. I can’t wait for that one. I hear it’s it’s going to be June. Unlike just the other side of Italy. And I’m excited about that. See some different water in my life. Be be a hell of a good time.
So you should you should come to all of those. You should just be like a like a like what do they call those people who follow like the Grateful Dead around? Yeah, you should do that with me, right? You can be a data head or something. Sounds unfortunate. And then that June is it for me for a little bit, I guess. And then past summit in Seattle, Washington, November 9th through 11th. And well, I don’t know, maybe I’ll find some way to fill up my calendar between June and November. But well, we’ll have to see what’s out there. Anyway, it is my favorite time of year. Baseball is in play. Right? Got to see a Mets game, got to see a Red Sox game all in the same day. It was glorious. Drink beer and listen to the sound of bats hitting balls. Well, that’s, that’s, that’s, that’s my, that’s my idea of a good time. Anyway, let’s talk about clustered tables. So we are going to use a somewhat different store procedure.
And if you are interested in what this store procedure does and what is happening behind the scenes, of course, this is all available in the bigger class materials. So you can click on the link and you can buy the class. But we’re going to load up our clustered table here. And we’re going to put a little over a million rows in it. Not because a million rows is an impressive number, because it’s just enough of a number to make some other numbers more obvious.
So we’re not trying to be crazy here. But the first thing I want to show you is that there are not forwarded records, unlike yesterday with the heap, there are not forwarded records in a heap, and there will never, in a clustered table, and there will never be forwarded records in a clustered table. All right. But right now, what we have is this many pages, 5862 in the clustered table, this many rows, which is the same in both the clustered table and the nonclustered index we created on the clustered table, and our average space used in percent is pretty good. This is not logical fragmentation. All right. This is not data pages being out of order on disk. This is the fullness of our data pages.
This is how many rows we have crammed onto these data pages. All right. So this is an interesting number for what we’re going to look at in a moment. Keep that in mind. Right. So just like yesterday, we want to find the middle row of the table. And the middle row for us is going to be, well, since I’m recording both videos the same day, it’s literally the same day, but whatever. So what we’re going to do is just quickly look at what we get back in here, right? So if we look at general performance with these queries and over in the messages tab, we will see that the read from the clustered table, and if you didn’t know about this, then it’s a neat thing. You can actually edit the text in the messages tab.
The reads against the clustered table, about 1.6 million, and the logical reads against the nonclustered index are about 5880. I’m not someone who pays a lot of attention to logical reads. I’m not someone who uses them as a tuning device. I find them to be quite a secondary indicator of a problem. For me, CPU and duration are far more important indicators of problems.
But I do want to show you that this does have an effect on things. And one easy way to show you that it has an effect on things is how many more reads the table does after some things happen. So yesterday, we looked at forwarded records or forwarded pages a second from the perfmon counters.
Today, we’re going to look at page splits because the heap did not have page splits. The heap had forwarded records. With a clustered table, SQL Server will split pages once they become full, and it will take about 50% of the rows, and it will move them to a new page. And this is sort of the different type of data movement within a table.
So like with the heap, it just took a row and moved it to whatever page had space on it and left behind a forwarded record pointer. With the clustered table, it takes half the rows and puts them on a new page and keeps things in sort of clustered key order. So we ran our update, and as far as page splits go beforehand, we had 647528.
I should have restarted my server before I did the zero. It’s a lot easier to compare zero to something. So we had 64752898 and then 6481308. So we changed the third significant digit there by one, which is, I suppose, interesting for us.
But we can see that the number of page splits that have occurred on the server have gone up a bit here. We can see that that number has increased. So if we look back at the table itself now, and we look in here, right, we’re still not going to have any forwarded fetches because that’s never a thing.
But we do have a lot more pages in the table now, and the average page space used in percent has gone down dramatically. This was at 99-something percent. We are now below 50%.
We are at 46% full. Well, that doesn’t feel too good. What this means is that SQL Server has a lot more pages in the table now that are a whole lot less full, which means we are sort of like yesterday when we deleted a bunch of data from the heap and we still read empty pages.
This is sort of like that, except now we’re reading a bunch of pages that are about half full, so it takes twice as many pages to get through things. This is far more of a problem if we are reading pages from disk into memory than if we’re just reading stuff from memory, or even if we’re just seeking into the table, this number doesn’t really matter, but it is something to keep track of, right?
Like this might be a good reason to rebuild an index because you want to reclaim page density, right? You want to read from smaller objects. You want to make sure your queries are not I.O. bound, reading lots of extra stuff from disk into memory.
You also want to make sure that your indexes are not taking up like twice as much space in your buffer pool as they need to because they are full of half full pages, right? So if we look at the stats.io performance here, come on, finish.
There we go. This one still does about 1.6 million, but this one does way more. Now, this was at like 5,800. Now, this is at 65,000, right?
So our query here that had to go do a bunch of, that just scanned the clustered index, or sorry, yeah, that scanned the clustered index. This is not a heap means this is the clustered index, right?
If we, did I turn on query plans? I didn’t. Well, dumb me. I’m, all right, let’s turn on query plans and look at that again so I can prove to you what I’m saying here because the, you know, clustered query plans do a lot more proving than anything else, right? So this one does a lookup against, or does a key lookup, right?
Hit the nonclustered index, do a lookup to the clustered index to get the stuff we care about. This one here just scans the clustered index. So this one here did far fewer logical reads before.
Now does a lot more now. So the page splits didn’t really change reads for the seeks, but it did for the scan. The thing is that neither CPU nor duration really went up for a lot of them, right?
Because we’re not just, we’re just not doing so much that it makes a difference, right? This is just a million row table. It’s not terribly hard to deal with performance on a million row table.
You’re not going to see big changes in performance on a million row table. Typically tables have to get pretty big before you should start caring about these things with them. But once they get to that size, or once they get to a size where they start making a difference for you, this is the kind of stuff that’s good to know about.
That’s why this is the kind of stuff that I’m teaching you about. So let’s turn off query plans. And let’s look at how things appear in our table now. Because remember yesterday, when we deleted all those rows from the heap, we’re using the cursor.
Remember that the table was the same size after we did all those deletes, because SQL Server didn’t deallocate the pages. It kept them around just in case we decided to load more data in, so it didn’t have to allocate pages while we’re loading data in.
That is an optimization that you get with heaps that you do not get with clustered tables. So query plans are turned off, and now let’s cursor through our table and wait for those deletes to finish. Remember that yesterday, the heap delete took about nine seconds.
I’m not promising that this is going to be any faster or any slower. It is just the way it worked. This took 11 seconds. So two seconds slower, I guess.
But now, if we look at the size of the table, we will see that we have a few root and maybe intermediate pages still kicking around, but this table is no longer 550 or so megs, and we have gotten rid of all of the data in there doing the deletes. Unlike with the heap, where you need a tablock hint or other manifestations of voodoo, this went sort of on its own.
And we’ll do our little checkpoint thing here, and we’ll find that pages are completely deallocated. So we just don’t have anything in there. It’s empty.
Anyway, the thing that I think is interesting, like index fragmentation, when people talk about it, they’re talking about logical fragmentation, it is largely just very goofy.
It is just like 2008 mentality. It’s like people who harp on about VLFs and other stuff like that. It’s like, come on, like, get with it, right? What year is it?
You’re treating SQL Server like it’s still 2008, and you’re goofy, and everyone thinks you smell funny. But one thing that I want to show is that having pages be less dense, right? Having the sort of physical fragmentation can decrease the efficiency of read-ahead reads, because you have to do way more small ones instead of fewer large ones.
This is a byproduct of lower scan density when your pages are not very full. This is one reason why I sort of get on people’s cases when they start messing with fill factor, because you’re sort of artificially introducing physical fragmentation to your tables or indexes.
The cluster table will suffer from sort of increased read-ahead. It’s not going to be a dramatic impact because the table is not huge, but just something to be aware of. So let’s reload both tables, right?
And what we’re going to do is run these two store procedures. Again, the code for these store procedures is available if you buy the full course material. But what we’re going to do is we’re going to test read-ahead against the heap and the cluster here.
Now, the query plan up here shows us the heap scan. That takes 125 milliseconds. This is the query plan for the clustered index scan, right?
That is 114 milliseconds. Those things are pretty close there. But if you look at the total number of read-aheads, right? We don’t have any forwarded records on these.
We’re not going to. But the heap did 9,400 read-aheads or 43,000 read-aheads a second. And the clustered index did 5,900 read-aheads. So the clustered index is pretty efficient on those read-aheads, right?
Did fewer read-aheads. So, because it could read lots of stuff. Now, we’re going to run these two things. And these two things, unfortunately, well, the update against the heap takes a real long time.
Not, like, terribly long, about 30 seconds or so. But the reason why the heap takes a long time is because it has to do all that forwarded pointer stuff, right? Every time a row outgrows a page, SQL Server has to go find a place to stick it.
Which means it has to keep looking at data pages and maybe sometimes even, like, adding a data page to put something on. And so that can just take a little while, right? It’s, like, an arbitrarily annoying amount of work.
So, if you’re, if you have heaps and you’re updating them and those updates are pretty slow, well, part of the reason just might be that you are, you’re making SQL Server do a lot of forwarded record work. Maybe, like I said yesterday, updating, heaps are not made for updates, right? So, if we look at the query plans for these, we’ll see that the heap update took 33 seconds, right?
It’s the same amount of work. And we’ll see that the clustered index update took about four seconds. That was far less time, right?
Updating that heap, not good. If you’re a heap updater, please stop. You’re causing problems for yourselves. Now, if we look at the readahead test for this, right? I’m going to run these.
Look at this. Now, things get, now things completely flipped, right? So, the heap is going to, like, the readahead stuff is going to get, is going to be harmed by the forwarded records, right? Because now we have a crap load of those in the heap, right?
And that takes one and a half seconds. Now, remember, that was, like, 112 milliseconds before. So, for the forwarded records in the heap, like, that can hurt readahead because this was, like, 43,000 things per second before. Now, it’s at 20,000.
Now, this one is interesting because the time didn’t really get hurt, but we do a lot more of these. We do a lot more smaller readaheads. Readaheads work best when they are big, right?
You want to do big readaheads. You want to max out your readaheads. If you’re only reading ahead small chunks, it’s far less efficient, right? So, this thing here, not so crazy about that, right? Because it took, we had to do a ton of very, very small readaheads.
Anyway, that’s all because of a thing called an allocation order scan. And even though you can get an allocation order scan with a clustered index, and you can get those readaheads, the underlying table is still not exactly the same as a heap member, that a clustered table is still a B-tree index.
So, it’s not the same flat structure that a heap has. Remember, heaps are not trees. They are just flat structures.
So, like, you still have this sort of navigational stuff that has to happen with them. So, while you can get allocation order scans, and they can be good with clustered indexes when readahead reads are very, like, wide and efficient, or very big and efficient, you don’t want lots of small ones, because it’s still not a one-to-one with the allocation order scan on a heap.
So, I don’t know. That’s all I had to say here. Clearly, that’s the end of the file. But there’s a lot more in the full course content. So, if this is the kind of stuff that you’re interested in, I would suggest that you buy it so that you can see more of this stuff.
It’s wonderful. Anyway, thank you for watching. I hope you enjoyed yourselves.
Hope you learned something. And I will see you in tomorrow’s video, where we are going to talk about nonclustered indexes and their relationship to the tables that they are created on. All right. Thank you for watching.

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.

Learn T-SQL With Erik: Heap Table Problems with Updates and Deletes

Posted on by Erik Darling

Learn T-SQL With Erik: Heap Table Problems with Updates and Deletes

Chapters

Full Transcript

Erik Darling here with Darling Data. Excited to finally get back to some more SQL Server specific related content, not about monitoring tools or vectors or any of the other stuff that we’ve been talking about here on the Chan recently. Today we’re going to get back to some of the Learn T-SQL with Erik content, recent updates, and we’ll see some more updates as soon as the material is fully tech-reviewed and revised. But today we’re going to talk about heaps. And I know what you’re going to say, Erik, heaps are not T-SQL. And I agree. But part of being an advanced T-SQL practitioner is understanding different storage formats that SQL Server uses and some of the upsides, downsides, and sidesides of them. So in today’s video, we’re going to talk about heaps generally, one condition that they avoid and two conditions that they are prone to. This is also, we’ll talk briefly about sort of the proper place in the world for heaps as well. Before we do that, if you look down in the video description, you will find all sorts of helpful links. You can hire me for consulting. You can even buy my training, including the full version of the small snippet of training that we’re going to be talking about in today’s video, which is the Learn T-SQL with Erik course. There is a discount code down there if you want to check that out. You can become a supporting member of the channel. You can ask me office hours questions. And one thing, I mean, it’s not just exactly a link that you can click, but there are buttons that allow you to like, subscribe, and share these videos with people who you feel they might demonstrate some value to. So, you’ve got all those things available for free and for pay. Another thing for free is my SQL Server performance monitoring tool. That’s at my GitHub repo. The link is there, but the link is also down in the video description as well. It’s totally free, totally open source, doesn’t require you to sign up, give me your email or anything like that. It’s just a bunch of T-SQL collectors that go and monitor all the stuff that I would monitor if I really wanted to monitor a SQL Server.
And so that’s what I do. It captures all the stuff that you would care about. It’s got a Nox-style dashboard if you want to just get a quick glance at server health. And if you are a fan of our new robot overlords, it has optional, built-in, you have to opt in to use them, MCP servers. So you can have your favorite robot talk to your performance data and just your performance data uses a bunch of read-only tools to do that. And it can give you all sorts of potentially helpful, dangerous advice that I am not liable for. Anyway, I’m going to be getting out and about in the world, all sorts of places that I am excited to be.
SQL day Poland will be May 11th and 13th, right after Pass on Tour Chicago, which is May 7th and 8th. So I have like a day to get there. I think I land in Poland on like the 10th in the morning. So I’m sure by the 11th, I’ll be in great shape, great spirits. Take some Benadryl with me on that one.
Then I will be at Data Saturday, Croatia, June 12th and 13th. And I will be at a Pass Summit in Seattle, Washington, November 9th through 11th. Excited to see everyone all over the place.
And of course, you know, Seattle and Chicago I’ve been to, but Poland and Croatia, it’s some first-timers for me. So the old man’s going to get some new stamps in the passport. Excited about that. So hopefully I meet nice people along the way.
But it is, well, actually, when does this get published? This, this, I might need to change this to the new image. Let’s see. Because this might be the April 1st video.
And this is not a, an April Fool’s joke video. So we are, we are firmly into baseball season and we are excited about that. And I am excited by this, this fellow right here with his floating, floating hot dog.
I don’t know. I don’t know how that’s staying weird. Maybe he’s got a little hot dog holster there. I don’t know. Everyone else holding their beer and hot dogs firmly.
I don’t know where this guy in red showed up from. All of everyone else is wearing blue, but I don’t know. Anyway, good job. Data baseball. We’re, we’re in there.
Anyway, let’s switch over to SQL Server Management Studio for the first time in a long time. And let’s talk a little bit about heaps. Now, heaps have two things about them that make them, sort of pigeonhole them into, like, like an append-only territory.
And that is that updates and deletes do not get along terribly well with heaps. Heaps are, of course, tables that do not have a clustered index on them. They may, they may very well have a primary key of the non-clustered variety.
They may have any variety of nonclustered indexes or other constraints on them. But without a clustered index, they are still a heap, meaning they are a collection of unordered pages. So you have the base heap table and then other stuff.
We’re going to talk about clustered indexes, which I prefer to call clustered tables these days. Next. Tomorrow’s video. Don’t get ahead of yourself.
Anyway. Whew. Feeling good. Feeling a little jazzy. So if we look at, let’s actually run this procedure called load elhepo. If you’re interested in getting the code or seeing what all this stuff does behind the scenes, you can buy the course and see all that stuff.
But I’m just giving you a small taste of what’s in there. So if we look at forwarded records for the heap at current, we will see that we do not have any. This is the column that we care about.
So we, of course, inserted a little over a million rows into the heap itself. And then we built a nonclustered index on the heap. So we’ve got that. That’s about all we care about here.
Okay. Because you’ve got nothing to show. So this query will help us find sort of the middle value in the table. And the reason I want to find the middle value is because I want to update about half of it. Because updating half of it in one go sort of simulates, you know, sort of data updates over time that you might see if you are misusing heaps and putting them in a position where they are constantly updated but never, like, truncated or cleared out and then reloaded with other data.
Or just the data is not just append only because that’s where things get into trouble. So if we look at these two queries as they are today, we will see that they have absolutely no forwarded records. Well, I mean, okay.
So if we run these two queries and we look at the before and after of the forwarded record counter, we will see that it is the same after both of them, right? 9582.
So we did not encounter any forwarded records. I just showed you the table with none in there. And as far as page splits a second go, we have this number. This number might change a little bit just because there might be other stuff going on in the background on my server. But this number won’t change because of the heaps.
But what I’m going to do is I’m going to update about half of the table and I’m going to inflate one of the columns a bit just to create some forwarded records. We’re going to fill up some data pages so that SQL Server is going to say, well, this row no longer fits there. We need to move you somewhere else.
I talk more about the mechanics behind that in the training course. But for now, just understand that when you update a heap and rows no longer fit on a page, SQL Server moves those rows silently. Well, sometimes not so silently to other pages where they fit.
So, again, this might change a little bit because of other stuff going on, but I don’t think that number changed at all. That looks the exact same to me. The number that will change is if we go back to that original query and we look at this, we will now see that we have a whole bunch of forwarded fetches against the heap.
The nonclustered index doesn’t have any forwarded fetches in it because nonclustered indexes do not behave the same way heaps do. But that heap now has 475,762 forwarded fetches, right? So, that is the number of rows that have moved about in the heap.
So, if we run these queries again and we just look at the forwarded fetches here, or in this case, they are called forwarded records a second, we’ll get these two queries will eventually run and finish, and this counter value will go from 485344 to 961106, right? So, that just about adds the number of forwarded records there are in the table to the counter there.
It’s pretty amusing, right? Now, of course, you can rebuild the table to get rid of the forwarded records, but that will also rebuild the nonclustered index. At the same time, if you have a heap with lots of nonclustered indexes on them, fixing the forwarded record problem might be a bit painful for you.
It’s pretty common if it’s a large heap to drop or disable the nonclustered indexes, rebuild the table, and then recreate the indexes sort of one at a time, so you don’t have to do that all in one big thing. So, if I turn on query plans, and I look at the alter table rebuild for this, the query plan, when this eventually finishes, shows two things, right?
We see the first section, if my little grabby friend will help here. So, we have the insert into the heap here, right? So, this is rebuilding the table, and then this is rebuilding the nonclustered index, right?
So, we do this work twice. Notice that we have to sort the data here before we do the index insert. That is a nonclustered index key order being sorted.
So, forwarded records, if you’re updating heaps a lot, they can really add up. They can really slow queries down eventually. It sort of introduces a strange sort of jagged I.O. pattern because you have to start scanning through the heap, and then if you hit a forwarded record, you have to jump to where that row lives, and then restart, then come back and do this, and jump to the next forwarded record, and you just kind of end up doing a lot of jumpy, jumping-around work that you don’t want to do when you’re trying to scan through something nice and easily.
The next thing that heaps can have trouble with is deletes. So, unless a delete acquires a table-level lock, either naturally through lock escalation or by specifying a tab lock, potentially x hint, then pages will not automatically be deallocated from a heap, which means that if a page completely empties out of rows, SQL Server is not in a rush to be like, hey, we should get rid of this page because it doesn’t have anything on it.
SQL Server uses that as sort of an optimization, kind of figuring, well, if we’re going to load more data in here, we might need these pages around, and we could just reuse these empty pages, and then, you know, we wouldn’t have to create a new page.
We could save ourselves some time, right? Optimize the inserts. Heaps are meant for, right? Fast insert. No sorting data. No indexes to keep track of. Just whoosh, data in.
All right. So, one thing that is interesting here is that if you have a row versioning isolation level, even a tab lock hint won’t help here.
It’s a whole thing. But you may see similar behavior if your heap contains lob columns, varchar, nvarchar, var binary max, XML, vector, JSON should probably be on that list, or essentially any column that could cause row overflow data, even if you have just very wide rows.
Page deallocation is completely disabled, even with a tab lock hint. The data does not actually have to use lob or overflow to separate pages. The mere possibility, that possibility existing, is sufficient for that to happen.
This is, of course, by design. This is not a bug. Don’t report it to Microsoft. Don’t say, Eric said this is bad and you should fix it. This is the way it works.
I’m just trying to teach you how it works. That’s my job here. So, if we look at indexes on the heap table, we will see that we have two of them still. We have our nonclustered index, which is about 17 megs, and we have our heap, which is about 260 megs.
We can see the in-row used page count over here and the row count that we have in the table of 104, 8576, which is exactly how many rows I loaded into it.
Now, we’re going to use everyone’s favorite. We’re going to use a cursor to delete through the table. And we are going to do that in such a way that we do not acquire a table-level lock, right?
So, we’re going to run all this stuff. Do-do-do-do-do. And I turned off query plan, so this shouldn’t take too long. It should be about 10 seconds or so.
But if it takes longer than that, well, I just might start drinking on camera. Cool. Nine seconds on the nose. I guess I’ve run this demo before, haven’t I?
So, now when we look at this, we’ll see a couple interesting things. The nonclustered index, that’s this one right here, is down to 0.16 pages, right? Or megs of pages assigned to it.
But the heat table still has all 260 megs assigned to it, right? And still has all of this, even though it has no rows in it, right? There is not a single row in there, but it is still the same size.
Every page remains after that delete, right? So, if I run this, if I checkpoint, right? And I run these two queries, let’s turn query plans on, right? Like, none of these, neither of these return any rows, right?
But notice that we still do this table scan here, right? And if we look at the statistics I.O. output for this, we still do a whole bunch of logical reads against the heap, right?
But we do almost none against the nonclustered index, right? Coming down here, that’s what was happening. This is our index scan, which was, you know, the index that now has no pages assigned to it.
But this thing up here still scans every single page, right? Fun times are afoot. So, if you’ve got heaps, if you’ve got tables that do not have clustered indexes on them, remember that they are flat structures.
They are not tree-shaped. They use something called a row identifier or a RID, which is the file, page, and slot number combination for the row to, instead of a clustering key, to identify rows.
They’re tracked by something called the index allocation map. That is the IAM. And it tracks about four gigs of heap data apiece, or they track about four gigs of heap data apiece.
The good stuff that you can get out of heaps are fully parallel data loads because you don’t have any indexes to maintain. You don’t have to deal with page splits on updates, just forwarded records. There’s less logging overhead for bulk operations.
And empty pages will stick around. They’re not deallocated in case you want to do some more inserts. The not-so-good stuff is forwarded records when rows grow and move pages. We saw that with the updates.
We saw that pages where all the rows got fully deleted off of them don’t get deallocated automatically, and the scans still read a lot of empty pages.
That can be really surprising and really impact performance if you’re not paying attention to it. You need table locks or lock escalation for page deallocation to occur. If you’re trying to get the fully parallel insert into the heap, and if you have any nonclustered index on there, it messes them up.
Role versioning isolation levels will prevent page deallocation, even with the tab lock hint. Rebuilding the heap also rebuilds all the nonclustered indexes at the same time. Forwarded records make nonclustered index lookups a little bit more expensive because you not only have the lookup, but now you also have the following page forwarded records around in there.
If you really just need to dump a bunch of data into a table quickly, heaps are probably the way to go. And simple recovery model will really help with minimal logging.
But, you know, a lot of people look upon heaps as if they are evil structures, but really, you know, they’re just things that are… They’re very specialized structures with very specific purposes that unfortunately have been sort of abused and misused by people over the years and have gotten a bad name.
Sort of like cursors. Ha! See how I did that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll check out the full training course. Again, that’s available.
There’s a link down below where you can get the Learn T-SQL with Erik course. And, yeah, that’s that. All right, cool. See you in tomorrow’s video where we will talk about clustered indexes. Won’t we then?
All right. See you then.

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.

Test SQL Server Backup Performance For Free

Posted on by Erik Darling

Test SQL Server Backup Performance For Free

Chapters

Full Transcript

Erik Darling here, Darling Data, continuing my epic journey through monitoring tool mogulhood. I want to walk through a stored procedure that I wrote to help, I mean, I wrote it for one person to help them test different backup configuration performance, but I’m sharing it with you because that’s how I roll, right? Lots of stuff gets out in the world for free. It’s over at my GitHub repo at code.erikdarling.com. That’s also where you can find other free stuff that I’ve done. This one’s in the Darling data repo where you find the majority of my other SQL Server related scripts, but there’s other stuff up there too, like performance monitors, like this one right here. Look at this handsome devil telling you all sorts of stuff about SQL Server. And then there’s an alternate version of the monitoring tool that also tells you all sorts of useful stuff about SQL Server. You can go read about all the differences between them and what they do. And then there’s Performance Studio. Where you can get good advice about query plans. Just open one up in there and you have me looking at your query plan for you and giving you feedback on it. So there’s all sorts of free stuff that I do that I just want to point out. So this is a stored procedure though. And just kind of walking through some of the code. The whole idea here is that there’s all sorts of stuff that you can do to try to make backups faster.
So for example, you can mess with how many files you stripe it out to. You can choose to compress a backup or not. You can mess with more sort of rocket science-y settings like buffer count and max transfer size. And there’s stuff that you might want to know about your backups while they’re running, like how are they making progress. So you can choose that here. And you can also choose how many times you want to run through the test because just running a test once, you know, you can get some good information. But you kind of want to run it maybe more than once to figure out, like, was that an outlier? Was that like the good average of what happened? So there’s things that we can do programmatically that are a lot easier to do. But it requires testing, right? We’ve got to know what we’re in for here. So the stored procedure, what it essentially does, there’s a help section here in case you forget what any of those things mean. But there’s also an example execution here if you’re into that sort of thing.
But it’s kind of what this thing does after it, you know, does some initial sort of smart stuff is it, it takes all of the parameters. So the you pass in parameters is a comma separated list. And what what I do, there’s a table where we hold the sort of logging results for each run through. What it does is, it takes all of the different things that you pass in and those CSVs. So like, like compression settings, number of files you’re striping to max transfer sizes, you know, buffer counts, things like that. And to put in and it parses those comma separated lists out into temp tables. And then what it does is it cross joins all of those temp tables together, right?
And what that does is it creates a combination of everything that essentially explodes all those CSVs out into into a whole bunch of different test combinations of things. So you can test combinations of like all that stuff in one run through over many, many iterations, then, you know, of course, the usual sort of, you know, logging and, you know, message tab awareness of where you are in the loop, because that’s important stuff, right? If you’re running things, you kind of want to know where they are. So you’re not like, hey, when’s this thing going to be over? What am I doing here?
So that’s what like a lot of this code does. And then, you know, after we kind of go through all this, the cursor that does important things, and more printing things out, that’s fun. And then when we get down, though, this is where we build up the sort of backup command that we’re going to run. And then we grab a whole bunch of information about what happened there, and we log it for each run through.
And then that allows us at the end to not only show you sort of a general, give you a general sense of which ones work best, but also to sort of do some reporting on things, right? So like down here, this will, the first report result that you’ll get back is going to be the result of all configurations ranked by which one had the best throughput. So like which one, which one completed the backups the fastest.
Then there’s another report that will tell you the best config per compression setting. So if you test compressed and uncompressed, this doesn’t, like if you only, if you only test compressed or only test uncompressed, this one’s a little bit less useful. But if you’re testing both, then you’ll get the best configuration per compression setting.
And then the third one is which parameters had the most impact. So like when we want to figure out like, you know, like, like, like across all of the backups that we took, which ones like had the biggest impact on like, like, like every backup that they were involved in. That’s what this will tell you.
And then, which might be important for your environment, right? That’s, it might be a good thing to know about. And then we have another report that tells you the best throughput per megabyte of RAM. So like, you know, different types of backups require different amounts of memory.
Uncompressed backups, you know, can, like the amount of memory buffers required is going to depend on max transfer size and buffer count. And then like compressed backups are actually sort of like 3x the memory size usually because you have three different compression streams that you kind of have to deal with. So you use like, like, like, well, compression is wonderful for like what ends up on disk.
The memory that it uses while the backup is being taken can be, you know, is more by 3x. And then there is a final fifth report that only runs if you did multiple iterations where we talk, where I basically report on how consistent each backup setting was. So the lower the standard deviation is, the more predictable it is.
And that is ordered by most to least stable, which helps you sort of figure out if there were outliers or if like, you know, taking like one set of backup options just had wildly different metrics after each run through, then that’s what, you know, that’s what this would show. But anyway, there’s an example run of this over here where I tested this just backing up to null. If you do backup to a path, it will clean up the backups after each iteration through.
So you’re not going to have to worry about like disk space blowing up. It uses the built in sys.delete, xp delete file thing that like all the scripts use it. It’s like like one of like the purge things and maintenance plans, but it allows you to delete backups from a particular path.
So if you are using a test backup path, don’t back, don’t run these backups to your like default location. That’s insane. But it will delete like the backups that it made.
It’s not going to delete all your backups. It keeps track of which, like the backup files that it created and like the name of them and the path to them. And it just deletes those. It’s not deleting every backup.
Don’t worry. I don’t want to get in trouble for you being like, I went to test my backups and Eric deleted all my backups. I wouldn’t do that. That’s cruel. So, but I just ran a sort of quick round through these and the results look about like so. It is maybe not terribly interesting backing up to null.
Oh, look at that. We expanded all our results at once. What a good time. So this is what it’ll show you. And I use the Stack Overflow 2010 database for this, which is maybe not the greatest example because it’s only about like 8, 10 gigs. All right.
It’s pretty small. But I just needed something that I can run through kind of quickly to show you what the results look like. So this just shows you sort of what I got locally backing up to null with a pretty small database. For you with bigger databases, you might find different things make more sense.
You might find that different configurations, maybe even depending on like how your database is set up internally with like files and file groups. You might find that different backup strategies work a little bit better for you. But for me, this is what the numbers look like.
Again, take these with a grain of salt. I’m not saying that this is like my results here or how you should take your backups. What I’m saying is you should use this procedure to test and see which ones work best for you, which is the whole point of why I wrote this. So, yeah.
Again, this is all available at code.erikdarling.com. You can go find it in my GitHub repo and it’s in the Darling Data one. It’s going to be called Test Backup Performance.
It’s got its own folder with a little readme in there so you can read all about it and enjoy yourselves. And maybe you’ll even use it and run it. Maybe you’ll even, I don’t know, want to do something with it. So, this is not part of the bunch of scripts that I usually run to do like server analysis.
Note that there is no SP underscore in front of the name because this one is a little bit outside of what I would normally be doing with someone. But I had to do it. I thought you might like it.
I thought you might find it useful. So, there we have it. Anyway, again, that’s at code.erikdarling.com. Check it out. If you run into any issues with it, open a GitHub issue because that’s where issues get solved. Don’t email me.
Say, I hit an error running your procedure. Help me. I’m going to tell you to open a GitHub issue. All this stuff is on GitHub for a reason. Open an issue where it makes sense.
Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I hope that you will test your backup performance. And I will see you in tomorrow’s video where we will talk about, where we’re going to get back to, I’m going to get back to our roots a little bit and we’re going to talk about SQL Server stuff that is not an open source tool or vectors or backups. Backups.
Can you imagine me having to do backups in this day and age? Anyway. All right. Thank you for watching.

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.

Advanced T-SQL Triage: I’m Coming to Zagreb

Posted on by Erik Darling

Advanced T-SQL Triage: I’m Coming to Zagreb

On Friday, June 12th, I’ll be in Zagreb, Croatia, running my full-day workshop Advanced T-SQL Triage: The Art of Fixing Terrible Code as a pre-con for Data Saturday Croatia 2026.

Quick pronunciation notes up front, because last time I tried to teach in Wroclaw I got a lot of corrections:

  • Zagreb: ZAH-greb. Okay, easy enough. Even my American mouth can make that happen.
  • Hrvatska (what Croatians call Croatia): HUR-vat-ska. Yes, it starts with “hr.” Yes, that’s a syllable. The Slavs are economical with vowels.
  • Hvala (thank you): HVAH-lah. You’ll need this one. Use it: Hvala for being patient with me.

A cultural side note for fellow stationery enthusiasts: Zagreb is where Slavoljub Penkala patented the mechanical pencil in 1906, AND the solid-ink fountain pen in 1907.

I’m told Croatians still call any pen a “penkala” after him. The Penkala-Moster factory was one of the largest pen-and-pencil operations in the world. I like a nice pen.

For me, this trip is basically a pilgrimage with a query refactoring side-hustle.

Now, the actual reason you’re here.

What we’re doing all day

A full day rescuing T-SQL from itself. And you. Mostly you.

We’ll look at queries that are actually broken and fix them:

  • MERGE misuse: the statement even your mom warns you about, and somehow it’s still in your codebase
  • JOIN problems. ON. WHERE. Things people screw up constantly.
  • Paging logic that feels like it’s coming from a dot matrix printer
  • Window functions that need a Windexing
  • CTEs and their unnecessary evils
  • Scalar UDFs and how to rewrite them so they stop wrecking your execution plans
  • Dynamic SQL that’s parameterized, fast, and safe

Fast-paced demos, from the nightmarish world of SQL Server consulting.

Details

Quick housekeeping note from the organizers: after seven years of running Data Saturday Croatia for free, this year there’s a €10 entrance fee for the Saturday event. That’s €10. Roughly the price of a coffee and a pastry beer in any city you’ve been to recently. Pay it. Show up. The community deserves it.

If you’re in the region: Croatia, Slovenia, Hungary, Austria, Italy, Serbia, anywhere a reasonable train ride/puddle jumper away, come fix some queries with me, then stay for the Saturday community event. Zagreb in June is genuinely lovely, so I hear. It’s my first time going, and I’d hate to be all lonely in a new city.

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.

SQL Server Performance Office Hours Episode 58

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 58


To ask your questions, head over here.

Chapters

  • *00:00:00* – Introduction to the session
  • *00:01:15* – Parameter sniffing and its impact
  • *00:03:10* – Parameter sniffing examples and explanations
  • *00:06:35* – Parameter sniffing solutions and workarounds
  • *00:10:26* – Query runtime variability and its causes
  • *00:13:33* – Conclusion and next video preview

Full Transcript

Erik Darling here, Darling Data, and it is Monday, officially, official Monday, which means it is officially time for an Office Hours episode where I answer five of your wonderful, thoughtful, community-submitted questions. We’ll see what we have this week. It should be amusing. Oh yeah, Office Hours, that thing. If you want to ask your own Office Hours questions, you want to have something that you want to ask me instead of a robot? Well, you know, I want some of that human-generated warmth and thoughtfulness. You can do that. There are links where the finger points down below in the video description where you can submit your question to me. There are also other ways for you to interact with me if you’re interested in giving me money. Like, you can become a subscribing, supporting channel member. You can buy my training and you can hire me for consulting. So, all of all of those things give me money. The Office Hours thing does not give me money. Likewise, liking, subscribing, until a friend does not give me money, but does bring me incredible joy as I see the numbers on the YouTube channel continue to rise beyond my mildest expectations. If you want to get some free SQL Server performance monitoring, there is also a link down in the video description for that. I’ve released a free open source SQL Server performance monitoring tool. There’s no email involved. There’s no phoning home. There’s no telemetry. You can see everything it’s doing. You can contribute to it if you want. You know, it monitors all the stuff and all the stuff you would care about when it comes to performance on a SQL Server. All the stuff that I would monitor, which is a pretty good slate of stuff. And then if you prefer the robots, of course, there are opt-in optional MCP servers that you can interact with or you can have your robots interact with. And they can look at your summarized, time-sliced, well-collected, informed performance data. And they can maybe give you some answers pretty quickly about it. I’ll be leaving the house, bringing my human-generated warmth to the people of the world, all over the place. I will be at SQL Day in Poland, May 11th through 13th. I will be at Data Saturday, Croatia, June 12th.
12th and 13th. I will also be at Pass On Tour and Pass Summit in Chicago and Seattle. Chicago is May 7th and 8th. Boy, that cuts real close to Poland, doesn’t it? Real close. Boy, oh boy. That’s right there. I’m actually flying from Chicago to Poland. So from Little Poland to Big Poland. And then, of course, Pass Summit in Seattle, Washington, November 9th through 11th. Bit shorter this year, but that’s okay. Saves me a little money because travel ain’t cheap. Anyway, let’s answer these questions. All right. So we’ll come over here to our Excel file of joyous questions that shows me what people have submitted here.
And the first one is, if your primary reason for using the Legacy Cardinality Estimator is to improve join estimates, why not use either Optimize For or Assume Join Predicate Depends on Filters? Your article on the latter is excellent. That is not my article. That is Joe Obisch’s article. I just have a sort of annoying plug-in on my website. It’s called Last Updated. And if I have to go in and make any, not editorial, but other changes to the post, usually, sometimes I’ll tweak the footer information down at the bottom. It changes and says, it looks like I wrote it.
I don’t have a way to fix that. So sometimes it looks like I’m taking credit for one of Joe’s posts. But I assure you, it is not mine. It is his. I would never take credit for that. It is a very good post. But the reason why I don’t use it is because it is far more typing.
No. So when I’m looking at a query plan, join cardinality is one of the things that will stick out to me as a potential reason to want to use the Legacy Cardinality Estimator. So that’s usually where I start. Do I ever end up there? Sometimes. But most of the time, just doing the Legacy Cardinality thing works just fine for me.
So, it’s not like I’m saying don’t use that, or it’s not like I’m saying it’ll never be useful. But for me, most of the time, just using Legacy Cardinality Estimator is what gets me where I need to be. All right. Next question.
We aggressively removed key lookups by adding tons of covering indexes, and somehow overall performance got worse. Can you explain how that happens in practice? Sure. So, assuming that you, well, I mean, obviously, you said you added tons of covering indexes.
What are they covering now? How big did your indexes get? How big did they used to be? So, what you’re going to have now is lots of presumably much larger nonclustered indexes, and much wider nonclustered indexes, which is going to have some important ramifications on several crucial, critical components of your SQL Server.
One is the buffer pool. Much wider indexes take up much more space in the buffer pool, so you are no longer able to cache as many competing objects in your buffer pool, which means that you will most likely have to go to disk more often.
The other thing that you are going to affect pretty significantly is around the potential blocking and deadlocking issues. Because now that your indexes are much wider, there is a much greater chance that, well, at least an update, but certainly an insert or delete will have to modify those.
And you may have queries that did not used to get blocked, now get blocked. Third and finally will be your transaction log. Because now that you have all these much wider indexes, it will be absorbing many more modifications.
You will also need to write more to your transaction log. So, in practice, those are the three basic things that much, much wider indexes will mess up. So, while all your key lookups may be gone, and who knows if any of them or all of them or what portion of them were a problem in the first place, you probably made three other things a whole lot worse.
So, use wisely. Let’s see here. There’s my little dot.
Next up. Everyone says, who’s everyone? Tuning queries saves cloud money. But we tuned a ton, and Azure costs barely budged. Is that normal?
I don’t know. Depends on how good of a job you did tuning. Depends on what you tuned for. And also, it depends on a couple other things. So, if you follow sort of old advice and you tune for things like logical reads, well, that may not necessarily drive down CPU.
If you did not do a very good job of tuning, then perhaps costs did not improve. I’m assuming now that you’re on the DTU model here.
So, that would be where you would see, like, insta-savings because you would no longer be using any more, you know, of those sort of cost units that Azure puts on the DTU skews of Azure SQL database. If you’re not using the DTU model, if you are using, like, the sort of, you know, core-based model for things, well, not only do you have to tune, but you also have to make the instances smaller.
Or else, you’re still paying for all that potential bandwidth, even if you’re not using it. So, depending on where you are and what you’re doing and what you tune for, just saying that, well, I don’t know, that’s probably why. But, you know, you may have something else fun and weird going on.
So, as always, my rates are reasonable. If you actually want to save money on Azure, we can talk about that. Is it better to have a stable plan that’s a little slower or a faster plan that sometimes misbehaves?
Ah, you know what? Honestly, depending on what value of little you’re using here, I would take the stable plan that’s a little slower. There are, you know, it’s not fun if you have a plan that sometimes misbehaves, even if it is, you know, a little faster on the whole, because dealing with those misbehaviors is not enjoyable, especially if they happen late at night on weekends, while you’re at a kid’s birthday party, all that stuff, all of a sudden, email starts blowing up with alerts.
No good, right? So, I would take stability sometimes. There are some times stability is nice. You know, it’s sort of like the difference between having a job and consulting, right? Like, you have a full-time job, you get a steady paycheck, you know, 401k, health insurance, consulting.
You can usually make a lot more money than a full-time job would pay, but sometimes things misbehave. And, you know, sometimes that trade-off is worth it, sometimes not. But for the most part, if we’re just talking about you doing your full-time job, like as a consultant, you know, a plan that sometimes misbehaves, I can make some money off that.
As a full-time employee, you make the same amount of money, whether you have to deal with that misbehavior on Saturday at 2 a.m. or, you know, anytime, right? So, for you, if you’re a full-time employee, I would take the stable plan.
As a consultant, I would take the faster plan that sometimes misbehaves because taming that misbehavior, that’s where I get to send someone an invoice. Anyway, last but not least, this is going to be a fun one.
We can enjoy ourselves here, can’t we? So, same query text, same parameters, same plan, wildly different runtimes. I feel like I’m losing my mind.
What causes this? Well, let’s just assume here, because I’m going to pretend that you are a very, very smart person. Even though I know nothing about you, I don’t know who you are, I’m going to pretend that you are very smart.
And the reason I’m going to do that is because I don’t want to answer another boring parameter sensitivity question and explain that whole thing again. I’m going to pretend that what you mean here is that let’s say you have a store procedure that accepts one parameter, and let’s just, for the sake of argument, say that sometimes you execute that with parameter equals one, and it’s fast.
And sometimes you execute it with parameter equals one, and it’s slow. There are going to be three things that I would look at that might tell you what’s happening. One is overall server load, right?
You might execute that at a time when there is a whole lot more going on with your SQL Server, and that query may suffer. The sort of hardware concurrency issues.
The other thing that might happen is that query may sometimes be getting blocked. All right? So if sometimes you run that query and it’s slow, well, there’s other stuff going on in the server, you may be suffering from sort of logical concurrency issues where a SQL Server, like some of the pages that your query needs to read are being locked by a modification query, and it takes, like you’re looking at your watch or your clock or whatever, and you’re saying, hey, this query’s taking a long time compared to how it used to run.
I wonder what’s happening. For those two things, what you could look at is probably who is active, right? Take a look at how busy the server is when the query is slow.
And you could also use SP who is active to see if your query is getting blocked by anyone when it’s slow. The third thing, and this is another sort of hardware concurrency related thing, is that sometimes the data that you’re reading might be in the buffer pool, and sometimes the data that you’re reading might be on disk.
If the data that you’re reading is already in the buffer pool, then it will probably be pretty fast. If you have a lot of data to read, and it’s not in the buffer pool, it’s only on disk, you have to read that data from disk into memory.
That can slow your query down quite a bit. So, those are my three best explanations for a very assumed set of circumstances. And I think that also brings us one, two, three, four, five entire questions.
Cool, we’re done. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you did learn something. Learning something in the future might be.
Everyone learns something in the future, I think. And I will see you in tomorrow’s video, where we’re going to talk about a store procedure that I wrote to help a client out.
They were trying to test different backup strategies, and so I wrote a store procedure to test a whole bunch of them and report back on metrics. So, we’ll talk about that in tomorrow’s video.
It’s already up on the Darling Data GitHub repo. So, if you want to see it ahead of time, you go to code.erikdarling.com and you can see it there.
All right. Thank you for watching.

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.

Better Domain Knowledge Makes For Better Queries

Posted on by Erik Darling

Better Domain Knowledge Makes For Better Queries

Chapters

Full Transcript

Erik Darling here with Darling Data, continuing my upward trajectory as a monitoring tool mogul in the SQL Server community, but today we’re going to take a little break from shoveling free software to the masses, and we’re going to talk a little bit about how you can use better domain knowledge to write better queries, and I’ll give you a little example of that using the Stack Overflow database, because that’s usually how these things work, isn’t it? Anyway, down in the video description, you will find all sorts of helpful links wherein you can hire me for consulting, buy my training, become a paid supporting member of this YouTube channel, and also where you can do other things, like ask me office hours questions, and as always, if you enjoy what I’m doing over here, please do like, subscribe, and tell a friend, because I like to see the number, I like to, bigger number is better for these things, so please, please spread the good word, my friends. Okay, we’re going to briefly pimp some free software here. I built a free SQL Server monitoring tool, free, open source, available on GitHub, the link to that is down in the video description as well.
If you are in need of that sort of thing, if you care about the performance of the SQL Server, but maybe don’t have the budget to get a paid monitoring tool, which charges you way too much for the pieces of crap that they are, then you can use mine, which is not a piece of crap, and well, you know, it’s pretty good at this point. So, you’ve got that. It’s an option for you, right now. I will be out in the world doing all sorts of fun things.
I have SQL Day coming up in Poland, May 11th through 13th. Data Saturday, Croatia, June 12th and 13th. Pass on tour in Chicago, Illinois, May 7th and 8th.
And, of course, Pass Data Summit, the full big deal out in Seattle, Washington, November 9th through 11th. So, if you are in the ticket buying mood and any one of those locations seem seemly to you, well, I suggest you go buy tickets. I’ll have pre-cons at all of them, teaching advanced T-SQL stuff, so, you know, you should show up, do that.
Anyway, it is March, but it doesn’t feel much like March today. Actually, I’m not sure what it feels like, because my fingers aren’t really feeling much. So, you know, hopefully it’s not a stroke, but I’m pretty sure it’s just cold, because I can still do a drumroll.
So, anyway, we are back into having two spawns of SSMS open territory in my life, so here we go. Now, a lot of times when I am helping clients write queries, you know, it takes, there’s like a learning curve with just about any data set. Because when you first sit down and you look at things, you might say, oh, well, they’ve got some tables.
And, you know, this is the way that they’re joining those tables together. These are the existing queries, so maybe I should follow that pattern. But then you might find that some of those patterns aren’t really correct and don’t really yield the results that anyone would expect or want.
You get kind of weird stuff back, and you’re like, I don’t think that’s exactly it, right? Like, you know, you’ll learn to get a sort of taste for these things the longer you’re consulting. And so, like, a lot of the time that I spend, aside from, like, making the queries faster and, you know, figuring out why the queries are slow, stuff like that, is just sort of figuring out the data that’s underneath them.
Because sometimes the more you look, the stranger things you find. So, if you were to sit down at the Stack Overflow, sit down and you became a developer at Stack Overflow. Not that I think they’re hiring at this point.
No, this is the same. If you were to become a developer at Stack Overflow, you might see, like, users and you might see posts and you might say, well, I’m going to assume that there is a pretty strong relationship between users posting things and posts existing and stuff like that. And, like, I’m pretty sure this seems like, you know, a pretty strong relationship, right?
Users post things. And so, you might get a request to write a query and you might say, no problem. I need to find the user with the most posts, so I’m going to go do that.
And you might run a query that looks like this. You might get no results back using the magical live zoom feature available in SQL Server Management Studio 2022 point something. I can zoom right in there and my green screen is having a tough day.
But I can zoom in and see that I got no results back and I am quite saddened by this. So, this is where sort of, like, examining the data and getting better domain knowledge about it can help you write faster queries better and better queries faster. Both ways, right? Effective and efficient.
People say you can’t have both. I say you can’t because I care about you. So, if we were to look at who has the most posts in the post table, we would find this owner user ID zero. Who is this mysterious owner user ID zero with 226,238 posts?
What a prolific, prolific thing that must be. What a user. Well, we don’t have that user ID in the post table.
That is not a person. That is not a thing that exists. And so, we become confused and sad. Maybe a bit listless, limp.
You know? Just a flab in the world. And then we start to lose faith in these relationships. You might say, well, gosh darn it, why don’t we have a foreign key?
You might, maybe it’s not enforced or something, right? Maybe someone untrusted the foreign key. There’s all sorts of things that are like, ah, I thought that made sense, but now it doesn’t make sense. So, like, again, the more domain knowledge you have about the environments you’re working in, the more effective you become in them.
So, if, you know, going through this exercise and saying, well, I no longer trust, like, that foreign key, SQL Server doesn’t trust it. I don’t trust it either now. You might say, well, what if I go and check first, right?
So, what if I say, now, instead of just getting whatever from the post table, I say, well, I need to add a qualifier here. I have to say that whatever user ID I find, that user ID has to exist in the users table because without that, we get no results. And that doesn’t make for a very good query experience.
And so, with that domain knowledge applied, now we can run this query and we can get back a very expected result, right? We get back top post to John Skeet. So, my advice here is don’t take relationships for granted in SQL or in life, I suppose.
When you’re, you know, first starting to work with a data set and learn about that data set, I do encourage you to explore it so that, you know, because you might be helping someone tune a query that’s wrong. You might be helping someone tune a query that doesn’t make sense, right? So, the more time you spend learning the data and exploring, the better off you are.
And I guarantee you, it will help you not only write better queries, but in some cases, it might even help make your queries faster because you’ll be avoiding touching a lot of data that you don’t have to. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you m-m-m-m-monday for office hours. All right. I hope I have some questions. We’ll have to go look. I’ll go check out the Google form today.
All right. Goodbye. Have a good weekend.

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.

Advanced T-SQL Triage: I’m Running a Pre-Con at SQLDay in Wroclaw

Posted on by Erik Darling

Advanced T-SQL Triage: I’m Running a Pre-Con at SQLDay in Wroclaw

On Monday, May 11th, I’ll be in Wrocław, Poland for SQLDay 2026, running my full-day workshop Advanced T-SQL Triage: The Art of Fixing Terrible Code.

Quick pronunciation note before we go any further: it’s *VROTS-wahf*. Not “Rock-law.” Not “Rack-claw.” Not whatever you just tried in your head. The “W” is a V, the “ł” is a W, and the “c” is making its own decisions.

I was wrong about it for years. You will be too. That’s fine. The Poles are patient people (I hope).

SQLDay is the largest data conference in Central and Eastern Europe, held at Centennial Hall — a UNESCO World Heritage building that genuinely makes the average US convention center look like a storage unit.

Workshops are Monday the 11th, the main conference runs Tuesday and Wednesday. If you’re anywhere in Europe and you’ve been meaning to catch one of these workshops in person, this is the one.

All attendees get free access to Learn T-SQL With Erik.

What the day looks like

A full day rescuing T-SQL from itself. We’ll look at queries that are actually broken and fix them:

– Paging logic that scans when it should seek
– Window functions that spool and spill because no one gave them a sort to work with
– Indexed views that look clever on paper and lock everything in practice
– Data modifications that block like linebackers
– Dynamic SQL that’s parameterized, fast, and safe
– When CROSS APPLY is the right tool, and when you’re reaching for it because you don’t want to think
– Views vs. inline TVFs vs. scalar UDFs, and why the optimizer treats them very differently
– Why RIGHT JOIN is not simply LEFT JOIN spelled backwards
– Rewriting scalar UDFs so they stop wrecking your execution plans

You’ll leave with a cheat sheet, a working mental model for diagnosing slow queries without guessing, and — if you haven’t been to Wrocław before — a strong case for staying an extra day.

Details

– Workshop: Monday, May 11, 2026 (all day)
– Conference: Tuesday–Wednesday, May 12–13, 2026
– Where: Centennial Hall, Wrocław, Poland (in person + online)
– Register: Here

If your queries scare you, come fix them with me. And if you’ve already got them under control, come anyway — there’s a three-day conference right after, and Wrocław’s old town is worth the flight on its own.

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.

Query Hashes and Parameter Names in SQL Server

Posted on by Erik Darling

Query Hashes and Parameter Names in SQL Server

Chapters

Full Transcript

Erik Darling here, with Darling Data. Removing my monitoring tool mogul superhero outfit costume, play suit, for a moment. Talk about, I don’t know, something interesting that I ran into while working with some stuff in Query Store, and I thought that I would share with you. And it is the behavior of how queries get hashed when parameters get used. And I think if you’re the type of person who goes looking through Query Store for specific things because you use store procedures like SPQuickieStore and not the Query Store GUI, which, unfortunately, as of this late date and its inception, still does not allow any real, does not really have any search capabilities. Down in the video description, you will see all sorts of links that will help you get closer to me.
You can hire me for consulting, which is probably the ultimate in closeness. You can buy my training. You can become a supporting member of the channel. You can ask me office hours questions. And, of course, as always, if there is someone in your life who you feel needs this brand of enlightenment or harassment or whatever I happen to be up to, please do share, like, subscribe, tell a friend, all that good stuff.
Anyway, I have a new, well, I guess, I mean, I don’t know, like a little over a month old now, SQL Server Monitoring Tool. Totally free, totally open source, no intrusive stuff in your life. Just a really great way to keep an eye on SQL Server performance for servers you care about, but may not be able to get budget for a paid monitoring tool that would do this job.
Collects all the stuff that you would want to know about, all the stuff that I would care about and get into during my consulting engagements. And then for those of you who have embraced the robots, there are opt-in MCP servers that can talk directly to your collected performance data so that you can, you know, have the robots to reduce some analysis on just what got collected. It’s all nice and sliced up over time.
So they’ll be able to do a much, much better job of looking at those things than they would if you just set them free to a running SQL Server and you said, go run some DMV queries, I trust you. I wouldn’t do that. As far as me getting out and about in the world, apparently there is still some hunger for in-person connections.
So I will be traveling around. I will be at SQL Day in Poland, May 11th through 13th. I will be at Data Saturday, Croatia, June 12th and 13th.
Those are my fabulous upcoming international events. I’m very excited. You know, you can get away for a little bit. I’ll also be at Pass On Tour, Chicago, the Illinois, May 7th and 8th.
And I will be at Pass Summit in Seattle, Washington, November 9th through 11th. So I believe tickets are on sale for every single one of these things. So there’s very little excuse for you to not come see me in person as long as, you know, I guess, as long as you can get there.
Can’t we all, can’t we all just get there? Anyway, it is time at long last to look at this interesting thing. So let’s go over to SQL Server Management Studio.
We haven’t seen one of these in a while. And the first thing I want to say here is that this is not a political statement. I was born the night of this election and I often use this as a sort of jokey reference point to things in my life. But we have two queries here that are fundamentally identical, right?
We’re saying select c equals count big from dbo.users as u, where u.id equals some parameter. Right? And the reason why this is interesting is because for a very, very long time, I was under the, I guess, mistaken impression that parameter names were taken into account when generating a query hash. But after asking my dear friend at Microsoft, who I guess in turn asked their internal AI chat bot about this, which I thought was funny.
They said, the chat bot came back and said no. At this point in the code, when SQL Server is trying to figure out what a query’s hash should be, it sort of substitutes the parameter with some just constant node in there. And it’s just like, it doesn’t matter what you’re named.
Right? You can be anything. You’re just a parameter. What good are you? Right? You can be anything. Right? It doesn’t matter. So I thought that was funny because it turns out I was wrong. And the reason why this came up is because I was working with a client and we came across a troublesome query.
And I was like, well, let’s, let’s, let’s look this up by query hash and query store. Let’s see what we can’t get out of this thing. Let’s see what this thing is fully up to out in the world.
And so we put the query hash in the SP quickie store and I said, go find it, SP quickie store. And as fast as lightning, SP quickie store was out there finding query hashes for me. And it came back with two rows, one for the query we were looking for and one for another query that looked rather similar, but had a different query, but had a different parameter assigned to it.
And I said, oh, well, that is fundamentally distressing. Anyway, enough about me. How about you? Let’s run these two queries.
And, you know, we’re going to get execution plans for them. Not that the, not because the execution plans are of any interest to us whatsoever here, but because when we go and look at the query plans, we are going to see some interesting things.
So if we get the properties of this one, right, we have all of the usual attendant, lovely information that Microsoft has provided to us in the properties tab. And if we zoom in over here, we will see this thing. We will see the query hash and query plan hash for this.
These are somewhat long and difficult to remember. But if you sort of just get a general sense for them, I like to remember like sort of the last four digits, the last four bytes of the hash.
So we have like 9E02 and 7383. They start with 452 and 134. All right.
And if we just sort of keep this in mind and we go over here, we notice that when I switch back and forth between them, the parameter names change, but the query hash and query plan hash don’t budge, right?
None of that changes, right? We get a different SQL handle for it down here, right? We can see the SQL handle change because I suppose that does take the parameter name into account, but the query hash and query plan hash stay absolutely identical, right?
Down here on Jimmy Carter, we have the same thing. Starts with 452, starts with 134, ends with 9E02 and ends with 7383. So if you’re ever looking through a query store in this, you know, unlikely scenario, you’re using SP Quickie Store available at code.erikdarling.com.
You’re looking through a query store and you say, I want to find something by this query hash. So you use my very thoughtful include query hashes parameter to do that.
And you find multiple queries, the same hash. Well, if they have different parameters, that’s why. Parameters don’t factor into the query hash.
You learn something new every decade at least. Alright, that’s about it for me. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I’ll see you in tomorrow’s video. Have a good one.

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.

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

Posted on by Erik Darling

T-SQL That Doesn’t Suck: I’m Running a Pre-Con at PASS Summit East

On Thursday, May 7th, I’ll be in Chicago at PASS Data Community Summit East, running a full-day pre-con called T-SQL That Doesn’t Suck: Solving Performance and Concurrency Problems.

The pitch is simple: you already know how to write T-SQL that runs. It compiles, it returns rows, nobody’s filed an incident yet. The problem is “runs” and “runs well at scale” are different conversations, and production tends to be the one asking the hard questions.

All attendees get free access to Learn T-SQL With Erik.

What we’re covering

The day splits roughly in half.

First half is the performance problems that don’t show up until you actually have data and traffic behind them:

– Implicit conversions that quietly kill your seeks
– Non-sargable predicates hiding behind innocent-looking WHERE clauses
– Parameter sniffing traps — when it helps, when it hurts, what to do about it
– Joins that look fine in the plan right up until they aren’t
– Temp tables vs. table variables, and when each one actually wins
– CTEs that help vs. CTEs that just make the query feel organized
– Window functions that don’t spill to tempdb

Second half is concurrency — the stuff that turns a Tuesday afternoon into a war room:

– Blocking chains, and how to actually read them
– Isolation level surprises
– DML that holds locks like it’s paying rent
– Patterns that let readers and writers coexist without fist-fighting

We’ll also put AI-generated T-SQL on the table. Not to pile on — it’s showing up in pull requests whether you like it or not — but to talk honestly about where it falls apart and where it actually saves you time.

Details

– When:** Thursday, May 7, 2026, 9:00 AM – 5:00 PM
– Where:** Hyatt Regency McCormick Place, Chicago — Jackson Park B
– Level:** 300 (if you’re past “what is a clustered index,” you’re in the right room)
– Register:** Here

The hotel discount at the Hyatt cuts off **April 22**, so if you need a room at the conference venue, book this week.

Chicago in May. T-SQL all day. Come write queries you’d be proud to put your name on.

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.