SQL Server Performance Office Hours Episode 73
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:02:34 – Understanding Query Performance
- 00:09:01 – Optimizing Modification Queries
- 00:11:46 – Testing and Development Environments
- 00:15:02 – Conclusion
Full Transcript
Take three, Erik Darling here with Darling Data, and today is Tuesday, and the weather is mostly back to normal. We went from startlingly hot to constant rain. Summertime, baby! Anyway, it is office hours. This is where I answer five user-submitted questions that hopefully, well, hopefully users, I don’t know, is AI, are people sending AI questions in? I don’t know. I can’t tell anymore. I’ve given up on the world. Down in the video description, you will find all sorts of helpful links. Probably the most helpful video description on the internet.
If you want to hire me for consulting, buy my training. Become a supporting member of the channel for as little as $4 a month. Ask me office hours questions like you see here.
Or even like, subscribe, and tell a friend. All of this is down below if you just put your eyes a little bit… It’s like, I’m not going to tell you my eyes are up here because the links are down there. So, whatever. If you would like, if you would enjoy free SQL Server performance monitoring in your life, I happen to have that totally free, open source, no email, no phoning home, just pure T-SQL collection running, getting important stuff about your SQL Servers, wait stats, blocking, queries, CPU, memory, disk, tempdb, you name it, it’s in there. And I give you a way to have your robot friends talk to just the collected performance data. So you don’t have to worry about them going and running crazy queries on your prod servers. They’re just in there talking to them. That’s it. Thanks for watching. I’ll see you next time. Bye-bye.
performance data and that gives them a slightly better chance of saying something that is not completely out of this world outlandish um you know i mean maybe maybe spicy moment here but um you know i’ve uh i’ve been trying to give the robots some more chances to do stuff with like query and index tuning and man they they feed me some real dumb lines about stuff uh it’s it’s it’s annoying right and i’m like like do you do you remember who you’re talking to and they’re like i’m sorry i started apologizing uh and you know um you know yeah anyway let’s answer some questions oh boy wait time per core per hour second and sp blitz first first high impact weight type and sp perf check eric to help answer if top weights are high low in sp blitz first since startup equals one per core per hour divided by server time since restart uh i’m gonna paraphrase this a little bit because this is a lot of words um so uh in sp blitz first and i forget where this come from this came from this might have been a jeremiah thing uh but at least you know back when this stuff was getting written uh the like per core per hour per whatever thing um was like like dmv company policy uh i never quite understood it it never really sunk in with me why that was better um you know uh i think in in your case specifically where you have the default cost threshold for parallelism and max stop set to two uh it yeah like the parallelism weights are gonna look weirder than they do uh if if you just look purely at like the the totals and stuff um you know like i i i guess it can i don’t i don’t even know um i i i don’t i don’t have a good answer for why it’s done that way um or uh why why that that’s the ongoing thing um it it never again it never really resonated with me um i’ve had much better luck doing things the way that i do them uh which is you know why i do them the way i do um you know i i can’t really think of a situation where i would maybe want to factor in purely the number of cores on a server without contextual information like you have with like well what is max stop set to what is cost threshold set to like things like that um because just purely looking at at cpu time or cpu cores um i feel like that that loses some stuff so um i’ve never had uh terribly good luck following that that analysis pattern but i don’t know maybe i’m the dumb one let’s see what we got here is blocking a concern when querying the dmvs if not how does microsoft keep that data updated without queries blocking them um so i i do see uh blocking sometimes when i’m trying to query dmvs um you might notice that some of my uh scripts even have a locked timeout on them um as active has a couple lock timeouts uh when looking at when when trying to fetch query text and query plans there’s some cursors in there that do that but that are that have locked timeouts in them um i think sp blitz index has a locked timeout and it’s for for a lot of them it’s because you know you you can get blocked while stuff is um you know is going on in there and i there are there are a few remaining bugs um if i remember correctly the one in sp blitz index is around uh identity columns uh there’s like weird blocking that can go on uh trying if like you know the identity columns are um being inserted into uh in in some way uh i think that blocks stuff up there’s just been a number of weird stuff in there i mean the safest thing you can do is just you know um use no lock or uh read uncommitted when when doing that uh i obviously don’t know what microsoft’s secret sauce is for a lot of the the things they do behind the scenes in order to log that data um so i i couldn’t comment on that uh i don’t know how they do that but i do know that you know just because of the amount of foolishness i’ve dealt with querying dmvs over the years um the old recommitted is the best thing you can do there all right uh it’s not your classes but now my girlfriend has a crush on you any recommendations uh you let her down gently uh you know i’m i’m a pleasantly married man uh i can’t do anything for you there um i i am i am willing if if you uh if it would help you out um i i would send i would make an eric darling mask for you to wear uh around the house for that whatever you and your girlfriend do, I don’t know, maybe if that would be good for you, but yeah, sorry, there’s only one of me, only one of me, but let’s see, grow a beard, get neck tattoos, what, I mean, maybe that’s just what you need to do, right, maybe there’s a pattern forming, I don’t know, is it possible to tune, blah, more blocking, tune blocking problems without switching isolation levels, hell yeah, it is, come on, so the thing that you need to sort of make yourself comfortable with at the outset is that you will not be able to solve every iota of blocking or deadlocking in the database, even with a better isolation level than SQL Server ships with, which is the default locking garbage read committed, if you were to use read committed snapshot isolation or even snapshot isolation, you would still need to do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, with either writers blocking each other or under snapshot isolation, potentially writers dealing with conflicts, so you can’t fix everything, but you can do a number of things to ensure that blocking is minimized, you know, don’t update a billion rows at a time, that’s usually a pretty good start, batching modifications that have to do large amounts of work, they might not get any faster from start to finish, but each individual chunk will do that, so I don’t know, I don’t know, I don’t know, I don’t know, I don’t know, be kinder to your server and allow other queries to sort of orchestrate themselves in, making sure that your modification queries have adequate indexes in order to get to the data that they will be modifying, that’s always a big one, if you’re doing like a delete or an update and there’s a scan in your plan, you can bet you’re doing way more locking work than you need to, and of course, write your queries in a way that minimizes the work that you’re doing in modification queries, you know, things like sargability and good estimates are often very helpful to modification queries, as are occasionally, if you have a modification query that’s doing a whole lot of work in order to figure out what result, what rows and what the resulting value should be, many times staging that stuff to a temp table can be faster than relying on a Halloween protection spool to populate and do the update, so sometimes separating the nasty part of the complex heavy-handed part of a query from the actual update that needs to occur can be very useful as well, let’s see here, how do you explain to developers, ah, I live a hammer, that works on my machine means absolutely, I think that’s a little unfair, it doesn’t mean absolutely nothing, it at least tests some compilation, it gives you some sense of validity, but, you know, I guess, depends a little bit on what their machine is, doesn’t it, depends a little, because if their machine is a big honking, you know, prod server size hardware with an actual production database, obviously with PII scrubbed out, so we don’t have any leakages or data theft or exfiltration or anything like that, it could be faithful, but most of the time it’s not going to be.
You know, I think that developers developing locally is, is totally fine to get, you know, like a prototype of something out the door, and for a simple enough query, that might be as far as it needs to go, but for anything where, you know, performance, concurrency, things like that start to become a concern, then it’s, they need to be, I mean, developers need to know that things just need to be promoted up to, you know, they need to be promoted up to, you know, they need to go through environments in order for, sort of, their, their work to be proven out, so I, I think that it, well, it doesn’t mean absolutely nothing, there is a bit more that goes into releasing queries that, that drive applications than, you know, just some, some C-sharp code that, you know, draws a Windows form or something, right, we have a, we have different sets of concerns, right, including, you know, making sure that, you know, the, the, the plan stays, the, the query’s execution plan stays pretty faithful, and the same indexes get used, and performance stays good, and all that stuff going up from one to the other. I mean, I guess it’s okay if the query plan changes, but you want to make sure that performance stays consistent on its way up, and of course, if your queries are parameterized, you, you can, you can, you can, you can, you can, you can, you can, you can, you can, you need to test multiple, sort of, parameter combinations of high and low density data to make sure that your query is not parameter sensitive as much as possible. This is, that’s actually something that the robots are good for, doing that sort of data discovery and testing type stuff, you know, I’ve built a number of, I mean, usually, the robots generally prefer Python for this stuff, you know, I’ve built a number of, sort of, test beds and harnesses for things, you can leverage SQL CMD for this, and, you know, you know, have the, have the robots collect execution, actual execution plans for you, you know, you, you can even have them use my performance studio app, if you go to code.erikdarling.com, there should be a link for that down in the video description, the performance studio query plan analyzer has a CLI component in there that you can, you can have your, you can have your robot friends use to spit out either JSON or human-friendly query tuning advice with, and that can be very helpful for them to skip over a lot of the dumb crap that they think about query plans, you know, like the, and it even sucks with, like, you know, having the test harnesses that I have built, where there’s, like, very specific guidelines and instructions, or it’s, like, I don’t care about logical reads, I will, I will, I will smack you into the dead end, and I’m going to have to, like, I’m going to have to, like, I’m going to have to if you tell me about logical reads, I care about CPU, I care about duration, if you start talking to me about, like, query, like, operator costs or anything, again, to the death realms, but, like, you know, they, they still slip up, and they’re like, oh, we did 10% fewer reads, I’m like, who cares, why does it matter, does the query get faster or not, right, yeah, I don’t care how many reads we did, faster, yes, no, right, CPU, higher, lower, yes, no, what are we doing here, right, give me something meaningful, users aren’t going to be like, hey, good job, 10% fewer reads, my life has changed, I care about, I care about their time, speaking of time, I believe it is time for me to end this one, I think I answered that question, I feel like I answered it, anyway, thank you for watching, I hope you enjoyed yourselves, I hope you learned something, and I will see you over yonder in tomorrow’s video, where we will talk, probably some T-SQL-y stuff, I think.
That sounds about right to me, all right, anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.