Blog

SQL Server Performance Office Hours Episode 90

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 90


To ask your questions, head over here.

Chapters

  • 00:00:00 – Introduction
  • 00:03:00 – Free Monitoring Tool for Server Baddies
  • 00:06:00 – Concurrency Problems and Wait Stats
  • 00:09:46 – Tuning Advice That Does More Harm Than Good
  • 00:12:40 – SQL Server’s Dumb Choices and Query Optimization
  • 00:14:40 – Optimizer Budget and Time Constraints
  • 00:16:00 – Conclusion

Full Transcript

Erik Darling here with Darling Data, and we are office hours-ing once again. I apologize for the sudden dearth of videos last week. There were a few technical difficulties with the setup that I just didn’t have time to get to. We’re all sorted out now, though. Everything seems to be back in working order. Functioning camera, microphones on, beautiful background here. What else do we need, folks? What else do we need? Anyway, down in the video description, if you want to ask me questions for office hours like all the other nice people do, at least most of them are nice. Some of them, not so nice. Some of them, I don’t know. Trying to, really trying to get me down. I don’t know why. I don’t know what’s wrong with you. I don’t know what I ever did to you. Link is down below. Other links sprinkled throughout my very helpful video description. You can hire me for consulting. You can buy my training. You can become a supporting member of this channel for as few as $4 a month. You can sacrifice, like, one Midwest coffee a month and give it to me to say thank you for usually producing a lot of videos every week. Last week was weird, though. Anyway, also, if you happen to enjoy this content in any way, shape, or form or, you know, you think someone else might just want to hit mute and, you know, enjoy themselves, like, subscribe, tell a friend, all that good stuff. If you are in the market for something that doesn’t cost any money, I’ve got a deal for you. I’ve got the steal of a lifetime. Free SQL Server and Postgres database performance monitoring. If you go to code.erikdarling.com, there’s more links down below that are actually clickable. You can’t click this one. It’s just, it’s on the screen. I’m not quite that adept at YouTube. Anyway, all the stuff that you would want from performance monitoring tools. I got to do more videos about those. It’s really, really turn it into a thing. It’s amazing when I use it, like, I turn it, like, use it at, like, client sites and stuff and I turn on people just like, that’s free. Like, yeah, I like you. Anyway, I wasn’t free, but that is free. Maybe that’ll, maybe this will all take my place someday. I hope so. That’d be fun, right? Just ruin everyone’s life by, anyway. It’s great if you need that sort of thing. It is almost the end of September. I suppose it’ll be closer to the end of September when this actually publishes, but I will be in November.

I will be in Seattle for Pass Summit West, taking place at the Hyatt Regency at 808 Howell Street in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL, called T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you, well, do you need to know any more. I mean, do you need to know anything? Maybe I will be in the middle of September 11th, taking place at the Hyatt Regency at 808 Howell Street, in the United States, November 9th through 11th. I have a pre-con there where I’m doing advanced T-SQL that doesn’t suck, and I also have a regular session there called Read Committed is Cruel by Default, where I will teach you things about isolation levels that you…

Well, I mean, do you need to know? Maybe you can just be better informed when you’re arguing with robots about them. That’s about what I got for you. But anyway, September is wrapping. This is not, this is this, the weather outside right now is not living up to this photo. It is very windy and rainy. The weekend storm. I know you’re seeing this on Tuesday, but right now there is a weekend storm outside, which is why I get to do this on a Saturday. Life’s grand. None of it matters.

Anyway, let’s go answer these questions. When is plan stability actually more harmful than helpful? I don’t know, actually. How about when you have parameter sensitivity issues? Sometimes one plan ain’t enough for a query. You need some, you need some planiamory or something. Some queries just need multiple plans. Other times, you know, I mean, there’s some obvious stuff that you might have going on. You might have some real issues with like, you know, indexes or perhaps the way the query is written or something. But yeah, some, some, some data patterns, some, some shapes of data do require some queries to take on multiple plan shapes in order to run quickly. That’s just the way of the world. A lot of queries, you know, you don’t have that problem with. They just run 10 million times a second in the background and you hardly even notice. Other queries, they, they blow up. And when they blow up, things get bad. Both from the perspective that you might have, like, let’s go a little bit further on this. If you have a situation where like a big plan and a bad plan don’t share well with each other, you, you get the little plan first, right? And all of a sudden, like queries that need big amounts of data really, really slow down and take a long time to run. And then you have the sort of reversi problem where the, the big query plan, uh, uses a lot more resources, right? Cause it’s like, oh, we have work to do. We’re, this is interesting. There’s a lot going on here. Uh, and, uh, you know, you get a big parallel plan, big memory grant, all sorts of other stuff happening. And then all of a sudden, lots of people start running that big plan and your server, uh, becomes bereft of resources rather quickly. So, uh, that, uh, you know, um, that is when plan stability can not be fun. Ah, it moved.

That wasn’t supposed to happen. Uh, how do you prioritize tuning when management wants everything fixed? Ah, that’s easy. Uh, you ask management what they do that’s slow. Cause as soon as they’re happy, they shut up and you can, you can go do all the rest of it. Like, uh, like, Hey, Hey boss, what’s slow. And like, uh, every time I hit this random layoff button, it takes forever. And then you go look and you’re like, ah, I see what the problem is. You’re ordering the whole company by new ID.

Um, that’s why. All right. I can use table sample and some other stuff to fix this up. Don’t worry about it. I gotcha. We’ll get those layoffs faster. Uh, but if it’s, if it’s not a case where management uses the software directly and has an opinion on it, um, I guess you could start by asking them like, um, like what do people who have their phone number complain about?

It was like, if someone like, if like some other honcho type is calling them like the gosh darn tootin cockadoody bird reported slow again, you’re like, well, let me just go find USP underscore gosh darn cockadoody report dot SQL. And you can take a look there. Uh, otherwise, you know, you can, you can talk to other people who have to deal with, uh, customers, uh, support people. Uh, often there is a trail of tickets. If you have a ticketing system where people have expressed their loathing of performance issues. Um, I, I, I have found, uh, robots to be very good at going through, uh, large, vast swaths of data like that and finding sort of repeated, uh, patterns and problems with stuff. They are very good at, um, that sort of mechanical labor. Um, so if you want to give, uh, I don’t know, like you want to give cloud access to your, like Jira, ACLI or whatever, and this is like, Hey, go for it, buddy. Uh, read away. Tell me what’s the worst stuff.

That’s, that’s one way of approaching it. Um, you know, other than that, I would, I would go with my sort of stock and standard methods. Um, there’s actually a very cool new, uh, parameter for, uh, SP quickie store called, uh, find high impact. Uh, I did a video about it where I used the wrong word like 15 times cause I was tired. And then Kevin Fiesel put it on his curated SQL thing. And like, there were a bunch of like, uh, corrections in it. I was like, ah, thanks man. I didn’t realize I screwed up that bad. Uh, there were, there were some things on my mind at the time. I think, I think a Pomodoro or something. Uh, there’s, there’s a hotel in France called, uh, Cheval Blanc and they, they make a drink called the Pomodoro, which is a tomato and mezcal drink. And it is absolutely delicious. And I was thinking about that at the time. Anyway, ah, there we go. Always comes back to Paris. Uh, but anyway, um, SP quickie store has the find high impact parameter and that seeks to score it rather than like, you know, like the normal sort of query tuning method, like sort the thing by CPU or duration or physical reads or memory or whatever. Uh, it scores your queries across a variety of metrics and finds the one, find the, find the ones that hurt you most across like a bunch of stuff. Right. And that breaks down like what percentage of CPU and duration and all the other stuff they use. So you can be like, Oh, I don’t like the looks of this one. Let’s, let’s go tune it. So that’s probably what I would do. Otherwise you could go to code.erikdarling.com, download an absolutely free monitoring tool and figure out what’s really driving your server baddie.

Cause it’s a pretty good way of doing that for free. How do you spot concurrency problems before users start complaining? Uh, well, let’s see. There are many levels to concurrency in the, in, in, in the database world. Uh, you know, at the, at the lowest level you have stuff like latches and latch contention. I guess you could go a level lower and do spin locks, but you know, don’t start there cause you don’t, you don’t want to start. I wouldn’t start with latches either. Um, you know, wait stats are probably a good sort of in the middle, uh, indicator of where you might be experiencing, uh, contention, but, uh, a level up from that would be sort of hardware resource contention. But, but the nice thing is that almost all of this stuff is available in wait stats. For example, uh, if you have CPU contention, you’ll see like SOS scheduler yield to be high up there. If it’s caused by parallelism, the CX weights will be high up there. Uh, if you’re having some form of like disk slash memory contention, uh, then you’ll see, uh, you know, lots of the page IO latch underscore weights because SQL Server ain’t, uh, keeping a lot of the data that it needs in memory.

And that could be because of big memory grants. And if you have a lot of big memory grants and you could be seeing resource semaphore waits. So, um, that’s, that’s usually where I start. Like, you know, um, it did, if you’re, if you’re just looking at a server for the first time and it’s been up for a long time, it’s not as valuable, which is why a good free monitoring tool that trends your wait stats over time would be really helpful for you to figure out where concurrency problems might be bubbling up from. Uh, and then, you know, while, while you’re in there looking at, at weights, you, you, you might spot some weights that start with LCK, the lock weights. Uh, and you might start looking in your free monitoring tool that you got from code.erikdarling.com. And you might start saying, Hey, uh, it’s, it’s got the block process report and it’s got the XML deadlock report in here for me. I can, I can figure out so many things now. All right. So that’s, that’s what I would do if I were me. And if you were me, that’s what you would do too. What an amazing coincidence that is. All right. Let’s see here. What tuning advice do you think does more harm than good overall? Uh, I’m going to talk more in depth about this in another video, because there’s a lot to say here. Uh, I think, um, looking at dumb metrics is, is harmful because a lot of the dumb metrics, um, they don’t really show you what the problem is. And so you end up doing things in sort of a scattered way that don’t address the problems you actually have. I think that’s a, probably one of them. Uh, you know, other stuff like, I just had a covering index, you know, that, that whole genre of dummy. Ah, I hope you fall off a building. Anyway. Uh, yeah, there, there’s a lot of it. Uh, but I’ll, I’ll, I’ll, if you don’t mind, I’m going to park this one and we’re going to, uh, do a longer video about that. I’m going to even write something. All right. Why does SQL Server sometimes make obviously dumb choices and how do you reason about them? Well, there’s, there’s a reason for everything. If, if you’re, if you’re, if by dumb choice, you’re talking about, uh, queries, well, it’s all down to costing, right? There’s only, there’s only so much time and effort that an optimizer can spend figuring a query out, uh, things that may be obvious to you as a human being who has vast quantities of time with which to explore various aspects of the underlying data and the indexes and, uh, many other things involved, uh, that, that make up, that comprise or compose. I forget which one.

