Tag: SQL Server Consultant

SQL Server Performance Office Hours Episode 71

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 71


To ask your questions, head over here.

Chapters

Full Transcript

Your best friend, Erik Darling. Your best friend and monitoring tool mogul, ErikDarling here for an Office Hours episode. You may notice that my beard has gotten a little bit shorter. It got hot and I got annoyed and my face got itchy. So, I gave things a little… I’m going to start things fresh. I’m going to let things naturally come back to a more familiar bearded state.

But for now, we’re going to enjoy looking quite youthful and not having inches of gray hair pouring out of my chin. So, that’s nice. It’s Office Hours time where I answer five of your questions. I don’t have questions. I mean, I have questions. It’s not about anything you care about. Bigger questions in life. Down in the video description, if you want to ask me your questions, don’t ask me my questions. I’ll jump out a window.

The link to do that is down in the video description. There’s an Office Hours link. It’s got the words Office and Hours in it. If you click on that, it’ll bring you to a place where you can ask questions. There’s all sorts of other useful things down there too.

For example, if you’d like to hire me to do things to your SQL Server, you can do that. If you would like to purchase my training so you can get better at doing things to your SQL Server, you can also do that. If you like this channel in some way, shape, or form enough…

That you feel like giving me like four bucks a month to support my efforts here, you can do that. And, of course, I always do appreciate the channel numbers growing and exceeding my wildest expectations. So please do like, subscribe, tell a friend, and help me overtake that Amiga Repair channel once and for all.

I’m going to show Jim who’s boss. If you’re in the market for SQL Server performance monitoring, I have a completely free, completely open source tool.

I probably owe you some videos on the updates to that because there have been some really good ones lately. A lot of work on the UI, a lot of work on performance of the UI, and things like that. So the more time that I get to use it on client production servers, the better this thing turns out.

Because… You’re always your own worst critic. At least I hope I am anyway.

Anyway, let’s go answer some questions here. We’ve got them all lined up. And let’s see. Number one here. Now that you’ve done half a year of lectures, what would be your main takeaway from Domestic vs. International workshops?

And where would you like to go back to? So… I strongly prefer international travel for these things.

I like getting a bit further out of my comfort zone. This year I got to go to two new places. I got to go to Poland, where I’ve never been, and I got to go to Croatia, where I’ve never been.

You know, the downside is that you are a bit tethered to some conference stuff, so I don’t get to get out into things as much as I would like to, like if I were just traveling on vacation.

You know, there’s all sorts of stuff that I would like to see that I just usually don’t get around to. But I strongly prefer the international travel for conferences. And it’s not that I don’t love this great big country of ours.

It’s just that I live in New York City, and going to Chicago is cool, because Chicago is pretty well a city.

Stuff like that. But some of the smaller cities, it’s like you get there, and you’re like, I guess I’m just going to sit in this hotel bar and see what happens.

I don’t know. There’s just not a lot to do out in the world in some of the smaller venues. So I prefer the international travel, where I get to go to another capital-type city.

And I know that New York is not the capital of America, but it’s a pretty strong city as far as stuff to do goes. So when I travel, I like to get out into another city environment, I am not much of a country bumpkin.

But I would go back anywhere. Yeah, I would love to go back to Poland again. I did not get to see a ton of Wroclaw.

And I would love to get back to Croatia. I know that SQL Day does a SQL Day Lite, and this year it is in Gdansk.

But I think with all of my fancy family summer travel plans, getting back out to Poland after that might be a bit much on, I don’t know, my body.

So I don’t know if I’m going to make that one. But I would love to get back to… Really, I want to start doing more of the European conferences because they tend to be a bit larger.

If you go to American ones, you have some… You can go to…

You can get to some bigger stuff, right? Like SQLCon, FabCon looks pretty big down in Atlanta. And I love Atlanta. I don’t… I’d probably have to start saying nice things about fabric to get a green light on that one, but I don’t know.

And then, you know, you have PASS Summit out in Seattle. I dig Seattle. Seattle is a good city too. But, you know, like the Data Saturday things, they don’t tend to be like, you know, 24 or 500 people the way that some of the European ones are because, you know, they’re like yearly things that happen and they have a bit more draw to them.

Whereas like the smaller local events, you know, you get somewhere 100, 150 people. You know, stuff isn’t as big and festive and fun. So, you know, I think I would like to get back…

I want to do more of the European stuff, I think, in 2027, assuming that I’m allowed to go anywhere and do anything. I don’t know.

That was probably a much longer answer than I intended. I was kind of rambling there for a minute too. I don’t know. Sorry about that. I’m coming off a rotten sinus infection.

So if I make noises, just deal with it. What do you generally hear about ClickUp? Nothing. I don’t know.

I have like… I know like two people who use it to look at logs. Yeah. I don’t know. I don’t hear a lot about it. This is not the forefront of where I spend my time. I don’t go around asking people about ClickHouse.

Neither. Yeah. I don’t know how to pronounce the first one. It kind of looks like the French word for other, but with some extra letters in it.

I never cared much for Aphex Twin. That was like pop music for me, you know. I never got into either one. That’s your thing.

Cool. I’ll turn it up. But not for me. Not for me. Hi, Eric. We have an Azure VM running SQL Server 2025 Enterprise. About a week ago, the VM unexpectedly shut down or restarted. According to Microsoft, it was triggered because the VM experienced excessively long disk IO waits. Have you seen this before?

And what kinds of issues could cause Azure to restart a VM under these circumstances? Boy, oh, boy. The more… The more… The more… The more… The more… The more…

The more… The more… The more… The more… The more… The more… The more… The more… The more…

The more… The more… The more… The more… The more… The more… The more… The more… The more… The more…

The more… The more… The more… The more… The more… The more… The more… The more… The more… The more…

The more… The more… The more… The more… The more… The more… The more… The more… something bad happened. Microsoft’s just like, oh, it was long IO. Well, I don’t know. You’re responsible for the infrastructure. What could possibly cause long IO waits, Microsoft? I don’t know. Kick it to them. Why are you asking me what Azure does? Azure sucks. See, this is why I’m not going anywhere. Anyway, yeah, it’s a nightmare up there. Absolute malpractice. Are there any real workloads where table variables actually make sense? Yeah. Ones where you put a small amount of data in them at a very, very rapid pace and you don’t join them to any other tables.

That’s about it. There are, of course, all sorts of maybe strange edge cases and scenarios where you might find a table variable for some whatever reason gets you a better query plan than a temp table.

Maybe it’ll happen for you once in a while. It certainly happened to me once in a while. It’s more with table-valued parameters than I think with strict table variables. Because table-valued parameters for a long time, they got the sniff, the number of row sniff that table variables were not getting. So you would sometimes just get better plans out of those. But yeah, I mean, it’s really like you got to really put a lot of data into a lot of different table variables, really quickly for them to make sense. And, you know, where like just the overhead of maintaining and caching and creating statistics on temp tables is too much overhead for you. But those are really sort of niche-y edge case workloads or even just like small parts of a workload where you would have to be sort of careful. You would have to like really know like just like the temp tables were too much overhead for you.

Either your very, very high-scale, fast-paced workload or this one portion of your workload where you’re doing lots of tiny little things with table variables very quickly. I don’t know. Anyway, let’s see. You cover international travel. It’s a sad question about ClickHouse.

I just don’t… Again, I don’t talk to people who use too many weird databases. It’s… I don’t generally hear anything about it. Techno I don’t care about. People who got suckered into Azure. Table variables. All right. Well, I guess we’re doing that here. Before I start coughing again, I’m just going to wrap this one up. Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in tomorrow’s video where we’re going to talk about a new peril with Insert Exec. So that’ll be fun. Fun for everyone, I think. All right. Thank you for watching.

Going Further

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

Learn T-SQL With Erik: Variables and Date Math

Posted on by Erik Darling

Learn T-SQL With Erik: Variables and Date Math

Chapters

Full Transcript

Alright, it’s the last one of the week, you can all breathe a sigh of relief, till next week, till hell begins again, unremitted, anyway, don’t let me rub off on you, in this video we’re going to learn some more about T-SQL, stuff around variables and date math, so that’ll be fun right, everyone likes fun, alright, down in the video description, you will find potentially one of the more important links that you will ever click on in your life, and that is the link to purchase this training for $100 off, there are also other links in there, which I think are equally valuable, depending on your goals and needs in life, where you can hire me for consulting and become a supporting member of this very YouTube channel, you can also ask me office hours questions, which I will answer every Tuesday, faithfully.

I used to answer them faithfully every Monday, but then I cheated on Monday with Tuesday, and now, I don’t know, I’m stuck with Tuesday, Monday dumped me, I don’t know, the whole sordid thing, might have to, I don’t know, I don’t know what to do here, it’s a sordid love triangle, anyway, and if you perhaps want to do, just do me a solid in life, you can of course like, subscribe, and tell a friend, in the video description there’s also links if you want to…

Have free SQL Server performance monitoring, you can do that, from me, it’s my gift to you, just for existing, and using SQL Server, that’s all, that’s it, the only bar for entry, totally free, open source, no weird sign up, phone home stuff, I don’t want to know more about you, or anything like that, just a bunch of T-SQL collectors, running on a schedule, collecting all the important things that you would ever want to know about your SQL Servers, wait stats, blocking, deadlocks…

Bad queries, CPU, memory, disk, you name it, it’s all in there, doesn’t get better than that, especially for that price, alright, anyway, let’s talk about this stuff here, let’s do the damn thing, so, one thing that I want to talk about, and this is a good start to things, is a pattern that I see in a lot of stored procedures, that I wish that I didn’t, and that is…

We have, for simplicity’s sake, we have one parameter in here, and it is nullable, right, by default, it is nullable, so you don’t have to pass anything in here, you’re not going to get an error, it’s like, SQL Server expects a value here, right, so, what a lot of people will end up doing, is, if the date comes in as null, they have a safeguard on it, and the safeguard, I mean, it couldn’t be anything, but, we’re going to use 2013-1201 for our safeguard, and we’re going to look at the side effects and repercussions of such a bit of code.

