SQL Server Performance Office Hours Episode 72
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:02:39 – Version Control for DBAs
- 00:06:13 – Next Episode of Bit Obscene
- 00:07:50 – Early Signs of Memory Pressure
- 00:10:41 – Deciding to Rewrite Queries
Full Transcript
Erik Darling here with Darling Data and it’s hot and I don’t know it’s too hot to do this so you’re gonna have to forgive me if my answers today are a little sweaty because man we are we are dying out here in this heat but it is time for office hours where I answer five of your questions before going to the beach. I don’t know is there other beaches in New York? Maybe I’m just gonna go outside and drink who knows anyway it’s time for office hours despite the heat if you want to ask me questions for office hours you can do that there’s a there’s a link down in the video description that will allow you to interact with me in that way you can you can submit your question and I will answer it faithfully I don’t care what the question is whatever you put on the teleprompter I will read there are other helpful links down there too like say maybe I don’t know you want to hire me for consulting I will show up like this if you want you can buy my training where unfortunately that is pre-recorded and I was wearing my my formal adidas attire for all of those but it’s still a good time and there’s there’s discount coupon codes in the thing down there too so you’re really out of excuses unless you are absolutely destitute you can you can also like subscribe tell a friend become a supporting member of the channel you should know all these things by now but I feel like a lot of you just aren’t paying attention to the importance I feel like a lot of you skip this part I also have free SQL Server performance monitoring tool it’s up on github that that link down the video description amazing times are ahead with that I’m rebuilding full is a better thing and the the light version is really alive and kicking a lot a lot of good improvements just across the board and that thing totally free totally open source you don’t have to pay me anything you don’t have to give me your email address and I certainly don’t collect any of your data because I’m not looking at that for free that’s that’s stupid it’s just a bunch of t-SQL collectors grabbing all this stuff that a a seasoned intelligent experienced SQL Server performance tuner like me would be would look at it and I put it in the palm of your hand assuming that you have a hand I don’t I don’t know maybe going into the 4th of July weekend that’s always a little perilous to assume that everyone has has hands or maybe maybe after the weekend when you watch this anyway yeah let’s let’s go answer some questions because the the heat insanity prediction is coming true my brain is melting brain is just entirely melting here all right let’s see what we got here do you think a DBA should know version control eg git and how does it fit into day-to-day database work and on that note do you happen to have any kind of version control training or module in your courses so it is becoming well I guess it depends on what you’re doing as a DBA a lot of places now if you want to you know get a like fix in somewhere for a query or a stored procedure or anything like that it has to go through version control and testing and you know so it is probably a good idea for you to have some idea what you’re doing there but this is also a place where the robot friends can be very very helpful because the robot friends know all the git commands and and and they can they can really help you out help you sort out some messy situations that would otherwise be really really annoying for you you know so I should a DBA no git it really it depends on what part of the DBA thing you’re in like no one’s checking a backup into source control at least I hope not right that’d be a bad time like yeah what are you doing I’m trying to commit this full backup last week so depending on what kind of DBA work you do maybe but like I think maybe a good way for you to get started with this is to get started with this that start a private github repo and like if you have scripts or you have a store procedures that you use maybe put them in there and just like for yourself start working on like you know what what what what like pulling them looks like and what you know maintain it like like creating a forking or not forking but creating a branch and doing some work on that branch and then committing it and creating a pull request and pushing that and all that stuff just do some work on some personal things that you use maybe like you know you’ve got I don’t know some script that looks at index fragmentation because you’re that kind of DBA and you’re like ah I want to delete this because it’s useless no I’m kidding but like there are probably things that you use day to day that would be good to that you like you know would would probably get some some personal benefit from just you know practicing with you know github is free right you can get github desktop I use github desktop for for my stuff because it makes things nice easy buttons to push I don’t find there’s a lot of pain or friction in there using github desktop but I would really I would say that you should you should just start a private repo with github and just mess with that a little bit I don’t I don’t do any training on that because it is not my forte I am not by any stretch of the imagination an expert when it comes to that sort of thing but if you if you want to learn that sort of thing you can do that all right next question really enjoying bit obscene when’s the next episode out Bob Ward is a guest me Bob Ward is not coming on my goddamn show I’m lucky to get Joe Obish on there when’s the next episode I don’t know whenever Joe gets his air conditioning sorted out and can can be presented in front of people again that’s that’s that’s that’s my best that’s my best estimate Joe is having some air conditioning issues right now that prevent him from appearing anywhere all right what are the earliest warning signs of memory pressure before everything melts down ah so the first sign of memory pressure is usually not from queries though it can be but usually not usually the first sign that you’ll see is other memory consumers sort of fighting with each other so you have you have a database right and you’re you’re looking at it and you’re like wow I have what’s this to make put some nice round numbers out there let’s say you have 128 gigs of memory and you have like 512 gigs of data that’s not like that like the data to memory ratio is gonna throw things off and so the primary consumer of of your memory right now would probably be sort of like the bucker pool and when you see a lot of weights on stuff like page IO latch right that means you’re queries are constantly going to disk for stuff so that means there is an immediate memory pressure because you are not carrying enough memory to deal with the data that you are currently working with so the first thing that you want to look for is churn in the buffer pool that would be that would be that would be the first thing to look at because that is only gonna get worse as your data grows now you know for that specific thing of course there are various ways to deal with that you know archival strategies getting losing data right getting rid of data uh you might be missing some indexes that prevent like large object scans we don’t care about small objects uh you might look at uh compressing page compression page compressing your rowstore indexes that would be a good start uh and then you you might also um i don’t know uh consider getting some more uh but usually that stuff all starts ticking up way before you’ll see like query memory pressure problems where you’ll have like like the bad resource semaphore weights um but if you if you if you also see those then well it’s it’s it’s you’re probably already having that meltdown you’re worried about so that’s your fault there all right how do you decide coin tosses obviously uh when to rewrite a query instead of just adding indexes well there there are two things there um well one of them sort of depends on what the index situation currently looks like right uh if you’re looking at a table that already has a kajillion indexes on it you might consider something like maybe not just adding another because uh the the chances are at least okay that there is already already an index right there’s already already an index that might help you out along the way so maybe just don’t just just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t just don’t jump to that. But if you have a table that’s rather bare and sparse and barren of indexes, then you might consider, I don’t know, adding one that would help your query. So I’ve done a bunch of videos on this sort of recently under the Learn T-SQL series of things where I talk about aligning queries and indexes. Sometimes you have no good index in place and there are query strategies like adding an index that would be good. And then sometimes you already have an index and your query is just not written in a way to take advantage of that. So if you’ve already got an index in place that would help your query, then it would be time to rewrite. But if you’ve got no indexes, then you must create at least one to help your query out. But the decision usually comes down to looking at the indexes and saying, oh, what do I got versus what are the goals of my query? Start with the where clause. That’s usually a pretty good place to start.
I’ve had to solve a dramatic number of query problems recently. With for sequence, that’s an interesting one, right? Because like, why is SQL Server choosing the scan? There’s an index it can seek to. And it’s like, I don’t know, because there’s like no rhyme or reason. Like I get costing, but you know, like, why are you doing that? And then SQL Server is like, well, I thought this scan would be cheap. And I’m like, cool, but you’re scanning 43 million rows. And you could just seek to 100,000 of them. And it’s like, oh, yeah, you’ve got a good point there, Eric. Thank you for forcing me to seek.
So that’s sort of that there. You know, if you’ve already got an index, write your query in a way that takes advantage of it. If you’ve got no indexes, well, perhaps then you create one, right? Seems reasonable to me. I don’t know. Why? Oh, this is a hum. Questions like this and answers like this are why Bob Ward is never going to be on Bit Obscene. That, I think, I don’t know. Perhaps Bob’s religious obligations would prevent him from being on a podcast. Sorry, on a radio program called Bit Obscene. But why does parameter-sensitive plan optimization sometimes make things worse instead of better? And the answer is because Microsoft completely beefed this feature. Man, they, like, it’s like clown walked up, like, pie to the face, spray bottle. It’s like Moe came up and just, like, Larry and Curly bonked their heads on something. But, man, they really screwed that one. So, like, right now you get three plans, right? I mean, just on the face of things. Like, there are all sorts of, like, ways you can get more plans. But right now, let’s just say for an equality predicate, you get three plan variants.
And the way things get bucketed, is you have the least common plan, you have the most common plan, and then everything in between. And I’ve shown examples of this in my other videos, where you’ll have, like, something at the very, so, like, I think it’s the votes table that I’ve shown with the vote type ID column, where the least common value is, like, 700 rows. The most common value is 37 million rows. So, they each get a plan variant. But the problem is that every, like, every time you get a plan variant, you get a three-row count between, like, 737 million shares the second variant, the plan two. And there are some very, very big swings in that data. You have some that are, like, 5,000 rows, and some that are, like, 3.7 million rows, and a lot of stuff in between. And the sharing in there is not good, right? So, a lot of that does depend on, like, which of the parameters gets fed into the variant two plan first. But it’s a bad sharing time within that chunk.
It just really messes stuff up. So, I was so excited about that feature, and then I saw it, and I was like, ah, yeah, complete screw job. But, hey, we got Fabric. That’s real groundbreaking stuff, right? If you ever want to know what Databricks looked like, like, seven years ago, try out Fabric. If it’s up, right? Who knows? It’s been down a lot lately. And, uh, not, not, really a lot of explanation of why. And, um, yeah. So, what a waste. Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I’m gonna go cool off now.
And, uh, I will see you in tomorrow’s video, where, uh, I don’t know. I don’t know. This might be too much clothes. We’ll see what happens. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.