Query Plan Archives | Page 50 of 147

Performance Tuning TOP PERCENT Queries In SQL Server

Posted on August 23, 2024January 22, 2026 by Erik Darling

Performance Tuning TOP PERCENT Queries In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into performance tuning top and percent queries—a topic that might not come up every day but is crucial when it does. We explore how to make these queries run much faster by using Common Table Expressions (CTEs) or derived tables instead of parameterized queries. By doing so, we eliminate the need for sorts in parallel plans, significantly reducing execution time. I walk through a stored procedure example where I demonstrate this technique step-by-step, showing how it can drastically improve performance without complicating your code too much. Additionally, I share some insights on upcoming events like Data Saturday Dallas and Past Data Summit, where you might catch me live. If you’re interested in learning more about SQL Server performance tuning or need help with health checks, performance analysis, or training, feel free to reach out; my channel is here for you.

Full Transcript

Erik Darling here with Darling Data. Look at that handsome logo. Look at that handsome head. Look at all that handsome floating around in here. We’re going to try something a little bit different in these videos because I did notice that when I told people to do stuff in the beginning of the video, they did stuff. So we’re going to start off with my appeals. at the beginning of the videos rather than just at the very end of the videos where most people have stopped watching because they’ve got what they needed from me. And usually those transactions don’t end quite as well. And also, it’s my channel and I can do what I want. When you get your channel, you can do what you want. In today’s video, we’re going to be talking about performance tuning top and percent queries, which is something you might not have to do very often in your career. But when you do have to do it, it can be quite an excruciating process. And we’re going to talk about how you can make it much easier on yourself. So about my channel, all of this content is free. And I’d like to keep it that way without commercials and other crap. So thank you, Intel for chiming in about the driver timeout. Great. Intel’s not doing so great lately. Their CEO was was praying on Twitter the other day as stocks are melting.

So we’re going to leave Intel be for a little bit. But yeah, all of this content is free. I like likes, I like comments, and I like subscribers. It’s a good way for me to know if I’m doing a good job or not. If you feel like supporting my channel, there are like low cost memberships where it’s like a few bucks a month if you just feel like saying thank you. You don’t have to, but if you’re feeling generous, go for it. I haven’t really promoted this much because I don’t really know what I want to do with it just yet. I think that ideally I would like to turn it into some more sort of like private one-on-one, well, maybe not one-on-one, but like private group stuff for people who donate at higher levels. We’ll see where it goes. I haven’t quite fleshed it all out yet, and I don’t know when I’m going to. So we’ll get to that. We’ll cross that bridge when we come to it. Hopefully, hopefully, and hopefully in a nice car. I’m a consultant. I do SQL Server performance tuning. If you need health checks, performance analysis, hands-on tuning, you have a SQL Server emergency, or you want to get your developers trained so that they don’t stink at SQL Server anymore, you can hire me. I do those things for money. That’s a good transaction for most people. I also sell training. If you go to training.erikdarling.com, there’s also a link in the show notes for this stuff.

You can get over 24 hours of streaming SQL Server performance tuning content at the beginner, intermediate, and advanced levels. And if you pay enough attention to this video or click the link in the show notes, you can get 75% off everything with the discount code SPRINGCLEANING. As far as upcoming events goes, where you can catch me live and in person, Friday, September 6th of this current year, 2024, I will be at Data Saturday Dallas. I have a pre-con on the Friday, and then I’m speaking at the regular event on Saturday. And of course, November 4th and 5th, I will be at Past Data Summit. This slide has a lot of white space on it, and I haven’t quite figured out how I want to fill that yet. It’s also possible that after September 6th, this slide will have a lot more white space on it, because I will be purely focused on being prepared for Past Data Summit.

So if you feel like seeing me in the flesh, as they say, those are two good ways to catch me there. And now we have gone black, which is a lot of the opposite problem of white space. And we’re going to move on and cover the subject of our video.

And well, let me actually close this. The query plans, we’re going to get to the query plans. But I have a couple indexes on the post table. I have one that supports what we’re searching for and what we’re ordering by.

And I have one that just supports what we’re searching for, because I want to show you two sort of different query plans related to that. Now, here’s the store procedure that I have that is looking for the top percent, top whatever percent we plug in rows for a specific post ID. You know, this probably is not, again, this is not the most common routine for returning rows out to a client.

But I do see a lot of people use it just because, you know, sometimes, you know, it becomes kind of wonky if you’re like, yeah, give me the top thousand rows, but a thousand rows don’t exist. So some people prefer to return a non-hard-coded number or a non-default number. They want to return a number of rows based on the population of data that they’ve got.

So, you know, it’s a somewhat, you know, somewhat less common thing to do, but it can also be a little bit less confusing when you’re like, yeah, give me the top thousand rows. But again, the thousand rows don’t come back. So I’ve got this query set up in a few different ways.

Four to be precise. Four. We’ll count them. Number one, just a regular top end percent query to start things off right here, where we’re just doing what we’re doing with no interference. And then below it, I have a query hinted to use the index that supports both the searching and the sorting here.

Then the third query is hinted to use the index that just supports the searching. And then finally, I have a query at the bottom where this is all expressed without any parameters. This is expressed with literal values, because what I wanted to show you is that this is not an effect of the top percent being parameterized.

This is just what top percent query plans tend to look like. Below this, I’m going to show you a way to make these go way faster. Okay.

So coming back to the query plans, I think potentially my new least favorite trail of query plan operators is a sort, which of course is parallel, because right next to it, we have a gather streams. And then that goes into an eager table spool. And the reason why I pre-ran all of these is you can probably guess by looking at some of the numbers here.

These are not the fastest boys in the world. No, these are very slow boys. This one takes nearly a full minute.

This one down here takes 30 seconds, which is an improvement by nearly half. But still not great. Still not what I would call fast.

And then, you know, down here, this is… I think the reason why I wanted to have the parallel… Rather, I wanted to have an index where both search and ordering is supported and where only search is supported is because I think the query plan difference here is interesting.

And the reason why I think the query plan difference here is interesting is, of course, because when you have a nested loops join, SQL Server only considers the stuff over here for, you know, how much faster it would be to execute the nested loops in parallel. Because parallel nested loops don’t operate the way…

Don’t always operate cooperatively the way that, like, parallel merge or hash joins do. Parallel merge or hash joins, you’re probably very used to seeing those where on the inner side of the join, the number of rows in the table is split up equally or split up hopefully as close to equally as possible amongst the dot threads in the query.

With a parallel nested loops join, you’re really running dot copies of the nested loops on the inner side. So every thread is going to have every row on it. There are a couple caveats to that that are too involved to cover here.

But just in general, when you see parallel nested loops, you should know that you’re dealing probably with a lot more rows than you think you are per thread. So this one is interesting because this query where we have the search and the sorting supported does not get a parallel plan. This gets a serial plan which runs for about 30 seconds, which again is twice as fast as any parallel plan that we have in here.

The one up top, fully parallel plan. Granted, we scanned the clustered index. We have kind of a nasty sort here.

But even going from the sort to the gather streams, that goes from 18 seconds, jumps up to 35 seconds. And then going into the eager table spool, that jumps up to 58 seconds. So really awful sort of like chunks of time spent waiting for those operators to process rows around.

The serial plan where we lose the sort, things do improve, but not, I mean, they improve and they improve dramatically. They improve their, this query is twice as fast as the other one, but it’s still slow as hell. Right.

And a lot of the time, you know, we have a full 18 seconds getting into there. And then, you know, this seek to key lookup thing is not a great scenario. We spend about almost 11 seconds just in these, these three operators alone. So that’s, that’s also unfortunate.

But what I thought what was really interesting was when we flip back to the parallel plan with this sort in it, we go from about three seconds here. This jumps up to 11 seconds here. So we spend, you know, just about eight seconds, you know, in the sort.

And then from there, it’s another like 15 seconds in the gather streams. And then a whole bunch of time in the eager index. So this, that series of operators is just real unfortunate in these plans.

And then, of course, the one way down at the bottom is, this is just the one with the literal values, which is pretty much an exact duplicate of the one up at top of the parameter. It’s a few seconds faster.

It’s about seven seconds faster, I think, for, you know, I don’t know, whatever reason. I didn’t really dig into why this one’s seven seconds faster. It’s not really pertinent. But in the moment, mainly what I wanted to show you here was just that you get the same plan shape with using literal values as parameterized values. So one way to tune these queries and to get much faster performance out of them is to use, you can use either a CTE or a derived table, whatever you want to do.

So I use the CTE here because I like to, you know, I like to give equal opportunity to queries where it doesn’t make a difference. And inside of the CTE, what I’m doing is I’m calculating the percentage using the top parameter and multiplying that by the count of records that we get from when we look at that. And then you divide that by 100 to get the percentage that you’re looking for, right?

So this piece of math right here will get us the top percent that we care about. And then what I’m doing down here is I am saying I am selecting. So that PCT table where I get the percent, that’s a pretty short hand abbreviation for percent.

I’m cross applying to the post table and I’m getting the top calculation from up from up here. So this, this records column, this is the one that this is the percentage. This is the number of rows that we need to get as that percentage.

And I’m passing that percentage in here. And I’m doing the same thing in here with the where clause. And I’m doing the same thing here with the ordering. And then one thing that, you know, you always need to be thinking about is that SQL Server does not guarantee ordering unless you tell it what to order by. So we’re even so just getting the select top percent in here ordered by creation date descending is not enough to guarantee that the external provided results, the presentation, the presented results will also be ordered correctly.

So we have another order by out here to take care of that. And the nice thing is that when we create this store procedure or rather when we alter that store procedure to use the new one, and I’m going to execute this the exact same way. Top one, post type ID equals one.

I’m going to execute this the exact same way. And this is going to be a lot faster. You’ll notice that, I mean, a lot of the time was spent returning the 60,000 rows out. The query itself actually finishes in about half a second.

So it’s very, very easy and convenient for us to seek to the rows we care about in here to aggregate, you know, do our count to get generate a number. And then inside the top here to get that 60,003 rows, which is the top 1%. All sounds great.

Everything’s good here. Where this pattern will generally apply well to most executions. As the percentage gets higher for a lot of rows, performance is going to suffer.

So if we crank this up to, let’s say, the top 20%, I mean, on top of the fact that, you know, we’re going to spend more time sending rows out to SSMS because, you know, 20% of 6 million is a, you know, fairly high number. Like, this is going to slow things down. But most of the slowdown is returning the results to SQL Server Management Studio.

You know, of course, I could dump it into a temp table and, you know, you know, return, maybe ignore it or return the results from there. But a lot of the time in here is just time spent returning the results. If you look at the actual query itself, the actual query itself finished executing in about three seconds flat.

We spent a whole bunch of extra time returning 1.2 million rows out. Now, there is a slight downside to this in that the top is always going to estimate 100 rows. But as long as your indexes are set up to support the seeking and ordering that you care about for the top end percent, that’s not going to hurt you too much.

Where it would hurt you is if you didn’t have the sorting element assigned in the index and you had a sort operator that had very variable, like, memory grant requirements. You know, obviously sorting 60,000 rows is a lot different than sorting 1.2 million rows as far as how much memory you’d need. Maybe if you’d want a parallel plan for that, stuff, things along those lines.

But generally, this pattern works out way better, like, up until a very, very high row count. And even then, so, like, let’s be a little bit honest about, like, high row count return queries. This thing dumped out 1.2 million rows, right?

That’s 20% of the 6 million something rows that have a post type ID of 1 in the post table. Cool. If you are returning 1.2 million rows to anybody with the exception of, like, exporting to a Excel file or some other kind of file format, ain’t no one looking at 1.2 million rows.