So we’ve got query plans turned on, and if we run this, and let’s say that a null gets passed in the first time around, and this is going to run, and run, and run, run so far away, I don’t know, something like that, and we look at the execution plan, we didn’t do too well, right?

SQL Server… SQL Server guessed that we were going to get one row, we got 1, 5, 2, 6, 9, 9, 7, we got 1.5 million rows back, we did 1.5 million key lookups, and we didn’t get a very, I mean, this isn’t the worst of it, because, like, we only go about 600 milliseconds in here, so that’s not, like, terrible, but, you know, our sort didn’t get enough memory, ba-ba-ba-ba-ba, we’re all sad, we’re all having a bad time.

And what’s funny… is that if we go into this portion of the query plan, this is in the properties tab, because we got an actual execution plan, we can see the compile and the runtime value here, and notice that this query was compiled with a cardinality estimate for null, however, it was run with a cardinality, well, not with a cardinality, it was run with the requirement to return everything.

Everything greater than 2013-1201, which is quite a discrepancy in rows, isn’t it? Sure is. Sure is.

So just replacing or overwriting a null with a value in the context of, well, in this context, it is a formal parameter, does not really get you what you want, I don’t think, because you still compiled with a cardinality estimate for null.

That was what your plan was compiled with, despite what it was run with. And if we look at the histogram for the, what do you call it, that we created, the index, we have this one thing here, and we’re basically getting this estimate for the, ah, PowerShell, go away.

I don’t know why. There’s too many button combinations these days. There’s too many hotkeys. It’s getting too damn hot. We basically got this estimate, all right? So that’s not a very good time for us, all right? We’re not enjoying ourselves.

We are not having a good time. Another problem that I see quite often is a little bit more like this, where someone will have, this is an example of someone passing in an integer value that gets added to a time.

So what that usually ends up looking like is two declines. The first one is, you know, we start with the integer value, which is the date of the time when the date of the end date is this one, right?

And the second one is, the second one is the start date, which is the time when the end date is this one. And the third one is the time when the end date is this one, right? And, gee, I hope I didn’t hit a weird button there. That jumped in a strange way that frightened me. I was like, oh, what did you do?

But if we run this store procedure with the local variables in place, and we run these three representative executions of the stored procedure, note the 1, 10, and 5 here, and we will get the same bad cardinality estimate for all of them, right? And this feels like a parameter sniffing thing, and like normally it would be a parameter sniffing thing if there were a parameter, but there’s no parameter within the perimeter, there is just a local variable, so we’re getting the density vector guess, we are not getting a compiled parameter, a sniffed parameter value guess, and that becomes especially incorrect, well I mean they’re all incorrect, right? We got like SQL Server is guessing 8, 6, 9, 7, 0, 8, 0 for all of these, even though we get back far less, so the estimated and actual rows for this are way off because we used these declared variables and we added some time to them.

So, let’s skip over, that doesn’t actually run. What you’re much better off doing, for these cases, is just using the expression itself in the WHERE clause, this is identical to what we had those local, the part that we had those local variables playing in the earlier bit of code, but now when we use the parameters here, SQL Server gets not only a stable guess, but a guess that is, ah, wait a minute, I did that wrong.

What I should have noted, before running those, was this being the old version, and this being the new version, right? So this is the one where we have the local variables, this is the one where we have the expressions embedded in the WHERE clause, and if we come back and look, this one gets the same bad treatment, with the bad cardinality estimate, but this one gets a much more appropriate cardinality estimate because we did not use local variables.

variables we put the expression directly in our where clause. So that is what we want to do and that is what you want to do when you are writing your store procedures. All right it’s all for me. It’s Thursday. It’s the last video of the week which means it’s a long weekend for everyone and I will see you next Tuesday with Office Hours. All right thank you for watching.

Going Further

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

Learn T-SQL With Erik: Date Bucket Difficulties

Posted on by Erik Darling

Learn T-SQL With Erik: Date Bucket Difficulties

Chapters

  • 00:00:00 – Introduction
  • 00:00:33 – Date Bucketing Importance
  • 00:02:21 – SQL Server 2022 Date Bucket Function
  • 00:04:06 – Comparing Old and New Methods
  • 00:05:39 – Date Bucket Function Details
  • 00:08:45 – Sargability Considerations

Full Transcript

Erik Darling here, Darling Data, the best SQL Server consultancy with the most reasonable rates outside of New Zealand. Alright, in this video, we’re going to do some more T-SQL learning, and we’re going to talk about date bucketing. And why is this important? Well, if you work with dates in SQL Server, you might need to do stuff like this.

And if you are using a more modern version of SQL Server, like, say, SQL Server 2022+, you might want to take advantage of the new date bucket function in various ways. And we’ll talk about how we do that. Down in the video description, if you would like to purchase the larger corpus of the Learn T-SQL with Erik material, there is a coupon code down in the video description for $100 off the course price.

Thank you very much for watching, and please consider giving me a thumbs up if you enjoyed this video. And don’t forget to subscribe to my channel, where I’m always happy to answer any questions you may have. I’ll keep you updated on new videos, on other projects, on other projects that I come up with.

And with that, I hope you all have a great day. See you next time. Bye. of my reasonable rates and other areas you might also in your travels through the video description see all sorts of interesting links for free SQL Server performance monitoring which I offer free because I know you ever tried getting someone to pay for something it sucks yeah anyway it’s really good and it’ll help you find and fix your your SQL Server problems which from from what I can see in the world you you need help with so why not do it for free right why not why not do it for free and anyway let’s talk about the old date bucket yo rusty date bucket what am I clicking on down here all right this one okay this should all be fine so SQL Server 2022 introduced a function called date bucket where if we wanted to bucket time let’s say a six-hour increment okay so let’s say a six-hour increment and if we wanted to bucket time let’s say a six-hour increment and if we wanted to bucket time let’s say a six-hour increment I chose the month of October because October was one of my my favorite months and the whole calendar you would just simply have to do this date bucket our six and then whatever column you want to break down into buckets of six hours before the date bucket function date bucketing involved a lot of stuff like adding hours to the date diff between hours and something divided by six times six and I mean that just rounds out the date diff. Down here I have a couple other things that I’m doing like me getting all the hours in October with the generate series function and then me getting all the days in October with the generate series function here. But if we highlight this entire thing when we run it you’ll see that both of those things return equivalent results and it works out pretty well but one of them is much simpler than the other. So what we’re looking at here is well date buckets so we finally got to the first six hour bucket and then the 12 hour bucket and then the 18 hour bucket and then the 20 well the 24 hour bucket is down there. Well there’s there is no 20 I guess there is not a 24 hour bucket. 24 hour bucket people.

But it’s just a much simpler way of doing the same thing which is much harder and more mathematically annoying in older versions of SQL Server. As was doing this in older versions of SQL Server generate series truly makes life easier in many ways. But just like the date trunk function that we looked at date bucket returns a dynamic type whatever print whatever thing you pass in is what you get out. So you do have to be careful with you know various things that you’re going to get out of it. So if you’re going to get out of it you’re going to have to be careful with things that you might do with this in making sure that those things can compare well to you know columns in your database.

So like I was able to get a variety of different data types to return like date time to date time small date time date time and date time offset. All coming back from the date bucket function all via the magic of go away SQL prompt all the via the magic of SP describe first result set which tells you which gives you a which gives you a which gives you a description of your result set but only the first one. There’s no SP describe second result set or end result sets unfortunately. Date bucket is of course wonderful for grouping things like that. But you know just like any other function if you if you wrap it around a column you know all of a sudden that column sargability starts having some some wild difficulties. So we do have to consider that. So we’ve created an index on the the creation date column in the comments table right with our our favorite index creation options sort and temp DB and data compression if I were in a more highly concurrent environment I might consider other things like online equals on I might even consider if this was a really really big table I might even say max stop equals zero so that I can read from my source data structure with as many as many dops as I can. Right.

That’s that’s that’s a good idea. That’s how we live over here. We live efficiently.

Maybe not. I don’t know. I think we might also live deliciously sometimes. Depends on the day of the week. But, you know, just like what you would expect with any other column wrapped in a function, we get a scan of our index, which, you know, we would probably not be happy with if we cared a lot about performance.

But what’s also strange here, too, is that SQL Server estimates one row very reliably for a date bucket. So, you know, you might be careful about that as well, especially if you’re returning far more than one row. You might be unhappy with the one row estimate.

It might bring you back to the bad old days of table variables and off histogram values, stuff like that. But, yeah, anyway, I had a point with all that. Let’s do this.

So, usually, like any other date math thing, you really want to not wrap your column in the date bucket function. You really want to wrap whatever expression in the function and compare to that instead. Life is generally kinder to you, especially in SQL Server world, when you stop wrapping your columns in the…

These presentation layer functions and you start wrapping your expressions in presentation layer functions. Because that’s a scalar one-time thing, whereas when you run those functions against your columns, it’s every single row that has to pass through that function, which is far less kind to SQL Server.

Also, the storage engine cannot do anything with those functions. Those functions have to get passed up into the expression service. And the expression service is not merely an efficient mechanism for filtering, but it’s a storage engine.

It’s crazy once you start thinking about these things, isn’t it? It’s wild. You’ll see the same thing if you start working with variables in this whole mess.

But local variables, even wrapping local variables in expressions, will still get you the sort of, you know, same local variable weird cardinality estimates, the density vector…

Should we call it a guess? Should we call it an estimate? I don’t know. I don’t know. Call it whatever you feel like. Call it guesstimate. Land somewhere in the middle. Be neutral, you Swiss.

All right? But just like with any other situation, option recompile does tend to help things out a bit with local variables at the cost of…

The parameter embedding optimization is one of the primary benefits of option recompile. Maybe even the primary benefit of option recompile. Maybe even the primary benefit of option recompile.

Does get us back to a much more on-the-nose cardinality estimate. So, date bucket, date trunk, just like any other functions in SQL Server, don’t wrap them around your where or join clause columns, all right?

