ICYMI
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I delve into a lively discussion about various database management topics, diving deep into execution plans and offering insights on how to interpret them effectively. Starting off, I share my experience as a DBA and highlight the importance of questioning every aspect of an execution plan—seeking out seeks or scans, understanding join types, and analyzing aggregation methods. The conversation then shifts to more specific scenarios, such as managing log files in data marts and the practicalities of moving from Azure SQL Database to managed instances. I also address the cost-effectiveness of cloud solutions versus on-premises databases, emphasizing that while performance can be challenging to control in the cloud, it’s crucial to weigh the costs against potential benefits. Throughout the session, I encourage viewers to explore resources like Grant Fritchie’s books and my own for a deeper understanding of execution plans and best practices in database management.
Full Transcript
I’m alive and fully mustached. Full, full on mustache. No one is here to admire my mustache. How very sad. Do-do-do. Do-do-do. Do-do-do. One, two. Oh yeah, people are coming in, hanging out. This is wonderful. Wonderful. Makes me feel so not alone. This time, I’m going to have the chat window open so that I don’t have to look at my phone like a scrub.
Welcome Lee, finally made it to one. Is that Luli or Leli? How do you say your name? You’re going to have to give me instructions so I don’t mess it up. Penel is here. Watch out. I might get some weird questions today if Penel is here.
Lou. All right. Lou it is. Yeah. So, for those who don’t know about my antics coming up at BITS, I’m, for charity, not just like to quell some strange desire with me, for charity, I am dressing up like Freddie Mercury to do my index session. And I have this lovely studded belt. All right. So, I got that. I got my macho man armband. All right. This. All right. Got this going on. I have a pair of straight up dad jeans. Check these out straight up. Now you all know what size jeans I wear, which is maybe embarrassing too, but straight up dad jeans that I’m wearing that I got. And of course, I have the white tank top ready to go. So, I have the whole thing. I even have the sneakers, but I didn’t want to look like a noob. Cause I know that like, uh, Manchester is the home of lots of people who wear and take Adidas sneakers very seriously. So, um, I’ve been, I’ve been wearing my, my white Adidas Sambas, breaking, breaking them in a little bit. I don’t want, I don’t want to look get fresh white Adidas Sambas stepping off into Manchester. I might get beat up. Might be some, some soccer fans there who, uh, who just tear me a new one. So I’m getting that going. The mustache is happening. All right. Going to have that full Freddie mustache. I’m not going to have the armpit hair. I can’t do that, but everything else is good to go. Lee, I’m looking forward to seeing you at SQL bits in Manchester. That’s going to be fun. I’ve, uh, I think I have all my material pretty well wrapped up for that. So I’m excited to excited to deliver it. Finally. I also have stickers live. I mean, not really live there. I think they’re, I think they’re dead stickers. So I’ll have stickers for everyone going to bits too. So everyone will, everyone will get something.
Except I don’t know, maybe, maybe not everyone. Maybe some people won’t. I don’t know how that works. So, uh, I don’t know. Does anyone have like questions about SQL Server? What it’s like to have a mustache? Like, I don’t know.
I’ve had, I’ve had sort of a funny day, actually sort of a funny couple of days. Um, I’ve been, uh, let’s see. Julie says, if someone can’t make the SQL bits, how can we get a sticker? Uh, well, Julie, I think, I think since I recognize your name enough, if you want to either, uh, email me your address or, uh, or, or DM me your address on, on Twitter, I will, I will send you a sticker. Dan, if you want one too, let me know. Since you, since you also recommended sticker mule to me and they are awesome stickers. I, I, my, my, my original order was from the company called sticker you. And they sent me the worst stickers that I’ve ever, I’ve ever seen in my life. Like, like, like the logo is illegible and like, like faded looking. And like, I ordered like, like gray, like, like solid gray, but it came back like, I don’t know, like spotted. It looked like TV noise. It was awful. So yeah. Yeah. I might even send you more than once. And some, since some people like to put them on their phones and laptops and foreheads and get tattoos with them. So it’s all sorts of stuff that you can do with that. Uh, now, now I’m, uh, putting the, the final touches on, yeah, you’ll be, but now we’ll get it at SQL bits, but I’ll take the whole stack of them.
And I’ll be, I’ll finally be famous in India. Uh, but yeah, I’ve been, uh, uh, wrap the final touches on, uh, my, my demos for the indexing session. And that’s going to be fun. Um, that’s, uh, that’s gonna, it’s gonna be, it’s gonna be interesting. And, uh, let’s see. Oh, we finally have a SQL question. Lee says, how do you replicate a poorly performing query? I see many badly running queries, but replicating these on a dev environment can be difficult due to not knowing what values are being used for parameters. So, um, you can get from the plan cache.
And this is something that I wrote into SP blitz cache. If you want to use it, uh, you can, from the plan cache, you can get the parameters that code was initially compiled with, but you can’t see what it was last run with, which is, um, uh, sort of downside of dealing with the plan cache. I want to say that Grant Fritchie recently wrote a blog post about how to get that from extended events, but I would have to track it down and find it for you. Um, but that’s, so if you, if you want to just get in that, and that’s a great way to start troubleshooting parameters, nothing, right?
Because, uh, especially if, if, you know, someone is coming to you and saying, hey, when this query, this query is slow, when I run it, then they can perhaps give you the values that it was compiled with that, that was slow for them. And you can compare that to what it, or rather what they ran it with and you compile, uh, compare that to what it’s compiled within the plan cache.
Let’s see. Uh, I have a question. How often does Microsoft release patches for SQL Server 2017? Um, so I don’t know, cause I don’t work for Microsoft. Uh, if anyone from Microsoft is watching and listening and you want to give me a cool job working on SQL Server, I, I won’t complain, but, uh, I, I don’t know. Usually it’s supposed to be every month for the first year and then quarterly every, or every three months or quarterly after the first year, whether that happens and they stick to it, I don’t know. Um, there, uh, there have been some, some downfalls in, in, in the agility that Microsoft has tried to discover in the, in the CU release process.
Yeah. CU 13 now is to do the math. Ah, yeah. Good, good luck on that. You know, uh, they, they, they say they have a new servicing model. So let’s, let’s see if they stick to it. Right. I guess technically, well, no. Yeah. Yeah. You’re right there. I’m sorry. They, they messed up.
Yeah. Uh, it might, it’s funny. These t-shirts always look so much cleaner, like when I’m looking at them and then I get on camera and I just look like a mess. Let’s see. Rowdy has helpfully posted a link. Uh, let’s see. Lisa says, I know you briefly talked about Azure managed instances, but what are your experiences with them? We were seeing some real gutches at work while using them. Um, my experience with them was when, uh, was we got a preview version, uh, to play around with when I was with Brent and, um, I didn’t do a lot. I didn’t like really kick the tires on it. Um, because we didn’t have it up for very long because those things still cost money.
I think it was costing like 1500 bucks a month to keep it up. So we didn’t keep it up very long. Uh, we just, you know, did enough to like, kind of like get some initial stuff back on it. Um, and, uh, you know, yeah, they are expensive. Uh, but I, you know, I actually, over on Twitter the other day, uh, I talked to the, well, I didn’t talk to, I tweeted at and got a tweet response from the PM, uh, guy named Jovan. And he said that they’re working on something for developers that will be less expensive so that you can kind of kick the tires and blog about them and stuff. So that’ll be nice to see when it comes out, but there’s nothing public on that yet. Uh, as far as, uh, tell me, but I would love to hear about the, um, uh, the things that you ran into with managed instances, because, uh, it would be nice to have sort of like, uh, you know, like I have a real world use case and this didn’t work out for me sort of stuff to talk about because, um, right now I got nothing.
Even like, even if I, even if I got like a dev thing to mess with, I would like put stack overflow on it and be like, works for me. Not run into stuff that like, you know, people in the real world might have to do. They are crazy expensive though, but I think, you know, that’s, it’s totally worth it.
Um, you know, uh, as far as being a good mix of on-prem and managed database instances, I think, I think Microsoft hit a good, uh, a good mix of, you know, uh, out of the, uh, on-prem features with the, um, the managed side of the, of the server. See, Darren says, I would too, because I’d like to move from Azure SQL days. Yeah. I think you and everyone else who is on Azure SQL DB wants to manage, wants to move to a managed instance because they, I think, I think comparatively, they are, they are just fresh to death. Let’s see. Uh, Rowdy says a buddy familiar with AWS RDS SQL said that the community really loves it. Have you had any experience with their managed MS SQL?
Uh, what do you mean? Familiar with managed instances? Uh, let’s see. Lisa says we looked at SQL DBs, but the performance just wasn’t there. Yeah. Uh, so, I mean, there are aspects of the cloud where performance is just really tough to lock down, like, you know, uh, storage, networking that, that stuff gets crazy expensive. Like it’s, it’s tough to, you know, and it, and it’s, and it’s hard to like, you know, say, oh, well, the cost here is totally worth it because, you know, um, often it’s not often.
It’s just like, I’m getting soaked on this. Rowdy has more links. Rowdy is amazing. I hope Rowdy is always, always unemployed. So you can always show up and put links in chat for me. Just kidding, Rowdy. I, uh, I have passed your information along to people. Uh, let’s see. Uh, SQL Dev DBA says, uh, we have a 25 gig database, a data mart that has a 39 gig log file in the full recovery model. We take log backs every five minutes. Any thoughts?
I’m not really sure why you’d want to have a data mart in full recovery model. I’m not sure that that sounds like, um, a good, good mix for me. Like, like, like I, like I wouldn’t expect to see like a data warehouse in full recovery model. Um, any advice? Jeez. Uh, so, you know, is it how big, like, I guess, you know, it’s, you gotta have some historical questions about that, right? Like, was it always that big? Was there like a one-time transaction that made it that big? Um, you know, this is like a lot of, a lot of stuff comes to mind when I’m trying to figure out like, well, you know, like, is it worth it to shrink down the log file or am I just going to suck it up because 39 gigs of space just isn’t all that much these days? Like, I can’t imagine sweating 40 gigs unless I only had 20 gigs left, I guess. Let’s see. Uh, Lee says, when you first started as a DBA, what did you find the most useful aspect of executions plan to learn first? Seriously, need to get better at reading them, but it’s entries of it. Um, well, you know, for me, the hardest part was always figuring out like, is this a good execution plan? And, um, you know, I think the best way to start is, uh, like the way that you read the plan right to left and just ask questions to yourself about everything that happens. Uh, ask, like, we start with like, how did we access the index? Was it a seek or a scan? Why was that so? Um, do I not have an index that I could seek into, do, uh, do I have an index that I could seek into, but I didn’t for some reason?
Um, I’m doing this type of join. Why am I doing this type of join? I’m doing this type of aggregation. Why am I doing this type of aggregation? Uh, I think, you know, the best way to learn is truly by questioning plans that you see and like learning about the different operators and why they pop up.
And like over, you know, over the years, it’s funny because the stuff that you care about really does start flowing from right to left. So like, you know, you go from caring about, oh, did I seek or did I scan to, oh, what kind of join did I do to, uh, oh, I did a key look up to like, you know, well, something else downstream. And then you start caring about like the weirder operators are like, what are these spools? Like, what’s going on? Like, why are you spooling data out there? What are you doing to me? And then like, you know, you learn, you learn about, you know, the differences between cash plans and actual plans where like an actual plans, you see all this new information, especially nowadays, Microsoft is filing cool information, uh, actual plans. Um, let’s see, but you know, uh, if you want some reading material on it, uh, Grant Fritchie keeps putting out these books about execution plans that have tons of good information in them. And, you know, even if you only like, even if you don’t read them front to back, even if you just say, all right, look, I need, I want this reference material when I come up to this, when I come across something weird in a query plan, I’m going to go look at the book. It’s totally worth it to have on your shelf for that. Uh, Grant puts a lot of work with them. Grant’s a super knowledgeable guy. I would, uh, I think having, having his book on your shelf, if you’re trying to learn about execution plans is probably a really, really good idea.
Uh, let’s see, uh, SQL dev DBA follows up with log DB will typically be at least some size above the largest table to accommodate reboot. Yeah, it will. So it’s going to be, I mean, I would say at least the size of that object plus 50% ish, but you know, again, 39 gig log files, and isn’t really going to be like my biggest concern. Um, yeah, I mean, unless, unless you find yourself frequently having to restore those, but even, I think even then with instant file initialization turned on the data file will go quick and spacing out the log file will be kind of painful, but, uh, most people aren’t restoring data marts. Most people are just kind of rebuilding them. Let’s see. Uh, let’s see. Mike Whitty says, yep, that’s been our experience. Lee, cool, rowdy.
We do hourly imports from an Oracle database. Uh, once you have the data imported, I mean, uh, it’s a good question. Uh, yeah. So if you’re just dumping data into the database, it might not be, uh, as big a deal as if you’re, you know, doing some aggregations or moving stuff around or, you know, doing some kind of, uh, flat or what do they call it? Like presentation type logic, uh, to the data once it comes in. Good question. Trying to think of some other stuff. Um, Ben Navarez had a good, had a pretty good book, uh, about SQL Server 2014. That’s a, I mean, it’s not like dated, but it’s, you know, it’s not, it’s not as up to date as Grant’s book is now. And of course you could always buy my book from, it’s like, I think it’s free for Kindle users. So if you want to, if you want to just read the Kindle version of my book, you can. There’s, there’s a few things about execution.
And also if you’re, if you’re coming to my, my, my pre-con in, in, in, in jolly old Manchester, there’ll be lots of stuff about execution plans in there. Lots of deep probing, long fingers going into query plans and saying, what’s wrong with you? Why are you doing that to me? Uh, SQL WDB says we’re not manipulating it. We have views that use the data and we massage them with the views in order for power BI to access them. Might have to consider putting the data mark to recover. Yeah, I would. I mean, so putting it, so just to, you know, kind of set some expectations here, putting it into simple recovery model, isn’t going to fix the size of the log file. It might give you a better idea of, um, you know, how big the log file should be, but, uh, it’s not going to like magically shrink the log file for you. What I would do.
So here’s what I would do. Uh, I would, uh, set up a query to, to run and look at free space in the log file and have it run like every, you know, have it, have it run like every minute because you’re taking log backups every five minutes. Uh, I would have it run every minute and just kind of look at how log space is actually used. And it might be that, you know, you, you could shrink your log file down once to like, you know, maybe half the size and just leave it for a while and see if, see if it grows again. Let’s see. Oh, Julie posted a link and I have to okay it.
It’s funny. Like Amazon makes me okay. Uh, Amazon. Jeez. This is an Amazon link, but, um, YouTube makes me okay. Every length that comes in. Uh, let’s see. Lou says, uh, have you tried? Oh, the Azure DevOps studio. No, I haven’t. Uh, and I know I’m probably a bad DBA for not that, but, uh, I’ve been really head down, uh, lately trying to, uh, you know, get the consulting thing rolling and, uh, trying to get, uh, all of my various presentations and whatnot lined up for, uh, for a future use. So I’ve been really trying to kind of have not had a lot of time to experiment with new, new bells and whistles, but I probably should. It’s, it looks neat. I think the main drawback for me of, um, of, uh, Azure DevOps studio, or as they call it on, on Twitter, ADS is that, uh, it does not do well with execution plans right now. And that’s kind of like my bread and butter. So like whenever I want to like do something like in management studio, there’s like a 90 ish percent chance that there’s an execution plan involved. So I don’t know. I don’t know if like, I’m gonna, I don’t know if I’m to hop on that, hop on that yet. Cause I need, I need my execution plans or else, you know, I have no blog posts, but I don’t have execution.
It’s funny how that works. I should probably, probably learn how to blog about other things, right? Put some pancake. I have like, I have like pretty good recipes from getting laid off. And like, no, no, like I, I, I make, make pancakes and French toast and all sorts of other stuff for my kid in the morning. So it’s fun. It’s funny to have like that kind of time on my hands. I’m just like, what, what shape would you like it in today? A unicorn head. Of course, let’s do that. Like paint brushes and little spatulas and like details. It’s fun. I don’t know. Maybe SQL Server isn’t my calling. Maybe, maybe custom pancakes are my calling. Am I on Instagram? No, I’m not on Instagram.
Uh, I, I am baby stepping into social media. Uh, I do not, I do not do terribly well with it. So, uh, um, I’m, I got on Twitter because that seemed like the easiest to manage. And, uh, I could just say things instead of always having to have a picture to go with them. But, uh, maybe, maybe Instagram is next. Maybe I should at least like parking spot my company name.
Now someone’s probably gonna like hold it ransom from me. You don’t have to pay a million dollars to get Erik Darling data on, uh, on Instagram. Right. It says I do vlogs from your neighborhood. Uh, so I was thinking about like, I, like at first I was like, oh, I’ll do them from the gym.
But then like, no one wants to watch that happen. No one wants to like watch me deadlift and yell at things. There’s just no, no audience for that. This is, there’s like a million people who do the exact same thing. Plus I don’t leave the house except to go to the gym. I like walk there and back. And then that’s it. My favorite restaurant closed, or I would say our favorite restaurant, like, uh, like the family’s favorite restaurant closed. Uh, after the first of the year, the, the chef got a new opportunity to do like some cool thing and they closed. And that was kind of a blessing in disguise because it saved us a ton of money, a ton of money to eat there like twice a week.
It’s ridiculous. Let’s see here. Any other questions? I mean, I wish I was on Instagram. I don’t know. I guess the question for you is, are you, are you on Twitter? Are you, are you, are we friends on Twitter? Are we tweet twins? Fweets? What do we call it? I don’t know.
Let’s see. Uh, oh boy. Yeah. All sorts of things. All sorts of things. All right. Let’s see. Uh, talk to us about trivial plans, the good, the bad, and the ugly. So trivial plans are a wonderful sort of optimization where, um, SQL Server will say, I have such an obvious way of doing this one thing that I am not going to think about. Like if I thought about, if I thought for like, like as many CPU cycles as you asked me to, to come up with a better execution plan for this query, I likely wouldn’t. The thing is that sometimes it’s wrong and sometimes trivial plans lie, uh, because trivial plans are tied into this thing called simple parameterization and simple and parameterization in general can, can cause issues with either parameter sniffing, not using filtered indexes, stuff like that. And so, uh, when you get trivial plans, often you will get a simple parameterization alongside it. It’s not guaranteed, but it’s, it’s in there. And, uh, you know, there are some downsides where, you know, sometimes there is a better plan waiting behind that trivial plan fence that, you’re just not finding, uh, trivial plans won’t ask for missing indexes. Trivial plans will never go parallel. You know, there’s just stuff that you don’t get from a trivial plan that you get from full optimization. Not that every query in the world needs full optimization. And it’s really tough to find like when a trivial plan does better with full optimization. So it’s just something that I keep an eye out for. Like if I see that a plan gets trivial optimization, I might throw a one equals select one on there, get a, get full optimization and just see if anything changes. If not, I go about my business tuning the plan at hand. Julie says, is there any way to limit how often a SQL agent job sends a failure notification? Uh, not that I recall. Um, I don’t know if there’s a way to, to sort of like spoof that and like, like damp that down a little bit. Uh, I know that most monitoring products offer a way to sort of, uh, uh, uh, what do you call it? Uh, well, Rowdy posted a link.
Yep. And the link I added. Okay, cool. Uh, oh, look there. Look at that. How can I live with the number of emails sent by SQL Server agent? I’m going to upvote that. Sweet. I’m going to upvote that answer too. That’s a good answer. Thanks Rowdy. Uh, let’s see. Lee says, we have seen cases of parameter sniffing at work. The easy fix has been to add that option recompile. Why is that a bad fix?
Overhead seems minimal compared to the bad plan. If the overhead is truly minimal, then I wouldn’t call it a bad fix. Um, usually when, uh, when I’m dealing with parameter sniffing, I ask myself a few different questions. It’s either, uh, like what’s the difference between the good plan and the bad plan?
Like, so it, with the, with the small plan, am I getting like some little like serial key lookup, like low memory plan. And when a big plan comes through or when a big value comes through, is that just overwhelming it? And then like, what plan do I get for the big plan? Like this, look at what the differences are because sometimes there are ways to, you know, sometimes it’s like, oh, if we have a slightly better index or a slightly different index, we can avoid having to, we can avoid parameter sniffing altogether. Other times it’s like, well, maybe if I just hint to like, say optimize for this value, which is a bigger value, it makes more sense. Um, option recompile.
I just, I dislike it. Not, not necessarily because of like the overhead, because most of the time SQL Server coming up with a query plan is fairly easy. The reason that I dislike option recompile is that we don’t have any, we don’t have sort of any good historical information in the plan cache about, uh, what that query is up to over time. And if there’s ever a problem with the plan that we get with option recompile, we don’t, we don’t have a good way to like sort of track that and figure it out. So it’s not that I’m against option recompile all the time. I just, you know, if you’re going to use it, you need to know like it, you don’t have that kind of good forensic information in the plan cache anymore. Um, you know, so take, take the good plan, take the bad plan, or take the, the plan that’s bad for some value and just try to look at the differences. Uh, you know, this is, this is a good time to, uh, I guess if, if you have, if when you get grants book, try to like, like look at the operators that you’re getting and try to figure out situationally why SQL Server may have chosen those operators for one plan and not for another. So that’s a, it’s a good bit of homework to do is figure out why the optimizer thought, well, like figure out like, like look at like, you know, uh, the plan that it comes up with for the small values and be like, okay, cool. We have that.
And then run it with the big value and say, okay, now it takes us long. Then recompile it and look at it for the, look at the big plan and say, okay, we got a totally different thing. And then it’s just helpful to compare and go back and forth and look at, you know, what, what one does and why SQL Server was like, oh yeah, we, we had to do this differently because we were dealing with a way different set of data. Right. Any more questions, any other things that we can talk about, do yell at each other about yes, no, maybe I don’t know. Rowdy, you might, you might, you might have to, you might have to come up with one for me.
Putting Rowdy on the spot. Mike Walsh is being funny. Mike Walsh does not like my mustache. Oh, you posted on my Twitter. Let’s look at my Twitter then. Let’s see what happened over there. Oh, that’s you. Okay. I didn’t, that’s why I didn’t recognize you because you, you are a picture of a dog over there and you are, I think not a picture of a dog over here. Yes. Not a picture of a dog over here. So yeah, Mike, I know it’s not very punk rock. The goal is not to be punk rock. The goal is to be late stage glam rock. And that’s, that’s my, that’s my goal to be like, like we, we were glam and now we’re getting out of glam and we just kind of have some weird side effects happening.
Let’s see. Uh, Roddy says 3d printed pancakes are just as good as homemade. I’ve never had a 3d printed pancake someday. I want to live in that. I want to live in that future world where 3d printed pancakes are a thing. That sounds awesome to me. I love, I would love to try 3d print, except steak. Steak is where I draw like biological matter is where I would draw the line on 3d printed.
Come to Dallas. Uh, I was supposed to go to Dallas to, uh, do a user group thing, but, um, I don’t know. I think they found someone cooler to do it. So I don’t know. Maybe I’ll do it again, but we’ll see. Who knows? Who knows what the future holds? Let’s see. Oh, a question.
What’s the best way to get more information. Nigel says, what’s the best way? Uh, okay. I’ll do this really quick. Uh, so, uh, Rowdy, no, it wasn’t going to be SQL Saturday in May. It was going to be, uh, like a training day type thing, but I think I want to say they got like Andy Leonard or something for it. I, I, I, I had a much or though, uh, let’s see. Nigel says, uh, what’s the best way to get information from deadlocks? We’re having some deadlock issues. And while we use monitoring tools, they don’t give us enough information. Uh, typical scenario is one update, one select.
The update is only updating one table. The other table is in the deadlock. The only clue is there is RL between the two, but the update does not involve the columns. Um, so, uh, if you use the first responder kit tools, there is a store procedure on there that I wrote called SP Blitzlock that gives you, that breaks down. I think, I think really well, the information that comes out of deadlocks. Um, if you run that, I think that often gives you, uh, better information than monitoring tools do. I think monitoring tools, uh, don’t, don’t go into the depth that they should of telling you, uh, what happened. Uh, so I would start there. Um, if it sounds like if you have a monitoring tool, they might even have a deadlock extended event session set up and you can point SP Blitzlock at the, uh, the deadlock extended event session. And you can get a ton of cool information out of that. Dan says, I think SP Blitzlock is great.
And yes, it is. And I wrote that drunk on a plane, so it has to be great. So they gave you maybe way better info than Redgate. Wow. Well, you know, Redgate has never asked my opinion on deadlocks. Maybe they should.
Let’s see. Dan says, uh, how do you go about determining or recommending the correct amount of RAM? I have a customer with one terabyte of data over multiple databases and 25 gigs of RAM in an OLTP environment. Holy smokes. Uh, wow. So if I, if I’m making a sort of just base out the box, you want to know how much RAM to have? I like to say 50% of your data.
Um, and that’s not because I think that 50% of your data is active. Usually it’s somewhere around like the 20 to 30%, but I want 50%. I want to, I want to, I want to, I want to have RAM equal to 50% of data because caching data isn’t the only thing that SQL Server does with memory.
Obviously query plans are going to ask for memory grants and some of them are going to be pretty big. And it’s going to, it’s going to memory that for memory grants is going to fight with memory for the, for, uh, the buffer pool and for the plan cache. And just to be safe and say, I have this much memory available to me, that’s what I’m going to go with. Uh, if you want more, like more, a more detailed breakdown of what you should have, um, I would say look at weight stats.
If you’re spending a lot of time waiting on memory ish weights. Uh, so, and by that, I mean, if you’re spending a lot of time waiting on disc because you’re, you’re the, your, your active data is not in memory when you need it to be. So you’re looking at like page IO asset page, IO latch sh and ex weights. Uh, that’s a pretty, that’s that especially sword does not responding well. Like you have long average milliseconds per weight on that. Then it’s a pretty good sign that, uh, you might want to have some more memory in there to alleviate, you know, time that you’re spending, spending waiting on disc, or if you’re hitting some of the poison weights around memory, like resource semaphore, resource semaphore query compile, that’s an even bigger argument to get some more memory in there. Cause well, that won’t solve the problem a hundred percent. Oftentimes you add more memory and you end up, uh, just giving, uh, memory grants, a bigger piece of a pie, bigger, bigger pie to ask for a bigger piece of, uh, it is, it can solve some lower level problems with a resource semaphore. So, uh, Darren says, yes, more memory doesn’t cost extra in licensing unless you need to break that magical boundary where you move to enterprise edition. And which case all of a sudden, all your core is magically cost $5,000 more licensing is so funny like that, right?
Like, like, like I’m like, I’m holding, like, let’s pretend that this تي 缶 What those & questions bearing You
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.