No one is going to go through all 1.2 million rows and do something with them aside from, like, copy and paste them to another thing to make those results more portable. Most likely an Excel file. Most, you know, if you dump out 1.2 million rows to an end user, they’re not going to do anything with 1.2 million rows in your application results, most likely.

They’re going to take those 1.2 million rows, paste them into Excel, do whatever, you know, goofy Excel stuff people do in Excel, and then, you know, use that for whatever they’re building, right? Whether it’s a chart, graph, something, VLOOKUP, I don’t know. Whatever people do in Excel.

It’s crazy. So, returning 1.2 million rows out to SQL Server Management Studio or an application is generally not something. Like, if you’re at the point where you’re doing that, I have questions that you can pay me to ask you about just what the hell you’re doing or what the hell an end user would be doing with that much data getting returned to them. Because most of the time, they’re not going to get to the end of 1.2 million results and be like, hmm, I’m satisfied now.

They’re just not. That’s generally not the way most human brains work, especially in this day and age where everyone is in therapy and medicated and has some sort of neurodivergency that prevents them from paying attention to 1.2 million rows of anything. Right?

Or that’s like, maybe that’s just basic human sanity. Like, 1.2 million rows? I’m not looking at all that. You would forget everything you saw. Right?

There’s generally no point to that. So, you know, I think for most people doing top and percent things, you’re going to be pretty safe with this setup because you will hopefully never be returning, you know, millions and millions of rows, especially to SQL Server Management Studio, which has a notoriously difficult time of ingesting, displaying, formatting, all that stuff quickly. It’s not a fast boy for that.

So, anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. I hope that you paid attention earlier in the video where I talked about liking and subscribing and hiring me and buying training and all the other stuff. Because, you know, I like to have friends when I record these and I don’t like to feel lonely.

So, anyway, thank you for watching. I’m going to prepare one of these other demos. You see I have many tabs open up at the top there.

I’m going to go prepare one of those to record. And then I’m going to change one slide in the deck and I’m going to do that all again. So, anyway, it’s my channel. Remember?

Do what I want. Okay, cool. Thank you.

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.

A Little About Computed Columns, Filtered Indexes, and Indexed Views in SQL Server

Posted on August 22, 2024January 22, 2026 by Erik Darling

A Little About Computed Columns, Filtered Indexes, and Indexed Views in SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the world of indexed views in SQL Server and how they can be used to circumvent some of the limitations associated with computed columns and indexes. I share my experience from a previous attempt where my voice sounded like it had emerged from a crypt, which led me to create this video with a bit more preparation. We explore why you can’t create filtered indexes on computed columns or indexed views, and how we can work around these limitations by incorporating the filter expression directly into the view definition. I also discuss the practical implications for standard edition users and enterprise edition users, emphasizing the importance of using the `NOEXPAND` hint to ensure that SQL Server utilizes the index view effectively.

Full Transcript

Erik Darling here with Darling Data. And this is actually my second take of this video because on the first take, it was actually the first words that I had spoken aloud all day. And I sounded like I had just emerged from a crypt. And it was actually, it even felt strange for me. So in this video, we are going to talk about how you can use indexed views to get around some of the limitations with computing. columns and indexes in SQL Server. It’s going to be a lot of fun, assuming I don’t start sneezing at any moment now. So the big problem with computed columns is you can’t create filtered indexes on them. You just can’t do it. It’s impossible. You just get errors. It’s actually kind of a miserable experience. Like many other things in SQL Server, it is pure pain and agony and suffering. So here’s what we’re going to do. We’re going to create this table called indexed view, which is not confusing at all, is it? It’s sort of like when you have a table with TBL in it, and then eventually something happens where you have to convert that to a view, it hits like a table with a different definition. And now you have a view called TBL something. It’s the same deal, right? So that’s good times. And we’re going to stick a little bit of data into this table just to make it semi-realistic. It’s only about 15,000 rows. Not a lot. That’s okay. We don’t need a lot to sort of prove through the concept. Now, the problem, like I was saying, is that you cannot create a computed column on an indexed view.

I’m sorry, on a filtered index. So what it says here is, filtered index n cannot be created on table dbo.indexed view because the column, not fizzbuzz in the filter expression is a computed column. So helpful. Rewrite the filter expression so that it does not include this column. Well, it’s the only column that I care about filtering, so what should I do there, Microsoft? Tell me what I should do. Illuminate me. I would love to know more. The one thing that I want to do can’t be done. Why bother telling me to do something else?

So here comes the indexed view. And before I create this, I should pause for a moment because, you know, again, like most things in SQL Server, there are agony and pain and suffering. Indexed views and their limitations are certainly a large source of pain and agony and suffering for many SQL Server users. They can be used to great effect in very simple scenarios. And perhaps their limitations are a feature and not just pure, you know, unabashed laziness.

Because they do kind of prevent people from putting really crazy things into index views and maybe index view maintenance for those things would be tough. But it is a real shame that even basic things like min and max aren’t supported by index views. Legend and lore has it it’s because Connor Cunningham decided one day that he decided when they’re putting index views in that having an additional nonclustered index to support the min and max was just too much.

So we don’t get that now. Great. Connor is working on CPU instructions and we’re still suffering with not being able to put min and max into an index view.

So the happy time for us, us leftovers in SQL Server. Grand. So, yeah, we can’t create this. We get enough errors.

No matter how many times you click this, we get errors. Now, the main place that I still find enough valid use for indexed views is on standard edition. And they’re great for standard edition because Microsoft has hobbled columnstore in batch mode in standard edition to the point of utter disrespect for their users.

So, like, often it’s just like, we can try it. What’s the point? It’s just, it’s like nothing.

It’s pointless. So, indexed views can be good because, you know, where columnstore in enterprise edition excels at being able to do large aggregations very quickly, you can often use indexed views to pre-compute those aggregations.

Indexed views do, of course, have, you know, maintenance downsides. But, you know, even in enterprise edition workloads where there’s some, you know, something about the workload or something about the table or something about, I don’t know, a million other things that make using columnstore indexes impractical or impossible, indexed views can even still be good there.

But, you know, in this case, we’re using it to get around a rowstore limitation with filtered indexes. So, let us continue. We are going to create a view.

And I just want to show you this part first because this part is interesting, too. We’re going to create this view called computed column. Again, not at all confusing. And we’re going to attempt to create a computed column, attempt to create a filtered index on that.

Now, we’re going to get a completely different error here. We are going to, it’s going to say, you know, let’s put this, let’s format this a little bit for easy reading. All right?

We don’t want, we don’t want to make reading any harder than it is. We are, we are high school dropouts after all. So, the filtered index can’t be created on computed column because it is not a user table. It is an indexed view.

Indexed views in general cannot have filtered indexes on them. So, but at least this error message is somewhat helpful. Consider creating an indexed view with the filter expression incorporated in the view definition.

Boy, howdy. We can do that, can’t we? We can follow those instructions. Those are actually useful instructions.

Whoever wrote that error message, you deserve some kind of, some sort of gold medal. I hope that you have gotten a good job after that. So, we’re going to put our filter expression into the indexed view.

And we are going to quote this part of the indexed creation out. And we are going to recreate both indexes. Now, this gets us where we want to go.

We have essentially an indexed materialized pre-computed thing filtered to the stuff that we care about. Right? So, like that computed column.

And the big problem, of course, was that we can’t create a filtered index on a computed column. We can’t create the indexed view to materialize that. And, you know, under most circumstances, as long as you have reasonable supporting backing indexes between, you know, the tables and the indexed view and the indexed view.

And, you know, just being honest, I’m really not a big fan of indexed views that span multiple tables. I’d rather create, like, two, like, indexed views with, like, one table apiece and then them join two tables in an indexed view most of the time. And it’s a treacherous set of circumstances.

But now we have what we want there. And part of this is going to be because of the small data set that I’m using. We don’t get a very, you know, interesting, we don’t get an interesting enough query plan just selecting some data from the table or just getting a count from the table matched on the not fizzbuzz column being equal to zero.

Right? We just, it’s just, it’s not 15,000 rows. It’s just, like, in real life, if someone was, like, we need an indexed view on this 15,000 row table, I would probably punch them.

It’s not reasonable. So when we run this query and we look at the execution plan, we will see something that we’ve seen in a few other examples and videos that I’ve recorded here before. This query is at the mercy of both getting a trivial plan and simple parameterization.

We can tell because the literal value that I used before has been replaced with at one. And that we have all these silly brackets that are completely unnecessary injected into our code. And we have a complete lack of as in the aliasing, which is something that I would never do because I’m a professional human being.

So in order to get around that, you know, you can always do the old one equals select one trick. If you’re going to, if you’re thinking about typing in the comments, why are you using one equals select one? I have bad news for you.

Type it into a search engine instead, and you will get both a blog post and a video where I explain it. So if you ask in the comments, you’re out banning you from my channel for life. Kidding.

I’m not. I will probably make fun of you a little bit, though. So if I run this and I look at the execution plan. Oh, yeah. Well, actually, I should backtrack a little bit.

What I meant to say up here is that because of all that, you can notice that instead of using the index view that I created, we’re using the base table, which is named index view. So that’s the thing we don’t like there. But if we add in the one equals select one, we avoid the trivial plan and the simple parameterization, we switch to using the index view that I created called computed column, which is precisely what we wanted.

But an important thing, particularly for standard edition users, the much abused, left behind standard edition users, is that you usually want to include the no expand hint. Standard edition does nothing for like computed column or rather does nothing for like index view matching. The no expand hint is necessary in like 99 point nearly infinite nine cases where I need where I want.

I need to I need to make sure that the index view is routinely hit rather than the base table base table behind the index view. The other really important thing, even for enterprise edition users, when it comes to index views and using the no expand hint, is this is the only way for SQL Server to create system statistics on columns in the index view. If you don’t put no expand in there, you don’t get any like it doesn’t create histograms for you.

It’s really weird. I don’t get it personally, but it is it is it does appear to be the case. So sometimes in query plans, you might see a warning that says like like, oh, columns with no columns with no statistics or something like that.

It’s a very misleading warning. Sometimes it’ll happen because it’s a index view and you don’t have statistics because of it. You didn’t use no expand when you queried it.

Other times it’s because you don’t have statistics in the very specific column order that SQL Server would want. And so like you do have statistics on these columns. It’s just not the exact it’s it’s almost like a like like a missing index request that’s wrong.

That’s like a missing statistics request that’s wrong because you do have statistics. It’s just not the specific statistics that SQL Server wants. So what did we learn today?

What did what did what did we learn? SQL Server has a lot of bizarre limitations. You know, you can create computed columns. You can create filtered indexes.

You just can’t create filtered indexes on computed columns. You can create index views, but you can’t create filtered indexes on index views. You can apply filters to index views and then create whatever indexes you want on that index on that view clustered and then whatever nonclustered indexes. But index views are a tough or a tough sell in a lot of cases.

You know, with the exception of people on standard edition who are have who, you know, can’t have like a just beat to death version of columnstore and batch mode available. You know, that’s that’s no good. And then like index views can be good there.

And then if you’re on enterprise edition, but for some reason column stores and column stores and no go for you for whatever other, you know, weird pathological reasons you have in your database, whether it’s, you know, the data types, cursors, you know, other kind of constraints and stuff. That’s another, you know, another story. columnstore does have some limitations, but, you know.

So I am still rather fond of columnstore most of the time. Rather, rather, rather sunny about columnstore. Got some good stuff going for it.