One of those, uh, that, that, you know, make, make, make your system a big circle. Uh, they, the optimizer can’t look at all that stuff very quickly and come up with a query plan for you. It’s, it’s on, it’s on a timer. It’s on a budget. Well, not exactly a timer. It’s on, it’s on a budget.

Um, and it doesn’t want to spend a lot of time figuring like, you know, coming up with a query plan. And so sometimes, uh, you don’t get the best possible plan. Uh, sometimes it is, uh, that SQL server, uh, explored a path that you think is, or that you, you might observe is much faster, uh, but it costed that path out for some reason or another. And other times you might just find that the optimizer, uh, exhausted its budget and did not get to that obviously good path that you have surmised from looking at things, maybe testing the query with various hints and all that other good stuff that, that you do because you’re a smart person with unlimited time on your hands and the optimizer is something that has to spit out a query plan quickly so that management doesn’t start yelling about stuff, right? Cause if you’re waiting 30 minutes for a query plan, you probably wouldn’t be psyched. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you off in tomorrow’s video, uh, where I’m going to provide some technical details, uh, that I’ve been, it’s a, it’s a video that I’ve been meaning to record for a while that, uh, I just got around to you. So some technical details about something, well, something special.

Anyway, goodbye. Enjoy yourselves while you can. All right.

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: Time Zone Confusion

Posted on by Erik Darling

Learn T-SQL With Erik: Time Zone Confusion

Chapters

Full Transcript

Erik Darling here with Darling Data. Coming to you late in the day. I had a very fun problem that I had to deal with earlier, and that prevented me from recording according to my usual schedule, but it will be in a written, a formally written blog post tomorrow, you lucky people. Coming to you live with my Jermaine Dupree bobblehead, Atlanta Braves bobblehead. I did not attend this game. I had to wait until I found it on eBay, but now I have my very lucky Jermaine Dupree, Atlanta Braves bobblehead, who will be guiding me through this perilous video, where we’re going to talk about time zone confusion. This material, like a lot of the other videos that I do, comes from the larger corpus of Learn T-SQL with Erik material. You can buy it down in the video description. You can get a hundred bucks off the full course, which is a fantastic deal compared to other training, because you get this for life. Your entire life. Think about how long and arduous your life is going to be. And think about how more long and arduous your life will feel without proper T-SQL training. Ah, it’s, it’s crazy. You ought to buy it. Small investment, make your life feel less long and arduous. I think anyway. Anyway, down in the video description, on top of the link to purchase this very course, you’ll find other helpful links should you choose to interact with me in more interesting ways. You can hire me for consulting. You can buy even more of my training. I have lots more training than just this. You can become a supporting member of the channel for as few as $4 a month. You can, you can say, thanks, Erik. And that’s it. It’s just, it’s just a thank you. You don’t get anything extra. You just say thanks with money, because that’s if that’s how most people do it these days, right? It’s hard to say thanks with coupons. And of course, if you enjoy this content, and you think that other people might enjoy this content, please do like, subscribe, tell a friend, all that fun stuff, so that I feel some sense of obligation to keep recording this. Otherwise, I don’t know what I’m going to do. Just sit around and talk to robots all day.

If you need free SQL Server and or Postgres, including Aurora Postgres database performance monitoring, boy, do I have a deal for you. Steal of a lifetime. Totally free, totally open source monitors both. It’s a grand time for everybody. And does all the stuff you would want it to do. It’s got fancy WPF Windows dashboard. It’s got a fancy web viewer dashboard. You can you can make custom views and stuff. It’s got MCP tools. It’s the works really. It’s amazing what I’m giving away here. And it’s amazing how much money you can save switching to this from your current crappy SQL Server and or Postgres, including Aurora Postgres monitoring tool.

Just think, imagine how happy your company will be if all of a sudden you were like, hey, I’m going to save you many thousands of dollars every year, and we’re going to have a better monitoring tool where we can actually figure things out. And people aren’t just sitting around staring at it like idiots. It just might happen for you, right? Go to code.erikdarling.com, the performance monitor repo. Everything will be explained there. There’s a wonderful reading file. In just a couple short months, I’ve got to book my travel. I will be in Seattle, Washington from November 9th to 11th at Pass Summit West, taking place this year at the Hyatt Regency, Seattle. I hope they have a good buffet, I think.

I won’t be staying there, but I will be presenting there. Never stay in the hotel where the conference is. It might seem like a good idea at first, but it’s not a good idea. That’s why when people follow you to your room, creepy. Anyway, let’s drink some beer, pitchfork some crows, and I don’t know. Buddy, I’m going to chip your teeth on that corn. I’m going to tell you. All right. Anyway, so when it comes to time zone stuff in SQL Server, one thing that is important to note is that SQL Server does track time zones, and it uses the Windows registry. It got better at this, but it still uses the Windows registry for time zone things.

And one thing that you might notice if you ever look at the sys dot time zone info. Now, there’s no DM in there. I put that into DMV names frequently for no apparent reason. All it tells you is the name, the current offset, and if that is in daylight savings time. There’s just standard time. There’s no daylight times listed in here, right? It only tells you standard time, current offset, and if that current offset is being respectful of DST.

Who knows if this will change if America decides to get rid of one or the other? I forget which one we’re getting. I can’t remember if we’re getting rid of the good one or the bad one. It’s tough to keep up with the news these days. But lots of other places have changed this too over the years. And depending on when you started logging time zone information, it could be right, wrong, or somewhere in between.

I did some research. I think there was maybe a Wikipedia article on all the time zone changes over the years. And, boy, people just keep messing with it. I guess it’s just a fun thing that no one can agree on whether to switch times or not. Personally, I’m not a fan of switching times. Not only because it’s confusing and because I still have things that I have to set manually.

Like the watches that make people mad and the times on the microwave and stove, which are not smart appliances, apparently. But anyway, I suppose we should run a query here, shouldn’t we? So if you’re doing DST handling, you do need to consider all of these things, right?

So let’s look at this query, which is, I don’t know, this query is actually a little bit long for my tastes. Eh, look at this thing. 50 lines? Something like that. So at various points in the year, various times in the year, you will have slightly different offsets and whatnots.

But UTC at least stays nice and faithful to what time it is, right? You can’t have a UTC that switches times on you. That would not be fun. This demo wasn’t particularly interesting, but it sets us up to talk about more interesting things later.

Like certain times that never happened and in certain times that happened twice. And if you’ve ever scheduled SQL Server agent jobs and they’ve ever run around a time switch, you’ve felt this pain. So again, UTC time zones just don’t have a daylight thing, right?

They just like, is currently DST? No, because they just don’t. So that’s one reason, again, in favor of storing everything in DST, because if you’re using UT, sorry, storing everything in UTC because there’s no DST.

And so you save your, you diminish your problems by at least two hours on two days a year, depending on, I guess, it depends. Does everyone switch clocks at the same time? I don’t think so.

But DST does create two problems. When you spring forward, the clock jumps immediately from 2 a.m. to 3 a.m. So the hour between 2 and 3 is like it never happened, right?

Back when yours truly was a bouncer in the clubs, this was a confusing time for people. As is the fallback where you might run into an ambiguous time, right? Because is it 1 o’clock or is it 12 o’clock again?

Like, what do we do here, right? It’s nuts, right? So when we spring forward, right, the first 2.30 never existed on 2026.03.08. And so at time zone, just hands you 3.30 to 4 instead, right?

It doesn’t error. It doesn’t say, ah, hey, careful here. It just says, hey, you’re an hour ahead now. Congratulations, right?

So let’s say that the date time that we wanted was this one. Where is my draggy thing? There is my draggy thing. Let’s move you up a little bit. Actually, you know what?

That wasn’t a perfectly good place before. Why did I mess with anything? I’m an idiot sometimes. So this is our date time that we’re asking for. 2026.03.08 at 2.30 in the manana. And when we say, hey, SQL Server, what time is that in Eastern time?

It says 3.30. 2.30 doesn’t exist. I’m sorry.

It’s not here. It might be somewhere. It’s not here, right? So just a fun little thing that might happen to you. Well, one hour and two days a year. One hour, one day a year.

But the second day of the year is even scarier, right? So 1.30 a.m. happens twice on 2026 1101. That’s right around past.

I’m going to have two mornings where I’m confused. So what do you name the second 1.30 a.m.? What do you call a time that you’ve already experienced? Is this just deja vu?

I don’t know. Quite frankly, confused by the whole thing, right? But we have the same local time with two actual real moments in time that happened. And if your table stores that same naive local value, those two rows are indistinguishable forever and ever, right?

You better have some unique constraints, buddy. You better have them, right? So if we run this query again, very long query.

Look at the size of this thing. Gigantic, right? Ah, we got to shrink that in a little bit. Let’s bring that in for a nice hug. Our local time, right?

2026 1101 at 1.30, right? And we say, ah, what about at Eastern time? The first time it happened, right? That’s 1.30 minus 4.

And if we look at that time in UTC, right? What do we see? Well, that’s 5.30 plus zero. Okay.

That seems sensible. But then it happens again at 1.30 minus 5, right? And in UTC, ah, man, you’re going to make me sweat and do all this column clickery, jaggery stuff. Do it!

Now, all of a sudden, it happened again at 6.30, right? So, in Eastern time, when the time switch, it happens once at 1.30. Oh, sorry. It happens once at 5.30 UTC, and then it happens again at 6.30 UTC.

