Office Hours: SQL Server Q&A at Dawn in Cabo

hello party people and welcome to office hours in cabo san lucas on the beach i was going to shoot the sunrise to let y'all see the sunrise as it happens but then as i was setting up the cameras i realized you can't see both me and the sunrise so the sunrise is going to be happening over there this is the uh what we call the end of the world here down at cabo san lucas the arch down there the sun rises up over there and you see all these swimming boats that are swimming those fishing boats that are coming out here today that are heading out now to go whale watching take people go fishing and all that kind of thing so what i'll do instead is i just move the camera around this way so at least i can be out on the beach and i'll see the sunrise you'll see things brighter but i'll show it to you as it officially starts to rise up here it's like 6 41 we got about 15 minutes before the sun rises and while we do that i will go answer your questions so the top voted question from pull gab is from junior wannabe dba junior wannabe dba asks hi brent a colleague wants to upgrade from standard edition to enterprise edition purely for the performance gains on several servers he says our servers are up to eight cores 128 gigs of ram sql server 2016 or 2019. is this change worth paying for i can't find any benchmarks to support or contradict such a change so what i would step back and ask is what are the sql servers top bottlenecks for each one of them what's its top bottleneck and what are the queries or indexes that are causing that bottleneck when you say you have up to eight cpu cores at eight cpu cores that's a difference in licensing of about forty thousand dollars just for that one sql server alone if someone wanted to spend forty thousand dollars to make performance better i would say that the onus is on them in order to go prove that it's actually going to make things faster because most of the time in my experience that one change alone without also making other changes like putting more memory into it isn't going to be enough to get you across the finish line however one scenario where it totally does make sense is if you start consolidating and paying licensing by the vmware or hyper-v host rather than each individual guest you happen to mention that you have several sql servers including one of up to eight cpu cores well that means that at uh you could start licensing the vmware host and then run as many guests as you want on there that's a really common scenario when you only have say only you only have a few sql servers like you mentioned that you have a few so just license one vmware host or hyper-v host license it with enterprise edition and then all the guests get to run enterprise edition you if otherwise if you're licensing via standard edition you have to pay the license every single guest which ends up in some situations costing you more than just licensing the whole host with enterprise so run those numbers and that might be a win next up and i i should answer too so you can't find any benchmarks to support or contradict yeah because i don't think that i've ever seen anything go out like that either if you're going to say just going to enterprise edition uh made this much of a performance boost you wouldn't see anybody running that benchmark unless they also upgraded memory at the same time usually when i see people go to enterprise edition like we talked about forty thousand bucks extra on a on an eight core server um like what you know as long as we're putting 40 grand worth of software in here how about we throw a couple few grand worth of memory in here and take this thing to 256 or 512 and then that's where you if you saw the server being storage bottlenecked you'd see the performance take off for other reasons next up we have oh my kingdom for a network engineer says hi brent you mentioned dnsc names to redirect during log shipping failovers my friend wants to redirect end users to a new production server after upgrading by changing their cname but knows nothing about dns c names or active directory my googling skills didn't help is this kind some kind of active directory magic it's not active directory magic you can do it with any kind of dns setup but here's the thing i would take a pause on if you don't have a network person uh whoever manages your active directory your logins your security if you don't have someone who manages that for you and you're trying to use this technique i wouldn't i would stop there and i would just go through and change the whenever you go do an upgrade move over to a new sql server just go power down the old change the old sql server's name to be whatever underscore old power it down power on the new sql server rename it to match what the old sql server was and there's a knowledge base article with the stuff you have to do when you rename a sql server but rename that new sql server to match what the old one's name was i know one company and one client of mine whose production sql server is still called sql 2005 for that exact reason it's a 2019 box but they still call it 2005. that way you don't have to do something that you're uncomfortable with and i would hate to have you do something uncomfortable with and put it in your production path if you've never done it before having said that i'm always real suspicious when uh companies are have database people and they don't have uh windows engineers or systems engineers who are handling that kind of thing that that make that gives me a little bit of pause and nervousness there uh neck because so why does it give me pause and nervousness i'm gonna move this camera a little further back because i'm getting ready to yell uh no so why does it give me pause and nervousness because uh if if you don't have a windows engineer or linux engineer whoever you wanna do enough that knows what a dnsc name is i get really nervous that we're taking other uh security shortcuts or os patching shortcuts too next question loud howard asks hi brent what are your top microsoft hype features of sql server that never caught on [Laughter] okay so one comes to mind immediately which is a java stored procedures when that came out i just could hear this thud in the room java stored procedures uh clr never really caught on that much the only reason people i say the only one of the few reasons where people usefully use clr these days is for regular expressions in sql server but i think the bigger complaint from the community is more like yo why don't we get ra regex support in sql server why do you make me deal with the clr crap not the clr's crap but i think my all-time favorite has to be sql server notification services uh back in i can't i want to say it was 2005 microsoft brought out sql server notification services and then deprecated it at the first service pack i think and i remember even hearing stories that someone was a notification services mvp from microsoft and that poor person lost uh lost out a heck of an opportunity there but i would always say my advice with features is always whenever something comes out let's let it bake for a version and see what they do in the next version because there have just been so many one and done features that never got a dollars worth of improvement and i'm going to rattle some feature names off here and it's going to piss people off but whatever change data capture change tracking replication there have been all these features where they come out and they solve a real world pain but then you hear all these screaming from end users about oh my god we need blank oh my god we need blank and they don't get any help from microsoft they just stop putting development money into it somebody in the comments is going to be like well but we just got something for peer-to-peer replication yeah but it's 2019 and that came out like two decades ago you know it's you can't invest something like once every two decades and call it a win uh next up sql steward says hey brent a friend of mine is trying to explain to his boss why using a function to create a temp table which you then recreate your main olap like data warehouse fact table every five minutes is a crazy bad idea how would you explain this in plain english i really appreciate all you do it's for me that one's very simple it's the transaction log throughput if you're replacing i'm just going to make a number up a 10 gigabyte table every 5 minutes that's 10 gigabytes of data that's going into the transaction log that then has to go get replicated everywhere else 10 gigabytes every five minutes isn't gonna kill you a hundred gigabytes every five minutes is gonna kill you dead so it's the act the part of writing all this stuff into a new table so then you have to start thinking about all the things that you have to do in order to avoid that problem like using minimal logging trying to do bulk inserts a certain way but just open that up and say did you think about this and if you haven't thought about this and you can help to illustrate that problem if you show them how large the table is today and do just do a simple insert into another table name and watch to see how much transaction log backups you have that's how much would need to be replicated to every always-on availability group replica database mirroring sand replication and all that kind of stuff let alone everything else involved with the locking and blocking francesco says hi dad to be clear i have no children and i have no plans to uh hi dad do linked servers handle parallelism on the remote server is the option max stop whatever worth using in a linked server query you rock make those turtles go faster for the record he's referring to the turtle nests and i i should move the camera around so you can see the little posts there are i don't know that you're gonna see it but there are posts on the beach that mark where the turtle nests are so you might see a couple of uh posts down there and then there's another post over uh here as well posts on the beach that mark where the turtleness are and the baby sea turtles make their way to out to the sea every morning so we go out there and look because they don't all hatch at the same time they hatch out of their eggs at different times so we now go out here and patrol the beach each morning to make sure that they make it all the way to the sea and that they don't get picked up by birds because sometimes they kind of get uh mixed up with where the lights are around here the bright moon and they don't see where the sea is so my answer to that about parallelism on the remote server uh so yes i think i'm getting bitten by something out here and i don't know what it is uh the so just does sql server handle parallelism on the remote server yes my bigger question though would be why are you sending so much work over to the linked server that you need parallelism if you find yourself doing big huge operations like scanning the table on the remote server to pull stuff back that's your sign that you would really just you need to connect to the server that has the data that you want i am okay with linked servers doing pinpoint pull this five rows back or pull this two rows back now doing a very quick go check the customer or something like that even though it's a nightmarish hellscape for caching and concurrency but if you find yourself performance tuning linked servers yo dog it's time to just go connect to the server that you need next up dba greg asks is there any reason to stagger start times for check db on availability group replicas we just added a third node and we have all of the replicas check dbs running on the same schedule is there any impact on things like log replays between nodes when checkdb runs and would the internal snapshot affect this i have absolutely no idea absolutely no idea i've never measured that or experimented with it if i had to go to start by checking some place frankly i would just probably just go do it and go see what happens the times where i would be concerned about it is if you have high transaction throughput at the same time that you're trying to do check to me like if you're trying to deal with a hundred queries per second which isn't usually a high throughput number that i use usually i think a thousand or ten thousand uh batch requests a second is my more mark of oh there's something going on on the server but if you're worried about check to be even at a hundred batch requests a second i would say okay then then go in and start running the tests but if you're not even running a hundred batch requests a second i don't know that i'd worry about it um david says hi brent is it okay to defrag the data files in disk the vm is hosted on a vmware platform the database sizes are two to four terabytes i would not bother with that no that isn't something that's going to get you across the finish line from making the storage fragmented from defragmented from vmware's perspective no that you can ignore that uh great work brent asks were you aware that your videos don't play in youtube's restricted mode my work enforces this mode it may be due to your graphically violent slaying of common sql problems or your profane and incendiary comments about stupid people doing stupid things or you have a drinking problem well that's a great question uh to for me for my videos to play in restricted mode i would have to say that they're okay for children and for me to do that like i feel guilty i don't purposely do anything foul or obscene or whatever but every now and then i'll let an f-bomb rip or i will be drinking booze while uh streaming i'm not doing it today it's early in the morning it's 6 55 a.m i will have a bloody mary with breakfast uh probably i say that i did that the last couple of days i probably won't today um but it's just not worth the risk for me that isn't something that i would want to to get because if you run afoul with it if they they if you say that your videos are safe for children and then they are not then youtube starts taking swings at you if things are reported so unfortunately i i would say that your the big takeaway here is that your boss thinks that your children next up qumeron asks hi brent we have a application with many dynamic sql stored procedures microsoft suggests to parameterize the xx sql and the app team prefers not to rewrite the stored procedures my friend says dynamic sql should be avoided for better performance and for query stats is he only right about dynamic sql at the app layer i think in here almost everybody's wrong so i wish i had a fast answer for this but i have about an hour on this in the mastering server tuning class around the problems with unparameterized dynamic sql and then i have about an hour and a half in the mastering query tuning class on how to build properly parameterized dynamic sql the thing that i would say to you is yes it's nice to want properly parameterized dynamic sql and dynamics equals wonderful for a performance perspective dynamic sql is better than stored procedures in many cases which we i show you why that is in mastering query tuning but the drawback of this is is this mild suggestion it's easy for a database administrator to say you should change the way you're doing t sql but it's really hard for application people to redo all of that work step back and ask what's the team's biggest problem like what's the server's biggest problem and how do i go about solving that problem as quickly and efficiently as possible because it's probably not going to be like giving advice to rewrite all your t-sql let's take a moment here to go shout out to our sponsor the sun uh so we have here the uh sunrise happening here in cabo so i'll actually i'll leave it there for a second while i do the next question here let's see the next question is uh qumran qumran again a different person or a different question qumran says could you suggest any solutions for sql server patching that is sql server feature aware no unfortunately the the problem with sql server patching is you can't just be feature aware you also have to think about things like backups if you have a running backup for example uh you can uh you probably don't want the patching to start in the middle of it and then make your backup fail that would be kind of catastrophic you would probably want the patching to allow the backup to finish before the patching kicks off so that's i the the problems with that with like long-running uh check db jobs with data warehouse loads you name it that's why there are no feature-aware patching type stuff i'm going to move the camera over here just because we've also got whales jumping over here and i don't know that i'm going to see him well with this camera normally i use a zoom lens for this but we'll all just go on with the next question and we'll see if we get lucky with uh catching any of the any of the whales jumping while we're out there now next up we have sequel 100 sequel 100 says when trying to design a data warehouse there will be data coming from different sources do you recommend converting the data into a csv file so that it can then be imported easily or should we do it directly from the database for this i really like an etl tool something like ssis azure data factory aws glue that manages those kinds of transformations i don't know if you see it but the the whale is right there is spouting over there that manages these kinds of transformations for you and i can't zoom because i don't have a zoom lens in with me and because the the reason why is the same tool that generates the csvs the work that gets put into generating the csvs it's going to have to do things like change detection and understanding you know which rows change which rows are new which rows need to be deleted well the kind of tool that you use to build that is typically an etl tool like ssis or azure data factory so you might as well just have that manage the entire import export process and then and you leave that to those professionals they pull they can pull directly from one database to another or you can have different teams uh put out an uh csv or a json file into a central file share that then everybody pulls from brave sir robin asks hi brandt should we do any sql maintenance tasks non-traditionally i don't even know what that means if we have crazy fast temp db and crazy fast san when you see any sql maintenance tasks non-traditionally i'm not sure what you're fishing for there uh you may want to be more specific about the problem that you're trying to solve now you know what is it that you feel like you need to change about your setups the the one when you say non-traditionally the one thing that always comes to mind is sand snapshot backups uh but so if you want to look into sand snapshot backups great but otherwise ask about the problem you're trying to solve and next up gob bluth asks hello brent how do you measure right latency to an always-on synchronous replica and here we'll switch the cameras back around how do you not that you not that you necessarily always need to see me but whatever there we have it uh says god blues asked how do you measure uh right latency to an a no to an always-on synchronous secondary replica for that what you want to do is look at the server's weight stats under weight stats there is a weight type called hater sync commit h-a-d-r sync commit and that measures the latency of sending transactions to the synchronous secondaries and as you're looking at that weight type what you want to look at is average milliseconds response time you have to calculate that yourself you take the total time that it waited uh divided the number by the number of times that you waited for that and that will give you in milliseconds how long the sync secondary took in order to answer every insert update or delete you can also think of that as the average length of time that's being added to each transaction next up uh a dba dba'ing says sometimes i see multiple values for the same parameter in the cached parameters section of sp blitzcash what is the reason for that the reason is that you have multiple plans in the cash for the same stored procedure and a common cause for that is if someone goes into management studio and they types set and they type set ansi nulls on or set antsy nulls off they have the opposite setting of whatever the application is doing you'll get one plan cached for ansi nulls on and another plan cached for ansi nulls off i heard a boat honk which is why i looked around you don't usually hear boats honking um different plans cached for different uh connection options um so we're giving you all of the parameters that populated all of the plans in cash and then we should end on this one raphra asks how do you select the picturesque places where you stay and stream your office hours from well i pick the place i want to go and then i just happened to take y'all with me uh so erica and i first came to the resort right down there the uh used to be called the resort at pedragal now it is the was it uh waldorf astoria waldorf astoria at pedregol i got fancy um several years ago on a black friday sale coincidentally erica found a black friday sale there was phenomenal love that resort it's absolutely amazing but these days it's uh two to four thousand dollars a night oh we we stayed when it was much cheaper and we had a black friday sale i think we only spent like 300 a night and even that was expensive but it is a hell of a nice place and so we fell in love with that and we came back there several times afterwards usually on black friday sales each time um and uh we ended up like i wanted to live here i was like this place is absolutely amazing but there's only one condo building on the beach which is where i happen to move into so it took a lot of planning and help from erica over the years and finally bought down here so you'll see more uh streams down in the baja peninsula of mexico as i go back and forth between san diego and mexico all right so the sun is coming up uh we have the sun has risen i will go pop this over here so that you can see just that the sun is rising now whereas is has popped back up here um so i'm gonna go head out for the day i'm going to go first get breakfast from the restaurant down at the condo and then go stumble around downtown i have to go get a pool raft among other things and i will see you all at the next office hours adios

Channel: Brent Ozar Unlimited

Views: 2,962

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: rtriOfpu_SU

Length: 25min 28sec (1528 seconds)

Published: Wed Dec 15 2021