And Microsoft actually actively seems to be working on columnstore, which is a nice change of pace from index views, filtered indexes, partitioning, you know, a billion and a half other features that have been left at sort of like V1, V2 with no real investment afterwards. So, you know, columnstore at least has that going for it. It’s okay there.

So, there we go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, then you should, you can like the video. You can comment on the video.

You can subscribe to my channel, and you can join over 4,000 other lovely data darlings and getting notified every time. I publish one of these videos so that you can stay on the, I don’t know. I’d call it the cutting edge, but, gosh, is SQL Server the cutting edge anymore?

I often wonder. I often wonder. It’s a fun thing. Fun thing to ponder.

Fun thing to consider. Why? What happened to SQL Server? Why is everything seemingly spinning out of control? So, you can do that.

And then, I don’t know. Maybe you just like looking at me. Maybe you just like the sound of my voice. I don’t know. I don’t know what it is that gets people to subscribe.

But, if you do that, you get notified. And I’ll get larger subscriber counts. And larger subscriber counts are kind of my fetish at this point. So, you know, the higher that number goes, the happier I am.

And the happier I am, the more I record. So, it’s like a good feedback mechanism. You subscribe. I record.

We’re good. Right? Everyone. It’s a happy ending for everyone. All right. Apparently, I have a call starting soon. So, I’m going to go do that. And that call is with a nice client who is paying me money so that I can record these things for free.

There aren’t even commercials on my channel. Like some other SQL Server channels. So, you can watch these things uninterrupted.

And if you would like to hire me for consulting, well, I mean, you know my name. You know that my name is my website. And you can always get in touch with me that way.

All right. Great. Cool. Now, we’ve done all our plugs at the end. Because I’m an idiot and I should do it at the beginning. But, you know. Can’t teach an old dog new dogs.

All right. Goodbye. Thank you for watching.

Going Further

So You Want To Implement Soft Deletes In SQL Server

Posted on August 21, 2024January 22, 2026 by Erik Darling

So You Want To Implement Soft Deletes In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the considerations for implementing soft deletes in SQL Server tables. Whether you’re looking to add a soft delete feature to an existing table or considering building new tables from scratch, there are several key points to keep in mind. I discuss the challenges of backfilling nullable columns and the importance of batch updates when making such changes to large datasets. Additionally, I explore the complexities involved in ensuring data integrity with unique constraints and check constraints, drawing on insights from Alexander Kuznetsov’s work, which is now available through an Amazon affiliate link. The video also covers practical advice for creating views and filtered indexes to simplify querying active versus inactive rows, emphasizing the importance of proper index design based on query patterns. Lastly, I touch on the broader implications of soft deletes compared to actual data deletion, including the potential benefits of using temporal tables or archiving deleted records in an archive table.

Full Transcript

Erik Darling here with Darling Data. And as promised, I’m here to talk about some considerations for implementing soft deletes in SQL Server. Now, there are a lot of reasons to want to do this. And really, like, the implementation of a soft delete or the inclusion of a, like, you know, which row is currently active type thing, they’re almost two different outcomes, right? Because, you know, there are some situations where you might, you know, want to be able to soft delete something just because it was a mistake or, you know, just to, you know, mark it for, like, archival or something. And there are other times when you only want to be, you only want to query the most active row. And we’re going to talk a little bit about both of those. Now, the first thing that I want to point out is that setting this up for a table that currently exists and is of any, you know, remarkable size is really difficult for two reasons. One, you know, not because, like I said, like, look, if you’re adding a nullable column, or you’re adding a nullable column with no default value, it gets, it gets sent to the table instantaneously, right? It’s, there’s no locking, blocking problems with that. Your problem after that becomes, okay, which rows are deleted and which rows are not. So you, like, like, it would behoove you to backfill that column with some data to make sure that people know whether a row is deleted or not. Because what you don’t want to do is add that in and start dealing with three value logic with zero, one, and null. That’s just a recipe for disaster. So backfilling those and figuring out which ones are deleted or not can sometimes be a challenge. If the goal is just, we need this new thing, and we’re going to start soft deleting rows, don’t worry about it. Make everything zero.

But don’t make everything zero all at once, because if it’s a table of any, of any, like, remarkable size, you’re going to want to batch, batch those updates to set that to zero. That’s a lot kinder on your server than just adding a, you know, column with a default of zero. And, you know, having that happen to all the rows in one go. Batching them up is a lot, it’s a lot nicer. So if it’s a situation where, you know, neither one of those scenarios really fits what you want to do, there are definitely times when building new tables with those design improvements instead is a smarter and safer bet. I’ve been a part of a couple projects that were geared towards doing that.

And, you know, wrote all sorts of things to migrate the data to the new schema, you know, make sure that everything is set up the way it’s supposed to be, all that stuff. You know, so like, that is a doable, it is a doable, achievable task. You know, it’s just, you know, one of those things where it really comes down to what the goal is. Now, if you only want to have one row per group be active, you’re going to have to deal with at least one unique constraint.

And depending on the complexity of what defines an active row, you could need a whole bunch of unique constraints. Now, this guy, Alexander Kuznetsov, and I don’t know, I apologize, Alexander, if you watch this video and either I butchered your last name or you prefer to be called Alex. I’m not sure. I’m not sure you’ll ever see this.

From what I know, you’ve moved on to Postgres. But Alexander wrote a book that I actually, I read and loved a long time ago called Defensive Database Programming in SQL Server. However, I will have an Amazon affiliate link to that book.

So if you do decide to buy it, I’ll make a third of a cent. I’ll finally make some money off one of these videos. But Alexander also was a blogger for a while.

And he wrote on SQLblog.com, which has been dustbin, which is a shame because there are a lot of great blog posts on there that you can only get through like the archive.org stuff. And so one of the articles that he wrote was about storing intervals of time with no overlaps. And this is the table definition that I’m just going to format this a little bit so that when I, this is not how the table appeared in the original post.

I just need to format that a little bit so that when I zoom in, my giant head doesn’t cover anything important. So in this, so in order to implement this for a table that has one setting ID and value started at, finished at, previous finished at, five columns, you need one, two, three, four, five separate check constraints to ensure that this data is stored correctly. You need a primary key.

You need a unique constraint on setting ID and previous finished at. You need a foreign key in the table that references itself. You need another check constraint to ensure that there are no overlaps between previous finished at and started at and started at and finished at. There are, there’s a lot that you have to do to make this work right in order to ensure that your table doesn’t have any bad data in it.

So you can already imagine the complexity and imagine, imagine that you have a table that you thought was set up to do this. And you’re just now realizing that it wasn’t set up correctly to do this. And now you have to add these constraints in.

I will bet you a million dollars that adding at least one of these will fail. Right. Adding at least one of these constraints to your current set of data will fail miserably. In some cases, you just might want to use temporal tables.

Now, temporal tables are a feature that I do not get excited about because there are a lot of problems with them. There’s a blog post by a very famous Bob about when you can encounter error 1, 3, 5, 3, 5, where data, where having temporal tables can cause data modifications to fail. This is just the tip of the iceberg when it comes to problems with temporal tables.

There are all sorts of very strange bugs that can happen under concurrency and using transactions to modify data in a table just like this that can make things really weird and complicated and even make you end up with probably incorrect data sometimes. So temporal tables aren’t a feature that I get very excited about, but given a simple enough implementation, they’re probably okay for some requirements. Again, nothing that I’d get like, wow, use temporal tables.

They’re amazing. It’s more like you could try them. Make no promises. It’s like getting on an old roller coaster. You don’t really know how safe it is.

You don’t really know what might happen. You just don’t want to be that unlucky. You don’t want to be that unlucky passenger. You know, Microsoft and Boeing have a lot in common where there’s been this real degradation in quality over the years.

And a lot of really half-baked features have been implemented with not a lot of assurances that things will go well with them. So be careful out there. All right.

But if you’re going to, you know, let’s just say that, you know, you go the route of implementing soft deletes in your tables. And you want to make sure that everything works right. One thing, actually two things you’re going to want to do is you’re going to want to create a couple views.

And you’re going to want to create a couple views because you should never trust a developer. Never trust a developer to do the right thing or remember what they’re supposed to be doing. So whenever I’ve implemented soft deletes for clients, you know, the nice people who pay me to make these videos, you could be one of them someday.

You know where to find me. Is I will create a video that explicitly, I will create a video. No, Erik Darling will create a view.

Erik Darling does create videos, but not for soft deletes. Well, actually, I am creating a video about soft deletes. This is wild. But when I implement soft deletes for clients, I will create views for active and inactive whatevers so that specifically have a filter for where, for whatever we’re looking for. Is deleted equals zero or is deleted equals one?

Because I want to make sure that, you know, there might be even be a third one that, you know, just hits both. But that could just be the table. But really what I want is for people to be looking in the right place. And from my perspective, it’s a lot easier to see if a developer is hitting the wrong table than if they forgot something in a where clause.

So there’s that. The other thing that you’re going to probably want, and this is a very common thing, is you’re going to want a unique constraint on your table to sort of ensure that there is only one not deleted thing for a person. Now, going back to the Alexander Kuznetsov stuff about, you know, with the overlapping stuff, that’s a lot different than just figuring out if, like, just making sure that someone only has one active row period for themselves.

There might be all sorts of other considerations for that. And you might need more than just the column that I’m showing here for the unique index. It depends on what you’re trying to accomplish.

But in general, you just want to make sure that there’s one unique ID that is not deleted, right? You generally don’t want a bunch of active rows. If you want a bunch of – if you’re okay with having a bunch of active rows in there, if there might be duplicates for some reason, then you could just create a regular nonclustered index.

But having that filter operator in there is very important. The other big thing about doing this, and this is just a general piece of advice, because filtered indexes where you don’t have the column or columns that you’re filtering on in the where clause as part of the index somewhere, generally having them as an include is good enough. If you don’t have them in the where clause, if you don’t have them in the where clause, you are – it’s a real crapshoot if that filtered index will get used when it should.

That goes for bits. That goes for null or not null. That goes for, you know, greater than, less than, in, whatever the setup is.

Just do yourself a favor. Don’t screw this part up, because you might get real disappointed with your filtered indexes. Please put the columns that you are filtering on somewhere else physically in the index, either as a key or as an include.

Like I said, generally the include is good enough, but if it’s very special to you, you might want to put it in the key. All kind of depends on what the column is filtering on. Now, the nice thing about doing this with the views and the filtered indexes is let’s assume that the majority of your code is either from an ORM where things are parameterized or based on store procedures where things are parameterized.

What can happen, or even if you have forced parameterization turned on for your database, as long as your query is partially parameterized or in a store procedure, you don’t have to worry about the issue with filtered indexes and parameters or variables. So one big catch with filtered indexes is that if you have a piece of code, and let’s just say that that piece of code is, you know, filtering on the deleted column, is deleted column, and you want people to be able to search for either is deleted equals zero or is deleted equals one. Again, we’re throwing three-valued logic out with this.

If you allow nulls in a bit column, there is a special place right next to me in hell. So you better watch out. But when you have a parameter or variable for this, SQL Server can’t take advantage of your filtered indexes unless you throw a recompile hint on.

There are other ways to snake around it with dynamic SQL and putting a literal value in for one part of it. But in general, you know, if you’re like, you don’t want to have a parameter to search on a filtered index column because SQL Server needs to cache and reuse an execution plan that’s safe for is deleted being zero or is deleted equal being one. So, you know, at that point, you’re going to probably ignore your filtered index and get an unmatched index warning.

Now, the reason why I like to create these views up here is because these are literal values. All right. And even with forced parameterization turned on, if your query is in a stored procedure or partially parameterized, SQL Server won’t try to parameterize this part.