Time was a mistake is really the big deal here, right? But once that transition is behind you, the ambiguity, well, the ambiguity of the moment is gone, but the ambiguity in your data might persist forever, right?

So, you know, once that moment is over, all of a sudden, everything seems back to normal, but the data in your tables might not be back to normal. One other thing to note about at time zone is because of all this foolishness, it is non-deterministic, right?

So, if we try to create a table, and we try to say, we’re going to get ahead of this time zone stuff, we’re going to have good performance, we’re going to stay on top of time zones, and we try to create a persisted computed column, we can’t do that, right?

Because it’s non-deterministic, right? It’s not going to happen for us. So, because it’s not deterministic, that means that, though we can add it as a non-persisted column, that means we cannot index it, because this is also non-deterministic, which is a real, real sad state of affairs.

We can’t even have deterministic time in SQL Server. What kind of fake database is this? Boils the blood.

Anyway, it’s time for me to go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s written blog post about query store stuff. You’ll love it.

Or hate it. I don’t know. I don’t know what you’re responsible for. But goodbye from me and Jermaine. Alright. Have a good one.

Going Further

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

SQL Server Performance Office Hours Episode 89

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 89


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and thanks for noticing, I did just take a shower. Anyway, it’s time for Office Hours, where I answer five questions from you happy, lovely people, and hopefully make your lives better. So, we’ll do that. Down in the video description, as always, you’ll find very, very helpful links for you. To interact with me in various ways that will be advantageous to the both of us. You can ask me Office Hours questions, down there. You can purchase training from me, which is great training, at a very reasonable price, and you get it for life.

The rest of your God-given life, it’s yours, in your hands, forever. You can also hire me for consulting, which, I mean, if you want to do that for the rest of your life, too. Who am I going to argue? I argue with you. That would be silly.

And of course, if you enjoy this content, please do like, subscribe, tell a friend, and all that good stuff. My free database performance monitoring continues to grow, get better, improve. I am fully reworking, right now, the analysis engine.

SQL Server always had it, but I wanted to rework some of the stuff in there. And as part of that, I am also reworking the analysis engine for the Postgres side as well. Not reworking, implementing, porting over the SQL Server analysis engine to Postgres, the Postgres data collection as well.

So you should see that coming up in the next release this week. Big things afoot here at Darling Data. If you’d like to try it out, go to code.erikdarling.com.

It’s the performance monitor repo. Again, totally free, totally open source, doesn’t require you giving me an email address or any of that stuff. And it’s got all of the best stuff that you would want, desire, stuff that you might not even know you wanted in a monitoring tool.

It’s absolutely phenomenal piece of work. My only remaining speaking engagement of the year is Pass Summit West. That is happening in Seattle, Washington, November 9th through 11th.

I’ve got a pre-con and a regular session there. Pre-con is T-SQL that doesn’t suck. We’re going to teach you how to overcome the dumbness of the robots.

And then I’ve got a regular session called Read Committed is Cruel by Default. And it’s about my least favorite isolation level. But for now, it is still September.

We are still wiping the sweat from our shiny visages. Under carriages and trying to just make it through the day. So let’s go answer these questions.

Well, hello, Eric. Hello, howdy. How’s it going? In our application, in every update, we drop create all procedures and triggers. That’s, you know, you can alter them.

You could do it that way. We do not use QueryStore. What?

God’s green earth is wrong with you. Maybe you don’t use QueryStore because you’re on an older version of SQL Server. I don’t know. Could we trust Performance Monitor or it may get confused result-wise with the drop create thingy? No, it just resolves the object IDs when it collects stuff.

So it’ll figure out your procedure and object names based on the ones that come in. Assuming that you’re actually making changes to procedures and triggers. No, actually, no.

That’s like all, that’s either, yeah, it’s all like query hash, query plan hash. So like if you make changes and stuff generates new plans, then it’ll, you know, it’ll re-register those as new. But no, it doesn’t get confused so easily.

All right. Next question. Is it ever appropriate to put ketchup on a hot dog? Absolutely not.

If you put anything other than mustard on a hot dog, I don’t, I don’t want to know you. All right. Uh, why does SQL Server sometimes switch between good and bad plans every few hours? Well, uh, cause you, you lost a plan.

The plan cache is not, not a very, uh, dependable or reliable place in SQL Server. It being an ephemeral in memory construct, all sorts of things can happen, uh, that make plans disappear. New plans arrive, uh, show up and give you a bad time.

Uh, it could be auto stats updates. It could be manual stats updates. If you’re being aggressive with them, temp tables are a frequent reason for that. But then all sorts of other internal mechanisms and like, like memory pressure may, uh, relieve your plan cache of query plans.

And then SQL Server is forced to compile one on the next go around. And that plan may not be great. Um, I see a lot of, I see a lot of that problem for, uh, the ascending key problem, but, uh, you know, the initial compile time parameter, uh, I don’t know.

Maybe it could, maybe it’s good for that initial compile execution of the query, but, uh, further executions do not do so hot. So, um, that’s usually what I see. Um, if you have, if you have query store, unlike, unlike this person who says we do not use query store.

Um, you might try forcing plans that you know, love, and, uh, are familiar with. Uh, one could also make a reasonable attempt at using plan guides to stabilize things. Um, if the problem is around temp tables, you could try the option keep fixed plan hint.

Uh, otherwise, tune your damn queries. Or hire me to do it. That’s, that’s my job.

Uh, let’s see. Is it safe to leave query store always on and high throughput systems? Uh, for me, that depends a little bit more on, uh, plan volatility than high throughput. Uh, you could have very, very high throughput systems that have, uh, very, very stable execution plans.

And, uh, not see too, too much overhead. Um, if you have very high throughput systems that generate a lot of new query plans. Uh, and you’re constantly having to write new XML gobbledygooks into the, into query store.

Uh, then the overhead does increase. Uh, for me though, uh, the, for me, query store collection has, I mean, it’s not going to say never. Of course it’s had some stuff over the years.

But, um, for me, it’s more query store cleanup and internal maintenance that I see, uh, having problems in SQL Server. Uh, on top of that query and query store data, uh, exposes a lot of plumb foolishness that I don’t particularly care for. But, uh, in general, I like query store.

I use it, use it a lot. I think it’s a good thing. Uh, wish that it would get some of those rough edges cleaned up. Uh, what kinds of performance issues only show up in cloud environments?

Well, uh, I don’t, I don’t know about cloud only. Um, I think very specifically, uh, I mean like, eh, you can underpower. So, like, I don’t even know why you would put SQL Server on that though.

Like, like Amazon has like a T series of, of VMs that, uh, or, uh, EC2 instances that, like, uh, throttle CPU and stuff. I’m sure Azure has some of those same, same types of things. Uh, you don’t really want to put your SQL Server workload on a throttle VM though.

That’s probably not a good idea. Um, usually, uh, for me, I think a lot of it would be IO stuff. Um, cloud storage is, uh, often, uh, quite underwhelming, especially in Azure.

Uh, Azure storage is, uh, almost always a nightmare. When you look at it, even in the business critical tier, there are very, very strange, uh, things that happen. Uh, um, it’s rather unfortunate, but I don’t, I don’t think that’s cloud only.

You could have really bad IO on-prem. Um, I, I think probably the, the, uh, the biggest potential for cloud only stuff would be if you are using, uh, like an Azure managed service, like, uh, Azure SQL database, hyperscale managed instance, something like that, because, uh, you, you are essentially guinea pigging for Microsoft.

They might release a new feature. They might change something under the covers, uh, including your hardware surprise. Uh, and, and, you know, uh, you might, you might hit some oddities in there, uh, as, as new code hits, uh, your database that is maybe not as well tested as you would hope.

Um, but as far as like cloud only stuff, um, I can’t really think of anything that, that, that, like performance problems that I’ve only seen in the cloud. Uh, you know, I’ve certainly seen some performance problems get exacerbated by, uh, by the choices of cloud hardware, uh, you know, the underlying infrastructure stuff. But I don’t, I don’t think that I’ve, I’ve ever noticed anything in the cloud that’s like, wow, that could only happen here.

All right. I’m going to ramble long enough. I’m tired.

I’m going to go do something that makes me untired. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I’m going to talk about some performance monitoring stuff. 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.

Microsoft Executes Code Against Managed Instances That Doesn’t Follow Basic Security Practices

Posted on by Joe Obbish

I’m the kind of guy who logs all of the errors that occur within SQL Server, so it was easy to notice when some unusual code that we didn’t write was throwing errors. The code was executed under a client app name of “DMVCollector” which sounds kind of sinister if you ask me.

The Code

IF OBJECT_ID('tempdb.dbo.#memory_optimized_tables_internal_attributes ') IS NOT NULL
    DROP TABLE #memory_optimized_tables_internal_attributes

-- Create the table with the proper schema
SELECT TOP 0
    newid() as logical_database_guid,
    db_name() as logical_database_name,
    db_name() as db_name,
    object_id,
    xtp_object_id,
    type,
    type_desc
INTO
    #memory_optimized_tables_internal_attributes
FROM
    sys.memory_optimized_tables_internal_attributes

-- This is a DB-scoped DMV. Iterate over all user databases (id > 4) and collect the data.
DECLARE @name sysname;
DECLARE @logical_database_guid UNIQUEIDENTIFIER;
DECLARE @logical_database_name sysname;
DECLARE @database_id int;
DECLARE @SQLString nvarchar(max);
DECLARE DbCursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT
    PS.logical_database_id [logical_database_guid], D.name [name], D.database_id [database_id], DB_NAME(D.database_id) [logical_database_name]
FROM
    sys.databases D join sys.dm_hadr_fabric_partition_states PS ON PS.database_name = D.name

OPEN DbCursor
FETCH NEXT FROM DbCursor INTO @logical_database_guid, @name, @database_id, @logical_database_name

