Tag: Indexing

Free SQL Server Performance Monitoring: Where Things Are And Where Things Are Going

Chapters

• 00:00:00 – Introduction
• 00:00:33 – Update on Performance Monitor Project
• 00:01:19 – Server Size and Cost Analysis
• 00:02:19 – Storage Growth Tab Overview
• 00:03:17 – Locking and Index Analysis
• 00:04:19 – High Impact Queries and Connections
• 00:05:33 – Monitoring Tool Overview
• 00:06:55 – Query Store and Graphs
• 00:08:09 – Built-in Plan Viewer
• 00:09:04 – Memory Analysis
• 00:10:09 – File IO and TempDB
• 00:11:34 – Agent Jobs and Server Configuration
• 00:12:20 – Future Plans for Monitoring Tool
• 00:13:39 – New Full Dashboard
• 00:15:00 – Alerting and Notifications

Full Transcript

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.

Two Insert Exec Problems

Chapters

• 00:00:00 – Introduction to the Learn T-SQL with Eric series
• 00:01:34 – Understanding Insert Exec and Its Implications
• 00:07:18 – Blocking Issues with Insert Exec
• 00:12:59 – Performance Issues with Insert Exec
• 00:16:07 – Conclusion and Next Steps

Full Transcript

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.

SQL Server Performance Office Hours Episode 71

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:01:58 – Travel Preferences
• 00:04:29 – Conference Locations
• 00:07:33 – ClickUp Overview
• 00:08:38 – Azure VM Issue
• 00:10:32 – Table Variables Discussion

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

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

Learn T-SQL With Erik: Variables and Date Math

Chapters

• 00:00:00 – Introduction
• 00:02:15 – Understanding Parameter Sniffing
• 00:03:13 – Query Plan Analysis
• 00:08:48 – Performance Comparison

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

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

Learn T-SQL With Erik: Date Bucket Difficulties

Chapters

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

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Going Further

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

SQL Server Performance Office Hours Episode 70

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:00:36 – Tuesday Office Hours
• 00:01:17 – Performance Monitor Questions
• 00:05:08 – Temp Tables and System Performance
• 00:09:42 – Table Variables Usage
• 00:11:32 – Conclusion

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thank you for watching.

Going Further

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

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

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

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

What Darling is

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

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

What it collects

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

The store is yours

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

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

Alerts without a babysitter

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

An MCP server that can do things

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

Watch it from a browser

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

Custom Views and notebooks

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

How to get it

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

Download Darling

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

And Lite is not going anywhere

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

Going Further

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

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

Chapters

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

Full Transcript

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

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

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

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

Not eternally. Just… Just a couple of seconds.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thank you.

Going Further

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

Learn T-SQL With Erik: DATEDIFF Considerations

Chapters

• 00:00:00 – Introduction
• 00:02:15 – T-SQL Basics
• 00:04:18 – Date Diff Considerations
• 00:06:14 – Date Add and Date Diff
• 00:07:56 – SQL Server 2022 Improvements
• 00:09:35 – Time Boundaries and Chasms
• 00:11:31 – Data Type Considerations
• 00:12:58 – Conclusion

Full Transcript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

All right. Thank you. Goodbye.

Going Further

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

SQL Server Performance Office Hours Episode 69

To ask your questions, head over here.

Chapters

• 00:00:00 – Introduction
• 00:02:15 – Office Hours Promo
• 00:04:05 – Personal Interests
• 00:06:06 – TV Shows
• 00:07:39 – EXISTS vs JOIN
• 00:08:45 – Reducing Compile Time

Full Transcript

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

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

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

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

Going Further

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