Right. If you have a trivial enough execution plan, you might get simple parameterization. But, you know, if, you know, again, most people live in a sufficiently complex world where that’s not something that’s reasonably going to happen.

You never know. But there’s something to watch out for. So when you’re going to implement soft deletes, the three things that you need to consider are what’s really your goal?

Are you going to allow multiple not deleted or can you only have one active not deleted row per user or whatever entity, whatever you want to call it? How are you going to set up for people to query those so that they don’t have to remember to apply some predicate to it? Especially if you are using ORMs, it can be really difficult to remember to do these things in your queries or even know how to do these things in your queries.

That’s why the views work out really well. You’re going to figure out whether you need unique indexes or non-unique indexes to maintain the referential or not the referential integrity. Just like the sort of constraints of the table.

And depending on query patterns, not every index can be filtered. Right? Not every index is going to be geared towards a query that’s looking for is deleted or is not deleted. There are some that might just have to span the whole table.

Implementing soft deletes is often a lot less scary to business users than actually deleting data because once you delete it, it’s gone. Now, granted, you can do things where either, again, temporal tables or you could put a trigger on your table so that if someone deletes a row, that row goes off to some other table until you’re legally not required to have it anymore. If you’re going to go that route, though, you might as well implement the soft deletes.

And then you might as well have a process that moves soft deleted columns off to some archive table eventually. It can be after three months, six months, nine months, seven years, whatever you’re really meant to do. Because, you know, even with filtered indexes and even with, you know, queries geared towards using only the non-deleted rows, it’s usually a good idea to keep your tables on the small as possible, especially if the deleted rows really start to pile up.

So that’s another big thing to consider. What is going to be the ratio of deleted to not deleted rows in your system, right? Because, you know, well, like, you know, at that point, you have to wonder if filtered indexes are even useful to you, right?

Because if your table is like, you know, 60, 70, 80, 90% not deleted rows and soft deletes are a rarity, filtered indexes don’t really buy you all that much. Unless you need that uniqueness, unless you need to ensure that only one user can have an active row, have a not deleted row at a time, it doesn’t really make sense to add those in, except as sort of like a logical check constraint to keep your data the way that you expect it to look. So generally, you know, these are the questions that I ask.

These are the things that I talk about with clients. You know, we might come to an understanding that perhaps adding soft deletes to existing tables is not what we want to do. Perhaps we would prefer temporal tables if we are willing to accept all of the risks that come along with using them.

There might be a solution with triggers and other things that would be more palatable. You know, you might want, you know, figuring out exactly what the end goal of the soft deletes are, figuring out exactly like, you know, the requirements before we implement stuff usually leads to a much better solution than just saying, oh, you wanted soft deletes? Cool.

Here’s a new bit column. That’ll be $10,000, please. Because I’ve seen a lot of that happen. And what the end result is, is people have this soft delete column. No one actually soft deletes anything.

And it’s kind of a bummer. So, you know, really, really figure out the problem you’re trying to solve by implementing soft deletes before you just go and throw a brand new column in a table or you go through a bunch of work to try to establish all the other stuff that we’ve talked about. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. If you like this video, you are free to give this video a thumb, a nice happy comment, a smiley face. Whatever you’re feeling at the time, feel free to comment.

If you like this sort of SQL Server content, you can subscribe to my channel. There are buttons that allow you to do all sorts of fantastic things in that regard. And lastly, but not leastly, thank you for watching.

I will see you in the next video. Where hopefully I don’t have to soft delete this video for any reason. That would make me sad.

That would just mean that the last 20 minutes of my life were all for naught. Which, you know, has happened far too often to me. Ugh.

You know, I think if you really tally up the number of regrettable 20 minute spans in your life, you can come up with a lot of those, right? Anyway. I’m going to go think about that for a while.

Might need a drink to wash that thought down with. So, I will see you in the next video. Goodbye.

Goodbye.

Going Further

One Way To Track Row Changes In Place In SQL Server

Posted on August 20, 2024January 22, 2026 by Erik Darling

One Way To Track Row Changes In Place In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into the world of tracking changes in SQL Server tables using temporal columns—a feature that can help you monitor when data modifications occur without the complexity of full-fledged temporal tables. I start by explaining why change tracking is often a problematic choice and highlight how change data capture (CDC) simplifies setting up change tracking for new or existing tables, though it comes with its own set of challenges. Then, I demonstrate how to add temporal columns to an existing table to track modifications, emphasizing the importance of hidden junk date columns and default constraints that help record when a row was last modified. Through practical examples, I show how these changes can be implemented and queried, providing insights into their usefulness for data auditing and movement processes.

Full Transcript

Erik Darling here with Darling Data. Of course, I forgot to adjust my camera before I go to recording, so we’re going to make sure that we are crisp, clean consultants for this video. And I’ve been told that I should do this at the beginning when people are still watching. Apparently it’s more effective. If you like SQL Server performance tuning content or just SQL Server informational stuff in general, and you end up liking this video, you can like the video and you can comment on it. You can also subscribe to my channel and subscribe to my channel. if you want to be notified whenever I publish these things. So, uh, we’re doing that upfront this time. I’m not going to thank you for watching because you haven’t watched it yet. In this video, we’re going to continue on with, uh, they call that A-B testing in market research. Uh, in this video, we’re going to continue with the theme of yesterday’s, the last video, not really yesterday yet. Uh, uh, that was deep. Uh, with different ways to track changes in SQL Server. So, uh, in yesterday’s video, we covered the fact that change tracking is one of the worst features that Microsoft has ever added to SQL Server. And, uh, that change data capture was fantastic. Uh, in this video, we’re going to talk about how you can use temporal columns, not temporal tables. Temporal tables have way too many problems for me to, for me to say, yes, you should use them. Uh, but temporal columns can help you make this a little bit easier. Now, full disclosure, uh, just, you know, change data capture is a lot easier to set up for a table, uh, because you just start tracking new changes and all that stuff. If you wanted to like add this to an existing table, especially if it’s a big table, it could be painful to add these columns because you are adding not null columns with default constraints, which means a whole lot of data pages get written to. But if you have a brand new table or you’re designing a table and you want to be able to track when data change in that table, this could be a good way to do it. Uh, now keep in mind this, this of course, unless you’re using soft deletes, this will not change when something got deleted. You know, soft deletes will track like when the change happened to a row, like when you change is deleted from zero to one. Uh, but this won’t like if you delete a row out of the table, this won’t check that. This won’t track that you would need something more robust, like change data capture. But if your only goal is to figure out when a row got modified, this is a pretty good way to do it. So what I’m going to do is I’m going to, uh, drop this table if it exists and then also create this table. And this table is going to have all of the hallmarks of a table with, uh, for the, they would, they would be set up as a temporal table, but there’s no history table for it. We’re not assigning a history table. We are adding two columns to it that you need to have, but this is, this is really important. We’re going to talk, talk about two important things at this juncture.

One, we have this column called junk date and we don’t really need junk date. This, this doesn’t do us any good. So we’re marking this as hidden. We don’t want this to show up in select queries. We don’t need it. It’s useless to us. The other thing I’m going to cover is this. So we have a default constraint on the column that we do care about that shows us when something was last modified. I have sysdate time here because this will help me know exactly when a row got into a table, right? That’s, that’s, that’s useful in some cases. In other cases, you might only care when a row changed in the table.

If that, if that’s the case, you might not, it might look kind of weird and confusing to have a current time in that row or a current date in that row because that might’ve just shown up in there. We don’t know if that’s when anything actually happened or not. So you, if you don’t, if you want to know like that a row actually changed at some point in time, having a current date in there might be a little confusing.

What might be a little less confusing is if you put like a really old date in there, like 1900, oh, one, oh, one or something. So you could, you know, that obviously wouldn’t tell you what that the, when the row got into the table, that might just give you, I mean, unless you’re, you, you had a SQL Server in 1900, in which case, share your time traveling secrets with me, dear leader. I have some ideas.

One of them is called the stock market. Sounds like a winner. The other one is becoming a credit card company. That sounds like a, that’s another good one. That’s, I got goals, you know.

Anyway, so these are the two columns that we have to add to the table in order to make this whole thing work. And then of course we have this period for system time, blah, blah, blah thing, because apparently that’s just, that’s just syntactically correct. So let’s stick some rows from the votes table into the votes track table.

Not a whole lot of them, just about a year’s worth. And then we’re going to look at what data ended up in there because, I don’t know, it seems like a reasonably fun thing to do. And of course this insert is taking its sweet time, even with a tab lock hint, screw you SQL Server.

All right. So let’s take a quick view of what’s in this table. This is everything for vote type ID 7.

And you’ll see that we have a last modified date over here of the day and time that I’m recording this. And since we have bulk inserted this data in, these are all going to have the same value. If you were inserting like a single row at a time or like two or three rows at a time, they would all have the same value.

But, you know, this, because it was just a big whopping insert, they all have this last modified date of today, which is not yesterday yet. Very deep, very deep thinkers here at Darling Data. And so we got that and that’s all well and good, right?

We have that. So let’s look at, so right now, let’s just make a mental note that this is 2024 08.01 at 18.02 in 34 seconds, right? And now let’s update the bounty amount column and set that to four nines, which is not three sixes, four nines.

We’re not getting weird here. And let’s look at what’s in the table now. All right.

So this is obviously incremented by however long I was talking for, because if we look at, you know, the results of these two things, they will have slightly different times. So we did not affect vote type ID 4, which still has a last modified date of 18.02. And the last modified date for the rows we did change is 18.03.

So this is another way of figuring out when data changed in your table. If you need to take that data and either, you know, audit it, be like, hey, this looks funny. It changed.

When did it last change? Huh? Like, you know, you know, it’s not going to tell you who changed it. It’s not going to tell you what the previous values were. So it’s not really, like, good for, like, a data auditing scenario. But it is good if you need to take this data and put it somewhere else and you need to figure out, you know, again, sort of like we talked about with change data capture.

Let’s say you have a process that will look at this table every X minutes and look for data that has changed since the last time it ran and put that data into another table. Maybe it will aggregate it and do something else with it, but this is one way that you can figure out, hey, these rows changed since the last time this process ran. I need to take these rows and move them over.

And it’s kind of cool for that because, you know, it’s not change tracking, which is the worst feature that Microsoft has ever added to SQL Server. Or at least, you know, probably top five worst features. Again, I’ve seen that thing cause more trouble than it’s, I don’t know.

It’s just brutal. And, yeah, so this is just kind of another fun way to do that. And I’ve used this with a few clients to help them with, you know, data movement processes, which, you know, they turned out to be pretty happy with.

And happy clients is what I aim for, right? Again, clients, the nice people who pay me to make these free videos. If you would like to hire me so that you can support this channel and I can keep making free videos, you know how to find me.

We already talked about liking and subscribing and commenting, so we can skip that part in case you forgot because some of you people are a little forgetful up here. But anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in the next video, which will not at all be about tracking changes. It just might be about considerations for implementing soft deletes, which would actually kind of go hand in hand with this. So, we’ll see how that goes, I suppose.

Maybe I’ll even see you there. Maybe you’ll even decide to subscribe and get notified for when that video comes out. Because I promise you, you will learn something.

I hope. I hope you will learn something. Or you will enjoy yourself. Or both. Lots of options. We have lots of potential here.

You and me, we’re going to be together for a long time. Anyway, thank you for watching.

Going Further

Join Me At Data Saturday Dallas Sept 6-7

Posted on August 19, 2024August 1, 2024 by Erik Darling