WHILE @@fetch_status = 0
BEGIN
    SET @SQLString = N'INSERT INTO #memory_optimized_tables_internal_attributes
        SELECT '''
        + convert(nvarchar(100), @logical_database_guid) + ''', '''
        + QUOTENAME(@logical_database_name, '''') + ', '
        + QUOTENAME(@name, '''') + ',
        object_id,
        xtp_object_id,
        type,
        type_desc
    FROM '
        + QUOTENAME(@name) + '.sys.memory_optimized_tables_internal_attributes'

    BEGIN TRY
        EXEC (@SQLString)
    END TRY
    BEGIN CATCH
        -- Swallow the exception.
    END CATCH

    -- Move to the next database.
    FETCH NEXT FROM DbCursor INTO @logical_database_guid, @name, @database_id, @logical_database_name
END

CLOSE DbCursor
DEALLOCATE DbCursor

A Quick Code Review

  1. The code comment says “Iterate over all user databases (id > 4) and collect the data.” but that wasn’t implemented.
  2. I’m not familiar with sys.dm_hadr_fabric_partition_states, but the join clause PS.database_name = D.name typically will compare a name to a GUID. Perhaps PS.database_name = D.physical_database_name is what was intended.
  3. It feels odd to return both sys.databases.name and DB_NAME(database_id), but to be fair, maybe there are instances where those can be different.
  4. The dynamic SQL query throws an error due to this part: ''' + QUOTENAME(@logical_database_name, '''') + ', '. A single quote is added to the beginning and quotes are added around the name, so the result is ,''msdb',
  5. The only reason the above error occurs is that the code clumsily tries to build its own protection against sql injection and special characters with EXEC() instead of sp_executesql with input parameters. Can we please move past 1997 coding practices?
  6. Instead of swallowing the error, maybe get some feedback that it occurred?

Final Thoughts

I understand that going into a cloud platform means giving up full control and that cloud vendors will always be snooping, but I would appreciate it if their snooping code followed basic security standards and actually did what was intended instead of throwing errors. Can Microsoft please do better?

Free Database Performance Monitoring – The Web Viewer and Custom Views

Posted on by Erik Darling

Free Database Performance Monitoring – The Web Viewer and Custom Views

Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we, by we I mean me and Batsmaru here, are going to talk about my free database. And I have to say database now, it’s not just SQL Server, since it gained the capability to monitor Postgres and Aurora Postgres as well. The thing is, titling this stuff is like, Free SQL Server and Postgres is it Aurora Postgres, it doesn’t roll off the tongue. So, free database performance monitoring. I can’t see a world where I start bridging into, like, Postgres or, I mean, it’s a bridge into Postgres, MySQL or Oracle, or, you know, some other obtuse, obscure database. But, you know, I think SQL Server and Postgres are probably good enough for me for now. Don’t ask about MongoDB monitoring. I, I, even I have morals and standards. Don’t, don’t give me that. But, uh, I’m going to do this a little bit backwards. Uh, and because, uh, doing a video of an install, uh, turns out not very interesting. Doing a video, doing videos about the stuff you get after you do the sort of, like, boring run of PowerShell command install, much more interesting. So, today we’re going to look at the web viewer for this. Um, If you, if you recall, uh, maybe the earlier versions of the, the, the light and the old full dashboard thing, they only had the WPF viewer, which was very Windows-centric. Uh, moving beyond a Windows-centric view of the world, uh, it turns out web viewers, wonderful for a lot of people. And you can do a lot of stuff in a web viewer that you can’t do, uh, well in, like, a WPF app. So, anyway, down in the video description, you will find all sorts of helpful links to interface and interact with me.

Uh, where you can, uh, hire me for consulting, purchase my training, become a supporting member of this fine YouTube channel. Uh, ask me office hours questions. I do those every Tuesday. If this is your first time here, I do those every Tuesday. Answer five community-submitted questions. Uh, and of course, please do like, subscribe, and tell a friend. Uh, all of the, the helpful links are down in the, your video description. If you would like to take a look at this free SQL, free SQL Server and Postgres monitoring tool, uh, I, I half fixed this slide. The, the headline is fixed, the, the byline is still not fixed, or whatever that’s called. Uh, uh, again, totally free, totally open source, no email signup, no phone home. I don’t try to keep in touch with you for any reason. Uh, unless you want to pay me, that’s different. Uh, but it gets all the stuff that you would care about from, uh, for, from, your servers in order to, uh, monitor, uh, monitor and even troubleshoot, uh, performance issues. Uh, if you are a particularly robot-y type person, as many people are these days, uh, there are built-in MCP servers that, uh, allow you to interface and interact with your monitoring data, much in the same way that you could interface and interact with me, uh, but they can’t hug you. Uh, and you can just, you know, have the robots sort of look at that collected data and, uh, tell you the wrong things.

It’s fun. Anyway, uh, I am speaking at Pass Summit West. I should make this more colorful. I need to get some stuff in here. Uh, I started to mess with this slide and then I got bored. Uh, November 9th through 11th, I have a pre-con there and I have a regular session there. It’s going to be a good time. Uh, this slide is going to be much better in the next video, uh, now that I’ve realized that I forgot to finish working on it. Anyway, let’s go talk about, apparently reaping time has come. We got our beer, we got our size and pitchforks. We got, we got our crows flying. We got, yeah, we’re having a nice time. Anyway, uh, over to the web viewer, right? This is, uh, what you see when you get into it. Now, uh, when, when you say the words web viewer, a lot of people will be like, ah, man, and this, that it does refresh. It blinks when it refreshes. It’s not, it’s not you. You’re not having a brain episode. It refreshes and blinks itself. Uh, when you say the words web viewer, uh, security people tend to get a little worked up, right? Cause they’re like, well, who, who can view it?

So this, the, the way that this works, um, um, the, the, I’m implementing OIDC for this thing, but, uh, you, you also get a bearer token with this and the bearer token, uh, controls, uh, not only, uh, what, like, you know, who gets to see it, but the level of access that they have. Uh, there is like an admin, uh, uh, set, uh, admin token where you can get in and do whatever you want. And then there’s a read only token. Uh, read only is a little bit loose because you can still create custom views with the, the read only token. Uh, but you can’t change like monitoring settings and stuff. You can’t like mess with other stuff, but this is kind of what you get, uh, out of the box. Uh, you get a fleet overview. So this shows you, all right, boinky. This shows you kind of what’s going on. Uh, you, you, you might notice here that I have not only, um, uh, my, my SQL boxes, but I also have, uh, these Postgres boxes being monitored. Uh, these, uh, things are not very active at the moment. Uh, cause I don’t, I don’t generate a lot of Postgres workload here.

All of the Postgres monitoring was done by sort of, um, dog fooding at client sites. So they were just like, yeah, go ahead. Uh, want to monitor Postgres? Yeah. And build, use ours. Take a look, see what you can get. So that’s what I did. Uh, so, uh, get that. Uh, this is the, the, the thing is, is, is, is you see it when you first walk in. Uh, you’ll see SQL Server 2025 needs some attention. I’ve got HammerDB running on SQL Server 2025 at the moment. So there’s some stuff going on. Uh, but the rest of the things are kind of quiet. Uh, there is also, uh, reasonably good availability group monitoring.

Um, if you have one of those crazy, uh, you know, uh, you know, distributed AGs, uh, life gets a little trickier. So, uh, not quite there yet, but here, uh, you, if you have a normal-ish AG, uh, I can tell you all sorts of things about it. Uh, this fleet sweeps thing, uh, this sort of goes through and gives you, um, it, that, uh, whatever cadence you desire, uh, a sort of overview of what’s going on with your entire, uh, fleet at once. So, like, how this changes throughout the day. It’s like, you know, reports and it says, hey, things got weird since the last time we looked.

Uh, there are all sorts of, there’s all sorts of alerting that you can do with the monitoring tool. Generally, it can go to Slack, it can go to Teams, it can go to PagerDuty, uh, all those, the normal things that DBAs sort of rely on to get alerted for stuff. Um, and then, uh, let’s see, let’s just go down to the server that is currently in the red. And we can, we take a look at this and we will see that we have some spikes. Uh, we have our overview here with all this stuff going on.

We can sort of get a, get a sense of what the server is under the covers. Uh, maybe I need to install a CU or something. Um, and then, you know, sort of like, you know, what’s going on in the server? We’ve got some IO latency, we’ve got some blocking, we’ve got some other stuff happening. Uh, you know, from the web view, I can’t do as many interesting things with the web view as I can, uh, with the WPF viewer as far as, like, interactivity goes, but the web viewer does give you a great way to view what’s happening.

So we get wait stats, we get CPU, we get memory, uh, we get blocking file IO, you know, the queries that we’re running, how the server is configured, if anyone’s changing stuff, um, you know, perfmon sort of activity on the server. Um, uh, system events from the, uh, from various, uh, views within the server to look at CPU and other sort of overall server health stuff, system health event type stuff. Uh, and then, you know, something that sort of tells you about the collector health in general, because what good is a monitoring tool if it doesn’t monitor itself a little bit and tell you if things are spitting up, right?

Because, you know, like, ah, everything looks fine. Oh, wait, it’s not collecting anything. That’s not right. That’s not a good time. So, uh, that’s what you get sort of from the web viewer experience. Um, down here are the, a couple of custom views that I have. Uh, this one is, uh, that I put together. This one is showing me how much resources that HammerDB, uh, queries are using and what they are specifically responsible for. And down here, uh, here’s another fun one, uh, the HammerDB TPCC workload stuff going on. Oh, look at all these deadlocks. You can see all the things going on in here. Then these, these are custom views that I created, uh, just within the web viewer itself, putting those things together. Uh, there’s a sort of server health one in general that just sort of, um, you know, puts together a, um, you know, it’s like sort of whatever, whatever metrics you want to mish mash together. Like I put together like the, the sort of bigger dashboard, the grand overview of everything up there. If there’s a bunch of stuff that you make sense to you, for you to look at together at once for a server, go ahead and mish mash it in together here. When you want to create a new view, just go into the new view, choose whatever stuff you want to put in there. Uh, you know, you can add panels, you know, you can do all this fun stuff, right? And you can measure all the cool, all the stuff you want in your SQL Server, highly configurable. Um, I tried to, tried to make this as sort of open-ended as possible, uh, to allow you to, uh, create whatever views make sense for you to keep track of what’s happening on your servers. But, uh, you know, if, again, this is, this is an open source tool. If there’s code you want to contribute, you’re welcome to do so. If you’re, if you’re one of those people who’s like, but I don’t know how to code, you, you can have your robot friends help you with stuff. Um, it’s, it’s fine with me. Uh, you know, if you have, if you find a problem, GitHub, port a bug, I’ll fix it, right? It’s a, it’s a beautiful thing, right? And again, totally free, totally open source, costs you nothing. I don’t want to bother you with stuff. Uh, but yeah, it’s, it’s, it’s been fun working on this, uh, highly gratifying experience. And, uh, uh, everyone who uses it loves it. That’s why it’s called Darling. All right.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I’ll see you in tomorrow’s video where we will, uh, talk about some boring time zone stuff. But then, uh, next week, I’m going to do some more walkthrough videos of, uh, what’s been going on and what’s been, uh, happening with, uh, the, the, uh, development of the monitoring tool stuff lately. 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: Time Zones and Offsets

Posted on by Erik Darling

Learn T-SQL With Erik: Time Zones and Offsets

Chapters

  • 00:00:00 – Introduction
  • 00:02:30 – Time Zone Basics
  • 00:05:15 – Using AT TIME ZONE and SWITCHOFFSET
  • 00:07:45 – Examples of Time Zone Adjustments
  • 00:10:10 – Time Zone Storage Best Practices
  • 00:12:30 – Time Zone and Clock Changes
  • 00:14:00 – Time Zone Query Considerations
  • 00:14:55 – Summary and Next Steps

Full Transcript

Erik Darling here with Darling Data, and this week and next are going to be just a couple video weeks for me. It turns out that after being traveling abroad, which is really just a trip because when you have kids and laptops with you, you’re just home somewhere that you like a little bit better. It turns out that you have a lot of stuff to catch up on when you get back, and that’s where I’m at. This week, actually, just earlier today, because it is 9-10. Well, it is September 10th. I just released a new cut of my free SQL Server and Postgres SQL Server monitoring tool. Yes, that even covers Aurora Postgres, the thing that everyone is falling over their willies for. We’ll talk about that a little bit more. I have a bunch of videos that I need to record to talk about where the monitoring tool has gone, all the new stuff in it, because there really is a lot. I’m going to talk this overview a little bit in this video, but this video is going to continue the Learn T-SQL stuff that I did not finish before I went away, and we’re going to finish up talking about time zone stuff.

So after this week and next, we’ll get back into the other stuff. Down in the video description, as per usual, as per my last video, you’ll see all sorts of helpful links. You can hire me for consulting, purchase my wonderful, fairly priced training, become a supporting member of the channel. You can ask me office hours questions for free. I will still be doing office hours. I might even do some extra ones. I was like, I’m going to clear up the office hours queue. I didn’t come close to that. I missed a bunch of stuff. There’s a million questions in there. So I still have to work on that. But as always, please do like, subscribe, tell your friends, tell your enemies, tell all the witches and warlocks that you know. I still have to fix this slide too. My free SQL Server and Postgres performance monitoring tool. You can read more about it at this link over here. There’s also a Postgres. I should probably just add a Postgres page.

I’m going to show you in a second. Totally free, totally open source, no email, no phone home. We are now focusing on the Lite version, which you can just crack open the executable, plug in a server name and immediately start monitoring. If you just, you know, have some stuff that you want to poke around with. But if you are looking for more of an enterprise-ish monitoring tool, then you would get the new Darling version. It is a headless window service connected to a Postgres and timescale backend. Doing that helps to keep things free.

You know, I got asked why I won’t use SQL Server as a backend, and it’s because I don’t want to cost people money. Sorry. This thing being free, I can’t say it’s free and then make you pay for SQL Server licensing. It can either make you steal from poor beleaguered Microsoft using Developer Edition for your monitoring tool.

But it runs 24-7 agentless, collects stuff from all your SQL servers. I’ve got a couple client sites with around 150-200 servers being collected on a single VM, and it’s handling all that well. I had to make some adjustments to some of the compression stuff in there to keep things running smoothly.

But all is looking absolutely, well, I’d say Gucci, but Gucci is just cheap crap these days, right? We don’t care for cheap crap around here. But our databases are feeling the end of summer.

I think, is that corn whiskey or Everclear or something? I don’t know. But anyway, just to sort of give you an idea, just a brief idea where things are. This is the WPF. This is the sort of executable viewer.

You can use this with Windows. And I’m going to talk more next week about the web viewer, because the web viewer for this is really cool and has a lot of neat stuff in it. You can build custom notebooks.

If you’ve ever used Datadog and you’ve made a notebook in Datadog that lets you graph and charts very specific things for specific servers or store procedures or queries or whatever, you can do that with my web viewer now, too. It’s pretty neat.

But, you know, just over here, stuff that I’ve added kind of recently, availability group monitoring. This is just a local Postgres instance that I have. It’s not doing a whole lot. There’s no load on it.

It’s just a sanity check and make sure everything’s working. But this is my monitoring tool, monitoring Postgres now. So good for me. And, of course, if you still just love SQL Server, maybe you don’t love when Windows Update kicks in and messes up your graphs. Thanks, Windows Update.

You’re a real, real sport. Then you can continue to monitor SQL Server performance for free with this as well. But we’ve got a lot of cool stuff in here. Anyway, let’s go talk about what I promised to talk about.

And I forgot to go back up to the top of this. So this video, again, part of the Learn T-SQL series, the full course is available for purchase down below if you want to learn a lot about T-SQL from, you know, someone who spends a lot of time writing T-SQL and writing T-SQL monitoring tools and stuff like that. I don’t know.

It might be a good idea. It might be a legitimate, reliable source of information. It might get fewer things wrong than the robots. It might even have the robots help tidy things up sometimes. I don’t know.

You know. I don’t want to get a tangent, but man. It’s been still not a fun time talking to the robots about performance. Still not a fun time.

They follow instructions well sometimes, but they say so many dumb things. Anyway, if you are finding yourself in need of working with time zones, there are some things that you are going to care about if you are working in a database that maybe doesn’t have any time zone information and you need to get it in there or something.

Or maybe you just started a new job where they cared about time zones and then your other jobs didn’t. So any, all kinds of crazy things can happen. But when you’re working with time zones, especially if you have dates that are not time zone oriented in any way, like for example, this one, this is just a date time too.

And getting used to the functions and how they work and behave can be a little tricky at first. It can be a little mind numbing. So if we run this query, what I want to show you is just we start off with a date time too that has no time zone affiliated with it.

And if we convert that to a date time offset, let me blow this up so it looks a little bit better, then it just gets assumed UTC, right? So like, you know, I’m here in New York City, so I’m in Eastern time. And so if it was 1430 Eastern time and I converted that and I said, hey, well, now you’re in a time zone.

SQL Server would be like, you’re UTC. And I’d be like, I don’t think that’s right. And so if you if you try to use switch offset, just converting, you know, a date time or date time to to a date time offset, then it will start subtracting hours incorrectly from it.

What you need to do is use to date time offset instead of just that so that you get the sort of correct time and time zone applied. Right. And then you can do the old double jump. You can say switch offset to date time offset and you can you can it’s sort of like doing at time zone twice, which we’ll talk about later.

But this will actually get you to the right, you know, just to UTC time, you know, because this is 1430 minus five, the correct one. Sorry, 1430 minus five, the correct one. And then we get that to UTC where it’s plus zero, but it’s 1930.

All right. And if you’re American, welcome to 24 hour clocks, I guess. I don’t know. Using switch offset correctly is, you know, you really do kind of have to start with something that already has a time zone attached to it.

Right. So if you look at these queries, right, and we run this stuff, we can see our original time is 1430. Right. And I guess Eastern ish time, I guess. Right now, that might be wrong, depending on daylight saving stuff.

Who cares right now? Videos are forever. Right. This could be any time of year. Right. I don’t know when you’re going to watch this. It’s eventually consistent.

But original time is 1430 and minus five hour from UTC time zone. And then we can get to Pacific time, which is minus eight, which just subtracts the correct time there. And then we can go to UTC and we can even time travel all the way to Tokyo, where it is tomorrow.

Right. We go from 115 to 116 because Tokyo is so far ahead. Right. And all of this stuff ends up, we just do this same instant check between these things just to give us just to make sure that we get everything correct. To date time offset is an interesting one.

And you kind of have to look at why where you attach stuff matters a bit. Right. So if we run this set of queries, right, we can we can not only so go back a little bit here. In this set, we use a mix of things.

Right. We can you can either do like you can either do a string like this where you tell it how many like what the the the hour offset is or you can even use this to do like minutes. Right. So four 80 zero. You can add 540 minutes.

Right. Subtract 480 minutes. Go to UTC by adding zero. So you can do, you know, like do the same thing just, you know, with with numbers across the board. Right. So it doesn’t matter how you do the attach.

It just like what matters the most is that you just get the numbers right. You know, some people will store and we’ll talk about this a little bit, too, is some people will store like the name of the time zone, which is probably the smartest thing to do, because then you can look up what the current offset is and you don’t have to keep track of it.

Right. It’s like daylight savings and all that stuff gets real messy. If you just have a table with like some initial offset, you have to go and change it. You don’t know what the time you don’t know what the actual time zone is like for, you know, for let’s just say Eastern Standard Time.

There are a bunch of other time zone names that have the same offset as Eastern Standard Time, just storing the offset. You don’t know exactly which of the time zones it is. You just know the offset that might be good enough for you.

But usually you want to know. And this is usually what people do when they need to get something right. So we’re going to look at the timeline here where we have learned T-SQL.

We have started drinking because we were on a really long trip. And then we have forgotten T-SQL. And if we run all this stuff, see that this is a pretty good, let’s just call this canonical way to figure out what time things were originally logged.

Maybe, you know, what the original log time zone was. And then, you know, usually you want to, usually you would, so usually you store like, if you’re smart and you’re using time zones, you store everything in UTC, you store the user’s time zone, you do the lookup, and then you come back and you figure out what the offset is, like what the hour difference is there, right?

But that is what this sort of brings up here. The one thing, actually I’ll talk about that thing tomorrow, but let’s say that we have a table of sales orders here, right? And we have, let’s say, two options, right?

You can say what city someone is in and their current UTC offset, or you can store the name of their time zone, which I know strings in a database were a mistake. I agree usually, but, you know, what can we do here? But when you want to do a query that, or rather when you want to query this data, having the name of the time zone stored is much, much smarter, right?

Because at certain points, you might be wrong by an hour because you are not keeping track of time changes, right? Time zones are one part of the problem, clock changes are another part. And so you might be wrong, I should go this way, by some number of minutes sometimes if you are only storing the offset and not the actual time zone name, right?

So if we, you know, use the time zone name here, then we can, like, this will just screw you up, right? It’s not a good time because we’re just using offset minutes, but if we are storing the time zone name, then we can, you know, we can send a time to the time zone name, right?

Even just stored in a column like this, and then we can send it to UTC, and then we can look at the wrong by minutes column that just shows us how, if you’re only storing the offsets, you can be very wrong as clocks adjust throughout the year. And, of course, what was that comment? Ah, there we are.

At time zone returns date time offset, so switch offset works on the result too. These two do the same thing. Pick whichever reads better to you, right? So we could also just do something like this, right?

Switch offset with the time zone name, and we get back presumably all correct results, right? So we can do at time zone, or we could do switch offset. Both of these end up being correct.

You know, whatever query form you like the best, do that. Just as I said in previous videos, don’t do this in a where clause, because that makes Windows API calls, and that’ll really be bad for you.

You won’t have fun with it. All right. That’s enough about time zones. Thank you for watching.

Hope you enjoyed yourselves. I hope you learned something. I hope you will pretty please with free cherries on top. Check out my SQL Server performance monitoring tool.

It’s absolutely free. It’s available on GitHub. Links and stuff are down in the video description. If you want to monitor a SQL Server, availability groups, Postgres, or a Postgres, it’s all here.

It’s all free. Anyway, I’m out of here. I will see you next Tuesday for Office Hours. All right. Thank you for watching. 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.

SQL Server Performance Office Hours Episode 88

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 88


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data. Boy, am I happy to see you. Another, I mean, you know, it’s so hard. After doing all those Office Hours episodes where I was in fun places, and then one where I got to read a bunch of questions from people who really don’t like me, I feel like, I don’t know, it’s a little bit of a letdown to just be sitting here doing this here. Maybe I should start, I don’t know, going in my backyard. I don’t want to annoy my neighbors. It’s the problem. I have a real aversion to annoying people publicly. Anyway, this is the time of the week and the time of the day where I answer five very important questions from between one and five very important people. I am unsure of who asks these questions and what their question asking cadence is. It could be any number of things. But anyway, down in the video description, all sorts of great helpful links for you to hire me for consulting, buy my training, become a supporting member of the channel, continue to ask me these Office Hours questions despite my distinct lack of exotic locales. And of course, you can always like, subscribe, tell a friend. I do like seeing the numbers go up in some facets of this channel.

Maybe. When numbers go down, that’s when I start getting worried. I don’t know. Anyway, I finally got around to fixing this slide. If you want free SQL Server and Postgres, including Postgres Aurora performance monitoring, I’ve got some of that for free. If you go to code.erikdarling.com, it’s pretty clearly labeled the performance monitor repo. So, you can download it and you can start using it. It’s MIT licensed. You don’t have to do anything with it. I’ll show some stuff when I’m out of the slide deck and before I go into the other thing. But yeah, all the important stuff that you want to collect to know about performance issues. And that’s about it there. It’s free. I make the same amount of money whether you use it or not.

But now it is time. We are officially Septembering. And because we are Septembering, I should probably unhide this slide for next time. So, I can say, hey, in like two months, past Summit, well, sorry, past Summit West. Not West. West is taking place in Seattle, Washington from November 9th through 11th. I have a pre-con there. And I also have a regular session there. And so, you should come to both. Otherwise, because that’s numbers going up for me.

And when numbers go up for me, look how happy I am. Isn’t making me happy important to you? It should be. Anyway, free monitoring for SQL Server and Postgres. You can see very clearly a mix of SQL Server. We have availability groups. We have various Postgreses. I shrank that window by accident.

And then, of course, SQL Server 2016 through 2025. Also supports Azure, VMs, SQL Database, Hyperscale, Managed Instance. You name it. I support it. I monitor it. I just don’t keep the Azure stuff in there because it costs money. Also, Amazon RDS, of course. All the major stuff. Gosh, you’re lucky to have someone like me in your life, aren’t you?

I think I want to repay me with thumbs up someday. Anyway, let’s answer some of these questions here. Let’s see. First one. Ah, my favorite person. It’s been awfully quiet in Paul Whiteland lately. As someone who seems to know him pretty well, is he happily enjoying retirement, secretly building the world’s greatest query plan, or should we expect him to pop up again someday?

Okay. So, I made a deal with Paul White that I would not ask him any dreary questions. We do talk frequently. And part of that is we get along about stuff outside of SQL. We just happen to meet via SQL Server, right?

We just get along pretty well in many topics and regards outside of databases. And so, when Paul decided to do his life thing, and I’m not comfortable telling everyone exactly what he’s up to. But when Paul decided to go out and do some life adventuring, I said, let’s keep talking, but let’s not talk about databases.

And we both enthusiastically agreed to these terms. I did have to insist on them. I don’t know if he’s going to blog again.

I think after many, many, many years of doing it, the man deserves a break. And, you know, maybe if something catches his interest in a new version of SQL Server or a new feature, you might see something, but I couldn’t tell you. All I know, and what I am happy to let you know, is that Paul is happily enjoying cruising around the wilds of New Zealand and doing very scenic things at the moment.

So, I don’t know. I don’t know. We’ll see.

Maybe, you know, at some point, you got to think, maybe he’s done enough, you know? Maybe the man has earned some respite in his life. I think he has.

All right. Have you found a way to have Nutanix? Jeez.

Nutanix. Are you kidding? Hyperconverged storage provide low disk response times. We keep getting 100 millisecond spike. Yes, by moving off Nutanix. Now, this is one of those things where, you know, I’m sure that you have done your due diligence in working with your Nutanix representatives and tech salespeople in order to align your system with best practices and all that stuff.

And, you know, I’m sure that there are people out there who use SQL Server on Nutanix and are happy with it. In my position, I just don’t talk to people who are in a very happy place most of the time. Most of the time, I talk to people who are sad, angry, depressed, confused, just don’t know what to do with anything that’s going on around them.

And so I’ve met a lot of people having some really, really bad times with Nutanix, you know, hyperconverged or not. And, you know, I think just past a certain point, that’s not the platform for you. You know, if you’ve already gone through the correct steps with your sales and tech people and you are unable to get satisfactory performance, it’s time to go.

So it’s sort of like when someone points me to a view and they’re like, it’s 4,000 lines long and it’s slow as hell. And I’m like, yeah, no kidding. Look at it.

It’s a junk drawer for all your worst sins and ideas. It’s every table in the database joined and, you know, it’s just a mess, right? You have picked the incorrect vehicle for this thing, right?

Like if you had a stored procedure instead of a view, you could express this logic in perhaps more sane, rational and easily, more easily optimized ways than you have chosen to express it. And it’s the same thing over and over again. If you have done everything within your realm of responsibility and you are not getting adequate performance, you are in the wrong realm.

All right. Is it ever worth chasing fragmentation numbers for performance reasons anymore? So I do think that it is worth checking in on page density.

I don’t think it is something that is worth automating. You know, you would like, you know, like the logical fragmentation pages out of order thing. You can’t, you just can’t talk me into caring about that.

But the page density thing, which can be affected by, which is affected by deletes and can be affected by updates, would certainly be something that I would check in on from time to time. And, you know, if you have very big tables with, you know, like weird page density issues, then sure, I would absolutely, I would absolutely say, you know what, throw a rebuild in that. And while you’re throwing a rebuild at that thing, you should apply page compression.

Just like do something good along with it. And the reason for that, and this is something that actually happened with the client thing that I was doing. I was helping a client of mine move indexes from the primary file group to other file groups.

And I didn’t look at, like, page density stuff beforehand because, like, it was immaterial to what I was doing. But there were some very, very large tables that shrunk appreciably in size when things were rebuilt, especially with page compression on the new file group. But I think most notably, there was a table that was around 1.3 terabytes that ended up being, I think, something ridiculous, something like 30 gigs or something.

There were a lot of, there was a lot of lob data in there that got cleaned up and stuff. But, you know, like, there were some other things going on with it. But I think every once in a while, sure, it’s worth checking in on the page density thing and, you know, perhaps rebuilding on the larger objects.

If only because, you know, especially when you double that up with applying page compression, you can end up with much, much smaller objects. And that’s a good thing for most servers because most servers don’t have enough memory to deal with the amount of data they’re carrying around. And so you take up less space on disk, you take up less space in the buffer pool, things are generally in a happier place.

So there are reasons to do it. I wouldn’t say that it would be like, you know, some, like, I wouldn’t call it like a general query performance thing. You know, if your queries are scanning large amounts of data, of course that improves.

But if your queries are seeking to data in a B-tree, then either one is not going to make such a profound, like, you know, it’s not going to make, like, fixing logical or page density fragmentation isn’t going to make a profound difference to those queries. But I think, you know, every once in a while it is worth checking in on that and maybe doing something about it. So why do some workloads completely fall apart under concurrency even though single user tests look fine?

Well, I don’t know. Why do you get, why do you drive somewhere so fast when no one else is on the road? Traffic’s not fun, right?

When you have competition for finite resources, and, you know, I don’t mean to get all Adam Smithy on you here, but when you have competition for finite resources, someone is going to have to win in that. Like, you’re sharing resources amongst many, many queries, some serial, some parallel, others taking up memory, others going to disk and trafficking things back and forth. That’s, of course, not, you know, going to contribute to queries that in isolation run perfectly fine, not running so perfectly fine anymore.

You know, that competition can show itself either worker thread starvation, query memory grant starvation, taxing the paths from servers to the disks. And, of course, you know, unless you are a real smart cookie and you listen to my advice and you use an optimistic or row versioning isolation level, well, you are going to have some pretty annoying blocking and deadlocking issues in SQL Server because read committed is a garbage isolation level.

So, the shortest answer I can give you is competition, resource competition, lock competition, all those things. How do you tell when memory pressure is coming from SQL Server or the OS or VM host? Well, if you’re goofy enough to have other stuff going on right next to your SQL Server, you should probably rethink that.

You know, the thing that I always try, the thing that I always look at, rather, the first order metric that I always look at is wait stats. And the reason why I look at those is because I can at least start to get a sense of a couple of things from a memory perspective. Memory pressure shows up in SQL Server, either at the query level is resource semaphore, that’s query memory grant stuff, or is any of the various page IOLATCH underscore letter letter weights.

And, you know, if you have resource semaphore waits going on, then what you’ll find is very large portions of your buffer pool are likely getting cleared out and swapped to disk, and then queries have to repopulate the buffer pool, and that’s when the page IOLATCH weights kick in. But even without resource semaphore waits, you can see rather high page IOLATCH weights because you might just have way more data than memory, and you might not be using page compression, and you might not have indexes well-tuned to your queries so that you can seek to smaller regions of them rather than having to scan the whole thing.

You might have very, very beefy indexes because of the data types that you’ve chosen, like large strings. You might be holding on to far more data than you are legally required to hold on to, or even professionally hired to hold on to. I don’t know, there’s lots of stuff that can happen out there, right?

And so for me, you know, it’s been quite a while, I think, since I’ve seen memory pressure coming from outside. You know, like every once in a while, I’ll see, you know, like, especially CrowdStrike. CrowdStrike does a lot of awful things to SQL Server, and, you know, so I’ll see, like, you know, some, like, antivirus or some other filter driver ballooning memory and sort of, like, messing with SQL Server, like, from within the box.

So if you’ve got that stuff going on, of course, look into that. You know, a lot of security people do not follow anything close to a best practice when it comes to excluding SQL Server there. But, you know, for me, mostly, you know, it’s just been so long since I’ve seen anyone doing anything too atrociously bad with either a virtualized SQL Server or with installing nefarious, well, installing things on the SQL Server that are obvious memory hogs, like, you know, like some Java application or IIS or something like that.

People still put CrowdStrike on there. In fact, it has dealt with CrowdStrike causing an AV exception on a SQL Server a couple weeks ago. So, anyway, mostly my investigations are things happening within the SQL Server.

And if you just truly can’t explain why SQL Server is experiencing memory pressure by looking at the activity on the SQL Server, then I guess at that point you could look outside of it. But at least in most of my recent experience, I would say that the memory pressure is coming from inside the SQL Server.

Anyway, it’s gone on long enough here. Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in another video this week where we will finish talking about time zone stuff in T-SQL. At least to the extent that I care to talk about it.

There might be other things to say about it, but I don’t care to say them. Anyway, thank you for watching.

Going Further

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

SQL Server Performance Office Hours Episode 87 – Questions From Haters

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 87 – Questions From Haters


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and I have returned home, unfortunately, and, well, time for office hours. This one getting done a little bit late on a Tuesday because, you know, long weekends is long, my friends, and, you know, sometimes, sometimes festive, I guess. But today’s office hours is a special one. This is a first edition of office hours where it is purely questions from haters. There’s some, some, some, some, some attempts to make your friend, your best friend in the world, Erik Darling, feel bad. And we’re gonna, we’re gonna answer those questions today. All right. Down in the video description, because it’s been a while since I had a PowerPoint to read from, down in the video description, there are all sorts of helpful links. where you can hire me for consulting, you can purchase my training, you can become a supporting member of the channel. You can ask me office hours, hopefully nicer office hours questions than the ones you see today. And of course, as always, please do like, subscribe, and tell a friend. This slide I have to fix, or I have to do something with, because very exciting stuff. If you’ve been keeping up with my free SQL Server performance monitoring tool, you may have seen recently, that it is not just for SQL Server anymore. Now it can also monitor Postgres and Aurora Postgres, which, I guess are popular out in the world. So we hit those now too. So it’s not just SQL Server, but it is the same wonderful, free brand of monitoring that SQL Server has just expanded to Postgres. There’s still some like sort of work that I have to do to make sure that everything is kind of equal in the world. The Postgres side needs the alerting and stuff that SQL Server already has. But we now monitor Postgres and Aurora Postgres. So all good things there. The link to check that out is still down in the video description. And because I was away for the month of August, you were all sadly denied the joy of the August database party image.

And so just going over this one, this database seems to have lost an eye. Probably double fisting beer by a fire. That seems like a good way. This, you’re missing your mouth entirely. I don’t know if that’s blood or wine, but it looks a little strange. And I don’t know, this chicken wing has an, this drumstick has an eye. So I don’t know, man. I don’t really know what that is. But this was my month of August database party image. It’s got some Stonehenge-y stuff back here. That’s kind of cool. Look at these flower headband things. That’s, man. How do I get invited to that party? That looks like a good time. I don’t know. Is that a tooth? Just chopping teeth up there. And then this is, um, oh, actually, was that July? I don’t know. I have to go through these. Uh, this, this is, I don’t know. Maybe this was August. Uh, I gotta figure it out. Anyway, let’s go answer these questions from people who don’t seem to like me very much.

First one. Put some clothes on. These videos where you’re in a tank top or shirtless are vulgar and your tattoos look retarded. Well, uh, first of all, sir or ma’am, uh, you did not even see a nipple in any of my shirtless or tank top videos. So, uh, your, your, uh, your assessment of vulgarity, I think, is a bit on the sensitive side. I think, I think you are overreacting to a little bit of skin that, uh, really wasn’t that, really wasn’t all that revealing.

Uh, as for the tattoos, uh, you know, uh, I’m starting to wonder if my mom left this comment because she says similar things about my tattoos. Uh, so, I don’t know. I, I, I like them kind of pretty well. Uh, I, I don’t notice them, I guess, as much anymore because I’m quite used to them. Um, but, you know what? Uh, I, I, I’m, I’m happy with the sort of overall visual effect.

Um, maybe they’re not all the best tattoos because I did start getting tattooed before tattoos were, like, good. It’s not like, and, uh, you know, some of my earlier tattoos were, were not done in the most legitimate or, uh, legal of settings. Uh, there were some kitchen table pieces in there.

So, uh, you know, maybe not all the best work, but I think overall, uh, I’m, I’m happy with the way that, that things have turned out. So, um, you’ll be happy to know, though, that I, I do have clothes on again. This, I, I, you, you still do see some skin and some tattoos, so hopefully this is not too much for you.

All right. Next question. Not even a question. Statements. These are, like, just statements from the haters. Uh, you and Brent show off too much. Him with the cars and you with the watches. My $10 Timex tells time just as well.

Well, I’m sorry that we did well. My apologies. Uh, but to, to, to, to, to, to, just to get to the root of this, uh, this, this final sentence over here is the interesting one to me. Uh, yes, your $10 Timex maybe does tell time just as well, but you don’t wear a watch like this to tell time.

You wear a watch like this to tell other people what your time is worth. So, if you would like an invoice for that, let me know. All right. Third one.

I don’t understand why anyone comes here to ask you questions when they have AI. Face it, you’re a has-been. Well, I’ll take being a has-been over a never-was any day.

Uh, I don’t understand why, okay, you, uh, you know what? Maybe, maybe I don’t either. Uh, that’s, but, that, that they still do it. In fact, I didn’t even get close to running through all the questions that are in my office hours sheet while I was away traveling.

Can you, can you, just, can you believe that? I mean, it’s a lot of questions on there still. And I just feel, I, I just was scrolling through them all and I, and I, and I noticed that there, there were five of them that were particularly bitter and spiteful.

And you, you, you, you were, you were one of those entries. Uh, I don’t know. Great.

You don’t get it. I don’t get it. But it keeps happening. I don’t know. Maybe I have good answers. Maybe, maybe people like the way I answer better than the, than the way AI answers. You don’t, I don’t know.

I’ve never, I’ve never actually done a poll on it. I’ve never done any market research on that. All right. Oh, look. Another totally unprofessional bad boy, in quotes. Consultant.

You would never make it in the real world. I don’t know what the real world is. I feel like I’ve done pretty okay. Uh, maybe the real world is not for me. Maybe the fake world that I, I traffic in is where, exactly where I belong.

Because that seems to be what allowed me to buy a watch that made someone else mad. So, I don’t know. All right.

Here’s another, here’s another banger here. Uh, why don’t you have AI generate better questions and answers for these? No one seems to have a new or good question anymore.

Uh, well, I’m not a cheater. For one. At least, at least not with these. Uh, these, I, I, there, there are lots of things that I am, I am happy to have AI do for me, uh, because it is tedious, mechanical, uh, or too far out of my realm of expertise to indulge in.

Uh, but there are certain things that I, I do still hold sacred and try to keep AI far away from. Um, you know, like, uh, when I, when I do write blog posts, uh, when I do write things, uh, I tend to, unless it’s like documentation type stuff, uh, I tend to keep, uh, AI out of that, but, you know, being handed a coffee here.

Uh, so, you know, that, that’s why I don’t do that here. Anyway, the mean-spirited people today. Thank you for watching.

Hope you enjoyed yourselves. You probably learned nothing from this, but, um, I had, I had fun. Anyway, 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 86 – Drinking Champagne in an Algarve Bathtub

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 86 – Drinking Champagne in an Algarve Bathtub


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here, with Darling Data, coming to you live from a bathtub, drinking champagne. That’s this office hours for you. Coming down to the end of the trip here, I didn’t end up recording anything in Kash Kash, because there was really no good place to record it where I felt okay doing this, because I don’t want to annoy people in public. Anyway, we’re going to clear out some more of this office hours question queue, because good lord, do we have a lot of questions. First one, from, well, I mean, I don’t know who any of this stuff is from. That’s great. I don’t have to know your name, and neither does anyone else. How are you feeling about managed instance? So, I get questions about Azure a lot, and I always try to answer them in the same way. Back in World War II, there was a saying that I would rather have a sister in a whorehouse than a brother in the Navy. And if you just replace Navy with Azure, you get the saying. So, that’s how I feel about managed instance. I would not curse anyone with it. I would not wish that on anyone. I don’t know how Microsoft managed a screw managed instance.

Azure SQL DB, Azure SQL DB, and all the other stuff so badly, but, yeah, no. All right. Next question. What are the most misleading perf counters people rely on? PoE? Any sort of queue depth? Logical reads? That’s another dumb one. How fast is a read? I don’t know.

How slow is a read? I don’t know. I tend to look at things that are… So, these are all sort of what I would call like second order metrics, right? The first order metrics would be things like wait stats or a query CPU and duration or other things that are a bit more directly indicate what you’re dealing with and the extent to which you’re dealing with it. All of the second order metrics are just a complete waste of everyone’s time.

You’re looking at something that is not directly measurable in a real way instead of looking at things that are directly measurable in a real way. And you’re using them for… I don’t know what. I don’t know. You people terrify me with the things you’ll cargo cult around.

Let’s see. How do you diagnose problems that only happen at very specific times of day? Well, that’s a great question.

I set up my free SQL Server performance monitoring tool, and I monitor the server. And then when things happen at a specific time of day, I have all of the stuff that happened before, during, and after the event. If you go to my GitHub repo, you can get my free SQL Server performance monitoring tool.

Bathtub and champagne not included. All right. Let’s see.

When is partitioning actually helpful for performance and not just management? You mean data management? Good question. You can also get partitioning. Usually when paired with columnstore. Clustered columnstore indexes.

When you set up partitioning correctly with them, correctly is an important word here, then not only do you get the usual segment row group elimination with your columnstore queries, but you also get the partition elimination of it as well.

So that is the one time when partitioning is actually useful. Let’s see here. Is query store wait stats reliable enough for real troubleshooting?

So, sort of, kind of. It’s certainly an interesting view of the data, and I don’t fault Microsoft for implementing it the way that they did. But there are all sorts of pre-execution wait stats that it does not track well.

So things like resource semaphore, query compile, resource semaphore, threadpool, things like that. They are invisible to query store because those are all things that happen before the query executes. So if you’ve got a lot of, like, pre-executions, and they’re not counting compile time because compilation metrics are in query store.

But if you’ve got a lot of other stuff going on in the server, then you might find that those wait stats are not being captured in query store. But for your usual stock and standard, what is this query doodling around with type wait stats? Yes.

However, and there’s a big however here, and it’s a sad however. However, the query store wait stats capture and cleanup mechanism in query store is very high overhead in my experience. And, you know, a lot of the times when I’m dealing with clients who have very high throughput systems, one of the things that I do, if they’re already using query store, then, you know, we’re seeing some observer overhead there, is to disable the wait stats collection.

Let the clean stuff up as it catches up with things, but stop capturing wait stats. If someone doesn’t have query store turned on, for whatever reason, maybe because they tried to turn it on in the overhead from wait stats was just too much, then I will usually say, well, we can just turn query store on and turn wait stats off.

Because a lot of the times the wait stats capture can be the most painful part of query store stuff. I forget if that was five questions or four questions, so I’m going to answer one more. This can either be the correct number of questions or it can be a bonus question, depending on how you look at it.

Let’s see here. What’s a good one? Why does SQL Server sometimes favor merge joins that look terrible on paper? I guarantee you they don’t just look terrible on paper.

There are very few circumstances where I prefer a merge join, especially in the batch mode world that we live in. Usually batch mode hash joins and even having to sort some data in batch mode is preferable for larger data sets than an order-preserving merge join would be.

There are some upsides to merge joins and stream aggregates and other order-preserving operators where across a query plan you don’t have to resort data at any point, which can be nice if you’re trying to avoid memory grants or trying to minimize memory grants.

But for the most part, especially parallel merge joins, they terrify me. Any sort of order-preserving operator around a parallel exchange, especially if it makes the parallel exchange acquire the order-preserving attribute, that can be really not a good time.

So for me, a merge join and like, or merge, merge, let’s expand that a little bit. Not just merge joins, but order-preserving operators in general in a serial execution plan if your goal and your aim is to preserve order and not have to resort data out of index order.

They can be useful for some things, but for the most part, boy, oh boy. But why does SQL Server choose them? It’s the same reason it chooses anything.

It costs things. It might look at the cost of resorting data or hashing data or all the other things and say, this merge join looks mighty cheap to me.

Those merge joins. Often more trouble than they’re worth, especially in a parallel execution plan. Anyway, that’s either five or six questions.

I forget. Again, it’s either a bonus or it’s not. I am going to continue to enjoy my champagne in a bathtub. Thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And, well, I probably won’t see you in your bathtub. Maybe you should watch this from your bathtub and then you can pretend that we’re just like Bert and Ernie or something hanging out together.

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 85 – Lisbon is for Lovers

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 85 – Lisbon is for Lovers


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, coming to you today from Lisbon. I’m not sure how much of the background you can see out there, but we’ve got a view of the replica Golden Gate Bridge and the replica Rio de Jesus Nero thing in the background. Also in the background, traffic and a train and an airplane. So again, no matter how far I go from New York, I never seemed to be able to escape city sounds. Except when I was in the Azores and the only thing allowed there was the ocean, which I suppose was a nice change of pace.

Anyway, we’re going to keep clearing out the office hours queue here. And we’ve got five more questions to deal with from my lovely, adoring fans who so far have been kind enough to not stalk me while on family vacation.

anyway I’ve never seen you suggest optimized locking as a solution to a problem do you dislike it no I like it just fine it’s just so few people are in a position where they can use it it there’s not a lot of point to recommending it right it’s like it’s SQL Server 2025 it’s you know I guess if you’re in you’re unfortunate enough to be in Azure you you might find yourself using it by accident. So if it’s solving a problem for you, great, but recommending it, you’re either there or you’re not. It also solves a very, I would say, a very narrow class of problems. Just as a small for instance, when I was redoing some of my locking material for a past webinar and for my session materials in Seattle coming up in November and what it actually broke exactly two of my demos where I had locked there was just a single primary key being locked. So I guess if your problem is with a single primary key being locked, well, there you go. I think the one thing that I do like about optimized locking from an outside perspective, and this is not because of the feature itself, is that the feature documentation pretty explicitly says this works best if you enable read committed snapshot isolation. So further legitimizing read committed snapshot isolation as the wave of isolation level future is, I guess, a nice side benefit to that.

All right. SQL Server sometimes runs better right after a restart. Why does that happen?

Well, do you sometimes run better after a night of sleep? I know I do. I’d like to get one of those sometime. You know, you clear a bunch of junk out. Who knows what was going on on that server?

Who knows how long that server had been up? Sometimes you clear a bunch of stuff out, you get some new plans and cache, SQL Server is feeling good about itself. It’s the same thing. Sometimes that works really well.

I don’t recommend it as a performance tuning thing. Don’t just restart your server every hour, but every once in a while, maybe patch things up. Maybe don’t, I don’t know. Restart your server, give it a fresh perspective on life, let it take a little nap.

sometimes that does work pretty well. How do you identify queries that are killing overall throughput but not necessarily slow themselves? I’ll usually look in query store for things that have a very high execution count because for me those are usually the things that just keep CPUs unnecessarily busy and give other queries some sort of fits when it comes to co-scheduling things.

So that’s usually what I look for. You know, like in there, you know, you might find some stuff that could use a little bit of help, but usually those queries are very fast.

And the options there are, of course, usually, usually, but not always things that you would want to control outside of SQL Server. You would either want to implement some caching for them, or maybe you would want to rate limit your various application APIs and whatever, just to sort of take some of the burden off of SQL Server and not let the queries execute as much.

Those are usually the ones that meet that criteria. you. All right. Why does increasing parallelism sometimes reduce spills? So, well, there are a few ways you could go with this. I would say that that is maybe generally not the experience that I have.

You know, because like, okay, so like in SQL Server, all queries start as serial execution plans. That’s when you get your memory grant. If SQL Server later chooses a parallel plan, the memory grant gets divided up amongst threads.

So, you know, if there’s very uneven parallel row distributions across threads, you can see spills. I would say the only time that that is sometimes different is if you increase max stop and all of a sudden SQL Server thinks oh well with max stop set to 8 instead of 4 the parallelism has greater benefit and now we’ll actually choose a parallel plan and sometimes the parallel plan that gets chosen might have some additional memory consuming operators in it that might increase the memory grant and then you might get like just a plan with a better suited memory grant to what you’re doing. It’s also possible that you know you might see plan changes around like you know you change MacStop you know SQL Server is like well new dot new plans.

You might you might get a plan that now has batch mode on batch mode in it. you know you might get just any number of things might change you know maybe I don’t know with all the memory grant feedback stuff I you know this stuff generally tends to sort itself a little bit more more easily than it used to or a little bit more automatically than it used to but my general experience has not been that increasing parallelism reduces spills so anyway all right the final question here how do you approach tuning vendor applications oh boy well you can’t change queries or indexes much uh well i i change what i can uh you know if if hardware is uh impacting the way queries run then I’ll usually suggest hardware changes. Sometimes you can change other things about the database, sometimes it’s the compatibility level, sometimes it’s the isolation level.

You know, there are all sorts of things that you can do, but like, you know, if you can change things but not change things much, you know, change things to the greatest degree you can. But, you know, it’s not uncommon for me in my consultancy to start working with customers of software vendors and have those customers refer me to the software company to help them fix the application more broadly. You know, usually the people who call me up are, you know, people who are like one of the biggest consumers of an application. And so when they have problems with it, the application vendors tend to listen. You know, there’s sort of a joke in the, I guess, whatever space that you never want to be an application’s biggest customer.

I think that is somewhat generally true, but it does come with some benefits. Like you can twist their arms a lot more and you can refer young, handsome consultants with reasonable rates to your software vendor to help them tune their applications that deal with SQL Server.

So if you are out there and you’re using a third-party vendor application and it is struggling with SQL Server, you know, we can always chat about how I can maybe work with that software vendor to help them make things go faster for you. Because that’s one of the things that I do. All right.

Anyway, those are the five questions for today from Lisbon. tomorrow we’ve gone to Kashkaj where hopefully it will be a little bit quieter and we’ll have some more wonderful scenery but for now thank you for watching, hope you enjoyed yourselves I hope you learned something and I will see you tomorrow

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.