Office Hours: Ask Me Anything About SQL Server, Húsavík August 2021

hello and welcome to hussevik in iceland it's the whale watching capital of iceland and i hear of all of europe a lot of these boats go out every morning to go and look for whales all kinds of whales around here so i'm gonna take a moment to go through and answer some of your highly voted questions if you want to ask a question make sure to hit the link below so that you can ask your question there don't ask it in the youtube comments all right first up we have david asks do you think that sql server 2019 is production ready yet or should we still stick with sql server 2017 i have this real weird feeling here i so i've blogged in the past about how sql server 2019's cumulative updates haven't been exactly chock full of quality that we've had all kinds of quality problems oh my gosh up my phone i know i'm going to regret it if i set that thing down where we've had all kinds of quality problems and i say we microsoft i still don't get a warm fuzzy feeling about 2019's cumulative updates they keep shipping them and they keep having all kinds of weird crazy problems with them so for me i like 2017 still in terms of maturity 2019 seems to be where the the latest and greatest cutting-edge stuff comes out and people might be thinking well haven't they fixed everything in 2019 by now because it is 2021 the problem is that they keep putting in new features into the code base and not documenting them which can have blowback effects when even when you're not actively using them the code base that they have that's changing is causing problems in production so right now my thing is i would avoid change if i could i would leave things the way that they are now i'm not saying don't patch you should patch just because we kind of have to to fix known bugs but i don't know that i would go with new versions i'll give you a great example one of my clients has been on sql server 2016 and it's been rock solid stable for them two years in a row they've had the opportunity to swap out their infrastructure and they've returned to sql server 2016 both times even though that they're newer and you know better quote-unquote versions out there the problem is just that they know sql server 2016 really well it works really well for them so it doesn't make sense to take a gamble uh when they're getting satisfactory performance on sql server or 2016. so there you go uh cue the fanboys next up i am an angry asks does with no lock cause high cpu usage wow a customer's sql server was maxed out at 100 percent sql server was using most of it the first responder kit pointed me toward a stored proc that had with no lock in it i took away the with no lock and sql server dropped down no what happened was is you had an instance of parameter sniffing sql server sniffs the first set of parameters that are used when you run a query or a stored procedure or whatever sniffs that in order to build an execution plan when you alter the stored procedure even if you don't make a change to the proc even if you use exactly the same code even if you wouldn't have used nola if you left no lock inside there if you had just altered the stored procedure add a space run alter on there then sql server would blow that plan from the cache and it would use the next parameter that comes in in order to build the plan classic uh thing that causes this or that makes people think that rebooting the sql server fixes the problem for example because what they'll do is they'll restart the sql server they'll make a change to the stored procedure and then what do they immediately go do they go run it with the parameters that were slow but then they get a plan that's perfectly customized for those parameters and it runs quickly again so to start you're going to have a whole new world because i have a whole bunch of classes on this if you search for brent ozar parameter sniffing or if you go to brentozar.com click on training at the top of the site another key set of keywords to search for is slow in the app fast in ssms slow in the app fast and sms which doesn't describe the problem that you think you're having but it's the problem that you're having and you'll learn why when you read that post it comes up all the time comes up on every office hours it's crazy um next one dave said the new shop that i'm working at has a lot of queries with the merge statement in them i always thought that merge didn't work consistently and i've avoided it i seem to remember a blog post detailing the gotchas am i correct or way off and what should i deal with or what should i do there yeah google for or bang i support alternative lifestyles if you google for uh aaron aaron aaron bertrand merge statement or use caution with sql servers merge statement uh that is a blog post by aaron bertrand that details out all of the issues with the merge statement most of which are either closed as by design or microsoft has said they won't fix them so i'm not a fan of using the merge statement when it is so horribly broken and microsoft is like would you look at that oh those boats are pretty sure gotta go we'd love to work on the merge statement but uh i think i'm gonna go take a sale today uh when they know it's this horribly broken and it's been complained about for so many years i would not invest additional development time in it at some point they might fix it but they certainly haven't shown any interest in doing so so what are you supposed to do instead conventional write your own upserts which i know sucks it's more work but if you want reliable results then that's what the game seems to be i wish they'd fix it but they don't seem to be interested in it next up david asks but open row set is so easy david's probably referring to the fact that i blogged about several issues around linked server query performance he says but open row set is so easy if i want to reduce the points of failure what other options other than ssis packages are secure and simple enough to run in a stored procedure well david there's this old saying when you build stuff good fast cheap pick any two good fast and cheap pick any two linked servers and open row set are fast and cheap fast meaning they're quick to build not that they perform quickly they're fast and cheap they're just not good so what it sounds like you're asking when you say i don't want to play around with ssis what it sounds like you're saying is brent isn't there an option that is good fast and cheap no moving on to the next oh i'm such a terrible person the last question we'll take for today is david asks how can you tell different david oh that one was romero and this one is toby david says how can you tell if the server has enough cpu resources and collect the evidence we buy microsoft software assurance to run on vmware and the minimum licenses is for cpu cores a consultant said that two cpus is faster than four so prod is configured with two i need evidence okay so first off the the underlying question in there is whatever consultant told you that two cores is faster than four cpus are they or two courses faster than four cores are they correct yes in the case where in your vmware host there's lots of contention for cpu long time ago in a galaxy far far away vmware's cpu scheduling wasn't very advanced and if you if your vm had four cores it needed four cores of actual physical power available in order to make progress if four cores weren't consistently available for a length of time then vmware wouldn't let your guests run would kind of think of it as being paused until four cores were actually available now we're talking 10 15 years ago over time they got gradually more and more advanced so if you had tasks that only needed one or two cores then vmware would let those one or two cores let that task run with just one or two cores and let that get ahead even though the other two cores weren't available at the time i have not kept up with vmware cpu scheduling in the last five years or so but it hasn't been a problem for me in the last 10 years the difference between two and four cores i don't know that i would sweat it but if you believe or if you're worried that you don't have enough cpu cores in my mastering server tuning class we talk about how to measure your sql server and gather evidence about whether or not you are cpu bottlenecked and basically you're looking at weight stats to see whether or not your top weight is sos scheduler yield means that the sql operating system is having to wait to yield or is waiting to get back on a cpu scheduler after it's yielded the cpu scheduler and in that class we talk about how you look at average milliseconds wait time on that weight in order to determine how overloaded the sql server is most sql servers that i see aren't in aren't uh cpu bottlenecked this is it's in the top six weight types overall through sql constant care in sql constant care i monitor the uh like three four thousand sql servers in the world and tell you what their top weight types are and that's what i focus the training on uh because that's one of the things that i love about doing a combination of sql constant care and teaching training classes is that i can assess weight types across the world and give training that's the most relevant to the most users and so a scheduler yield is there but it's it's in the top six it's not number one and it's not number six it's not the thing that most people hit having said that two cores is really really low i mean i think my phone has eight cores and it's uh i get that you wanna uh conserve cpu power on the host overall or not but if the if the consultant is telling you that you need to downgrade from four to two in the year 2021 my guess is that the consultant probably went to training 10 15 years ago and just hasn't been since there's this awesome concept in development called cargo cult programming cargo cult programming means you're just repeating something that you saw or heard once without really understanding the underlying mechanics of what's going on and the name cargo cult stems from i have very little knowledge of this i'm just repeating things that i've read uh from over the years the name cargo cult programming stems from there used to be these cults in the or that really tribes in the pacific uh on pacific islands during world war ii and during world war ii american armies came over they didn't invade the island they just occupied it and the they gave all kinds of stuff to the tribe there's there's colonialism right there gave all kinds of stuff to the tribe members gave them coca-cola food you know clothing etc uh while they were occupying the islands during the war then when the americans left some of these islands evidently didn't understand how to bring the americans back and they made planes and runways out of grass like they cut down the grass and made airplanes out of grass they wore fake uniforms trying to act as if they were radioing in supplies they didn't speak english they didn't understand how the americans would bring in all these plane fulls of cool supplies so that was referred to as cargo cults islanders who just didn't understand why things had happened the way they did and they were just mimicking actions that they thought would bring about the results that they wanted there's a lot of good articles and books about cargo cult programming but that my guess is is that somebody's repeating that four cores downsize to two cores as a result of cargo cult programming so this also brings to mind one other thing when someone tells you that something is true like you should drop it from four cpu cores to two cpu cores often you'll want to get evidence from them as well okay great you know where's your evidence for that and i bet they actually did have evidence because a lot of sql servers out there don't need four cpu cores it's just that like the the like you're aware there there microsoft charges you a minimum of four cpu cores in terms of licensing uh when you're licensing a vm just as a side note i would be a bad consultant if i didn't mention that what a lot of folks do when they're licensing ver in virtualization is they'll license the entire host and i say entire as if it's some big palatial monster but you know you can get a four core host it's easy just one socket quad cores very fast cpus and then you license those four physical cores and you can turn on hyper threading you effectively get eight cores now this does require enterprise edition but then you can run as many guests in there inside that server as you want if you're doing that if you're buying really low core count numbers like four or eight cpu cores then and you're licensing it with enterprise and cramming a bunch of clowns into that car then yeah every cpu core will be important and i wouldn't be surprised at all to see sql server guests running with just two cores all right that is today's uh a list of highly voted questions i am now going to go back to the hotel and go back get some more coffee and uh sit here actually uh sit here and wait for the i say the sun to rise but it's already risen what i really mean is wait for erica to rise and then today we were going off to the meevo nature baths which is this kind of like the blue lagoon but out more in the countryside it's a set of geothermally heated baths i will not be doing it office hours from there but i just love these things we've been having a whole bunch of spa days when we're out in iceland you may have also noticed a whole bunch of little animals flying around my head through the course of this webcast the iceland likes to say that they don't have mosquitoes but instead what they have is midges they're these tiny little you know flying insects they don't bite but they're just annoying as all hell especially during the summer now here they're not so bad but where i'm heading today meevon they're epic terrible so i'm hoping that they're not too bad over at the meebo nature baths all right well thanks a lot for hanging out with me and i will i guess i'll walk away from the camera and i'll leave y'all a little bit of time uh watching the the boats here the the engine-ish noise like the mechanical noise that you hear in the background one of these boats has a bilge pump on and i'm not exactly sure which one not like i care which one it is but that's where the noise is coming from adios you

Channel: Brent Ozar Unlimited

Views: 3,509

Rating: 4.927928 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: BMvm7_D9RwU

Length: 17min 20sec (1040 seconds)

Published: Wed Aug 04 2021