Spring Training

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!

Going Further

Making It Easy To Tell When Data Changed With Change Data Capture In SQL Server

Posted on August 16, 2024January 22, 2026 by Erik Darling

Making It Easy To Tell When Data Changed With Change Data Capture In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into tweaking change data capture (CDC) tables to make them more human-readable by adding a `change_time` column that captures the exact timestamp of when rows were modified. This simple tweak can significantly ease the process of working with CDC data, especially for tasks like populating data warehouses or performing other data movements. I also share my personal disdain for change tracking (CT), describing it as one of SQL Server’s worst features due to its tendency to cause server backups under high concurrency. The video walks through enabling CDC on a sample table and demonstrates how the `change_time` column can be used to streamline data movement processes, making it easier to identify new or updated records based on timestamps.

Full Transcript

Erik Darling here. Still Darlinging the data, as far as I know. Haven’t quite had any acquisition offers lately, so, you know, who knows, maybe someday. You know, the dream of every founder is to be acquired and, you know, go start some new business. So, you know, any day now, I’m sure. My billions of weight. Maybe I got into the wrong line of work for acquisitions, though, who knows. Anyway, today’s video, we are going to talk about how you can tweak change data capture tables so that you have a more human-readable idea of when rows ended up in your CDC tables. This can make all sorts of things easier. Now, look, there are built-in functions that can help you with this that do all sorts of things easier. There are lots of wacky stuff with log sequence numbers. The problem is, they’re complicated. It’s hard. I don’t like doing it. I like things to be easy, especially when my goal is to build bigger tasks around change data capture. Whether it’s populating a data warehouse or, you know, doing some other data movement stuff. This is one of my favorite tricks. Now, for so many reasons, I absolutely loathe change tracking.

Change tracking is one of the worst features that Microsoft has ever rolled into SQL Server. It is crap on wheels. I have seen it take down so many servers under high concurrency because what happens is you start to stack up these commit table weights. It’s commit underscore table weights. It’s a monster. And when your server backs up on those commit table weights, there is no helping it. You’re going to flame out. Again, change tracking, CT, is god-awful. If you turn it on, no one’s going to want to help you. I don’t even know if anyone at Microsoft still does anything with it.

I think it’s abandonedware at this point. Change data capture is beautiful. It’s wonderful. It is my precious angel because change data capture works asynchronously. You set up change data capture for a table or tables. And as changes happen to those tables, change data capture reads from the transaction log and moves that data into tables that you can actually see and do things with in your database, which is wonderful. Because you can do all sorts of neat stuff with those tables, including index them any way you want. You can add columns to them. You can, I don’t know, let’s see, you can prune them out whenever you want.

It’s not like change tracking where you have to do magic incantations and hope that whatever thing you run actually clears things out of the change tracking tables. Again, change tracking, nightmare nuisance, change data capture, beautiful, wonderful angel. So if you take one piece of advice from me and you work at a company where someone is like, do we want change tracking or change data capture?

Please say change data capture and save yourself all the problems in the world. Now, neither one of those things will tell you who changed something. Right? That’s auditing. That’s a completely separate thing.

You just need to track what data changed. Change data capture is what you should be using. So in my database called crap, and if you do not have a database called crap, I question whether you are a real database person, because every database person needs a database called crap to do crap like this in.

It’s just the way it goes. So I’ve created a table called posts that mimics, make that formatted correctly, that mimics the post table in the Stack Overflow database. At least, you know, the publicly available copy of it.

I’m sure the actual, you know, production copy of the post table is a nightmare. And I’ve just stuck a thousand rows in there to make life easy, because I felt like it, because I don’t need a huge table to show you this. So what we’re going to do is we’re going to walk through enabling change data capture for the crap database.

Oh, that happened so quickly. That was nice. And then we’re going to enable change data capture for the post table. And this will take a couple seconds to kick in, because it does all sorts of stuff in the background.

And, well, you know, okay, well, whatever. Cool. And what we’re going to do is we’re going to add a column to the post table that adds a… We’re going to… Jeez.

We’re going to add a column to the change data capture instance for the post table called change time, with a default of the sys date time that will tell us exactly when data changed. So let’s go and alter our table.

That happened very quickly. That is lovely. Now, if we go and look in here, there will be nothing, because we have not captured any data changes. There simply have been no data changes to capture.

That’s okay, because we’re going to make some changes. It’s going to be great, right? It’s going to be so much fun. So we’re going to update the table, and we’re going to plug that in there. And one row got affected.

And now, wonderfully, magically, we’re going to have data in our change data capture table. Look at all this great stuff. Now, if you were looking to use change data capture professionally with the built-in functions, you could use the LSNs in here and whatever to figure out when things change.

But that’s Bush League. You want to do things in a way that people will actually be able to use, right? It’s like extended events.

Like, Microsoft just made it as hard as possible to do anything. And they’re like, why doesn’t anyone use this? Why do people still use Profiler?

Why? Well, I’ll tell you why. Because you made it impossible. You jammed it full of XML. What did you expect was going to happen when you made something as difficult as possible? You built something that was great for, like, you know, like smart support engineers to use.

You ruined it for normal people. Much like using LSNs to track when things changed. But now, if we scroll over a little bit in our change data capture results, we will way over at the end here have this lovely column called Change Time with the exact time and date that something changed in.

This gives away when I’m recording this, doesn’t it? Crap. You’ve got me. Now, if we make a different update to that table, we’re going to just add 1,000 points onto the score here.

And we revisit our, oh, that highlight did not go well at all. And we revisit the change data capture results. Now we have some additional data in there. And if we scroll over a little bit further, we will see that the change time column is now incremented to exactly when we made the change over here.

Right? So this is about a minute and a half apart. So we see exactly what time something came in. This can be useful for all sorts of things.

Like, if you’re the type of person who uses change data capture to push data to another source, or rather another destination, another target, it’s a lot easier to use this change time in your queries to figure out if this is new data that you need to change.

Because in whatever process you do, you can say, last data movement was it this time. Is the change time column greater than this time? Yes!

We move that data. Is it less than that? No, we don’t touch it. No more futzing around with anything else. You can have a jolly old time moving your data over without having to worry too much about it. And so for these reasons, which I believe to be self-evident, change data capture is superior in absolutely every single way to change tracking.

If you don’t like it, again, I’ve got the Delta Miles to come fight you. I’m happy to do it. So now we’re going to finish this thing off by turning off change data capture for our table and database.

Because if I don’t, then every time I run SPHoo is active, I’ll have some weird job running in the background that just makes things weird and confusing. And no one needs that. So there we go.

I’m going to finish this video by saying, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. I hope you never use change tracking in your life. I hope you always opt to use change data capture instead.

I should really start doing this stuff at the beginning of the video before everyone stops watching. But, you know, if you like this video, I like thumbs ups and I like comments.

Again, even mean comments because I do have the Delta Miles to come fight you. But still, you know, take your chances. Why not?

If you enjoy this type of SQL Server content, you can subscribe to the channel and join now over 4,000 data darlings in their joyful bliss every time they get a notification that I publish a video. So you should do all three of those things.

Except, I don’t know, maybe be nice. You could choose to be nice. What does it people say? It costs nothing to be nice.

I don’t know if that’s true. Sometimes it takes a physical, there’s a physical cost to being nice. There’s a physical toll on being nice. But other times, you know, it’s cool. It’s fine.

You know, tip your bartenders. It’s basically my, be nice to your bartenders. I’m all, I’m, the people in the world who I am the nicest to are bartenders because anyone who just keeps bringing me drinks is best friend.

So much like the way a dog will look at you when you give them a liver treat, that’s how I look at bartenders who bring me drinks. So that’s, that’s me though.

You might have your own kink. I don’t know. Whatever. Anyway, I’m going to get going. Apparently I have a phone call starting in a few minutes. So I should probably, I should probably attend to that. That would be a client call.

One of the lovely people who paid me money to do SQL Server Consulting so that I can keep recording these videos for free. If you find yourself in the market for a SQL Server Consultant to help you with performance or choosing between change tracking and change data capture, you know how to get in touch with me.

Here I am. My rates are reasonable. Thank you for watching. I’m going to go, I’m going to go entertain someone for money now. That sounds, that sounds wronger than it actually is, but gosh darn it, it’s the truth. All right.

Cool. Adios.

Going Further

Five Reasons Why Your SQL Server Is Slow Right Now

Posted on August 15, 2024January 22, 2026 by Erik Darling

Five Reasons Why Your SQL Server Is Slow Right Now

Thanks for watching!

Video Summary

In this video, I delve into the reasons why your SQL Server might be experiencing performance issues right now. I start by setting up a clear environment using the remote DAC (Dedicated Administrative Connection) to ensure that you can run analysis scripts without interference from ongoing problems. I emphasize the importance of having useful scripts like `sp_whoisactive` and `sp_PressureDetector` at your disposal, as they provide critical insights into what’s happening on your server. The video then explores common issues such as CPU and memory pressure, long-running maintenance tasks, blocking, and parameter sniffing, offering practical advice on how to identify and address these problems before a restart clears out all the evidence. By sharing this knowledge, I aim to help you gather valuable information that can prevent future issues and improve your SQL Server’s performance.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about why your SQL Server is slow right now. I’m a little blurry. Hang on a second. I gotta fix myself up a little bit. There we go. Now we’re nice and crisp. Recently voted by BeerGut Magazine as the most crisp SQL Server consultancy in the world. So it’s always nice to get an accolade from BeerGut Magazine. It’s a nice change of pace from when they’re threatening to sue or assassinate me. So, you know, ups and downs in relationships, restraining orders, stuff like that. So the things you’re going to need when your SQL Server is slow are typically the remote DAC. That is the dedicated administrative connection. And you’re going to want that because you’re going to want a way to connect the SQL Server and run your analysis queries in a way that is not impacted by whatever awful explosion of poop stuff is going on all around you. Right? You need to be able to, you want to be able to run your analysis scripts unfettered by the catastrophe unraveling on your SQL Server. You also need some useful analysis scripts like SP Who is Active and SP Pressure Detector. Those are very good things to use in order to figure out exactly why your SQL Server is active.

So, you know, the SQL Server is unhappy at the moment. Right? So, connect to the DAC. Use the scripts. Third-party monitoring tools are cool, but under sufficient pressure, they can’t collect data either. You don’t want your third-party monitoring tools connecting via the remote DAC to get stuff because then it won’t be available to you if you need to do anything. So, you know, it’s, well, it’s attempting, right? Because you’re like, oh, if I just have them connect to the DAC, they’ll never miss out on anything, they’ll never miss out on anything, it’ll be great. Unfortunately, you might want that for yourself. So, don’t, don’t, don’t do that. Don’t, please don’t. Great. But, yeah, like, you know, if you might see if, you know, SQL Server really hits the skids at some point, you just might see big gaps in your monitoring tool dashboard where it wasn’t able to collect data either. So, at best, it can confirm when things hit the fan, but not which things hit the fan. And the reason why I’m doing this video is to sort of put a few different things all together in one place because I still run into this a lot and there’s nothing more frustrating as an outside SQL Server observer than when someone says, hey, we had a problem three days ago and restarted SQL Server. Can you tell us what happened?

Because the answer is no. Once you restart SQL Server, you clear out everything useful that I could have looked at. Granted, I get it. Sometimes you’ve got to restart SQL Server to resume, you know, those business critical tasks like making money. All right. If you don’t make money, what do you do? All right. And I’m looking at you, CrowdStrike. Stink. Stink bombs you are. So, you know, restarting SQL Server, well, it might get things back on track. It removes, you know, just about anyone’s ability to, like, tell you what exactly happened at that time. It clears out all the useful stuff, you know, and it might also prevent some useful things from being logged.