If you’re using local variables with them, beware, all right? It’s better to use literal values or option recompile. So, all that stuff applies as normal.

Now, one other stuff that’s worth talking about is… We have option recompile here. There’s not even a good reason for it because there’s no local variables or parameters that might be sensitive here, right?

But notice that SQL Server does not do a particularly good job of estimating how many rows might be compressed down once date bucketed, right? I’m pretty sure that…

Well, that’s a number right there, right? And that’s a number right there. And, well, I mean, that continues to be a number, but it’s a very wrong number, right? Not a very good estimate out of SQL Server on that one.

And that holds up, you know, pretty well across all the various cardinality estimator versions, right? We’re on this one. What does SQL Server guess?

Well, it’s a little bit better, right? We get at least somewhere near reality that we do. We do double jump. We do double aggregate that one.

It’s a little quirky. Anyway, this stuff isn’t that interesting. It all kind of stays the same. Anyway, date bucket, neither cardinality estimator reasons with it very well. You know, grouping by it, you might have a tough time in your query plans downstream with those cardinality estimates.

New or lost. Legacy cardinality estimator. The grouping estimates on that are kind of a mess. I mean, cardinality estimates for group by are never been great.

They’ve never been awesome. But with this function, this actually kind of reminds me, I want to go back and look at date trunk now. With this function in particular, it does a not great job.

So beware out there with your grouping by date buckets. You might have some problems that would require the services of a young, handsome consultant with reasonable rates to assist you.

All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk a little bit more about local variables and the presence of various date maths and filtering.

So I will see you for that joyous affair. All right. Goodbye.

Going Further

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

SQL Server Performance Office Hours Episode 70

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 70


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with DarlingNada, and I do apologize if this video is not my most illustrious effort, the air quality in New York today, it is like breathing dirt, and my insides are burning here, so I don’t feel great, but I’m going to see how this goes anyway, because we’ve got to do office hours, because that’s what we do all the time. On Tuesdays, we love Tuesdays, don’t we? Down in the video description, if you would like to ask me office hours questions with your whole hand, you can do that, there’s a link down below, well that’ll work, let me move over a little bit this way, I think I moved my camera or something, no, I just lose a little finger.

You can ask me office hours, there’s a link down below, right in there, somewhere. You can also find ways to interact with me that will cost you money. Like hiring me for consulting, or purchasing my training, or becoming a, what do you call it, supporting member of the channel, there we are, like PBS, right?

I’m not sending you a tote bag, though. You can also, if you don’t feel like directly interacting with me in any one of those numerous ways, which will sadden and depress me, and I don’t understand why you’d want to hurt me in that way, you can also do stuff like like, subscribe, tell a friend. We are around the 85.

500 subscriber mark, which puts me on course to maybe break 10,000 and finally surpass that damned Amiga repair channel once and for all, so, you know, if you can do that, if you’ve got burner accounts, that’s fine, too. Also, down in the video description, you will find a link to my free open source SQL Server performance monitoring tool. That’s right, free open source SQL Server.

Server performance monitoring tool does all the stuff the big boys do, slightly different ways, of course, because I’m like the engineering team is me, the product team is me. That’s why I’m a monitoring tool mogul, though, can’t beat that, but I just released version 3.0. It’s got a lot of neat new stuff in it.

I’ll probably do a video talking about that at some point, but I got to talk about this Learn T-SQL stuff and do office hours for a little bit, so we’ll come back to that. But, yeah, it’s time to answer some questions, so we’ll go over to our Excel file. Maybe we’ll pop in our monitoring tool for a minute here, and we’ll just take a quick gander at the joy and the beauty of my monitoring tool.

Look at all these wonderful charts and graphs. Now we got queries, and there we go. I’ve been trying to do some work on the UI to make it a little snappier.

So now the clicks are a lot faster. Sometimes it takes a second to draw the graphs, so hopefully that’s a reasonable trade-off for most people. There’s nothing in these graphs, because this is the oh crap section.

You don’t want to see charts and graphs in there. You want those to be empty, because that means nothing bad happened. Anyway, oh yeah, Excel, that’s where the questions are.

All right, here we go. May I hear a story? Oh, why is that dot so big? Let’s shrink that down a little bit. There we are. That’s a little bit.

That’s more reasonable. There we go. May I hear a story of when you have saved the day by removing merge? Yeah, save the day by removing merge. I don’t know.

I mean, I’ve seen merge in all sorts of places where it didn’t belong. Mostly in very large ETL processes where it was just really bogging and dragging them down. And yeah, just switching those to use stock and standard insert update patterns.

It’s made a big difference. I don’t know. It’s like any other query tuning thing. It’s like, you hear a story about how you save the day by adding an index?

Ah, sure. I had an index and the query went from taking four days to four seconds. It was quite a time. I don’t know.

It’s not my most exciting moments. All right. Hey, Eric. Hey, you.

How are you doing? What are your thoughts on HTAP? Are there any solutions out there that run Transact? Transactional and analytical workloads equally well? Yeah, you know, I don’t do a lot of work with like the databases specifically in that space.

I’ve heard good things about TIDB, if that’s even how you say it. I’ve heard good things about single store. A lot of people really want to love ClickHouse, but I don’t know if they actually do love ClickHouse.

Personally, I think SQL Server does a pretty good job with both, you know. We’ve got probably a bestseller. We’ve got a best-in-class relational engine for OLTP stuff.

And, you know, we’ve got column stores and whatnot for the other stuff. So, depending on, you know, what you need to do specifically, SQL Server might be a good choice for you. Which I assume you’re using since you’re asking me.

So, why not prop up SQL Server a little bit? It was crazy. I think like MSBuild didn’t have anything about SQL Server. It was all fabric.

And, I don’t know, AI and stuff. So, that whole SQL Server 2025 from ground to cloud to fire is skipped over SQL Server, apparently. I don’t know.

Anyway. That’s a question about performance monitor. I am using performance monitor to have configured alerts to the UI on my laptop. The alerts work, but only when my laptop is connected to the network. Yeah, that sounds about right.

I’d like to set this up in a more reliable 24-7 manner. Ideally, on a dedicated VM. So, Teams. Yeah, so, right now, yeah, that is the recommended approach would be to use a dedicated VM with auto logon and launch. That is the best I can do right now.

Long-term architecturally, my plan is to get rid of what I refer to as the full dashboard. That’s the one that creates a database on a SQL Server and uses store procedures. And, what not, to log things to that database.

Long-term, my plan is to maintain a truly light version of the light dashboard and then make the light dashboard run a Windows service type thing. So, it would be headless, and it would be running in the background and doing stuff. But, that is a pretty big architectural shift for me.

And, just being one person who is pretty busy. I don’t know exactly when that’s going to happen, but that is my longer-term plan for this. So, right now, it is a bit clunkier than I want with the light one.

The full dashboard would provide you with that. It doesn’t require all the other stuff since it’s running normally. But, yeah, that would be the way to do it.

How do you tell if temp tables are helping or hurting overall system performance? Well… Yeah.

I mean, I suppose the most obvious thing would be to look for tempdb contention and make sure that it is properly ascribed to the use of temp tables. That would probably be the most obvious one for me.

If you are rewriting store procedures to use temp tables, and you are noticing that your store procedures generally get better faster and stuff, at least when you run the F5 and run them in isolation.

Then you would see other metrics potentially go down, like the annoying ones, like the queries don’t take as long, their duration goes down. Maybe they don’t use as much CPU or something like that.

Maybe they get better execution plans or whatever. And so metrics around stuff that queries use, aside from tempdb, would improve. You might see tempdb usage go up, of course.

Right? Right. I mean, temp tables are an ephemeral part of the workload, and their rather rapid creation and destruction is expected. I would say that I would keep an eye on the usual tempdb contention suspects, the page latch, ex and up weights, maybe even sh occasionally.

But for the most part, yeah, I think that’s really what I’d keep an eye on, is like weight stats there. Monitor for typical tempdb contention. Monitoring for tempdb usage would be useless there, because if you’re using more temp tables, then obviously tempdb allocations and stuff would go up.

All right. Are there any real workloads where table variables actually make sense? Yes, but they are quite rare.

I think you would have to be putting data into temp tables in a context where a parallel execution plan, because remember, the two things table variables have that are downsides when compared to temp tables is there are no parallel execution plans when you’re modifying a table variable, and table variables do not maintain statistical histogram information about the data that lives in the columns.

The best you can get is table cardinality, even when you index a table variable. So the context would have to be the data load into the table variable. Right?

It would not, the speed of that would not have to be dependent or reliant in any way on a parallel execution plan. And that table variable would not be used in a way relationally where the lack of statistical information would result in a subpar query plan.

So that pretty much means that as soon as you start joining table variables off to other tables where, like, things like histogram comparisons. Comparisons to make join cardinality estimates, or even knowing what data lives in the table variable to filter on it and then do some other stuff.

As soon as you start doing that, table variables tend to become a bad choice. Of course, there are all sorts of edge cases, and maybe sometimes SQL Server comes up with a better query plan.

But for the most part, you’re just not going to see that play out. I’m sure that you can find some example of that. In the greater world, and you can say, Eric, you’re so wrong, but I admit that they exist. I’m just saying that they are not the majority of the cases.

All right. I’m going to go spray some more stuff up my nose, and that is not a drug reference. Well, I guess oxymetazoline is a drug, but it is not a banned substance. So thank you for watching.

I hope you enjoyed yourselves. I hope you learned something, and I will see you in tomorrow’s video where we will continue learning T-SQL with some guy named Eric. All right.

Thank you for watching.

Going Further

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

Meet Darling: Free, Headless Fleet Performance Monitoring for SQL Server

Posted on by Erik Darling

Meet Darling: Free, Headless Fleet Performance Monitoring for SQL Server

Watching one SQL Server is easy. Watching fifty is where monitoring vendors smell blood. The price is per server, per year, and it climbs every time your environment grows. Your reward for paying it: your performance data gets shipped to somebody else’s cloud, where you look at it through dashboards built by people who’ve never tuned a query in their lives.

