Erik Darling here with Darling Data, continuing my fast ascendancy to monitoring tool mogulhood. What you see in the background is the latest iteration in advance in my free open source SQL Server monitoring tool. This is going to be the enterprise edition of it, but we’ll talk more about that later.
What I want to talk about in this video is a new Cloud Marketplace skill that you can see chugging away here in the background. And what it aims to do is take the analysis engine from my performance studio application, which includes plan parsing and analysis rules and all sorts of other stuff, and hand them over to an agent.
So you can use Cloud, of course, but there are poor unfortunate souls in the world who are forced to labor under the drudgery of GitHub Copilot. And I feel bad for them because Copilot is just plum embarrassing. I would smack the words out of its mouth if it were a human.
It is… There aren’t words for it. It’s dumb. But a friend of the repo, Hannah Vernon, was nice enough to open a pull request to give our imbecilic Copilot LLM access to this as well.
So hopefully you will gain some respite from its moronics. But back to the tool at hand. We have a Cloud agent here looking at a query plan that I gave it.
You can see up at the top it said, Hello, Cloud, can you tell me why this query plan is so gosh darn slow? And the Cloud pulled up the skill that I gave it.
Up here, let me look. Successfully loaded this skill. And now Cloud is chugging away. Analyzing the query plan with my analysis engine.
And you can see it using all that. And the reason why I did this is because I got so tired of re-explaining execution plan minutiae either to a new Cloud agent locally or abroad. You have to say like, no, I don’t want to hear about logical reads.
No, I don’t care about operator costs. No, Cloud, row mode operator times are cumulative. Batch mode operator times are cumulative. Batch mode operator times are insular.
No, that’s not what that wait step means. No, buddy, what are you doing? So I got very tired of having to do that over and over again and basically have to retrain every Cloud that I talked to on SQL Server execution plans. And so I have another agent working on the monitoring tool stuff in the background.
You’ll see that pop back up in a moment. So this Cloud has finished its analysis. And what it’s saying here is, well, let’s scroll back up a little bit.
And at any moment, the other dashboard is probably going to pop up and get in my way. But in the meantime, so we have an answer here. And Cloud is smart enough to see that we have an eager index pool.
It is 98% of the query runtime. It’s 69.726 milliseconds. And it explains what the query is doing.
All right. And it tells you that SQL Server built an index for this at runtime. And, wow, it’s roughly 32 gigs of pages.
Whoo-wee. That is a long time. All right. And it read 17.1 million rows to hand back 81,000. That’s a lot of work.
All right. And it also notes here that, well, I mean, I don’t know if I particularly agree with this. So, like, the tool can only do so much.
All right. It can still say, like, menacingly dumb things sometimes. All right. But it does understand that this is a .4 plan doing serial work and paying full freight for the privilege. Well, that’s a hell of a sentence there.
That sentence is doing some work. All right. And we see here, all right, Cloud knows that exec sync in a parallel plan indicates that an eager index pool is being built. All right.
All right. It could pop up for other reasons, but, you know, for our case, it is correct. All right. And now if we look down here, we even have Cloud smart enough to know if you create this index, my friend, the eager index pool will go away. All right.
The mechanism concretely, they both vanish from the plan if we have this index in place. All right. So it’s even cool. All right.
It’s even cool enough to know. We don’t get a missing index. We don’t get a missing index request from this plan because we don’t we don’t because SQL Server does not emit a missing index request when there is an eager index pool. All right.
So that’s good, too. All right. And it even tells you don’t waste your time with all this stuff. Just create that index. All right. So if you would like to try this new Cloud or Idiot GitHub copilot plug-in, there are a number of ways to do that. All right.
So if you would like to try this new Cloud or Idiot GitHub copilot plug-in, there are instructions to do that down in the video description along with a link to the GitHub repo where all the code lives in case you want to take a look at it. Maybe you even want to have your own robot, give it a once over to see if there’s anything that you might care about changing or contributing in there. I’m happy to take contributions on these things.
But you can do that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you enjoyed my new Cloud and or Idiot GitHub copilot plug-in that makes execution plan analysis go a lot better than it normally would if you were to just leave the robots to their own devices because their own devices are often not very good.
So if I could say just one last thing in closing, it’s that, you know, like I feel comfortable using the robots for certain query tuning tasks. tasks because i i know enough to to tell the robots when when they’re full of it but i i worry about the the folks out there who don’t know and who get the robot saying criminally insane things to them and criminally wrong things to them and then this being like oh wow that sounds so sure of itself i ought to do that it like it’s it’s rough right like like it like like they’re they’re very good at logically like you know looking at things and logically sort of like chaining things together but um man uh the the the advice and analysis portion is uh often difficult to overcome but uh this this will hopefully make it better anyway give it a shot it’s kind of kind of fun to have uh claude not be or kind of have kind of fun to have an llm not be be completely lost in a query plan 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.
Take three, Erik Darling here with Darling Data, and today is Tuesday, and the weather is mostly back to normal. We went from startlingly hot to constant rain. Summertime, baby! Anyway, it is office hours. This is where I answer five user-submitted questions that hopefully, well, hopefully users, I don’t know, is AI, are people sending AI questions in? I don’t know. I can’t tell anymore. I’ve given up on the world. Down in the video description, you will find all sorts of helpful links. Probably the most helpful video description on the internet.
If you want to hire me for consulting, buy my training. Become a supporting member of the channel for as little as $4 a month. Ask me office hours questions like you see here.
Or even like, subscribe, and tell a friend. All of this is down below if you just put your eyes a little bit… It’s like, I’m not going to tell you my eyes are up here because the links are down there. So, whatever. If you would like, if you would enjoy free SQL Server performance monitoring in your life, I happen to have that totally free, open source, no email, no phoning home, just pure T-SQL collection running, getting important stuff about your SQL Servers, wait stats, blocking, queries, CPU, memory, disk, tempdb, you name it, it’s in there. And I give you a way to have your robot friends talk to just the collected performance data. So you don’t have to worry about them going and running crazy queries on your prod servers. They’re just in there talking to them. That’s it. Thanks for watching. I’ll see you next time. Bye-bye.
performance data and that gives them a slightly better chance of saying something that is not completely out of this world outlandish um you know i mean maybe maybe spicy moment here but um you know i’ve uh i’ve been trying to give the robots some more chances to do stuff with like query and index tuning and man they they feed me some real dumb lines about stuff uh it’s it’s it’s annoying right and i’m like like do you do you remember who you’re talking to and they’re like i’m sorry i started apologizing uh and you know um you know yeah anyway let’s answer some questions oh boy wait time per core per hour second and sp blitz first first high impact weight type and sp perf check eric to help answer if top weights are high low in sp blitz first since startup equals one per core per hour divided by server time since restart uh i’m gonna paraphrase this a little bit because this is a lot of words um so uh in sp blitz first and i forget where this come from this came from this might have been a jeremiah thing uh but at least you know back when this stuff was getting written uh the like per core per hour per whatever thing um was like like dmv company policy uh i never quite understood it it never really sunk in with me why that was better um you know uh i think in in your case specifically where you have the default cost threshold for parallelism and max stop set to two uh it yeah like the parallelism weights are gonna look weirder than they do uh if if you just look purely at like the the totals and stuff um you know like i i i guess it can i don’t i don’t even know um i i i don’t i don’t have a good answer for why it’s done that way um or uh why why that that’s the ongoing thing um it it never again it never really resonated with me um i’ve had much better luck doing things the way that i do them uh which is you know why i do them the way i do um you know i i can’t really think of a situation where i would maybe want to factor in purely the number of cores on a server without contextual information like you have with like well what is max stop set to what is cost threshold set to like things like that um because just purely looking at at cpu time or cpu cores um i feel like that that loses some stuff so um i’ve never had uh terribly good luck following that that analysis pattern but i don’t know maybe i’m the dumb one let’s see what we got here is blocking a concern when querying the dmvs if not how does microsoft keep that data updated without queries blocking them um so i i do see uh blocking sometimes when i’m trying to query dmvs um you might notice that some of my uh scripts even have a locked timeout on them um as active has a couple lock timeouts uh when looking at when when trying to fetch query text and query plans there’s some cursors in there that do that but that are that have locked timeouts in them um i think sp blitz index has a locked timeout and it’s for for a lot of them it’s because you know you you can get blocked while stuff is um you know is going on in there and i there are there are a few remaining bugs um if i remember correctly the one in sp blitz index is around uh identity columns uh there’s like weird blocking that can go on uh trying if like you know the identity columns are um being inserted into uh in in some way uh i think that blocks stuff up there’s just been a number of weird stuff in there i mean the safest thing you can do is just you know um use no lock or uh read uncommitted when when doing that uh i obviously don’t know what microsoft’s secret sauce is for a lot of the the things they do behind the scenes in order to log that data um so i i couldn’t comment on that uh i don’t know how they do that but i do know that you know just because of the amount of foolishness i’ve dealt with querying dmvs over the years um the old recommitted is the best thing you can do there all right uh it’s not your classes but now my girlfriend has a crush on you any recommendations uh you let her down gently uh you know i’m i’m a pleasantly married man uh i can’t do anything for you there um i i am i am willing if if you uh if it would help you out um i i would send i would make an eric darling mask for you to wear uh around the house for that whatever you and your girlfriend do, I don’t know, maybe if that would be good for you, but yeah, sorry, there’s only one of me, only one of me, but let’s see, grow a beard, get neck tattoos, what, I mean, maybe that’s just what you need to do, right, maybe there’s a pattern forming, I don’t know, is it possible to tune, blah, more blocking, tune blocking problems without switching isolation levels, hell yeah, it is, come on, so the thing that you need to sort of make yourself comfortable with at the outset is that you will not be able to solve every iota of blocking or deadlocking in the database, even with a better isolation level than SQL Server ships with, which is the default locking garbage read committed, if you were to use read committed snapshot isolation or even snapshot isolation, you would still need to do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, with either writers blocking each other or under snapshot isolation, potentially writers dealing with conflicts, so you can’t fix everything, but you can do a number of things to ensure that blocking is minimized, you know, don’t update a billion rows at a time, that’s usually a pretty good start, batching modifications that have to do large amounts of work, they might not get any faster from start to finish, but each individual chunk will do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, be kinder to your server and allow other queries to sort of orchestrate themselves in, making sure that your modification queries have adequate indexes in order to get to the data that they will be modifying, that’s always a big one, if you’re doing like a delete or an update and there’s a scan in your plan, you can bet you’re doing way more locking work than you need to, and of course, write your queries in a way that minimizes the work that you’re doing in modification queries, you know, things like sargability and good estimates are often very helpful to modification queries, as are occasionally, if you have a modification query that’s doing a whole lot of work in order to figure out what result, what rows and what the resulting value should be, many times staging that stuff to a temp table can be faster than relying on a Halloween protection spool to populate and do the update, so sometimes separating the nasty part of the complex heavy-handed part of a query from the actual update that needs to occur can be very useful as well, let’s see here, how do you explain to developers, ah, I live a hammer, that works on my machine means absolutely, I think that’s a little unfair, it doesn’t mean absolutely nothing, it at least tests some compilation, it gives you some sense of validity, but, you know, I guess, depends a little bit on what their machine is, doesn’t it, depends a little, because if their machine is a big honking, you know, prod server size hardware with an actual production database, obviously with PII scrubbed out, so we don’t have any leakages or data theft or exfiltration or anything like that, it could be faithful, but most of the time it’s not going to be.
You know, I think that developers developing locally is, is totally fine to get, you know, like a prototype of something out the door, and for a simple enough query, that might be as far as it needs to go, but for anything where, you know, performance, concurrency, things like that start to become a concern, then it’s, they need to be, I mean, developers need to know that things just need to be promoted up to, you know, they need to be promoted up to, you know, they need to go through environments in order for, sort of, their, their work to be proven out, so I, I think that it, well, it doesn’t mean absolutely nothing, there is a bit more that goes into releasing queries that, that drive applications than, you know, just some, some C-sharp code that, you know, draws a Windows form or something, right, we have a, we have different sets of concerns, right, including, you know, making sure that, you know, the, the, the plan stays, the, the query’s execution plan stays pretty faithful, and the same indexes get used, and performance stays good, and all that stuff going up from one to the other. I mean, I guess it’s okay if the query plan changes, but you want to make sure that performance stays consistent on its way up, and of course, if your queries are parameterized, you, you can, you can, you can, you can, you can, you can, you can, you can, you can, you need to test multiple, sort of, parameter combinations of high and low density data to make sure that your query is not parameter sensitive as much as possible. This is, that’s actually something that the robots are good for, doing that sort of data discovery and testing type stuff, you know, I’ve built a number of, I mean, usually, the robots generally prefer Python for this stuff, you know, I’ve built a number of, sort of, test beds and harnesses for things, you can leverage SQL CMD for this, and, you know, you know, have the, have the robots collect execution, actual execution plans for you, you know, you, you can even have them use my performance studio app, if you go to code.erikdarling.com, there should be a link for that down in the video description, the performance studio query plan analyzer has a CLI component in there that you can, you can have your, you can have your robot friends use to spit out either JSON or human-friendly query tuning advice with, and that can be very helpful for them to skip over a lot of the dumb crap that they think about query plans, you know, like the, and it even sucks with, like, you know, having the test harnesses that I have built, where there’s, like, very specific guidelines and instructions, or it’s, like, I don’t care about logical reads, I will, I will, I will smack you into the dead end, and I’m going to have to, like, I’m going to have to, like, I’m going to have to if you tell me about logical reads, I care about CPU, I care about duration, if you start talking to me about, like, query, like, operator costs or anything, again, to the death realms, but, like, you know, they, they still slip up, and they’re like, oh, we did 10% fewer reads, I’m like, who cares, why does it matter, does the query get faster or not, right, yeah, I don’t care how many reads we did, faster, yes, no, right, CPU, higher, lower, yes, no, what are we doing here, right, give me something meaningful, users aren’t going to be like, hey, good job, 10% fewer reads, my life has changed, I care about, I care about their time, speaking of time, I believe it is time for me to end this one, I think I answered that question, I feel like I answered it, anyway, thank you for watching, I hope you enjoyed yourselves, I hope you learned something, and I will see you over yonder in tomorrow’s video, where we will talk, probably some T-SQL-y stuff, I think.
That sounds about right to me, all right, 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.
Erik Darling here. Suffering. Suffering in this massive heat wave. Don’t like it. Don’t enjoy it. I mean, the kind of weather… I don’t know. Where’s it always cold? I’m gonna move there. What’s Iceland like this time of year? I don’t know. Maybe Brent had the right idea in 2020. Move to Iceland. It’s never hot.
I mean, you know. I think every beer costs like $30 because they have to helicopter it in from somewhere, but it’s worth it. You never have to deal with heat. Occasionally eat shark that smells like stale urine, I guess. I don’t know. Anyway, let’s learn some more T-SQL. This is the wrong title. Screw that. It’s too hot to change it. In today’s video, we’re gonna talk about performance tuning queries that have to compare the date differences between two columns. There’s one very obvious thing that we can do in the form of the computed column, but then there are other things that we can do depending on our indexing.
We can write into the query. Again, coming back to the idea that sometimes we can align our indexes in our computed columns to our queries. And other times, we can better align our queries to our indexes. If you would like to purchase the full course, where I talk about absolute accuracy, and I talk about the data-driven, absolutely everything in great detail, and you have access to the scripts and the query plans and all the other stuff that we do in here. You can purchase it for $100 off down in the video description. It’s a great time. You can have the whole family sit and watch it, right? Put it on at dinner. Better than leave it to beaver, I think. You can also hire me for consulting. You can become a supporting member of the channel if you would like to part with $4 a month to say thank you for all the hard work that I do here. Ask me office hours questions. That is, of course, free.
So, you know, it makes me feel good. And, of course, please do like, subscribe, tell your friends. Get your children involved early. It’s never too early to start learning about databases. Ruin your childhood. If you are in the market for free, absolutely, totally, no strings attached free SQL Server performance monitoring, you can download the performance monitoring tool that I make. It’s on GitHub. You can read through everything. Down in the video description, you will find the appropriate linkage to get that and grab it. It’s a great time, and it’s only getting better. I can’t make it more free than it is because it is entirely free, but I can make it better. I can add more value to free somehow.
But now, let’s continue suffering. In this dismal weather, let’s talk about SQL Server. So, in the last video where I talked a little bit about precision and date and stuff, this one, we’re going to talk about performance a little bit more. So, we’ve got these two indexes already, right? We’ve got one on the post table in the Stack Overflow 2013 database, one on last activity date creation date, one on creation date last activity date. And a problem that we identified was that whenever we want to date these two columns, SQL Server has no choice but to…
to scan stuff, right? And that… this poses an issue for us as performance tuners because sometimes a scan really isn’t… really isn’t all that good. So, we have to scan our non-clustered index, right? It chose the index on creation date last activity date. Why? It doesn’t matter. It would have had to scan either one, right? And there’s no… there’s no difference. There’s no hidden seek plan here that would have helped SQL Server along. Now, flipping that around a little bit, right? Let’s say…
let’s say that we wanted to try to write this in a more sargable way. I don’t know. How do you describe Rob Farley? Is he a friend? Is he a foe? All I know is that he’s a magician, and you can’t trust magicians, but he is a pretty smart guy. And a long time ago, he wrote a post about sargability and called it, like, Look Both Ways, or something. And Rob had some very good points in that post.
And so, I’m going to try to expand a little bit on that in a slightly different way, because I’m not… addition. I’ll never hassle you with card tricks, but maybe some people are into that. So like if we tried to write this query in this way, so like say we wanted to remove the date diff function from the two column setup, and we said, let’s date add 10 years to one of these columns, maybe SQL Server could take advantage of an index on the other column, because we’re like saying, hey, SQL Server, you can see, you can do all the math on this one column, and then maybe you can seek to the data in this other column, because there’s no function wrapping around this column now. But SQL Server does not do that, right? SQL Server still completely scans this index on creation date, last activity date. And if we try to add in a force seek hint to say, hey, SQL Server, perhaps you would like to try seeking here instead, we get an error that the query processor could not produce a plan of that variety.
And so we are stuck. We were stuck scanning this. Now, one way that you can get around this is you can choose to write your query in a slightly different way and give SQL Server some boundaries, right? So like the whole point here is that we’re looking for places where there are a 10-year difference between creation date and last activity date.
So the first thing that we might do is try to find a maximum value to start with, right? That’s what we’re doing in here, right? We’re saying, give me the max. And this works in either direction, going max or minimum. So we’re going to say where creation date is less than this whole date add construct in here, where we find the max last activity date. And then we will do our final calculation in here like this, right? And this basically mimics the last query, except it gives us a place to seek into, right? And we can see that we do sort of find some stuff in here, right?
We find the max last activity date in this portion of the plan. And then we seek into one of our indexes here for the rows that we care about, right? So we get down to that one row max, and then we return stuff out. And this is a market performance improvement. It’s a little hard to tell because the last query just said 563 milliseconds. And it was very easy to see.
For this query, we have to go and find the max last activity date in this portion of the plan. And then we go into the query time stats to see this 69 finished in 69 milliseconds. So this was a pretty good strategy for this one. And you can even flip that if you need to find like mins too, right? So if we wanted to flip the order of this, and like rather than saying where creation date is less than or equal to subtracting nine years from the max, and then the last activity date is greater than or equal to adding 10 years to creation date, we could flip that to do a min on creation date and last activity date and kind of do that in here.
In this way. But my memory serves, this is basically the same plan. But yeah, so but well, actually, I guess this one is 54 milliseconds, but well, that may be 55. Let’s see. Let’s see what query time stats tells us. Ah, 56 milliseconds. So not so I would consider that 13 milliseconds, maybe some noise. Maybe even if that’s consistent, is 13 milliseconds worth all that flipping around? I don’t know. If you’re having the robots do it, probably it’s nothing happens. Anyway, your company’s paying for it. So again, coming back to the sort of ivory tower way of looking at things, store data, the way you query it, query data, the way you store it, otherwise, you’re gonna have to write some pretty weird queries to find performance satisfaction with things, right? At one point, Microsoft did make an attempt at this sort of thing. They added that date.
Date correlation optimization setting around 2005. But it never really took off. And the basic idea was that if you had two date time columns, and one of them happens to be unique and has a unique constraint or index on it, which I know you people, right? And you obey all the ANSI settings rules applicable for filtered indexes, computed columns, index views, and you create a foreign key between your unique date time column and your non-unique date time.
Then the optimizer would be able to figure some additional stuff out when you’re joining two tables together. So if you wrote a query like this that has all that stuff applied to it, SQL Server would turn it into a query that looks like this, right? It would add this date correlation stuff to it.
And it would say, oh, well, not only do I look this way, but I’ll also look this way. But the problem is that it did that by creating an indexed view for you in the background. And so it’s not terribly surprising why this didn’t go very far and get much traction with the general public. I think Fabiano Emerim is the only person who ever saw, like, really blog about it. But I guess the obvious thing, and this is something that we’ve talked about in many other videos, would be just to simply store data the way that you’re querying it, right? And just assuming that we don’t care so much about all the precision stuff that I talked about in other places, we would just create a computed column. And notice that computed column is just a column that does not have to be persisted in order for us to index it. And because all it produces is an integer, maybe, probably an integer, date diff result, it is deterministic out of the box. We don’t have to do any weird entangling of things in order to make this deterministic. And then all of a sudden, our queries magically pick that up, right? And we just say, hey, I know who you are, right? Unfortunately, I don’t think either of the fancier queries that we wrote pick up on that computed column, but maybe. Let’s just go and have a look-see. I don’t think it happens, but yeah, there we go. Yep, they just use the regular indexes there, which makes sense because the expressions that are in use here are certainly not anywhere near the expression that was used in our computed column, right? It would have to be far more precise than that. But our query picks up on it down here, uses our computed column. We don’t have to worry about the data we care about, and all is generally much better. All right. I hope you enjoyed yourselves. I hope you learned something. This is the Thursday video, so I will see you next Tuesday for office hours. And again, if you want to purchase the full course material that all of this stuff stems from, the link is down in the video below with a coupon for 100 bucks off. 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.
Erik Darling here, with Darling Data, continuing somehow to record these videos despite the terrible heatwave that is upon us. This is not for the fragile. This is the second European mass die-off happening currently. Well, I mean, I’m in New York with air conditioning. Still not fun.
We’ve been state mandated to not have our air conditioners go above 78 degrees, but I don’t know. I’m out here breaking all sorts of rules. Anyway, in this video we’re going to talk about two problems that you will face when you need…
This is an introduction to the problem. We’re going to flesh more of the problem out in tomorrow’s video. But we’re going to talk about two of the things that you will run into issues with when you start trying to figure out the date distance between two columns.
And there are some matters of precision that need discussing. So, down in the video description, you may notice that this is part of a larger course called Learn T-SQL with Eric. You are seeing little sniffs and dribs and drops and drabs of the full course.
But you can purchase the full course for $100 off down below. You can do that. You can find the will and the power to purchase all the material.
And then you too can know as much about T-SQL and performance tuning as I do. You’re fully up to snuff on everything if you ever watch it. Purchasing is half the battle.
Watching is the other 100% of the battle. There are also other links down there. If you want to hire me as a consultant, you can do that. If you want to become a supporting member of the channel because you’re like, wow, Eric sure does give us a lot of free stuff.
I wouldn’t mind giving him four bucks a month. You can do that. You can also ask me office hours questions for free. And as always, please do like, subscribe, tell all your friends. Join monitoring mobile party.
Anyway, speaking of monitoring, important stuff out here. My free open source monitoring tool available up on GitHub. This link and other important links where you can acquire.
This free thing are down below. Totally free, totally open source. You don’t have to give me anything, pay me anything. I don’t look at your data because you’re not paying me.
If you were, it might be different. But it captures all the stuff that I look at and care about as a performance tuning expert in the world. So if you would like to get your hands on that, you can do that.
There are all sorts of great things that go along with that monitoring tool. But let’s talk about it. Let’s talk about our data problems here.
So we’ve talked about local variables. We’ve talked about date functions a little bit generally. In part two, we’re going to talk about things you might want to think about when you’re doing cross column date math. So we talked about like if you have a variable or a parameter, the thing that a lot of people mess up is they do the date math on the column and compare it to the parameter or variable.
We know we don’t do that. Flip that math because that’s where it’s better. I’ve created a couple indexes here just to sort of illustrate the problem a little bit.
One on last activity date, creation date. One on creation date, last activity date. And the point that I want to make here is that no matter what we do when we run this query, SQL Server has no choice but to fully scan that index because SQL Server has no way of calculating this currently, right? It has to evaluate every single row that goes in.
And SQL Server doesn’t keep any information about like, oh, this date is five days from this date or anything like that. Nothing like that is stored. These are like because SQL Server by default doesn’t like know what you’re going to care about with these two date columns.
So it’s just like, oh, see what they do. But so it’s up to you to figure out what you care about with these date columns. And it’s up to you to create computed columns that match the things that you care about.
But just on its own, the thing that’s important is that SQL Server has no idea what you care about. Okay. you will care about in the future right sql server does not create indexes for you aside from meager index pools and sql server does not pre-compute any things like this it is up to you to design your database in a way that gives you sufficient access to your data the very ivory polished way of saying this is that one should query data the way that it is stored and one should store data the way that it is queried so if we need to if this is a persistent calculation we might want to store this calculation some we’ll talk more about that tomorrow today’s video uh one thing that i think a lot of people sort of misunderstand is precision when it comes to this uh we talked i think we talked about this a little bit before but it’s worth repeating because most people do not spend much time thinking about this i’m going to run these two queries together they were made to be run together of course and uh we’re going to look at the difference here so this one is uh simply calculating the date diff in years where the difference is greater than or equal to 10. all right and this one is using a much higher precision this one is taking 10 years worth of seconds all right that’s a crazy number and saying give only give me these and what we’re going to see is that there is a big difference and uh just what what is a 10-year gap to date diff and what is this you know a big number of seconds gap right because that is much more precise right we’re going 10 years down to the second not just 10 years down to the year so when we look at the results in here this first result returns 643 rows and this second result returns 23 rows so there are only 23 out of the 643 rows that actually are 10 years down to the second that’s a very very big difference when you’re calculating these things and what i think you might notice here is uh if we look at the first result we will see that there is in fact a 10-year difference between all of these but the number of months even does not add up to 10 years worth of months precisely right we have 109 month difference until we scroll way down and then we finally get to a 120 and 120 month difference and the the day difference is also consistently just continuing to sort of rack up up as we as we look down through the results for the second result again only getting 23 rows this is down to the second 10-year difference notice that we start at the 120 month difference here and we start at the three thousand thirty six hundred well the even number would be uh three thousand six hundred and fifty right ten times 365 days or so right uh screw a leap year even though there’s maybe two of them in there i don’t know maybe one depending on if you get lucky but uh no there’s definitely two in there just where those two follow will be up to the gods uh but this starts right at where we would expect it to so this is down to the second and this is just down to 10 years right the two like the the date part has 10 years between one and the other but the number of months and days and all the other stuff may not be precisely 10 years so be very careful with how you frame your date stuff because you might have some surprising results in there and i think the the truly shocking thing to me is that a lot of the time when people um start seeing strange results in the date math they rather than up the precision of what of what the date diff is calculating they’ll start doing all they’re still adding and converting uh they’re they’ll start doing all sorts of strange date math on the columns to make it conform rather than just using a more precise date diff calculation it’s a very very strange where developer brains go in these things me i go that’s what i care about even in the heat where my brain is melting right so if we do care about 10-year precision then we would want to make sure that we are using the most precise measurement right all right thank you for watching i hope you enjoyed yourselves i hope you learned something i will see you in tomorrow’s video where we will talk further about the performance ramifications of these calculations and how you can avoid ramifications 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.
Erik Darling here with Darling Data and it’s hot and I don’t know it’s too hot to do this so you’re gonna have to forgive me if my answers today are a little sweaty because man we are we are dying out here in this heat but it is time for office hours where I answer five of your questions before going to the beach. I don’t know is there other beaches in New York? Maybe I’m just gonna go outside and drink who knows anyway it’s time for office hours despite the heat if you want to ask me questions for office hours you can do that there’s a there’s a link down in the video description that will allow you to interact with me in that way you can you can submit your question and I will answer it faithfully I don’t care what the question is whatever you put on the teleprompter I will read there are other helpful links down there too like say maybe I don’t know you want to hire me for consulting I will show up like this if you want you can buy my training where unfortunately that is pre-recorded and I was wearing my my formal adidas attire for all of those but it’s still a good time and there’s there’s discount coupon codes in the thing down there too so you’re really out of excuses unless you are absolutely destitute you can you can also like subscribe tell a friend become a supporting member of the channel you should know all these things by now but I feel like a lot of you just aren’t paying attention to the importance I feel like a lot of you skip this part I also have free SQL Server performance monitoring tool it’s up on github that that link down the video description amazing times are ahead with that I’m rebuilding full is a better thing and the the light version is really alive and kicking a lot a lot of good improvements just across the board and that thing totally free totally open source you don’t have to pay me anything you don’t have to give me your email address and I certainly don’t collect any of your data because I’m not looking at that for free that’s that’s stupid it’s just a bunch of t-SQL collectors grabbing all this stuff that a a seasoned intelligent experienced SQL Server performance tuner like me would be would look at it and I put it in the palm of your hand assuming that you have a hand I don’t I don’t know maybe going into the 4th of July weekend that’s always a little perilous to assume that everyone has has hands or maybe maybe after the weekend when you watch this anyway yeah let’s let’s go answer some questions because the the heat insanity prediction is coming true my brain is melting brain is just entirely melting here all right let’s see what we got here do you think a DBA should know version control eg git and how does it fit into day-to-day database work and on that note do you happen to have any kind of version control training or module in your courses so it is becoming well I guess it depends on what you’re doing as a DBA a lot of places now if you want to you know get a like fix in somewhere for a query or a stored procedure or anything like that it has to go through version control and testing and you know so it is probably a good idea for you to have some idea what you’re doing there but this is also a place where the robot friends can be very very helpful because the robot friends know all the git commands and and and they can they can really help you out help you sort out some messy situations that would otherwise be really really annoying for you you know so I should a DBA no git it really it depends on what part of the DBA thing you’re in like no one’s checking a backup into source control at least I hope not right that’d be a bad time like yeah what are you doing I’m trying to commit this full backup last week so depending on what kind of DBA work you do maybe but like I think maybe a good way for you to get started with this is to get started with this that start a private github repo and like if you have scripts or you have a store procedures that you use maybe put them in there and just like for yourself start working on like you know what what what what like pulling them looks like and what you know maintain it like like creating a forking or not forking but creating a branch and doing some work on that branch and then committing it and creating a pull request and pushing that and all that stuff just do some work on some personal things that you use maybe like you know you’ve got I don’t know some script that looks at index fragmentation because you’re that kind of DBA and you’re like ah I want to delete this because it’s useless no I’m kidding but like there are probably things that you use day to day that would be good to that you like you know would would probably get some some personal benefit from just you know practicing with you know github is free right you can get github desktop I use github desktop for for my stuff because it makes things nice easy buttons to push I don’t find there’s a lot of pain or friction in there using github desktop but I would really I would say that you should you should just start a private repo with github and just mess with that a little bit I don’t I don’t do any training on that because it is not my forte I am not by any stretch of the imagination an expert when it comes to that sort of thing but if you if you want to learn that sort of thing you can do that all right next question really enjoying bit obscene when’s the next episode out Bob Ward is a guest me Bob Ward is not coming on my goddamn show I’m lucky to get Joe Obish on there when’s the next episode I don’t know whenever Joe gets his air conditioning sorted out and can can be presented in front of people again that’s that’s that’s that’s my best that’s my best estimate Joe is having some air conditioning issues right now that prevent him from appearing anywhere all right what are the earliest warning signs of memory pressure before everything melts down ah so the first sign of memory pressure is usually not from queries though it can be but usually not usually the first sign that you’ll see is other memory consumers sort of fighting with each other so you have you have a database right and you’re you’re looking at it and you’re like wow I have what’s this to make put some nice round numbers out there let’s say you have 128 gigs of memory and you have like 512 gigs of data that’s not like that like the data to memory ratio is gonna throw things off and so the primary consumer of of your memory right now would probably be sort of like the bucker pool and when you see a lot of weights on stuff like page IO latch right that means you’re queries are constantly going to disk for stuff so that means there is an immediate memory pressure because you are not carrying enough memory to deal with the data that you are currently working with so the first thing that you want to look for is churn in the buffer pool that would be that would be that would be the first thing to look at because that is only gonna get worse as your data grows now you know for that specific thing of course there are various ways to deal with that you know archival strategies getting losing data right getting rid of data uh you might be missing some indexes that prevent like large object scans we don’t care about small objects uh you might look at uh compressing page compression page compressing your rowstore indexes that would be a good start uh and then you you might also um i don’t know uh consider getting some more uh but usually that stuff all starts ticking up way before you’ll see like query memory pressure problems where you’ll have like like the bad resource semaphore weights um but if you if you if you also see those then well it’s it’s it’s you’re probably already having that meltdown you’re worried about so that’s your fault there all right how do you decide coin tosses obviously uh when to rewrite a query instead of just adding indexes well there there are two things there um well one of them sort of depends on what the index situation currently looks like right uh if you’re looking at a table that already has a kajillion indexes on it you might consider something like maybe not just adding another because uh the the chances are at least okay that there is already already an index right there’s already already an index that might help you out along the way so maybe just don’t just just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t jump to that. But if you have a table that’s rather bare and sparse and barren of indexes, then you might consider, I don’t know, adding one that would help your query. So I’ve done a bunch of videos on this sort of recently under the Learn T-SQL series of things where I talk about aligning queries and indexes. Sometimes you have no good index in place and there are query strategies like adding an index that would be good. And then sometimes you already have an index and your query is just not written in a way to take advantage of that. So if you’ve already got an index in place that would help your query, then it would be time to rewrite. But if you’ve got no indexes, then you must create at least one to help your query out. But the decision usually comes down to looking at the indexes and saying, oh, what do I got versus what are the goals of my query? Start with the where clause. That’s usually a pretty good place to start.
I’ve had to solve a dramatic number of query problems recently. With for sequence, that’s an interesting one, right? Because like, why is SQL Server choosing the scan? There’s an index it can seek to. And it’s like, I don’t know, because there’s like no rhyme or reason. Like I get costing, but you know, like, why are you doing that? And then SQL Server is like, well, I thought this scan would be cheap. And I’m like, cool, but you’re scanning 43 million rows. And you could just seek to 100,000 of them. And it’s like, oh, yeah, you’ve got a good point there, Eric. Thank you for forcing me to seek.
So that’s sort of that there. You know, if you’ve already got an index, write your query in a way that takes advantage of it. If you’ve got no indexes, well, perhaps then you create one, right? Seems reasonable to me. I don’t know. Why? Oh, this is a hum. Questions like this and answers like this are why Bob Ward is never going to be on Bit Obscene. That, I think, I don’t know. Perhaps Bob’s religious obligations would prevent him from being on a podcast. Sorry, on a radio program called Bit Obscene. But why does parameter-sensitive plan optimization sometimes make things worse instead of better? And the answer is because Microsoft completely beefed this feature. Man, they, like, it’s like clown walked up, like, pie to the face, spray bottle. It’s like Moe came up and just, like, Larry and Curly bonked their heads on something. But, man, they really screwed that one. So, like, right now you get three plans, right? I mean, just on the face of things. Like, there are all sorts of, like, ways you can get more plans. But right now, let’s just say for an equality predicate, you get three plan variants.
And the way things get bucketed, is you have the least common plan, you have the most common plan, and then everything in between. And I’ve shown examples of this in my other videos, where you’ll have, like, something at the very, so, like, I think it’s the votes table that I’ve shown with the vote type ID column, where the least common value is, like, 700 rows. The most common value is 37 million rows. So, they each get a plan variant. But the problem is that every, like, every time you get a plan variant, you get a three-row count between, like, 737 million shares the second variant, the plan two. And there are some very, very big swings in that data. You have some that are, like, 5,000 rows, and some that are, like, 3.7 million rows, and a lot of stuff in between. And the sharing in there is not good, right? So, a lot of that does depend on, like, which of the parameters gets fed into the variant two plan first. But it’s a bad sharing time within that chunk.
It just really messes stuff up. So, I was so excited about that feature, and then I saw it, and I was like, ah, yeah, complete screw job. But, hey, we got Fabric. That’s real groundbreaking stuff, right? If you ever want to know what Databricks looked like, like, seven years ago, try out Fabric. If it’s up, right? Who knows? It’s been down a lot lately. And, uh, not, not, really a lot of explanation of why. And, um, yeah. So, what a waste. Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I’m gonna go cool off now.
And, uh, I will see you in tomorrow’s video, where, uh, I don’t know. I don’t know. This might be too much clothes. We’ll see what happens. 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.
Erik Darling here with Darling Data, the one, the only, the monitoring tool mogul of SQL Server. Today I wanted to sort of update people on the state of the performance monitor project because there are some things that are useful to the general population that I feel like I should bring up.
So the current version of the performance monitor is 3.1. If it’s been a while since you’ve tried this thing out, I would suggest giving it another shot because there have been some really, really big improvements, not only in the collected data and sort of visualization and printification of things, but also in the UI, UX, the sort of experience that you get out of it.
The So The So The So The First Thing I Want To Walk Through I guess some of the new stuff in here. If it’s again if it’s been a while since you’ve looked at it there are some things that you may have missed in the meantime.
If It’s Again if It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Again If It’s Gone lrllrlrlrlrlrlrlrlrlp. Sort of where your servers are at with, you know, like if you’re your right size wrong size. If you need to up size down size like where you need to go and what you can do to sort of save money on your servers.
There’s a utilization tab that talks through that talks about how hardware is used on this if you are over provisioned under provision things like that. There’s this neat database resources tab which sort of breaks down by database which ones are doing the most work from a variety of perspectives. There is a new storage growth tab and if you right click on anything in here and you click show objects.. see that was fast even though it was a little behind. It’s not bad for opening a new window. You can see in here like which objects and this is just the hammerdb database. You can see which objects in here where growing the fastest and like how much they grow by. So order line is up at the top and speed is at the bottom or time line in the top.
right and you can see all the sort of growth trends for for that if you go into locking and contention this is currently for all databases but if we focus this to hammer db tpcc it makes the picture a little bit more clear where we can see which indexes specifically are hot spots in your database right so you can see like which ones get locked the most which ones get written to the most which ones spend the most time being read and all that stuff and of course how big they are that’s up over my head there there’s the database sizes tab which breaks down sort of databases by size and by database and files and all the sizes and whatnot there’s index analysis which runs sp index cleanup if you have that sort procedure installed it can tell you which indexes you can get rid of merge together all that good stuff there’s this tab here called optimization which tells you which uh right right resources you could stand to tune up the most and gives you some queries that relate to those resources. Then there’s the high impact queries one. This one will show you which queries do the worst amount of work across all your databases and which ones. One thing that I can’t fix is whenever I switch RDP sessions, the DPI gets messed up and things start showing up in weird places. That I haven’t figured out yet, but I’m working on it. And then if you need to figure out which applications do the most sort of connection work to your servers, there’s that. And then there’s a whole inventory of servers, which breaks down the name, the edition, the version, host OS, what kind of hardware is assigned to it, all that good stuff. And that’s before we even get into the performance monitoring part. There’s also a recommendations tab, which will look at all of the wonderful collected data that we have.
Tell you about the problems that you’re having just at a very high level. There’s all sorts of criticals and warnings and things that you can look at in here. And there are options for some of them. If you want to generate a prompt to start an MCP or another sort of investigation into things, you can do that. And then some of them will even have ways that you can fix some of these. Some of them will have buttons that say, hey, you want to fix this? We can fix this right now. Coming back, coming into the actual monitoring part.
Well, we’ve got this lovely overview of all the server resources right here. And it sort of gives you this lineup line so you can see exactly along the chart what was happening. And you’ve got this little hover overview thing that will tell you about which metrics were spiking, if they were up from prior samples. But I guess if you’re looking at a graph, it’s pretty easy to tell if something is up from a prior sample because the line goes up. But it’s just a handy way to see like what percentages of things and like what counts of things are up from a prior sample.
So you can see all the different metrics that were happening at any given time on a server. We have our wonderful weight stats tab. And one thing that has driven me nuts about every other monitoring tool is weight stats graphing is when you look at weight stats, they’re just like, here’s all of them. And if you have a spike in a weight you don’t care about, like let’s say backups, backup weights dominate this crazy chunk of graphs. And you don’t want to see backup weights. You want to see your other weights that are more pertinent to your workload. You can choose which weights you want to see over here.
It’s very, very easy. And you can also do a little bit of math here and there. So, if you’re looking at this graph, you can see that it’s very, very useful. We also have our queries tabs. And one thing that is worth pointing out is, before we go any further, is one thing that is really hard to do with a lot of other monitoring tools is figure out like which things are a root cause of a thing, right? So if we look at this graph and we say, wow, that sure is a lot of LCK MX weights. I wonder which queries were involved in there. We can right click on the graph and we can say, show queries with LCK MX weights. And we get a whole window here.
Of queries that were involved with LCK MX weights. We can get right to the root of problems, right? So back to queries. We have this graph, which kind of gives us different sources of queries and what was going on with them. Query duration, procedure duration, duration from query store. And of course, executions right behind me. So we can see spikes and when queries executed, which is a great thing to have. We have active queries, which is a snapshot of queries that were running over various points in time.
You can see all these LCK MX queries and LCK MU queries behind me. It’s crazy. We also get queries by duration. And we have a little breakdown over here of CPU by database, if you’re interested. The little button there, you can push if you want to see that.
So this is gathering from query stats. We have this one, which is gathering from procedure stats. And these time slicers, you can move them around so that you can focus in on like big jumps and things. And it’s very, very useful for that, right? So all this stuff you can do in here, very, very useful. We have query store, of course. We actually collect query store data, unlike a lot of other monitoring tools. I’m not going to name any names, but you all stink. And then over here, we have a query heat map where we can see at what points various queries ran and sort of which ones stick out the most in the workload. So this kind of gives you some visual indicators of when bad stuff happened and which queries were involved in that. And then a little notification that’s going to pop up, of course, while I’m recording this video in the wrong place.
That’s good too, right? Again, I’m working on that. All right. So all sorts of good things in here. We have a plan viewer. The plan viewer is probably most useful if we go get a query for it. So when we collect plans or we fetch data from your server, we have a built-in plan viewer. So you don’t have to even leave your monitoring tool in order to get a query plan information.
And the great thing about this is it’s not just, oh, here’s a query plan, figure it out. This is all primed with advice from me, how I would analyze query plans. It goes in, breaks, goes through the XML and the stats and everything else. And it helps you figure out where in the query plan you should focus and what you should fix and change.
All right. Pretty standard CPU graph. And again, anything, any one of these graphs, you can say show active queries at this time, and we’ll give you active queries at that time. We got memory. We got all sorts of memory. We have an overview where you can see total target buffer pool memory grants.
We got breaks down by memory clerks. So if you’re troubleshooting weird memory issues, you can see which clerks are clogging up the most of it. We start with the top five by default, but you know, that’s probably the most interesting thing. I don’t know.
We get stuff for memory grants, right? So we can see all of these good information about when queries asked for memory, if they waited for memory, if they got forced to use a lower grant, we have all that stuff. This graph is empty because I don’t have memory pressure on my server.
Because unlike you, I have enough memory for my server. Right? I, you, you never do. I I, I’ve seen your servers. Uh, we’ve got file IO, two different ways, right? Well, one is by latency, and we’ve got reads and writes for all latency, and we’ve got throughput so we can see how fast things are moving. Right? Two good ways of measuring the, the, how fast, how good your disks are, how well your disks are doing, right? Uh, English. It’s, it’s a, it’s a wonderful language. Uh, we’ve got TempDB here. Uh, we, we can see when TempDB grows and shrinks. So if you’re curious what was happening in there. We can do that. And then you can right click and you can say, hey, what queries were running then? And you can figure out which queries caused your TempDB growth. It’s all wonderful, wonderful stuff, right? We’ve got blocking. We’ve got blocking and deadlocks galore. We’ve got current weights around blocking. Again, it’s a beautiful thing.
We’ve got block process reports that fully spell out exactly which queries were involved in your blocking problems. We’ve got the deadlock XML report, which fully spells out all the queries that were involved in deadlocks. We’ve got Perfmon counters. If you’re that kind of person who enjoys Perfmon, and if you’re the kind of person who’s maybe a little unsure about Perfmon, we’ve got Perfmon packs up here, right? And what this will give you is if you are troubleshooting a specific issue and you want to see Perfmon counters that are related to that issue, you no longer have to remember the name of every Perfmon counter. You can look at memory pressure. You can look at memory pressure. You can look at memory pressure. You can look at CPU pressure. We can look at CPU pressure. We can look at I.O. pressure. We can look at 10 dB pressure. We can look at locking and blocking. We can do all this stuff, and we don’t have to remember the name of every single, gosh darn, Perfmon counter that is relevant to us, right?
If you care about agent jobs, we can see which agent jobs are running. Hey, what’s going on right now? Who’s running that agent job? Agent. Ah, that’s not surprising. Why are they doing that? It’s just scheduled. Man, there’s nothing you can… You can kill it. You can kill it.
You can kill it if you want. I don’t know. All right. We tell you about your server configuration at many different levels, right? Server configuration, database configuration, database scope configurations. If you have any trace flags active, apparently I don’t.
That’s cool, though. I don’t need them. I’m good, and I can make SQL Server work fine without them. That’s my superpower. We’ve got a daily summary. This one isn’t very interesting for me. It just shows the worst of the things that happen during the day. I wouldn’t take this one too seriously.
I would… I wouldn’t spend too much time on this one. It’s just kind of like, what’s going on here? Oh, that… Ah, yeah, I know about that. And then we’ve got stuff that will tell you how well the monitoring tool is working. A general collection health summary, a log of every collector that has run… Well, not every collector. It’s like the last 24 hours. So you can see if anything is screwing up. And then if you want to figure out if any of your collectors are taking too long, we have a collection trends tab. So you can see, ah, which one of these things… Is one of these queries taking too long? I don’t know. All right. So look here. Our block process report, query took a second one time. Ah, bummer. All right. Anyway, that’s a sort of speed walk through the monitoring tool as it currently lives. And I don’t… This is just… This is the light version. But what’s really exciting to me is where I am going with things in the future, right? This is just what you get now for free. It’s great, right? And you can see screens popping up here. So you get a little preview. That wasn’t intentional. This is just how things are working.
The next thing that I’m doing with the monitoring tool is I am getting rid of the full dashboard, meaning I am getting rid of the thing that creates a database and agent jobs and does all this stuff locally on a server. Far and away, every time I look at the downloads and the sort of trends for what people are using, what they’re doing with it, everyone loves light.
But light… But I need something that is more powerful. So what I’m working on now, that Fable is back. And, you know, I’m not like one of those, like, every time a new, like, Opus 4.7 or 4.8 or 4. whatever drops, it’s like, the most powerful and capable, blah, blah, blah. That stuff is like water off my duck’s butt.
But I don’t have a duck. I wish I did. But I don’t buy into a lot of that. But Fable really is pretty amazing for this thing that I need to do. So what I’m doing now is I’m working on a headless Windows service. The viewer will be portable. This is not replacing light. This is going to be the new full, right? So it’s going to be backed by Postgres with timescale, because timescale is great for very compressed data, and making queries against that data very fast. It’s good for exactly the type of data that we’re collecting here. And what was the other part?
Yeah, it’s got the headless Windows service, the Postgres and timescale backend, and the portable viewer, right? So that’s the next sort of iteration with these things for me. And that’s where I’m going with it next. Now, that doesn’t mean that I’ve shown you everything from the monitoring tool that I need to. One thing that is very important for you to know about is that there are settings for this thing. Like you can opt in to have an MCP server startup with your monitoring tool. And you can have the robot friends talk directly to your monitoring data and just your monitoring data. They don’t talk to anything on the server. They just look at what got collected and say, Oh, yeah, that’s great. I can I can, I can figure this out. We’ve got all sorts of stuff for notifications and alerts. So if you’re looking for alerts, right, you can you can configure all of this stuff and you can decide when you want to get alerted for things and you can decide what alert channel you want to use. For example, if you want to get email alerts, you can get email alerts. If you want to send notifications to Slack or to teams, you can send notifications to Slack or teams, right? You’ve got all sorts of web hooks and stuff in here that you can hook this up to and you can make you can get alerts where wherever you fetch alerts from, right?
Not everyone cares about email. Not everyone uses teams. Not everyone uses Slack. So you get alerts where you live, right? So that’s that’s pretty cool, too. And this is all free. And even the new thing is going to be free, right? This is just a free open source monitoring tool that does the work that a lot of paid monitoring tools just won’t do because they’re lazy and they’re not good at their jobs. Anyway, it’s real hot here today. So I’m done. I got to turn these lights off before I fall over. But thank you for watching. I hope you enjoyed yourselves.
I hope you enjoyed this video. And I’ll see you in the next one. Bye bye. I hope you learned something. I hope you will download this monitoring tool. You can get it from my GitHub repo. Or if you want a short way to get to my GitHub repo, it’s code.erikdarling.com. Remember, that’s Eric with a K, right? If you go to Eric with a C, I don’t know where you’re going to end up. You could end up in an organ harvesting ring. I don’t know. But, you know, be careful out there. So code.erikdarling.com if you want to get this. It’s a performance monitor repo, totally free, totally open source. You can see everything it’s doing. And if you’re interested in keeping up with the new version of this, just keep your eyes on things. I hope to have something probably by the end of the month that will be fully fleshed out for that. Anyway, thank you for watching. Once again, I’m going to go fall over in my own sweat now. 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.
Erik Darling here, Darling Data, I’ve got a rather exciting one for you today I think, probably anyway, we’re going to talk about two problems with insert exec, one of them I’ve actually shown on this very channel before, but since I have a new one I also want to include the last one because who knows how many of you have shown up to love, adore, and cherish our time together. Since I recorded the last one, I don’t know, I suppose there’s always a chance that some of you found that first video and that’s where you just decided this is the place for me, I’m here for life, but I don’t know, I don’t have those kind of metrics, no one tells me anything, so you’re getting the twofer, alright, good for you. Down in the video description, you’ll find all sorts of useful, helpful links in order for you to give me money in exchange for goods and services.
Services like SQL Server consulting, perhaps you would like me to address performance issues on your SQL Server, wouldn’t that be nice for you, right, you wouldn’t even have to talk to a robot for that to happen, I mean, aside from me. You can also purchase my training, down in the video description, there’s even a coupon code for the Learn T-SQL with Erik course where I talk about things just like this for hours and hours. It’s a Tantric experience, a Tantric T-SQL experience, perhaps the T in T-SQL is for Tantric, I don’t know, it’s Transact, alright, whatever. You can also become a subscribing member of the channel where you give me as few as four American dollars a month in exchange for all of this wonderful content.
You can continue to ask me office hours questions, I’m going to have to work on your taste in music and cloud providers. In the future for those, given recent dilemmas, but that’s, you know, something we can address later. And of course, if you enjoy this content, please do like, subscribe, tell a friend, yada, yada, yada, yada, yada.
If you would like free, as in gratis, gratis, gratissimo, SQL Server performance monitoring, boy, have I got a deal for you. More free, totally free, totally open source. You don’t need to give me an email address or, you know, worry about me, like, looking at your data secretly.
I don’t want it, unless you pay me. It is a bunch of T-SQL collectors running, getting all the important information about performance on your SQL servers and laying them out in nice charts and graphs for you to peruse, browse, and otherwise stare at in a flummoxed state of bafflement for as long as you can bear them. But there’s also…
There’s also a really nice thing in there. There is a built-in MCP server that is optional. You have to enable it yourself. I don’t turn it on by default so that you can have your robot companion friends read just your performance data. Look at just the nice collected aggregated performance data and perhaps give you a better chance of analyzing things a little bit more quickly.
It’s really helpful for folks who are not maybe as well-informed. in SQL Server performance issues as they would like to be or perhaps as well-versed as they should be. But a lot of folks do seem to like that part. But anyway, let’s you and I talk about Insert Exec because you got all sorts of stuff to talk about in here.
All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…
why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.
Very very shocking. So I’ve got a store procedure here. This is insert exec 2. We’re gonna have to nest things a little bit so I’ve got a 2 and then a 1. Insert exec 2 declares a trancount and holds the current trancount, deletes from a table called lockme, inserts into a table called lockme, and the insert of course does this. Now just to sort of exacerbate a locking issue, I have a wait for delay of 5 seconds inside of insert exec 2. So that’s gonna hold the locks from the delete and the insert above for 5 seconds.
Insert exec 1 just looks at the current trancount, creates a temp table, and then inserts the trancount into the table. Because remember insert what insert exec 2 to or insert exec 2 does is inserts the transaction count from in here right so really what this does is it just shows you the transaction count incrementing to prove to you that in the context of insert exec there is a transaction right so that’s the whole point of this one so if i just if i run insert exec 2 first this will run for five seconds because there is a five second wait for and it will return a tram count of zero right because there is no current insert exec for this but if i run execute insert exec 1 where there is an insert exec where because insert exec 2 up here right we have this block right this is the part where we run into trouble even just inserting into a temp table but the problem really is that we have a delete and an insert in here right and this delete and insert is going to hold locks while the other stuff happens so what i’ve got here is if i run insert exec 1 here and i run this over here and i run sp who is active over here uh we we lost it but that’s okay uh we can do that again real quick and we’ll see just you know immediately the tram count before insert exec was zero and then we smuggled back a transaction here so let’s do that again and let’s run that and let’s just get the lock information from this one so in here you’ll see that i’ve run this over here and i’ve run sp who is active over here and i run and i’ve run this over here and i run this over here and i run this over here and i run this over here we can see um this mouse wheel is weird we can see uh the the wait for delay right uh this is the five seconds that insert exec 2 puts into things to exacerbate locking and we can see our select query here trying to select from lock me uh getting blocked right there uh the blocking session id or rather the the blocking information from who is active points directly to uh session 78 blocking session 84 that’s our select here and if we look over in the locks portion the the locks portion for the query that’s taking the locks is perhaps not terribly terribly interesting um you know we can see the obvious stuff uh we took locks we deleted we updated blah blah blah um i don’t know that one’s not that cool then we we also see the open tran count of one over here right so there’s multiple ways to validate that the insert exec uh does take the lock and then the query takes the lock and then the query takes the lock and then the take uh open a transaction around the entire insert exec thing that does that does not let up until the insert is completed right so all the stuff inside the exec is like all the locks in there are held until the insert completes that can be a very shocking thing for a lot of people but what i want to show you next is something even well something even crazier right so uh what i’m going to do is show you um uh does it does this database matter no not really we’re using temp stuff anyway uh the insert exec can not only block stuff but uh with even just a moderately sized result set it can really really slow things down and it’s really hard to figure out where the time is going and being spent right so i have a temporary store procedure here and this temporary store procedure basically just takes a number of rows that we want to return that really should be a big end but uh we’re not we’re only using i think two million or something in this so it doesn’t matter too much but it should be noted that the input uh to top to a top and uh even offset fetch is is all big end based so don’t don’t be too harsh on me so we’re gonna make this store this temporary store procedure and uh we’re also going to make this one now this one has sort of two paths in it right um we’re gonna say if this temp table exists we’re going to insert uh this query directly into the temp table if not we’re just going to execute the query down here but and this is to show you sort of a fix for the um the insert exec problem right so let’s make sure that query plans are enabled uh and then here this is where we’re going to do two different things uh the first one that we’re going to do is we’re going to create a temp table and then we’re going to insert uh exec like this and uh and then second one uh what we’re going to do is just show um if we use the shared temp table and we insert into that shared temp table locally uh the the time is no longer weird with things but i i’m going to run this all at once because we declare some stuff up here and then we reuse it in both both branches and i think that’s probably not worth retyping for this demo because you know what they say typing in demos just gets you into nothing but trouble right so uh we’ve got some statistics time output which is sort of valuable here just to get sort of an initial look at things and we can see that the first batch in here uh takes about seven and a half seconds to complete we’ve got this weird sort of five and a half seconds thing here and then uh so that was batch a completing right that was this one so about seven and a half seconds for two million rows and then down below we have batch b completing uh which takes about 1.2 seconds for those same 2 million rows this is just inserting directly into the temp table now where things get interesting right is we have um this initial thing here right and this takes 819 milliseconds all right if we look at the properties of this and if you’re looking at query plans please always be looking at properties uh this query looks like it finishes in 819 milliseconds and if you are looking at this query plan and you said this finishes in 819 milliseconds i would not call you totally wrong but we have if we look over here we have an additional five and a half seconds right or we have five and a half seconds so let’s just pretend let’s round a little bit let’s just say we have five seconds of of time that we cannot account for right like maybe i don’t know something weird happened we also have this fun thing all right non-parallelizable intrinsic function uh darn it uh well that’s okay it maxed off one makes more sense for this anyway right and if we look down here that we have this sort of oddball second query right uh and if we look we have this insert exec dest right uh this takes 1.6 seconds uh and comes off 2.4 seconds here but this parameter table scan so what sql server does is it for when you do insert exec uh there’s sort of like a hidden work table type thing where sql inserts all of the rows into that work table right and this is again there’s a transaction here and then from that print from that work table which is the parameter table scan then inserts all your rows where you want them to go so you’re doing like a double right copy thing here with that right so that’s not a very good time if we look at the query time stats on this one right this will actually line up pretty well with what we did right they have cpu and elapsed time at 1.7 seconds there that’s close i mean we’re we still like kind of lost a second or i don’t know like 100 milliseconds or so but i’m willing to forgive 100 millisecond loss but it’s it’s just quite interesting the way that pans out there’s just a count query down here to sort of separate things make visually things make a little bit more sense and then of course we have down here the sort of plain uh insert uh into the temptation table in the store procedure and the query time stats here make total sense right this took one second of time here which lines up pretty closely to what we did here so the insert exec portion double copies the rows and we end up with this weird big sort of time suck of stuff that happens i did a lot of work with um like uh windows performance recorder and um the purview i think to like break down the call stacks and stuff there are some technically interesting points in there that just show like what functions internally the time is spent in but it’s not very interesting on video the big thing you you have to understand the big thing you should start doing is if you have insert exec code that is uh just slow for reasons that you cannot easily determine well stop doing this right because this is this is the insert exact pattern that we’ve shown is bad from a blocking perspective and from a performance perspective and instead inside of your store procedures where you have to um where you would uh let’s just say sometimes you uh and let’s let’s say sometimes you use insert exec and you dump the results into a temp table other times you just execute the store procedure and return the results out one way that you can get around that and you might have to do a little bit more work here with like dynamic sql or something but one way that you can get around that is just look to see and this is very similar to uh the pattern that i showed you about getting triggers to selectively fire if this temp table exists then insert the rows into the temp table right because the outer store procedure will create the temp table um so it’ll be visible to the store procedure on the inner block right so we can see this temp table i can’t it’s showing squiggles here but that’s okay because in the original store procedure up here we create the temp table and that’s that’s where that’s where it will go up and down so um and also That’s where, sorry, in the code itself, we create the temp table so the store procedure can see it, right?
That’s this part down here. We create a table called shared and then we execute this and sort of conditionally inside, the presence of this table means a store procedure takes a different path, does the insert.
If it doesn’t see that temp table, then it just returns the select. And you don’t have to worry so much about like weird if branching stuff. If your code is all parameterized in this way and you’re running the same query either way, then you’ll get the compiled plan for both branches for the set of parameters that you pass in.
It’s a pretty good situation. Anyway, that’s enough of that. We’ve talked for too long. 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 about, I forget, probably go back to talking about date and time stuff.
Go back to the Learn T-SQL with Eric experience. 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.
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.
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.
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.