Now, there may be some evidence of what happened in other places, like the error log, query store, maybe sometimes catches things assuming it’s query related and not like something different. But even then, the getting meaningful evidence out of query store after a tragic event is kind of, I mean, it’s a stretch, right? So, what I want you to be able to do is log in to SQL Server, use the remote DAC, run useful analysis scripts, maybe even take some screenshots, maybe even save some query plans, maybe, you know, get some stuff out of SQL Server before you reboot it and get business back on track.

You know, usually if you take an extra 30 seconds or a minute to get some additional information, it’s okay, right? Like, people might sweat it out a little bit more, but, you know, if you say, you know, this will help us avoid it in the future, you know, most people are going to be okay with you doing some, like, you know, forensic digging before the reboot. We’re going to leave aside some things that should be happening, you know, in a more controlled way.

So, things like hardware changes, index changes, and settings changes, those aren’t things that you should be doing while people are trying to make money. Those are things that you should be doing in, you know, low impact times, not business critical times, right? You should avoid those things then because you want to do those things when, like, you know, you have time to, like, make the change, look at SQL Server for a bit, kind of look around, get your palms a little sweaty, and figure out if that’s a change that is okay to stay or you need to roll back.

We’re also going to leave, like, the unexpected failover off this because, you know, the unexpected failover is sort of… Surprise failovers are almost like auto stats updates and they’re, like, sudden, like, kaboom! Why is everything awful? Ah, that’s why.

And let me tell you something. I am never going to troubleshoot a surprise failover. That’s not my deal. I know people who will do it, so if you need that, I can point you in the right direction. But if you want to know why your AG failed over, this is not the face you want on camera.

Surprise stats updates are tough, too, because there are so many of them that could happen. And there are so many different things that they could affect that it’s like, you know, well, was it one stats update that bonked everything? Or did, like, 15 different stats updates and, like, cause 15 different problems?

It’s very, very difficult to track that stuff down. And it’s well-nigh impossible to do it if you’ve restarted SQL Server. Now, for settings changes, if something happens out of band, right, you know, I’m not saying who did it.

Neither will SP Log Hunter, but let’s say SP Log Hunter will look through SQL Server’s error log, and it will track down what settings changed when, but not necessarily who did them, right? You might be able to figure out who did them, because someone might suddenly, like, set all their slack away messages and be like, Nope, out for a jog.

Couldn’t possibly have been changed in MacStop and CosRashold in the middle of the day. Wouldn’t be me. Nope. This video is more about figuring out kind of what’s currently happening and the best ways to focus in on different aspects of what might be happening. We’re going to cover CPU pressure, memory pressure, out-of-band, or long-running maintenance, blocking, and parameter sniffing.

So we’re going to, probably the five most common things I see when I’m troubleshooting SQL Server issues. There have, of course, been some really interesting problems that I’ve seen over the years. They’re just not common enough to put into a video because, you know, if they’re outlandish, wild, like once to half a dozen times in a consultant’s lifetime things, they’re probably not stuff that you’re going to run into often enough that you’re going to get any value out of it.

So let’s get started before I mess something up. And the first thing that we’re going to do is focus in on CPU pressure, right? So this is what this server looks like when everything is okay.

If we sort of focus in on this section here, we have 704 threads. There’s some background stuff running that’s used 82. So we have 622 available, nothing reserved, no threads waiting for CPU, no requests waiting for threads, and we have no current thread pool weights.

All good signs. Another good sign is there are no queries running down here. This section is completely empty. Grand for us.

So let’s stick this store procedure into… Oh, I already did that, and I’ve already got a thousand threads lined up for this thing. And I’m just going to kick this off, and I’m going to run SP pressure detector a few times just until we start seeing thread pool weights start to show up in here because that’s when we know things have gotten rather interesting.

And they should start piling up. There we go. All right. Now we’re cooking with gasolines. All right.

So I’m going to kill that just because we don’t need to make things any worse. So here’s what we need to… Here’s what you want to look for. Basically, remember when this was a positive number and not a negative number, and these were all zeros?

Those were much better times, weren’t they? Everything on our server was nice and hunky-dory, and we were having picanics together, and we were eating crepes.

We were Lady and the Tramping Crepes-Suzettes, and it was lovely. It was truly a magical time in our lives. That time has passed. And what happened was we had a bunch of queries all come in and beat the crap out of our server, and this was not a very good time.

For us, no. This is times of war, famine, and pestilence, plague. But enough about 2020.

So we had all these queries running, and we exhausted our available pool of worker threads. We went negative on the worker threads, and SQL Server was lining up queries to execute. Now, one thing that SQL Server will do when it runs low on worker threads is it will start downgrading queries to DOP1.

Now, if you notice that there is one query in here running at DOP16 still that has 80 parallel workers assigned to it. Wowee!

80? This must be some query. This thing has been, you know, this is just one query that’s running in parallel. The rest of these have all been downgraded to DOP1. Or actually, I mean, there’s a few other 16s in there that are sucking up resources.

So the SQL Server does sort of like have a protective mechanism in here. The funny thing is that you will see a parallel plan for all the DOP1 queries. They’re just, it’s a parallel query running on a single thread.

So have fun with that. So this is some of the stuff that you’ll see when things get bad, along with all these null session IDs just waiting on thread pool, right?

So this is the kind of stuff you’ll see when you’re under CPU pressure. Now, let’s look at what happens when SQL Server comes under memory pressure, because that’s an important thing to understand as well.

By default, SQL Server is willing to give any one query 25% of your server’s max server memory setting. And it’s willing to give any group of queries up to 75% of your server’s max server memory setting to run and do memory granty things.

Memory grants most often come from sorting and hashing. So I’m going to run, well, we don’t need a thousand of these. That’s just mean. We’re just going to do 10 of these.

And if we focus in on memory here, we’re going to see exactly kind of what happens when SQL Server runs out of available memory to give to queries. So here is where you can see what your memory grant settings are for SQL Server.

These come from resource governor. Whether resource governor is enabled or not, this is where the settings sort of come from. If you’re on enterprise edition, resource governor can be a great way to turn that 25 number down to a much lower number so that you don’t have to deal with queries asking for insane memory grants and not using them.

SQL Server is very aggressive when it comes to the memory granting. But what you’ll see under memory pressure is you’ll see probably something like this memory clerk SQL reservations number get real big, right?

That’s 50 gigs of memory grants. And you’ll see some numbers down here start to change too. So I’m going to rerun this after I talk about this stuff so you can see that these numbers do tank down when you’re under memory pressure.

So you’ll see we have three queries that have been granted memory, seven that are waiting for memory. We’ll see the 50.21 gigs that we saw before, right, of granted memory going out the door. And these numbers in here will change, right?

So these numbers in here will get lower, especially the available memory column. And if I give SP pressure detector a run with nothing going on, you’ll see that these total and available memory columns will float right back to 60 gigs, right?

So one thing that I want to say real quickly, though, is if you don’t know what’s happening on the server, you don’t have to worry about running SP pressure detector with any parameters. If you run it with nothing, it’ll give you all the results and you’ll be able to pick out from there what’s going wonky, right?

So just like a bare but naked run of SP pressure detector will give you weight stats, disk stats, what do you call them, perfmon counters, tempDB info, memory info, like all the stuff that we’ve seen with the CPU and memory.

Like it just returns everything. So you get all that stuff back at once and you can pick and choose from what’s happening. So under CPU pressure, you’ll hit a lot of thread pool weights. Under memory pressure, you’ll most likely hit resource semaphore weights and maybe even resource semaphore query compile weights, depending on how that memory is getting booted out the door.

So that’s a couple of signs of like physical pressure on the server, physical hardware pressure on the server. Another thing that you might run into is runaway maintenance. So I’m going to focus on dbcccheckdb.

But you know, it’s kind of funny. I sure do wish that when I saw runaway maintenance, it was dbcccheckdb. Usually when I see runaway maintenance, it’s some buffoon rebuilding and reorging indexes at 5% and 30% because they read it somewhere in 2003 from a blog post that doesn’t even have like work in code formatting or a bunch of broken picture links.

So as much as I wish that folks would listen to me and stop doing useless index maintenance, it still happens a lot and I still have to talk people out of it. And some people will cargo cult around this useless facility until eventually someone pulls them kicking and screaming away from SQL Server.

I don’t, you just can’t help some people. It’s something that I’ve learned after about 10 years of consulting is you just can’t help some people.

They will just do whatever they want to do. They find their comfort zone and they stick to it. Nothing can get them out of it. So I ran dbcccheckdb before I started recording this so that we would have a log of how long it took, which is 24 seconds.

I promise we’ll get back under my armpit there. 24 seconds for dbcccheckdb. Now, you know, you can think what you want about the 24 second number. Doesn’t make much difference to me.

But if we run, now if I ran this query workload without dbcccheckdb also running, I want to say it took like 15, 20 seconds. But if I run, if I kick off dbcccheckdb, here, and I start running this here and go boop, both of these, both of these will suffer.

Right? dbcccheckdb is going to take longer and this query workload is going to take longer because they’re happening at the same time.

Now, again, very simple and easy way to see if this is what’s affecting your server. Run spwho is active because you’ll see if dbcccheckdb or any other maintenance task is running. Now, things that I would absolutely check for, checkdb, index maintenance, and full and differential backups.

If I would expect to see log backups happening pretty often during the day, because if it’s important business data, well, guess what? Log backups are a good idea. Huh?

Yeah, they’re great. Log backups. Who would have thunk it? Amazing things. You see those running during the day? Okay. It’s fine. Checkdb, full backups, differential backups, and of course, any form of index maintenance could be a pretty tough sell there.

I would even say that unless you specifically schedule statistics maintenance to happen during the day, I would be also very surprised to see stats maintenance happening during the day.

So, just to qualify what I said about useless index maintenance, statistics maintenance is not useless. Do that. It is good for you. It is a part of a balanced breakfast. But now, coming back to what I was saying, with the query workload running and Checkdb also running, well, Checkdb took an extra 10 seconds down here, right?

Look at that. 35 seconds. Not anything outlandish, because, you know, this does have to sort of fit into a video that someone will watch.

And the query workload over here took 18 seconds. Okay. So, this didn’t change too badly, but the DBCC Checkdb took longer. But one thing that I would look for, I mean, that is about, I think, five seconds worse than it was without it.

But anyway, one thing that I would absolutely look for is runaway maintenance tasks, because, you know, DBCC Checkdb is not a blocking problem. DBCC Checkdb is like a resource usage problem.

You know, index maintenance very much so could cause lots of blocking problems, right? Even with online equals on, you can still see blocking from that stuff. So, be very careful there.

Moving right along, another very common reason why SQL Server might be slow right now would be blocking. So, let’s come over here, and let’s begin a transaction, and let’s do a very small update just to take out enough of a lock to cause a problem.

And let’s copy this, and let’s stick this in here. And we don’t really need to, you know, give this a lot of threads in order to show you what’s happening.

But we do need to run spwhoisactive in a very specific way. So, we are going to run spwhoisactive. We’re going to use findBlockLeaders equals one. My finger disappears here, apparently.

So that we get the blocked session count column in our output. We’re also going to use getPlans, because getting execution plans for these things can often help us figure out why blocking was going on for so long.

Might be a very inefficient modification query. Worth looking at. You might also be able to figure out if someone was running some sort of modification query when they shouldn’t have been.

Maybe they did the old forget-aware clause trick. I don’t know. But anyway, this is still kicking excellent. So let’s run this.