Darling is my answer to that. It’s the new flagship edition of my free, open source SQL Server Performance Monitor. One SQL Server or five hundred, one product, no per-server tax, and your data never leaves your network.

What Darling is

Darling is a headless Windows service. You install it on one monitoring host, point it at your servers, and it collects around the clock. Nobody has to be logged in. Nothing gets installed on the monitored servers for its own storage.

It brings its own database. The installer bootstraps a managed PostgreSQL instance with TimescaleDB and runs it for you. There’s no repository server to stand up, no schema to deploy, no extra license to buy. Darling replaces the old SQL-Server-backed Dashboard edition, which needed a SQL Server of its own to store what it collected.

What it collects

36 collectors, the same shared library Lite uses: wait stats, query stats from the plan cache, Query Store, active query snapshots, blocking, deadlock graphs, execution plans, tempdb, memory grants and clerks, file IO latency, CPU, Agent jobs, server configuration, and more. Deltas are computed for you, so you see the work done between snapshots instead of staring at cumulative counters.

The store is yours

Everything lands in a PostgreSQL store you own. Not an API. Not an export wizard. Not a vendor data lake. Point any SQL client at it and query.

— Top waits across the fleet, last 24 hours
SELECT server_name, wait_type, sum(wait_time_ms) AS total_ms
FROM collect.wait_stats
WHERE collection_time >= now() – interval ‘1 day’
GROUP BY server_name, wait_type
ORDER BY total_ms DESC LIMIT 10;

Views are included for the common questions, but you’re not limited to them. It’s just tables. Join them however you want, feed Power BI, export to Excel.

Alerts without a babysitter

A real-time alert engine runs continuously: blocking, deadlocks, poison waits, long-running queries, tempdb space, long-running Agent jobs, high CPU, and servers that stop answering. Since Darling is headless, alerts go out by email and webhook (Slack, Teams, or any endpoint you point it at). Emails carry the query text, blocking chains, and deadlock XML. When a condition clears, it tells you that too. For the alerts that cry wolf, there are mute rules by server, metric, database, query, wait type, or job, with optional expiration.

An MCP server that can do things

Both editions ship a built-in MCP server, so an AI assistant like Claude can read your performance data directly. Darling’s can also write. An agent can build Custom Views, tune alert thresholds and mute rules, and onboard an entire fleet, all over MCP. Standing up monitoring for twenty servers is a sentence, not an afternoon.

Watch it from a browser

An optional read-only web dashboard shows the fleet from any browser, no install. It’s off by default and binds loopback-only until you deliberately expose it, token-gated and scoped to the network range you allow.

Custom Views and notebooks

This is the part the desktop app never had. Compose your own views over the collected data: pick the metrics, filters, grouping, and charts, or build notebook pages that mix charts with commentary. Make them by hand, or have an AI make them for you over MCP.

How to get it

Download the Darling zip, run the scripted install from an elevated prompt, and it bootstraps the Postgres store and starts the service. Add servers by pasting a list into the viewer, or over MCP. SQL Server 2016 through 2025, Azure SQL Managed Instance, Azure SQL Database, and AWS RDS are supported. Every release is signed.

Download Darling

There’s no paid version, and no locked features. If your compliance team needs a vendor agreement and a support contact on file before anything touches production, there’s a support subscription for that. The software is identical either way.

And Lite is not going anywhere

Lite, the desktop app with its local DuckDB store, is still here and still supported. Same 36 collectors, same shared brain. Darling is the answer for fleets and headless monitoring. Lite is the answer for the one machine you’re sitting in front of. Pick the one that matches your day.

Going Further

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

Learn T-SQL With Erik: Don’t Be Slack With Data Types

Posted on by Erik Darling

Learn T-SQL With Erik: Don’t Be Slack With Data Types

Chapters

  • 00:00:00 – Introduction to Data Type Mismatch Issues
  • 00:02:45 – Using Date Functions with Incorrect Data Types
  • 00:06:27 – Plan Shape Catastrophe Example
  • 00:10:38 – Martin Smith’s VARCAR50 Demo
  • 00:11:29 – Conclusion and Next Steps

Full Transcript

Erik, big deal darling here with Darling Data and today’s video we are going to talk about how you should not be slack with data types and by that I mean you should always very carefully match your data types. It can be important both for performance and logical correctness when you write your queries to do this. And we are going to look at some examples around date time and date time 2 and stuff like that.

So with that out of the way, if you like this material, it is available as a whole video course and there is a link down in the video description where you can pick it up today at this very second. It is just available to you for $100 off down below. There are also other helpful links there if you would like to engage with me in other ways.

You can hire me for consulting. You can become a supporting member of the channel for $4 to $10 a month. It is a heck of a way to say, here is a little tip jar.

Say, thanks Erik for all the free stuff. You can ask me office hours questions. Keep that gravy train rolling. And of course, I always do appreciate as the channel grows. So if you would not mind doing some level of liking, subscribing, and telling a friend, I would be momentarily grateful for you.

Not eternally. Just… Just a couple of seconds.

Hey, look, the number went up. That is cool. Back to work. If you would like a free SQL Server performance monitoring tool, I have got one. I have been working on it for, oh, I guess, six, seven months now.

So things are maturing pretty nicely. Certainly competitive with all the paid T-SQL, SQL Server monitoring tools out there in the world. And of course, the price tag on mine is way better.

So, you know, if you are curious, you can go download it and start testing it out. And of course, if you run into any issues, have any questions, have ideas that you would like to see in the monitoring tool, just throw them up on GitHub. And my robot companions and I will respond just as quickly as we can.

They do not sleep, but I do. But anyway, let us continue our voyage through the allergic environment. Let us continue our voyage through the allergic heat death hell of June.

And I have got the ACs blaring, absolutely blaring. That is why I am not shiny. All right.

So I have created an index here on the creation date column in the comments table in the Stack Overflow database. And we are going to look at the difference in performance when we are slack with data types versus when we are not. So this is the current sort of method that you would use to flatten dates.

And notice that it, like, so I have to do a little bit of extra work here because I am just using, like, passed in string values. If you were using, like, if you are writing strings, you know, you have to, you should be careful about making sure that your strings are unambiguous and formatted in a way that the SQL Server does not have, there is no guesswork about them. Make sure that we are using the style.

Of convert that we need. 112 for dates. I think it is 121 for date time, date time 2 and stuff like that.

So make sure that you are doing these things because, or sorry, 112. That 112, that 121. There we go.

For that. So we want to make sure that we are doing these things because they help SQL Server make the best possible choices. And they help you from running into weird issues with ambiguous data. If you ever have to internationalize your audience, you will find very quickly that dates become a very murky subject.

And I am not just talking about time zones, but we will talk about time zones later. You have got that to look forward to. Woohoo.

High five. Time zones. Nothing better. Yeah. But using this method of date flattening and converting specifically to date times, we get a nice, tidy, easy seek into our index on the comments table. And all is fairly well with this query.

Now, like I said in the last video, date trunk returns a dynamic data type. So if we do not convert this from what is obviously a date time 2 based on the number of milliseconds that we have here, SQL Server will return it as a date time 2. And when we start comparing date time 2s to date time columns, the performance does get a little bit worse here.

This isn’t like the end of the world. But notice that this plan does look a little funny. All right.

It is a constant scan. We have got a compute scalar. And then we go into a nested loops join this many times to go find the rows that we care about. This is because we are being slack with our data types. We lose that nice, tidy seek plan and we get this plan with all this extra stuff to it.

We are essentially creating a row set and joining that over and over again. That is not fun. That is not the kind of execution plan you want to see.

But if we are taking a look at the data types, we get a nice, tidy, easy plan. We are taking advantage of SQL Server 2022. Like brand-new SQL Server 2025. I am actually using 2025 at this point.

I think it is finally enough cumulative updates in where I feel pretty safe running demos and everything on it. But if we run this, what we are going to hit is, of course, or rather if we run this, we will see we will go back to our nice, tidy seek plan because we are converting to a date time up here. Duh.

Date time. Good for us. And we can at least get back to the seek plan that we wanted before. So that is exactly what we want to see. Similar caution does need to be shown when assembling a date time or date time to from parts.

You have a variety of functions at your disposal to assemble a date from parts. You have date from parts, date time from parts, and date time to from parts. And if you are just throwing some strings into those, things can get rather perilous for your queries.

Just a couple of examples here. If I run these, this is the first one that is using date from parts. And we are back to this sort of weird plan with the constant scan compute scaleR and the nested loops join over to here.

I mean, it takes like 300 milliseconds, which again, this is not the end of the world. This is not supposed to show you a drastic performance change. But it is there to show you the plan shape and what you want to look out for in your queries when you want to get things right.

Notice down here, when we use date time from parts, we are back to our simple seek plan. We get a parallel plan from this, which is good given the number of rows that we are hitting. You can sometimes get parallelism with these, but the optimizer support for it is not so great.

But one thing that I want to show you is this plan, which is a real catastrophe. Right? We are going to, what I want to show you is what SQL Server is kind of doing when you mismatch data types badly, especially dates.

Right? So this is sort of the plan shape that I warned you about before, where you’ve got constant scan, compute scaleR, merge interval, and then a nested loops join to go find stuff. Right?

And this is because down in here, we created our table with a date time data type for the column. Right? We converted that column to a date. And then we asked where it was between a date and a date time 2, 7.

So SQL Server does have all sorts of stuff to do. If you open up the plan XML, this stuff doesn’t show up. This stuff doesn’t show up if you just look at the query plan.

But you’ll see stuff like this in query plans where SQL Server has to do extra work, get range through convert, get range with mismatched types. These are optimizer rules that SQL Server has built in. To try and help you or try to help queries that use mismatched data types do the right thing.

You can see where SQL Server is converting stuff and all that. So it’s extra effort for the optimizer to have to deal with your queries. This is a very interesting problem that my friend Martin Smith ran into with strings.