And what you’ll see is very useful material. I’m just going to roll this back so we don’t have to, I don’t forget about doing that later. But if we look at spwhoisactive, we’ll see a bunch of queries experiencing lock weightness. And we’ll see over here, we’ll see these blocking session ID columns populated with spids.

They will often correlate with session IDs you see over here, which is a wonderful thing, because then you know who’s doing what. And the real magical part of the way that I ran spwhoisactive was that the results, instead of getting ordered by the duration column, the ddmmhs whatever column all the way to the left, we ordered by blocked session count so we knew who was at the very top of the blocking chain and who was suffering underneath them.

But it is a little bit misleading to see this select query right here as a blocker for the other select queries. That’s just sort of the way it looks in the dmbs. And, you know, that’s not really anything that’s, you know, going to change your life.

You just need to make sure you understand it. The reason this query has nine blocked sessions behind it, because this query was blocking this query, right?

So this select query isn’t really at fault. It’s this update query. And it just kind of looks like this one was doing something that the rest of them needed to do. So blocking, very, very common reason why you might see a server having problems. Depending on what the lead blocker is, you know, you might be able to just kill the lead blocker and, you know, let everything else move on.

There are a lot of, you know, you do have to be careful about rollbacks, because rollbacks can, you know, and if you don’t have accelerated database recovery enabled, rollbacks are awful long, can be awful long running single threaded log reading tasks that you don’t want to deal with, because they can just make your blocking problems go on longer.

So just be very careful if you decide to start killing lead blockers, what ends up happening to them, right? If the lead blocker is, you know, not a query, if it’s some other weird thing, again, just be careful with what you’re doing there.

Another thing that can happen quite a bit, and that I see quite a lot in my consulting, is of course the old parameter sensitivity. And I’m going to create a couple indexes here.

And once these finish, I’m going to run this store procedure a whole bunch of times in here. And for this one, I’m just going to do 100 threads. And even for 100 threads, this finish is incredibly fast, right?

Let’s, even if I do 1,000 threads, this will finish very fast, right? This store procedure runs very quickly most of the time. Now, if I dial this back to, let’s just say 10 threads so that we don’t, we don’t overwhelm ourselves, and I run this store procedure with a different parameter, all of a sudden things will look slow.

And we’re going to run spwhoisactive with a different set of parameters than before. We’re going to use the getAverageTime equals 1. And what this will do is it’ll look for prior executions of this store procedure where, in the plan cache, and say, well, how long do you usually run for?

Oh, that long. Well, that’s abnormal, isn’t it? And so what you’ll see is a couple columns in here. This one is how long things are actually running for.

And this one is how long things are usually run for. And that’s where you need to be careful. So, anyway, let’s wrap this up. These are common reasons why SQL Server might be slow.

Thank you for watching. Hope you learned something. Hope you enjoyed yourselves. If you like this video, give it a thumbs up or a comment. If you like this video a lot, subscribe to my channel.

I love you. Thank you. Also, hire me to figure this stuff out for you because I’m pretty good at it. Bye.

Going Further

A Little About Out Of Date Statistics In SQL Server

Posted on August 14, 2024January 22, 2026 by Erik Darling

A Little About Out Of Date Statistics In SQL Server

Thanks for watching!

Video Summary

In this video, I dive into an often-overlooked issue in SQL Server: outdated statistics and their impact on cardinality estimation. Specifically, I explore how the default and legacy cardinality estimators can lead to significant performance issues when statistics are not updated frequently enough or rely solely on auto-update stats. By using a modified version of the Stack Overflow database, I demonstrate how adding new data without updating statistics can result in severely inaccurate execution plans, especially with inequality predicates. The video highlights the differences between the legacy and default cardinality estimators, showing how the latter provides slightly better estimates but still struggles when faced with ascending key columns that aren’t marked as such by SQL Server. Through practical examples, I explain the math behind density vector estimation and provide insights into why frequent statistics updates are crucial for maintaining optimal query performance.

Full Transcript

Erik Darling here with Darling Data, voted by BeerGut Magazine to be the producer of the freshest, hottest SQL Server content on the internet. So if you’re into that sort of thing, you’re in the right place. Welcome aboard! In this video, we’re going to talk about a somewhat overlooked problem that you can run into with outdated statistics. Especially, well, I mean, I guess it could really apply to either cardinality estimation model, either what Microsoft quite smugly refers to as the default cardinality estimator, which is the one that you use if you’re in a compat level higher than 120, which is around SQL Server 20, or 314, or the legacy cardinality estimator. Again, very smug nomenclature there, which is the cardinality estimator that you use if you’re in compat level 110 or lower, or you have the database scoped option, database scope configuration option to use the legacy cardinality estimator turned on, enabled. This thing is looking a little funny on me. It’s a little too happy down there.

One thing that a lot of people overlook. So when we think about outdated statistics, what a lot of people focus on is that you have this 200-step histogram, and perhaps the values in the histogram don’t reflect the actual counts of things that are in the table for existing data, which is totally a reasonable thing to be worried about. So this can happen either because, you know, you, well, let’s see, let’s think of some reasons here. Either you haven’t updated stats in a while, and the, you know, the numbers that are in the histogram just don’t, like, like a bunch of data got added, and like, you know, what, the old histogram said that you had like 500 rows for this thing, but now you’ve got like 500,000 rows for this thing. Granted, that’s a little far-fetched. If you have a big enough table that you can see that sort of difference, but auto stats will kick in eventually, and I know how wild you crazy kids are about rebuilding indexes, which will also update stats.

But let’s just say for a minute that, like, you know, you had some just wildly outdated stats that hadn’t been updated in a while because you have a huge table, you haven’t met the auto stats threshold yet, auto update stats threshold yet, and you just had like wildly inaccurate numbers. That could also happen if you don’t use a high enough sampling when you do a stats update. If you just use a default sampling, you can miss a whole lot of really interesting data points.

Apparently, um, apparently I smoke too many cigarettes, or apparently, since I don’t do that, we’re a family-friendly, we’re an addiction-free channel here. We are only addicted to making SQL Server go faster. You know, we don’t smoke cigarettes.

Though we do miss smoking cigarettes. But really, one of the big things that I run into is that, you know, you’ve added, like, new data to a statistics histogram that just isn’t reflected in it at all. A lot of people will call that something like the ascending key problem, which is a valid, you know, valid name for it.

You know, there’s all sorts of writing about it. And, um, it is a, it is a, something that can actually cause pretty severe performance issues. Because depending on, uh, which cardinality estimator you’re using, you could get some wildly different guesses depending on, um, what, well, like, how many rows are in the table.

And, you know, like, how many values match a certain predicate and all this other stuff. And you can end up with some really bad execution plans when you, um, you start joining, uh, those tables off to other tables. And the, you know, the predicate estimates, like rows coming out of the table, don’t really reflect reality.

And so you don’t get the good, the join that you get is, again, not in line with reality. And, you know, you get like a nested loops join when you really shouldn’t have. So, uh, we’re not going to delve that far into it in this video.

I’m just going to kind of show you what, what, what it’ll look like when that sort of thing happens. So in my copy of the Stack Overflow database, uh, what I’ve done is I’ve created a table, uh, called votes underscore beater. And we’re going to beat this table up a little bit.

Uh, I didn’t want to do it to my actual copy of the votes table because I, I, I don’t, I don’t like messing with, I don’t like messing with them. Um, uh, life is hard enough without having to, well, actually I don’t have to ever have to restore a database. If I do something like that, uh, I, I keep, uh, a version of the Stack Overflow database that I do nothing in called Stack Overflow clean.

So if I ever really mess something up in a table, I can just copy it from this other database. I’m a cheater like that. Cause I, I don’t, I don’t, I don’t like restoring databases.

It’s not, not my thing. Um, so I, I’ve created an, uh, an alternate table to use. And I’ve inserted all the data from the actual votes table into it. And I can see I did all my homework there and I created an index on it, uh, on the post ID column.

Because that’s the column that we’re going to focus on here. Now, um, at current, I actually know cause I already did all this stuff. Um, what this query returns is just some statistical information about what’s in there.

Now, um, right now I have 229,561 modifications against this table. In order for, uh, auto-update stats to kick in using the new auto-update stats thresholds. Uh, the old, old auto-update stats threshold was 20% of the table plus 500 rows, which is, you know, let’s just face it, 20% of the table.

And he, it kills me when he has to stick to plus 500 rows. Cause yeah, it’s still like, it’ll be close enough to 20%. If it’s a table big enough for you to care about, 20% is 20%. Adding 500 isn’t meaningful.

Uh, and the current range high key in the statistics histogram is this. But there’s a whole lot of data within these modifications that have, that has a range high key for post ID higher than this, right?

So that’s the important thing here. So, um, what I did in order to facilitate this, this nightmare is I created a temp table. Uh, it did some work to validate the post IDs that were going to be deleted, uh, deleted the top.

And now, so the, the stats modification counter, uh, that I just showed you here, uh, that was about 500 rows higher, um, plus 500, right? 500 rows, who cares?

Well, now we’re going to talk about how much 500 rows makes a difference. So I subtract, so they, when the table was a full size, right? Before I deleted rows out of it, there was, uh, the auto-update stats threshold was, uh, 230,062.

So I just subtracted 500 rows out of there. And, uh, so this number minus 501. And I deleted those rows out and I, on the delete command, I outputted those columns into the votes temp table.

Uh, then one thing that I did that’s really important before, uh, you know, before anything else was I re-updated statistics, right? The, the, the stats update here is to prevent, um, me going over that modification counter. Because remember, deletes count as modifications.

So I wanted to update stats to zero that out. And then I inserted all the rows from the temp table back into votes beater. And I did some work to find, uh, the highest post ID with more than one hit to it.

Uh, or rather the post ID over that old, the hot, the stats high key, the range high key. And for that, for the statistics object, uh, that has, that had the most in there. Right?

So I want to show you how this, the, the guess is crappy. So with the legacy cardinality estimator, the problem that you run into is whether you have an inequality predicate or an equality predicate. SQL Server only ever guesses that one row is going to come out of there.

And let me just scooch these, these dummies together a little bit. So for the inequality predicate, we get 229,877 rows back, but SQL Server only thinks that one row is going to come out of there.

For the index seek, where we, you know, find 300 rows, SQL Server only guesses that one row is going to come out of there. This is a thing with the legacy CE that Microsoft has done, uh, some, did some work to improve upon and has tweaked and mangled and done all sorts of things.

It had made it, you know, it’s still better, but it’s the constant tweaking. It’s, it’s like, like who can keep up? Right?

It’s like every CU, there’s just some thing that’s slightly different by like a percent. You’re like, nothing works anymore. Everything’s broken. Uh, with the, with the new cardinality, I see what Microsoft, um, so pompously calls the default CE, things look different.

Is, uh, and what I’m going to show you here, I mean, I have this use hint, this enable hist amendment for ascending keys hint on this. And the reason I have this here is because SQL Server has not marked the post ID column as, uh, being ascending.

Um, I tried it with a few different columns in the table, including the creation date column. Uh, and SQL Server did not think that any of them were ascending. So we’re just going to stick with post ID and we’re going to throw this hint on there to show you the difference.

So if I throw this hint on this query and we search for where post IDs are greater than, uh, what the, what’s in the current histogram, uh, we get a guess or rather we get an estimate that just about matches the, uh, modification counter in the table, right?

So two, two, nine, five, six, one. If we had two, two, nine, five, six, two, auto stats would have kicked in. So when we tell SQL Server that this is an ascending key and SQL Server runs this query, the cardinality estimation process, uh, looks at how many modifications there are and says, okay, well, maybe there’s that many rows.

This is for an inequality predicate. Uh, if we don’t add that hint on SQL Server, um, guesses about 30% of the table will be in there.

So this number changes from being the modification counter, uh, to about 30%, sorry, to about 30% of the modifications, right? So six, eight, eight, six, eight, eight, six, eight. I’ll show you the math on that when we get through these demos.

So you can be happy that you watch this. And when I say, I hope you learned something, you can say, oh yeah, I did learn something. I learned some math.

And then you’re going to say, oh, I learned math from a high school dropout. And then you’re going to say, maybe I shouldn’t learn math from a high school dropout. We’ll, we’ll see. I don’t know.

Uh, so anyway, uh, if we look at the equality predicate with, um, with the enable ascending key thing in there, uh, we will get a guess of 55 or an estimate of 55 rows, right? Out of the 294.

So remember with the default CE, uh, that, those are both guesses of one. So with the new cardinality estimator, you get slightly different guesses depending on, um, you know, if the, if SQL Server has already has marked the index or statistics options being an ascending key, you wouldn’t have to use these hints.

But in my case, I’m, I’m using them to sort of fludge things a little bit. So, um, what we get, if we look at the stats properties again, which will help explain those numbers a little bit.

Uh, so for the ascending key inequality predicate, uh, we got just basically the modification counter as a cardinality estimate. For the default CE without that on there, in order to get 6, 8, 8, 6, 8, which is this one, we basically, um, look at the modification counter, uh, times 0.3.

So about 30% of the modifications. For the default CE cardinality, uh, estimate, we, you know, we get here, we actually get a density vector estimate.

Now, if you’re unfamiliar with the density vector estimate, that’s something that comes from looking at, um, the statistics object, which we’re going to go do, right? We’re going to go look at this, and we’re going to go to the properties.

And these two numbers here, if you’ve, if you’ve ever, like, you know, watched my videos or read my posts or read anyone’s posts about local variables, you’ll know that when you have an equality predicate with a local variable, absent or absent or recompile hint, cardinality estimation changes from, like, a parameter sniffing thing or a literal value estimation thing to using some magic numbers.

Those magic numbers come from the statistics object. If we go into the details here, let’s just make this full screen to make it easy. If we look at the density of the column, of the post ID column, which is this, and the number of rows in the table, which is this, and we multiply this by this, we get that, that, we get that density vector guess.

The, all density is just sort of like the assumed uniqueness of this column. So the SQL Server does some math in there to figure out how unique it thinks the post ID column is, which is, which is again, a total guess based on, you know, like something times the distinct number of rows.

So SQL Server comes up with this number and multiplies it, that density number by the rows number. And that’s where we get that guess of 55 from. So that explains pretty much, you know, old versus new when it comes to outdated statistics, especially when those statistics don’t have, like when the high key of those statistics don’t have, it does not match what the current high value is in a table.

You know, it’s, it’s a lot, maybe it’s a little bit easier to think of in terms of like a, like a date column or a date time column where, you know, every row that gets added, it’s almost like an identity column where every row that gets added is new, right?

It’s a higher value than what was there before. It’s a, you know, it’s sort of like an append only, you know, type, type insert scenario. You know, it’s not like, it’s like a, you know, it’s like an order date column, not a ship date column or, you know, ship date comes in null and gets updated.

Order dates are always going to be new. Every order is going to be newer than the order before it. So that’s like an ascending key. Same as, you know, an identity column in that regard. So really one of the biggest dangers that I can see, uh, or rather one of the biggest, you know, performance landmines that I see a lot of people run into is when they don’t update stats frequently enough, or if they just depend on auto stats to do their thing, then they end up with these histograms that don’t represent the newest data, right?

It’s not really about the data that’s already in there. Most of the time, most of the time, the data that’s already in there is probably close enough to reality.

You know, there are obvious exceptions to that. Of course, you know, nothing is perfect, but it’s usually the data that lives beyond whatever the current range high key is that messes things all up. Now, of course, if we ran a stats update on the votes beater table, we would have, we would have representation for those higher values and they only wouldn’t have this anymore.

So it’s something you have to be really careful of, you know, especially if you’re using the legacy cardinality estimator, which I often think is the much better cardinality estimator. Getting that one row guess can be an absolute disaster.

So if you get that one row guess and SQL Server comes up with a, you know, crappy nested loops plan where, you know, you have lots and lots of rows and, but SQL Server’s like, I think there’s only one, you know, you could run into some real issues.

The newer cardinality estimator does do better with this. You know, it at least gives you a guess of higher than one. It might not always be perfectly accurate, but often, you know, the fact that it’s a bit closer to reality does, does buy you, you know, does buy you out of some, you know, potential performance landmines or pitfalls, potential performance pitfalls.

Pleasant, pleasant, pleasant, pleasant alliteration. Anyway, so you learned some math from a high school dropout. Hope you enjoyed yourselves.

I hope you learned something other than math from a high school dropout. If you like this video, you can, you know, if you want to create a bunch of YouTube accounts to give extra thumbs up, you can do that.

I’m not responsible for that voting ring, though. I would never encourage that behavior. I don’t even thumbs up my own videos, which, I don’t know, probably, probably tells you something about how, what an ethical, honest person I am.

If you like this sort of SQL Server content, please subscribe. Subscribers are awesome. We, we, I finally cracked 4,000, which means in the grand scheme of things, probably not a lot.

But if one of you lovely 4,000 or so people sees these videos and thinks, hey, we could use the kind of help that Erik Darling offers for SQL Server, you can hire me, too. I am, I am a consultant.

I consult and I fix these problems and I find these problems and I analyze these problems and I guess, actually, no, I said, I analyze these problems, find these problems, analyze these problems, and fix these problems.

So it’s the FAF method. The patented Darling data, find it, analyze it, fix it. The FAF of SQL Server performance tuning. Don’t steal that from me.

I’ll come find you, my Delta Miles. And I think, I think that’s probably about good for today. Um, uh, I should probably eat something I feel like. My, my, perhaps my blood sugar is cratering a little bit.

Um, I think I’ll, I think I’ll go eat a beer. Or something. Maybe, maybe some chicken. Chicken’s good, right?

Chicken’s healthy for you. But I do apologize to any, if any, if any of the data darlings out there are vegans, I do apologize. You can, you can, you can pretend I said something else. Tofurky.

Um, some, some sort, some sort of bean. But, anyway. Uh, I’m gonna go do that. And, uh, apparently I have some drivers to install, because Intel keeps yelling at me. So, I’m gonna do all sorts of fun things with beer and chicken and, and, and driver updates.

And I’m sure you’re gonna be very jealous. Um, I know that if I were, if I were not me, I would be very jealous. So, uh, as always, thank you for watching.

And I will see you next time in another video. Um, maybe, maybe, hopefully there will be no math. Because that’s, that’s, that’s a promise I’d like to keep. All right.

Thank you.

Going Further

Database Design Choices For Multi-Tenant Databases In SQL Server

Posted on August 13, 2024August 13, 2024 by Erik Darling

Rule Of Nerds

When you’re designing the back end of a database, people will have all sorts of ideas.

Normalization, partitioning, referential integrity, and, usually, trying to figure out what to do when you have more than one client.

If your application is user-focused (like Stack Overflow), you don’t have to struggle too much with the idea of isolation. But when your application is geared more towards supporting multiple entities that have one or more users, things change. Sort of like how Stack Overflow manages all the other Stack Network sites.

Were you to ask me which model I prefer, it would be every tenant getting their own database. Your other options are:

Everyone all mixed in together like gen-pop
Using separate schemas inside a single database

It’s hard to see any real upside to those propositions unless you’ve poorly chosen a cloud solution with limitations placed on number of databases. Yes, I’m looking at you.

The problems you run into with everyone mixed in together are just far too grand, especially if the concept of permissions comes into play within a tenant.

If a single user can belong to multiple tenants, forget it. You couldn’t get screwed harder if you bought a time share in Atlantis.

Embrace The Suck

Since we’ve discarded the lunchroom in Oz approach, let’s talk a little but about why the schema-per-tenant approach doesn’t work.

It doesn’t buy you anything from a maintenance perspective without dumping a whole lot of awful complexity in your lap. Worse, there’s no good tooling available to analyze anything in your database schema-by-schema.

Is your client in schema1138 unhappy? Okay, try to find out what’s going on with them in the plan cache or Query Store. I’ll wait. Look at how their indexes are used. I’ll be here. Wait stats? Blocking? Deadlocks? Hey, I’m comfy. Take your time.

Every client I have who started with this pattern has inevitably built a tool to move bigger tenants out to their own database to isolate them and figure out their problems, or move them to their own SQL Server instance. You will, too, if you choose… poorly.

While I hate SQL Server’s plan cache for finding performance problems, we are totally stuck with it as part of the query execution process. As much as I wish Microsoft would replace it with Query Store, they’re too busy trying to find ways to squeeze cloud customers for all they’re worth without the benefit of a lubricant to work on anything practically useful to SQL Server users.

So you end up with the same problem as you do with the database-per-tenant approach. Your plan cache has limitations on size and number of plans. When the same query executes from a different schema or database, a new entry in the plan cache is made.

This leads to a lot of plan duplication in there, and a lot of plans getting flushed out when those limitations are hit. The difference here is that it’s a whole lot easier to figure out what went raunchy for a specific database using Query Store than it is to figure out what’s gone sour in a specific schema in a specific database using… anything. Anything at all.

Perhaps thoughts and prayers are in order.

Rebalancing

I’ve run into far too many clients struggling to keep the lights on with the all-in-one approach, and the schema-per-tenant approach. Especially with the all-in-one approach, you end up with something like a TenantId column in every table, and in every index, sometimes even filtered down to specific problem-tenants. It’s kind of awful to watch.

This is true of many different areas, whether it’s scalability, security/compliance, performance, RPO and RTO, and of course costs.

Some readers may call out things like log backups, and Availability Groups. Sure, I get that! It might be hard to get a log backup job to run across a bunch of databases every RPO-minutes. It’s also trivial to put groups of databases into multiple log backup jobs so that you don’t have to worry about that.

For Availability Groups, I’ve seen more than a few cases where they were falling behind because there were hundreds of schemas in a database all making lots of tiny changes. Keeping lots of databases synchronized is certainly more responsibility, but no less in danger of things falling dismally behind.

Let’s say you have 400 databases. That may take way more worker threads to keep up, but at least they each get worker threads to do that business. A single database with 400 schema in it doesn’t magically get a bunch of additional worker threads to keep data moving, and it’s foolish to think otherwise.

When you start with the more sensible approach — database-per-tenant — you give yourself many more options. You can move databases to new server much more easily, and you can have different databases on different tiers of service way more easily. Think Standard vs. Enterprise Edition, SLAs, RPO, RTO, HA and DR, etc.

And of course, you can charge extra for those additional services. Don’t worry, after a decade in the cloud, everyone is used to getting their pockets shaken for every additional nicety.

Thanks for reading!

Going Further

Join Me At Data Saturday Dallas Sept 6-7

Posted on August 12, 2024August 1, 2024 by Erik Darling

Spring Training

This September, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning for Data Saturday Dallas.

All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of streaming on-demand content.

Get your tickets here for my precon, taking place Friday, September 6th 2024, at Microsoft Corporation 7000 State Highway 161 Irving, TX 75039

Here’s what I’ll be presenting:

The Foundations Of SQL Server Performance Tuning

Session Abstract:

I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.

Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.

Event Details:

Get your tickets here for my precon!