If you go to this link, you’ll be able to see the issue that Martin opened up here. Martin Smith, very smart fella. Incredible with SQL Server stuff.

One of my absolute heroes. And what he found was a very interesting problem where we have a VARCAR50 column collated like so. It is nullable.

And then what we would do is insert 20 null values into them. Get a count from the table. And then we would select another count from the table. And we would say where problem child equals this or problem child is null.

Now, this one is actually a little bit perilous because I verified this on SQL 22. But I said, I just started using SQL Server 2025. If it doesn’t repro here, good job, Microsoft.

If it does, you stink. Just kidding. You’re busy. You got a lot of fabric weaving to do. So let’s see if this thing still repros on SQL Server 2025. Let’s see.

It does. So we get so when we do a regular count from the table, we return the 20 rows, right? Because, I mean, just like you can see, I am very much limiting this to 20 rows going in, right?

Top 20. Generate series 1 through 20. When we do a count from the table, SQL Server correctly says there are 20 rows in there. But when we say where problem child equals, we have this Unicode string in there, right?

So we have an implicit conversion. Or it’s null. SQL Server counts 40 rows instead of 20 rows. Very interesting stuff.

And, of course, the execution plan looks just like some of the other execution. Well, I guess there’s an extra operator in there where it looks a lot like a lot of the other query plans I’ve shown you with this sort of weird constant scan concatenation top end merge interval situation over here. And then we have…

I should have highlighted the select so it stayed where it was supposed to be. And then over here we have our dynamic seek where SQL Server is doing an implicit conversion on our VARCHAR column. And it’s saying, is it this or is it null, right?

And, of course, we get back twice as many rows as actually exist in the table. So thank you, Martin Smith, for that wonderful demo. High five from wherever you are in the world.

I believe, according to his Stack Exchange profile, he lives in Rugby, England. Hopefully that’s not Dachshund. I never want to give away too much information. But thank you, Martin.

You are a wonderful, smart, brilliant human being. And I don’t know. I wish we got to spend more time together. You may probably don’t feel the same way. I understand. But I think you’re great.

Anyway, that’s enough for today. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you next week on Tuesday for Office Hours. All right.

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.

Learn T-SQL With Erik: DATEDIFF Considerations

Posted on by Erik Darling

Learn T-SQL With Erik: DATEDIFF Considerations

Chapters

Full Transcript

Erik Darling here, with Darling Data, trying to enunciate so the robots stop transcribing my name, and stuff all weird. Stupid robots. I probably shouldn’t say that, they’re gonna transcribe that and get mad at me. Robot, if you can hear me, I apologize.

Anyway, in today’s video we are going to continue our learning journey through the T-SQL language. That’s how we talk to our SQL servers. And we’re going to talk about some considerations around date diff that I think are interesting.

This is of course just small snippets of the full course material, so if you are interested in going beyond what I’m talking about in these videos, I would encourage you to go down to the links below, where you will find a link to buy this entire course for $100 off the manufacturer suggested retail price. On your way there, all sorts of other great links.

You can hire me for consulting, you can become a supporting member of the channel for anywhere between $4 and $10 a month, if you feel so inclined. You can ask me office hours questions. And of course, another thing that I would encourage you to do.

Actually, I have two things that I would encourage you to do. One of them is of course to like, subscribe, and tell a friend. And the other one is to check out my free SQL Server performance monitoring tool. It’s all the stuff that I think is important to monitor for performance in SQL Server.

And it is all stuff that I think is pretty well thoughtfully laid out. I am working on some cool new features now that you should see bubbling up. And it’s just a real good time and a very fulfilling process building a popular community tool.

I’m over 11, close to 12,000 downloads at this point. So I’m pretty excited about that. And with all that stuff out of the way, let’s continue our summer journey here.

And let’s, of course, you know, what do you call it there? . Let’s talk about T-SQL.

That sounds like a good idea to me. All right. We’re going to go to Management Studio. And let me just do a little bit of cleanup over yonder here. So working with dates and times, if you’ve ever had a weird experience, like maybe you haven’t, you know, used the right matching data types, and you’ve had a performance issue, or you’ve hit weird bugs, there’s all sorts of stuff that should rightfully strike fear into the very hearts, minds, and souls of developers when they’re working with these things.

So we’re going to talk about just some not terribly advanced stuff, but stuff that is at least worth making sure that everyone understands when it comes to the date diff function.

There’s not terribly a lot of advanced things to say about it, but who knows where you’re starting off. So one thing that seems to get on some people’s nerves is deciding on a boundary.

So when you say, I only care about a year of data, you need to think carefully about how you do that. All right.

Do you just say date diff year minus 1? Do you say date diff month minus 12? Do you say date diff day minus 365? These things can all measure slightly different things. Depending on where you measure your boundaries.

Not so much for dates, of course, but if you have date times or date time twos, more precise data types than just a date, these things become quite important to think about.

You might even figure out how many seconds are in a year and measure that precisely if you need an absolute year from when your query runs. These are things that not a lot of people consider.

This stuff all becomes… I think much more interesting when you’re dealing with somewhat narrower spans of time, like is the last day of data just day minus 1? Is it 24 hours minus 1?

Is it however many minutes or seconds minus those numbers? You have to think about this stuff. Then I think one thing that is somewhat excruciating about date diff is just, I mean, it’s not like you weren’t warned, but if you look at these two dates, we have 2025, 1231, at 2359, 59, a bunch of 9s, and then we have 2026, 0101, and a bunch of 0s.

If we measure this very last moment of 2025 and compare it to the very first moment of 2026, there’s a whole lot of date diff boundaries that will be true and will come back with a 1.

If we… Let’s see. Let’s blow this up a little bit. The date diff between 2025, 1231, well, it says that’s a year apart, which technically it is.

We crossed a year boundary. It’s also one month apart. We crossed one month boundary. It’s also one day, one hour, one minute, one second, one millisecond, and one microsecond apart because we have crossed all of those boundaries, but the difference between all of them is, of course, 1.

And this is just stuff that you need to sort of be aware of. When you are using date diff to look at the differences between two things, the results can get a bit surprising to some people.

So you should always think quite carefully about which chasm you are attempting to span when you are date diffing.

The other thing that you can do that becomes interesting with both date add and date diff is flattening dates.

Again, not terribly advanced stuff, but there’s been cheat sheets and all sorts of stuff throughout the years published where people will tell you all different ways to figure out how to add some span of time to make sure that everything is working.

But SQL Server 2022, they put in this function called date trunk. And date trunk, look how nice and compact this is, right? We’re just going to truncate this to the last year, which beats the pants out of all of us.

All the other code that you would have to write in order to do this in the past. You would have to add a year to the date diff between the year and 1900 or 101, and then all this code, right?

It’s a lot of stuff. But the good news is date trunk makes life a lot easier, at least for truncating back to a date, right? So both of those pieces of code, this being far more succinct, give us the same thing.

We just go back to the first of the year of 2020. 2026. But getting to the end of a thing is a bit more challenging, right? So I actually opened up a support issue saying that it would be cool if date trunk accepted a third parameter where you could add whatever span of time you wanted to this.

So like, if this were date trunk year at test date time comma one, then we would add one year to test date time and go forward a year. We don’t have that currently. We may never have that because apparently fabric is more important than SQL Server.

But it is there and it does work pretty well. You can flatten with date trunk down to all sorts of wonderful things. But if you want to add time onto that, you still need to do a little bit of extra annoying stuff.

So what I’m doing here is adding one year to date trunk. Like if we had that third parameter, we could skip all this stuff and we could just say add one year to it and truncate to that, which would be so much more nice and compact.

But one thing that occurs to me is I don’t know that the people who make SQL Server actually use SQL Server. You got to keep bothering them for things.

You got to keep making feature requests and all sorts of other annoying stuff. But just to be extra precise here, 50 nanoseconds just rounds to 100 nanoseconds, right? 100 nanoseconds is the smallest unit of sort of measure that you can get out of these things.

But if you do this, then we will get us to our one year thing, right? So this will get us to the very end of 2026, 1231, right? We could have just added a year or something.

But if you just wanted to get to the very last moment of 2026, that’s how you would do it. If you just wanted to get to the first of 2027, well, of course, that’s a little bit less math, which is always a good thing.

More math and more problems, right? But you still need to think about the chasms of time that you care about. So three months, 12 weeks, 90 days, like what actual span of time do you care about?

It’s an important question because you might be either including data that you shouldn’t or not including data that you should. And these are important considerations when you are measuring times with these boundaries.

So if I run these queries, or I guess this is one query with just a bunch of things selected in it, we have what is right now, right?

So this will bring us back to the first of June, which is just about a week ago now, at least when I’m recording this. When this gets published is, of course, in the future. We have the right now date, which is how you would do this if you just wanted to convert this from a date time with all the stuff in it, or I guess that’s a date time too, technically, to just this is a date.

You can get to the end of the month with the EOMONTH function. Pretty handy thing there. We don’t have any other functions, right? It says EOMONTH. There’s no EODay, EOYear, right?

Can we just get to the end of the month? Sure. So this is the reason why I think that datetrunk should accept a third parameter is because EOMONTH does.

This is an example of adding three months to EOMONTH, and this is an example of subtracting three months from EOMONTH, and this just gets us some slightly different things. So the end of three months into the future would be September 30th, and the end of three months ago would be the end of March, right?

So EOMONTH has this neat third input that you can use, but datetrunk does not. So it would be nice if datetrunk did, right? That’d be good stuff.

Datetrunk returns a dynamic data type. So you do have to be careful with how you’re doing this, because if you put in a system function like sysdatetime that returns a datetime2 and you compare that to a datetime column, you may find yourself in rather awkward situations, both with data correctness, of course, and with, you know, like query performance can also be impacted, the query plans that SQL Server comes up with, the little optimizer rules like getRangeThroughConvert and getRangeThroughMismatchType and stuff, they often result in very awkward query plans where you have these, like, these constant scans and these merge things and then like an awful little nested loop into your table billions and billions of times.

It’s not fun. So we have to be very precise with data types. But we can use this describeFirstResultSet procedure, and what we’ll see is that in the first one where I am, where my input value here is a datetime2 and here where I’m converting that, or rather I’m converting this string to a datetime27 and this one where I’m converting it to a datetime, SQL Server will respect whatever you convert it to.

So that first one comes back as a datetime2 and the second one comes back as a datetime. So just be very, very careful because that is a dynamic data type. You can’t control it, but it is dynamic based on what goes into it.

Anyway, not a lot of fireworks in this one, but some good stuff to think about if you haven’t spent enough time thinking about working with dates. We’re going to look at some more interesting stuff tomorrow.

This is just, you know, some things that you have to say to clear the air first. All right. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I hope you will think quite carefully about what chasms of time you are hoping to span when you start date diffing and date adding. And I will see you over there. I will see you over in tomorrow’s video.

All right. Thank you. Goodbye.

Going Further

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

SQL Server Performance Office Hours Episode 69

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 69


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here, with Darling Data, for another exciting episode of Office Hours, and that is where I wake up early in the morning and I answer five user-submitted questions, and if you want to ask your own question, you can find a link to do that down in the video description, that’s where that lives. There’s a link there with Office Hours, right in the words, so it’s very easy to figure out where to go to ask a question. On your way to find that link, it’s not surprising, you will find all sorts of other helpful links, where you can hire me for consulting, you can purchase my training videos, which are arguably the best SQL Server training on the internet.

You can support this YouTube channel with as few as $4 a month. I believe you could also do up to $10 a month, if you’re feeling particularly generous. Perhaps you’ve gotten lucky with the lottery, a relative has died, something along those lines, and you’re just feeling like spreading the joy.

And of course, if you don’t feel like doing any of those things, or you are not so monetarily inclined towards me, then you can, of course, just do the usual liking, subscribing, and telling of friends. If you would like a free trial of Office Hours, I’d be happy to help you.

you can go get it and you can start monitoring the performance of your sql servers uh in a way that is just as good as what all the paid tools do i’m adding some very exciting stuff to that right now it’ll be out in the next release um hoping that’ll be this week but we have to we have to tidy some things up first have to make sure that everything is working correctly before we go and do that but uh anyway um i’m skipping the the the uh the speaking promo slide because at this point i have nothing for several months so uh if something new comes up who knows right if some exciting opportunity arises uh then by gosh i will go ahead and do that but uh for now we’re just gonna just gonna admire our strange databases going crazy in a field with allergies anyway that’s enough of that uh we need to go to the excel file and we need to uh talk about this um does the option for in-memory tempdb make sorts faster i’ve never seen that claim before well sort sorts don’t always use tempdb if they if they spill the tempdb i suppose it won’t make the spill any faster but uh it used to be on older versions of sql server you could run into tempdb contention from lots of queries running at the same time and spilling so at least that goes away but uh no no that that that doesn’t do anything i forgot to highlight that was this question here that i was just answering uh let’s see here select uh someone’s being funny ah someone from a foreign land is being funny i see that you in color all right uh select count from eric’s closet at least you spelled my name right where type equals t-dash shirt and color equal color equals black and logo equals ad well i i do not have any t-shirts that are not black and i do not have any t-shirts that do not say adidas on them so uh really it’s just and i keep all my t-shirts in a drawer not in the closet i if i hung these things up it would look insane i send my laundry out get it folded perfect squares and i put it in a drawer and everything works out pretty well um i believe i have somewhere around 20 of these that i i wear it’s at various points wear them everywhere go to the gym wear them all day when i work don’t have to think about anything it’s wonderful wonderful wonderful wonderful eric tell us about you where do you live i live in new york city that one uh what do you like to do in your spare time well uh i have some probably some rather generic interests i i do like uh going to restaurants and i like traveling i like going to museums and hanging out with my family so generically that apart from that the only the only real i think hobby that i have uh is is barbell training um which i’m equally dedicated to uh is as databases at least i hope i am so that that’s that’s about me in a nutshell i’m a rather rather simple rather simple man heard that’s the way to be all right uh oh wait there’s another one what tv shows do you like oh well that’s where things get interesting isn’t it um i spend a lot of time watching um nostalgic television uh from my youth uh like cheers uh like the x-files i recently uh re-watched all of the nanny that was a very good time friend dresser in the 90s that’s a that’s a tough one to top man uh i don’t know uh i think 30 rock is probably about the funniest tv show i’ve ever seen uh community had a pretty good run um uh yeah i don’t know stuff like that you know up my alley i like i like weird sci-fi shows been watching uh widow’s bay lately uh forget what i forget where that’s streaming on but that that’s been that’s been a nice treat that’s been it’s been a good pretty good show i think so far but that’s the type of stuff that i enjoy um i attempted to watch the last season of euphoria but it was some of the worst television that i’ve ever seen in my life so ah i just i just read the spoilers anyway let’s see here uh is it bad to nest exist statements within each other rather than having one big select with lots of inner joins not on the face of it no uh i’m okay with nesting exists i think that’s perfectly okay with me no but i’ve never i’ve never found anything that is pathologically wrong with doing that but of course you know running the query is the real tale of the tape here is if if you are able to nest your exists and and get a good fast query with a reasonable query plan then keep on doing it if you need to switch things around i understand i’ve had to do plenty of switching around in my life uh wouldn’t be the first time wouldn’t be the last time but you know just you know i think the the thing with exists is uh you know they are i think generally a little bit more sensitive to indexing uh especially uh given the the row goals that often get introduced uh with them and the optimizers uh i don’t even know that i’d call it a preference but you know when of course the difference between join and exists is that you know exists only cares if a row is there or not joins find every match in like a one-to-many relationship or a many-to-many relationship so um you know the the row goal that often gets introduced there can can certainly inflict some weird plan stuff um the other thing that i find is that if uh the stuff that you’re checking the existence of if it is uh rare data if it is data that is not regularly occurring in your database you can get uh some some pretty choppy execution plans from all that so uh of course look at your execution plans i mean you have my blessing to try these all right so let’s get started with our try these things you just you just have to do the performance testing yourself unless you choose to hire me in which case i can do that for you oh boy i have a large view about 900 lines it’s pretty large right charles barkley said that’s like one of those san antonio ladies uh with about 60 60 joins that take six seconds to compile you are lucky it only takes six seconds to compile that’s that’s like one second for every 10 joints uh the business logic makes it near impossible to break this into smaller chunks how can i reduce the compile time well um i disagree with the business logic making it near impossible to break this into smaller chunks um even in the case where you need to uh like sort of you have like junction table joins where you have to do things uh it it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s it’s quite possible to break these things down uh the first thing i’ll say is that if if this is what you’re dealing with i would i would like the my first instinct is to say that you have chosen the incorrect vehicle for this logic um that’s that’s a that’s a lot of action for one query unless things are real carefully done really carefully done um if they’re all inner joins you could create an indexed view um if if if they’re not all inner joins you could take whatever inner joins are in there and make an indexed view and you could create an indexed view and you could create an indexed view that would at least reduce some of it you’re going to you’re really going to want to lean on the no expand hint there um you could you could force all the queries that hit it to run with option force order so that sql server does not think about uh the ordering of 60 joins but you would have to very carefully write those joins in the order that sql server that gives sql server the best execution plan for them uh you would have to look at the exit like a fast execution plan for this thing uh you would have to figure out the execution plan for this thing in the exact order that sql server joins the tables together in and then you would have to write your joins in that order you could try that weird bushy join syntax but i don’t know that that’s really going to value much of anything um it’s it’s something to think about but it’s for me even that’s it’s a dicey proposition um i i if i if the first thing that i would do um and and this is this is a good use case for uh the the ais is i would i would probably feed that query to them and and ask them uh how you could if you could break it up to some degree um i do believe that the appropriate vehicle for something like this often is a stored procedure uh where you can dump little bits of things into temp tables and then carry on from there but having never seen your your view i don’t know exactly i don’t know precisely what is possible uh under under the local conditions that you have in your database anyway that is five questions you’re welcome thank you for watching i hope you enjoyed yourselves i hope you learned something and i will see you in tomorrow’s video where we are going to continue uh the learn t-sql with eric material from my course which you can buy from the video description for 100 bucks off uh we’re gonna start talking about dates and date math and time zones and stuff so got a lot to look forward to in there don’t we sure do all right thank you for watching

Going Further

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

Learn T-SQL With Erik: Aligning Queries and Indexes Part 6

Posted on by Erik Darling

Learn T-SQL With Erik: Aligning Queries and Indexes Part 6

Chapters

Full Transcript

Erik Darling here with Darling Data, the one and only, the original monitoring tool mogul, just kidding, that doesn’t make any sense. In today’s video, we are going to finish up talking about the query align and design thing series of videos by talking about missing index requests. Before we get into anything else, this is where I get to share a funny story with you.

In one of my consulting engagements many years ago, I was talking to a developer about indexes. And they said, Erik, I’m going to be honest with you. And I said, this is the first time for everything, isn’t there?

And they said, I don’t really know anything about indexing. I just follow whatever the missing index requests say. And I was like, back then I thought, oh, so you just follow whatever the computer says, and now you don’t know anything about this important thing. And I was like, wow, that’s just like today.

Anyway, it’s a good thing you all got AI ready with me, so now you don’t have that problem. But in today’s video, we’re going to talk about how missing index requests will often leave much to be desired. We’re not going to do all of the content because this is, of course, a video.

It’s a small snippet from the larger Learn T-SQL with Erik course, which if you would like to buy that course, if you think this material might be worth your time and money, aside from these free passing moments on YouTube, you can purchase the course down in the video description.

There’s a coupon code on there for $100 off. I don’t know. Saving $100 is kind of cool. But there are also other links. You can hire me for consulting.

Perhaps you would like me to look at your missing index requests and make fun of them. Or give you better missing index requests, which I can do. I am capable of that. But instead of offloading that to the robots, you would be offloading that to me.

But at least I’m happy to teach you about these things. You can also buy my other training, become a supporting member of the channel for as low as $4 a month. If you just like this material enough to say thanks here and there for $4, you can do that.

You can also ask me office hours questions. And of course, as always, please do like, subscribe, tell a friend. Tell a family member.

I don’t know. If you think this content just absolutely sucks, tell your worst enemy and drag them down into the abyss with you. If you would like a free SQL Server performance monitoring tool, I have one.

Offer one. It is at my GitHub repo. The link is down again in the video description. Absolutely free, open source.

All of the performance monitoring metrics that you would ever care to have in front of you. And give your robot friends. A way to look at, because again, we are offloading everything to the machine. So let the robots figure it out.

And then you, I don’t know, I guess go do stuff. I mean, when you’re watching this, I will be at Data Saturday Croatia like tomorrow basically. So that’s fun.

I think anyway. But I’m not there now. I’m at home now recording this ahead of time so that the content flows while I’m traveling. But after that, I will be home for a bit and then I will be in Seattle in November.

So yeah, again, up in the air on how we’re going to fill that time. We might need to think of some things to do. But now it is time to continue losing our summer minds in this summer heat and talk about missing indexes.

So on missing indexes, generally, if you’re looking just at a query plan, you only see one up at the top. But there might be many. The impact number that you see is an estimate in how much it will reduce another estimate.

And that is a cost. So the impact number is really an estimate of an estimate. It’s a very, very fuzzy number.

Uses is more based on the plan cache than anything else, like actual queries using something. And the thing to keep in mind here is that they are quite opportunistic and they are not very thoughtful. Missing index requests occur as part of the process.

They are part of query optimization. And if you let the optimizer spend a long time thinking about indexes during query optimization, you would probably be unhappy at how long it takes for the optimizer to produce a query plan for you. So the missing index requests are a bit like a dating game where the optimizer is like, well, I really want an index that’s six feet, makes six figures, and has a six pack of Miller Lite Natty Ice.

Steel Reserve. But I don’t know. So it just figures out, well, I have this index, but I would prefer this index.

So it’s almost like a little bit of infidelity there, if you ask me. So you just really don’t want it to take a long time to do that. And if you see a missing index request, the best way to validate it is to run the query that is requesting it, get an actual execution plan, and then look at, if the slow part of the query plan lines up with the index that SQL Server is asking for.

In a lot of examples that I go through in my Stack Overflow database, SQL Server will ask for a missing index on like a scan of the users table, which is only a couple million rows.

And you would go from like, let’s say, 100 milliseconds to like 20 milliseconds. It’s not a big meaningful difference. You want to make sure that that index takes some meaningful chunk of time out of your query by being there.

Because indexes, they need to pay for themselves a little bit. Because once you create an index, now that index is costing you in transaction log space, in space in the buffer pool, in writes, in locking.

So there’s all sorts of costs to having that index, and you want to make sure that that index pays for itself by making queries faster. But there are some issues with missing index requests.

And one of them is that they don’t really understand selectivity at all. What you get from them is key columns based on the WHERE clause. And to some extent, like the other things, but really the key of the missing index request is just like the WHERE clause stuff that you see.

And then like anything else just goes in the includes as a mishmash. It doesn’t matter if you’re joining, sorting by it, grouping by it. It does not, SQL Server does not pay much attention to that.

Equality predicates will always go first in the missing index request, and inequality predicates will always go second. Anything else ends up in the includes. The order of columns within the equality and inequality predicate chunks, or groups, some people might call them, has nothing to do with the current predicates at all.

It just orders them by each column’s ordinal position in the table. There is a great question by a fellow named Brian Reebok, who I haven’t spoken to in a while, but I hope he’s doing well, over here.

If you ever feel like reading it, just click that link on your screen with your forehead. It’s really hard. Just click it with your nose or something, right?

You can do it. But by ordinal position, I mean this. When you create a table, the order that you create, the order that you list off your columns in, SQL Server, that is the ordinal position in the table.

So like in the POST table, these are the ordinal positions. It would make a lot of sense if I just said c.columnId. I typed it right, there we go.

If I did this, then you would see this is the ordinal position. So SQL Server will order the columns in your equality and inequality predicates by this, not by how selective anything is.

So sometimes that might work out in your favor. Because if we run this, answer count of 518, one row has that. POST type ID 1 has 6 million rows.

So if we look at the execution plan, there is indeed a missing index request. And answer count comes first, qualifies for one row. And if we created this index, we would be able to seek right to that answer count and then find the POST type ID associated with it.

So that would be cool. That would be a very efficient seek. Seeking through 6 million rows, maybe a little bit less so. You don’t know, right?

You never can tell. Other times, that might not pay off. For example, if we asked for a POST type, a parent ID of 0, which has 6 million rows, and a POST type ID of 8, which has 8 rows, SQL Server will say, Hey, give me a missing index on parent ID and then POST type ID.

Which is, again, not what we would want, right? Because if we were designing indexes, we would most likely want to have the more selective predicate out in front.

Now, this missing index request, I believe, because we have, again, we have two inequality predicates here, one on creation date and one on score.

So these are going to be grouped together. Before, we had inequality predicates, so those were grouped together. But now, these inequality predicates, this is obviously nonsense. And the reason why I say that is because not a whole lot of POSTs have a score greater than or equal to 25,000.

But every single POST in the POST table is between 2,000, 7,000, 1,000, 1,000, and 2,013, 1,231. At least in my copy of the Stack Overflow database, it is where the world ended on January 1st of 2014.

There are no POSTs beyond that date. So every single POST in this table is between those dates. Almost no POSTs have a score of 25,000 or greater than or equal to, right?

So if we look at the execution plan, we again have a missing index request. But SQL Server has said, No, give it to me on creation date and then score. Well, okay.

Let’s do what SQL Server asks. Let’s add this index on creation date and then score. So that’s what we’re doing. Here we’re also going to include owner user ID so we don’t have to deal with anything else.

I can’t remember if owner user ID was in the includes up there. I didn’t pay that much attention. But with that index in place, if we run these three queries, what we will see are three execution plans.

And none of these take terribly long. But notice that we scan through all of these. They are parallel plans.

And each one, let’s just say, they take close enough to 500 milliseconds across the board. Is this a particularly good strategy? I don’t know. Because we read 17 million rows from the table.

Because our leading key of the index, our initial seek predicate, was everything. And then we have this residual predicate come back to me, my love, where score is greater than or equal to either 5,000 or 10,000 or 25,000.

And all of those numbers are more selective than the entire creation date range in the table. But again, this is stuff we don’t want the optimizer thinking about at run time.

This is not something we’re like, optimizer, but this is a huge date range. We don’t want it thinking about those things. We want it thinking, come up with a good query plan. All right.

So, but that’s where we come in, right? Where we’re going to create, we’re going to reverse that index and we’re going to make one on score and then creation date, right? Because we are going to evaluate our data and our query.

And we’re going to say, I think our predicate on score is way more selective. And we’re going to be right, aren’t we? Are we ever wrong?

No. If we were ever wrong, it would be a terrible demo. Or I don’t know, maybe it’d be a very amusing demo for you. But now when we run these three queries, which are identical to the previous three queries that we ran, we will get much tidier, much more efficient query plans that seek and seek and seek.

And these all take zero milliseconds. Why? Because we sock to a smaller range of data first, and then we applied our range predicate, our larger range predicate second.

So when it comes to missing index requests, there are a lot of things for you to watch out for and be careful for. If anything, don’t take them literally. There are many other, there are many things where, you know, you should maybe not take them literally, but maybe take them as sort of like an instruction or something where you might say, hey, SQL Server is throwing a missing index request.

Perhaps there is something I should pay attention to here. Perhaps it is trying to hint me towards something. What’s that, Lassie? Timmy’s in the well? You go over there and like, you know, I don’t know, maybe Timmy’s in the well.

Maybe he’s, maybe Lassie’s just messing with you because Lassie knows every time she, she barks, you go and look in the well and she’s like, it’s funny to me. Dogs, you know, what are you going to do? Anyway, lots of things to take as a grain of salt with missing index requests.

Again, you may use them as a hint or an indicator that something about your query, the indexing for your query could be improved, but there are many things that you need to be careful of and many things that you should know about indexing before you go and create those missing index requests.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I will see you next Tuesday. I should stop saying that for office hours.

Hopefully I make it home alive from Croatia and all that stuff, you know, air travels. It’s been a little weird lately. But anyway, thank you for watching.

Going Further

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

Learn T-SQL With Erik: Aligning Queries and Indexes Part 5

Posted on by Erik Darling

Learn T-SQL With Erik: Aligning Queries and Indexes Part 5

Chapters

Full Transcript

Erik Darling here, with Darling Data, the best SQL Server consultancy outside of New Zealand, lest we forget, lest we forget the reasonable rates that Darling Data offers in exchange for making your SQL Server faster. Fair trade, believe in that. I think it’s a fair trade. Thankfully a lot of you do too, so that’s nice. But today we are going to get back to learning T-SQL, aligning queries and indexes, because these are good things to do, right?

Make queries faster, make your queries and your indexes line up better so that things happen for you that are good and fast and performance. You’ll get there someday. Anyway, if you want to purchase the full training, remember these are just little dribs and drabs and tidbits of the brilliance of the full course. There’s a link down below where you can purchase the course for a hundred bucks off.

It’s a nice coupon, right? Because I care about you and your well-being, your mental sanity and whatnot. You might have to learn this stuff so that you stop getting outwitted by the robots, right? You get smart enough to challenge the robots when they’re full of malarkey.

You can also hire me for consulting. If you’re interested, I can help you out. If you’re just not down with the robots and you just don’t have time to learn all this stuff on your own, I have crammed my head full of this stuff for like 20 years now.

So I’ve got a lot of things in there and I can do a lot of stuff pretty quickly, right? So that’s cool too. If you’d like to support this channel with money, like four bucks a month, you can do that.

Again, link down below, somewhere down there. I forget. It’s like a subscriber member button thing. If you want to ask me office hours questions.

Like I answered in yesterday’s video. At least I think it was yesterday. It’s hard to tell these days. Everything blurs and blends together. Or if you don’t want to do any of that stuff, maybe you could find it in your heart, your withered, sad, grinch of a heart, to like, subscribe, and tell a friend.

I wasn’t wishing like coronary failure on you. I just want you to like making some jokes. If you want free, this is how much I want you to like me.

This is how desperate I am for friendship and companion. If you want the free SQL Server monitoring, you can get it from your best friend, Erik Darling. You go to the GitHub.

The link is down there. You download it. You point it at SQL Server. It tells you everything you could possibly want to know about the performance of your SQL Server. It is fantastic.

It is a great time. And again, it’s free, right? You don’t need to talk to me. You don’t need to email me. It’s just there.

I will be, well, this week, yeah, in like a few days, magic, at Data Saturday Croatia, where I will be doing my advanced T-SQL pre-con. If they haven’t stopped selling tickets, you should buy a ticket, if not to my pre-con, then maybe to the Data Saturday event.

Maybe we could hang out there, and maybe we’ll be best friends in real life, too. Who knows? We don’t just have to be best friends on YouTube. And then, of course, Past Data Summit in Seattle, Washington, November 9th to 11th, which seems an impossibly far way off, but so far as I can tell, it keeps getting closer.

So that’s cool. But now it is June. We will suffer.

We will suffer summer. That’s also a good band name. Maybe not now. The reason why I’m not a professional band namer, I guess. Anyway, we have this index currently.

It is on the post table, on the columns, owner, user ID, and then creation date. And we’re going to write a query. First, we’re going to turn on actual execution plans because these are important things to have on in life.

When we’re attempting to talk about SQL Server performance, if anyone ever tries to talk to you about performance without turning on query plans, I would be very suspicious of them.

Right? That is a strange thing to do. But this is probably a reasonable way for most people to write a query. When we look at the execution plan, we’ll notice a few things.

One, we got a parallel plan. This thing went parallel, ran at DOP 8. It spilled a wee little bit on this sort.

We went from 72 milliseconds up to 119 milliseconds. 8, 20, 30, 40-ish milliseconds of time in the sort plus the spill. Not the end of the world.

This finishes in 120 milliseconds total. Is this good? Is this bad? Is this ugly? Well, I mean, to me, it depends a bit on a few things, not PowerShell. It does not depend on that.

But it depends on a few things. Right? We get a 406 megabyte memory grant, which in and of itself is not terrible. It’s not a big deal.

But we asked for 400 megs and we still spilled. Maybe I’m not in love with that. And this goes parallel to DOP 8. So in order for this query that returns 1,000 rows, SQL Server’s best idea for executing this was a parallel DOP 8 query with 400 megs of memory behind it.

I have two problems with that. One, it’s not that fast for 1,000 rows. And two, why do we need memory for this at all?

We have an index that has all the data that we care about in order. It’s on owner user ID and creation date descending. And we are looking for owner user IDs.

We are seeking the three owner user IDs and ordering by creation date descending. Why in God’s name does SQL Server need to sort this data? It is sorted for us. Cracky.

One kind of fancy way to rewrite this query is rather than an IN clause, we could put those values that we care about into a VALUES clause.

We could value our VALUES clause. And then we could take those values and we could cross-apply out and we could correlate to them. Remember our values are aliased up here as U and the column that we’re naming these values is ID.

So U.ID is really just those three values. And if we run this, we will get a query plan that is no longer parallel.

Right? We can see all of the visual indicators of parallelism have disappeared from this plan. And it has gotten faster. We went from 120 milliseconds down to 33 milliseconds.

But notice that we still, SQL Server is still like, ah, now we got to sort that data. That seems silly to me. A bit. And that sort takes, I don’t know, whatever 33 minus 9 is.

Right? Oh. Now we did okay here. We improved things. But, SQL Server should have better ideas for these things.

One way that we can rewrite this query, to again, give SQL Server a different way of thinking about this, would be to do this.

And we could say, where the owner user ID is in select union, select union, select. Right?

And if we run this query, remember that, we went from 120 milliseconds to 33 milliseconds. We still didn’t get rid of that sort. And before I run this maybe, we could just look at how much memory this thing takes.

1,024 KB of memory. So we went down from 400 megs to 1,024 KB. That’s pretty good. Right? And so, not only do we have like, you know, this query got, I don’t know, like 60 something percent faster.

But we still have this sort of, the memory grant went way down. Like granted, the memory grant, way down. So good job there. But, if you write the query like this, we get a very interesting execution plan.

If you’re sitting at home thinking, gosh, that returned 1,000 rows very quickly, you would be right. So we went from 33 milliseconds down to 3 milliseconds. And we get a very interesting execution plan.

Where for each of those union values, we now have one, two, three constant scans. And they each produce a singular seek through our indexes.

And we have this merge join. So, normally, I would, I crap all over merge joins. But remember, from the Office Hours video, I actually, there was, one of my, one of the things I said, is that sometimes SQL Server will choose merge in order to satisfy the ordering requirements of downstream operations.

And it has, my goodness. It’s almost like I know what I’m talking about sometimes. Right? So we don’t have a single sort in this query plan.

This query plan is still single threaded. We have no visual indicators that parallelism has. That parallelism has occurred here. If we look in the tooltip for the select, there is no memory grant anymore.

The memory grant is gone. And our degree of parallelism is one. So our query went from a 120 millisecond, 400 meg, DOP8 parallel plan at 120 milliseconds to a single threaded plan with a sort still at 33 milliseconds.

The sort, the memory grant went way down. So like, we’re still cool there, right? It spilled a little bit, but you know, it was still, still pretty decent to a query plan that now has no sorts, no memory grant, still run single threaded, and is now down to three milliseconds.

That is a pretty good deal. This is an admittedly funny rewrite. I get it. I maybe, you know, wouldn’t suggest you rewrite all of your in clauses to do this, but you know, sometimes it’s, you know, jibba jab there.

One thing that I think is missing from the optimizer is just the ability for it to spot these sort of patterns and apply these sort of patterns to our query plans. Because really, it would be identical to writing this query, right?

If we said select the top 1000, U dot star, and we could, I mean, the P dot stars in here don’t mean anything because we’re selecting U dot star out here, right?

So like we could, we could do this, right? And say, look at that. The single server comes up with the same basic execution plan with the, except we don’t have constant scans in these, right?

The plan is simplified a little bit. It still takes three milliseconds. There’s still not a single sort to be seen. And, and we’re still, no memory grant, dot one. So this is, but this is just one of the things that the optimizer is sort of not good at sort of spotting and working out.

It goes for like, like almost like the lowest common denominator plan sometimes. And that’s, it’s a little aggravating. We could also rewrite this query.

This is admittedly much, much more verbose, but it, it gives us the same basic execution plan that we’ve seen in the, the, the other rewrites that have given us this plan shape with the merge joint concatenations, the three separate index seeks and the no sort, the no parallelism, and again, finishing in three milliseconds.

So a lot of the, the query performance tuning that you see, performance tuning that you might have to do in your life is recognizing, recognize patterns that the optimizer is perhaps bad at dealing with, uh, and writing your queries in ways that make the optimizer not really have a choice in how they’re going to deal with those things because you have taken control and rewritten your queries in ways that the optimizer cannot ignore.

A lot of that stuff is going to come down to breaking your query up and, and, and sometimes, you know, uh, like obviously temp tables are a way to break a query up, but sometimes breaking your query up into, uh, even like logical units within the query itself is a pretty smart thing to do.

Coming back to the way that this query was first written, right? If we run this, we look at the query plan, we look at the seek, right?

We have three, three separate seek keys in here. This is a multi-seek or a dynamic seek, and this is something that I’ve talked about before, but it essentially takes away SQL Server’s ability to preserve the order of things, uh, as it, as it goes through because we’re, we’re, we’re not just doing one seek into the index and finding one range of data.

We’re doing three separate seeks into it and we don’t, it doesn’t maintain the order when, when we do that, which is why we needed to sort out here. So even getting rid of the, the spill on this sort, we are still at 107 milliseconds.

We still have the parallel plan. Now, I don’t want to crap on parallel plans because I use parallelism a lot. Um, all the time to make queries faster. It doesn’t make every query faster though.

This is unfortunate. Sometimes it, it does, it does have overhead and, uh, that overhead does, uh, you know, the, the, the, the assembling and reassembling of rows across exchanges and buffers and, you know, sending worker threads out and having them all work and do stuff.

It can’t, it doesn’t have overhead, but that overhead is, it amortizes itself in larger queries where parallelism is a more practical, um, is a more practical element.

And, and it makes the query faster because you’re spreading lots and lots of rows out across parallel threads. In a lot of cases, when SQL Server chooses a parallel plan, it’s just not getting so many rows that spreading them out across multiple CPUs is a really good strategy.

So we end up with sometimes when a query running at max.1 can be a lot faster for the small amount of rows than a parallel query, right?

There are times when that absolutely happens like the, the one I just showed you, right? But this thing really does, the way that this query is originally written, the two problems with it are one, the in clause doesn’t allow us to, with the multi-seq does not allow us to preserve the order of the, the nonclustered index that we have, whereas other query forms do.

Anyway, few different interesting ways to rewrite a query with an in clause. You thought, you thought it was just so, so simple. Uh, the, the optimizer is not good at sort of unraveling and being able to, uh, reason better query plans for, uh, and that I think you have just learned how to do.

And, uh, one, one thing that it probably is worth saying here is, uh, these are not query tuning strategies that our, our robot friends often come up with.

And, uh, the reason for that is because our robot friends, well, well, they are terribly, terribly logical and they, they exceed at, I think mostly at, at logical situations.

Uh, they, they are not clever. They are not clever like you and I. So, we still have hope out there. On top of the fact that, um, they’re, they’re getting very expensive and companies are like, wait a minute, now that this costs money, I don’t think I like it.

Uh, anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you next week on Tuesday for office hours. You’re great. And I hope your weekend is too.

All right. Goodbye.

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.