Office Hours: Ask Me Anything About SQL Server at Seltjarnarnes

hello party people and welcome to the celt jar narnis peninsula probably not quite saying it right just salt jar harness it's all just one word uh peninsula out in reykjavik i wanted to film here a couple of times before but you can only get out here to the lighthouse and to the lighthouse keeper's house there's this little island that houses this stuff you can only get out here at low tide it's a really beautiful area but then up at high tide the water covers the little peninsula and you can't walk out here there's even you can even see on a clear day you can see that volcano that's blowing out from iceland right now over on that side of the horizon there so it's just a really neat isolated place to be out here and now hang out with the birds it's a bird sanctuary too it's close to the public during the summer up until like june june or july i forget which one humans aren't humans aren't allowed to walk around out here so let's see what questions y'all had to operate a question was from geo geo says are there any known gotchas from using windows update to apply cumulative updates to sql server it says i use it to update windows and sql server at the same time for my development and log ship servers and so far it's been smooth sailing but i can't convince myself it's a great idea for the primary normally when people ask that they're talking about automating it like having windows update automatically apply things at certain times of the day or night i'm not a fan of that because you can have a long-running transaction or a backup get interrupted by windows update i've seen so many cases where people set up their windows update to run every night and then didn't realize that it was restarting at the same time backups were running so it's not like you get an automatic retry with sql server backups if your backup fails you just don't get another full backup until the time for the next full backup same thing with things like data warehouse loads long running jobs so when it comes to updating production servers i would just really prefer that there's a human being looking to check on that it's okay to do the sql server as opposed to just having it fire and forget automatically in the background and missing things like backups next up alwayslearningdba says hello brent a friend of mine wanted to know what camera system you're using when you're doing outdoor office q a sessions he's curious as the camera follows your movement and he says kind of moved around a little camera follows your movement as if the camera is uh someone's moving the camera physically and he thinks it's awesome it's called a dji pocket two i just bought this well in the last month uh dji pocket two it's a little handheld camera with a gimbal on it so that not only does it track my face and move around but it will also stabilize so that i don't have to worry as much about getting the tripod exactly level which is nice when i'm on places like beaches and islands the one downside of it is it the face tracking isn't very tunable you get fast and slow but that's all you get and this is even the slow option and i know some of y'all have complained that you get a little motion sick when i move around a lot because of course i'm very animated so i've been trying to move around a little less because otherwise it just you know bops around all the time when i'm talking i wish if it was more tunable i would slow it down a lot but next up pj pj pga says hi brent if i have first name and last name and one index and then city and age on another index and my query uses an order by with a column from one index and also a column from the other will both indexes be used they might be used for filtering with a where clause but they're not going to be used for filtering in an order by also this tells me a couple of things one you haven't watched my how to think like the engine class it's totally free go take a moment to go google for that how brenno's are how to think like the engine and it's available totally for free in both written format and in youtube format so whichever one you prefer and i i go into the mechanics of how that question works second thing it tells me is you're lazy because you put all this stuff together you really could have just scripted it out and tested it instead of waiting for someone to answer you on youtube go do a freaking experiment when you have questions like that people don't wait for someone to teach you just go try it you can see it in a matter of seconds impasta dba says hi brent the execution plans replicate over to the secondary in an availability group i'm wondering if during a failover occurred planned or unplanned will the plans be the same no execution plans do not copy from the primary to the secondary unless you're using query store to for any method of forcing query execution plans if you use a method to force query execution plans then that can carry over to the secondary but otherwise you can imagine why it would just be so speed intensive to copy every execution plan over to a secondary every single time an execution plan was compiled yeah so that's the end of that one this also means that if you have readable secondaries you can run into parameter sniffing issues where different secondaries have different execution plans because they were sniffed four different parameters because the first parameter that you ran is different on each of the secondaries jack says hi brent with all the certifications available now for azure amazon and google has your opinion on the value of certifications changed no they can't all be long answers i'm a potato who even uses an emoji that's good potato says hi brent is there a way to know the health of your statistics on sql server and prevent their most common issues yes if you look because you're here you're probably on youtube on youtube go on to the brando's are unlimited channel and scroll down under uh the different playlists that we have there's a whole course on statistics you can go watch the course on statistics learn the most common issues that statistics have and how you go about preventing them i know you have to learn right but it's free could be worse i could be sending you to a paid class that one's totally free thomas horner says and your experience is using always-on availability groups does it require more database administration and monitoring and maintenance i.e babysitting yes it's so much more complex with so many more moving parts there are so many more things that can fail and you're really putting all your eggs in one basket in the form of the windows cluster when that windows cluster goes down you are in trouble so i it like all of the replicas are unavailable and you have to fix the clustering before you can even get anything else back up and running online so yes absolutely it's much more complex rick says how should we capture a baseline or a series of captures with the blitz scripts that you wish would have been collected to help understand performance problems when the server is under stress at a later time or to paraphrase rick's question how do i collect metrics when during the good times that will help me during the bad times so what you can do is you can run sp blitz first it has a parameter for output database name output schema name output table name then if you read the directions for sp blitz first it tells you how to use those and capture every 15 minutes to a table that way you can go back through the history during the bad times and start to understand what was good and what's different but here's the thing there are a couple problems with this one an avalanche of data doesn't usually help you all that much when times are bad when times are bad what i find with a lot of database administrators it's that they're all nothing against them i mean this just is what it is i'm i was in the same bucket that more data wasn't necessarily helpful what i needed was a way to cut through the noise and get just to the signal like how could i quickly get to root cause analysis and the more data that you have the harder it is to get to the signal if you're not used to using that data for troubleshooting when clients come to me for performance tuning i actually sometimes they'll go here we've got all this data and i'm like okay yeah no hold on we're just going to go look at the production server and i'm going to see what's going on right now because i even if i know what's different in terms of the performance metrics i don't necessarily know what changed oh believe me nothing changed we didn't change anything nobody touched yeah no i don't buy any of that you might be gathering metrics from a different version of the app code before somebody changed the indexes before a sql server update so for me i don't want an overwhelming amount of background information i usually just look at what's going on with the sql server right now and go i understand that three months ago you were happy and healthy you're unhealthy now let me show you what's unhealthy so that we can get past and start fixing that oh i was like i hear something really loud but it's like on the next peninsula over there's some kind of loudspeaker thing happening i don't know what's going on there all right next up let's see here uh willem says do you know of any success stories of companies that have made the move to azure with a large sql of server estate like 80 enterprise servers and up we're trying to figure out how to attempt this but there seems to be so many pitfalls to be made aware of oh sure i've had clients go 100 to amazon 100 to azure 100 to google compute engine and i don't want you to think that hiring me was a key to that that that's not the case i'm just saying i know of a lot of customers who've gone through and done that i don't know any customers where it was easy i don't know of any customers where it was fast i don't know of any customers where it was cheap it was genuinely hard work in the sense that if you're old like me when we moved from physical servers to vms it was also a lot of hard work it was also a lot of planning a lot of preparation but by the time we got to the promised land with virtual machines then it we ended up saving money because we could pack more clowns into the car the cloud's kind of the same way you can save money in the really long term but in the short term in the first one to three years it's a much higher expense as you go through those migration processes i one of my clients uh huge thousands of sql servers um said all right we're going 100 to the cloud period uh that's all there is and uh they went from three dbas to 12 dbas in a one year time span in order to accomplish that project so it was you talk about not not not trivial work at all stockburn says hi brent any advice on deadlocks where the two processes are an update and a delete they both hit the clustered primary key and then as you as you put it one of them gets a bullet from the deadlock monitor thanks for these office hours you're welcome generally when that kind of thing happens it's because of either you have too many indexes and the two update and delete are colliding on the non-clustered indexes because not only do they have to keep the clustered index in sync they also have to affect all the non-clustered indexes i've seen situations where people have a handful or a dozen non-clustered indexes and you end up getting blocking in over on those and one one will grab an index before the other and end up in a dead locking situation the other the time when i've seen it is when sql server can't figure out how many rows will be affected by the update or delete and they both try to escalate to table level locks when those estimates were incorrect so have as few indexes as possible in order to support your workloads and make it as easy as possible for sql server to understand how many rows you're going to be updating and deleting in those individual statements next up catman jan says applications now have tech like kubernetes and are more or less hands-off excuse me i'm sorry that was not fair and hands come on now um what are you just like reading brochures have you actually tried to manage kubernetes why don't database engines do this too how many millions of hours have been wasted on okay so i i have a feeling that you're just uh you're just hearing the kool-aid of what some of those technologies are the other thing with that that is legit with applications using kubernetes is most applications are stateless they don't need to maintain a single source of the truth across multiple servers or the single source of truth that they're managing is in the database it's not in the application tier so you can lose application servers left and right and it doesn't really matter you can create new application servers left and right and it doesn't really matter they don't have some one piece of truth that they're all trying to maintain so that that's really the challenge with relational databases is you have this you have to deal with locking and acid properties to make sure that you when you update someone's customer balance that it makes it to disk nosql solutions solve this in a different way with things like distributed quorum and consistency handling you can learn more about that with if you google for the cap theorem for example you'll see how other platforms manage those kinds of trade-offs but sql server was never designed to work with multiple masters all agreeing on a quorum and really i don't think any relational databases were designed to work that way non-relational databases absolutely but so if that kind of thing is important to you if uh having the ability to put bullets and database servers at any time and keep up and running check out nosql solutions because they solve this in really cool ways i'm still laughing at that lazydee asks hi brent in upgrading from sql server 2014 and 20 to 2019 what can be done about clr so that it doesn't break due to the changes in clr security i don't do anything with clr so unfortunately i don't have the answer to that one i you know it's just one of those things where i'm like if you need to do c sharp code that's what app servers are for go use an app server next up vegas says what do you think of red gate sql tool belt a friend insists that it's necessary that it's a necessary purchase to help production dbas manage an environment i'm fine there are a ton of great utilities out there i'm totally fine with relying on third-party tools i can't in my weirdo job because i have to keep parachuting in from one customer to another and i can't rely on them having any one specific tool but if you're working at the same company for you know years on end yeah yeah go buy third-party tools that make your life easier he says any other similar tools or utilities that i'm a personal fan of things like excuse me source control comparison tools multiscripting etc for me now because i tend to not do multi-server management i just work on one emergency at a time but i think that if you are a multi-server production dba if you're managing a farm of databases you need in the year 2021 you need to know powershell and you need to know dba tools dba dbatools.io is a framework that helps you manage more servers with less work totally open open source it's free so i would totally go down that road again i don't use it i don't use i do use powershell in a really weird way but i don't use powershell and i don't do multi-server administration so i can't speak for them personally but it's obvious in the industry that those are just huge and they've taken over null pointer says i've been fighting with tuning a query or view this view joins to another view which when run by itself is super fast but when i join it to a larger query all the estimates are way off the stats exist on the column okay so this your and you go on into more details the thing where it's time for you to go head into is either my fundamentals of query tuning class which is one day or after that my mastering query tuning class which is three days long and i go into that exact problem and what your options are for fixing it oh man i wish i could teach it to you in 30 seconds but unfortunately that's why it's four days of training all together because there's a whole lot to cover inside there dave says what would you think about a disaster recovery plan that involves taking differential backups of the production server every two hours asking for a friend oh so funny i've run into that exact situation so i had a normally if you ask most database administrators they'd say that's probably not so smart you should be taking log backups instead but there are edge case scenarios where that makes sense and i'll give you an example one of the shops that i was working in can had an application that continuously updated the exact same data over and over every second they were executing thousands of updates to exactly the same rows trying to think of a way i could abstract it think of it as like a web page hit counter you remember those counters that used to increment how many uh times people have seen the web page imagine that it's like that where the application is continuously saying update view count set it to view count plus one so the application super chatty writing over and over again the transaction log would be huge if we tried to back up the transaction log but the actual data itself was fairly small so in that case because the business didn't need point-in-time recovery because like think web kit webpage hit counters you just you could lose a couple hours worth of data and it wouldn't really bother you that much in a case like that we did full backups once a day and then differential backups every so often so that whenever all hell broke loose we didn't have to worry about all the changes in the transaction log we could just apply the diffs which were extremely small where it makes less sense is where the differentials are large take a long time to run take a long time to restore etc and the log files are small so just that that helps guide you down the path there next up scott mcfadden asks hi brent i appreciate your sql server book recommendations they're spot on i'm glad you enjoy those do you have any book or training recommendations for learning postgres no except for there's one course that gets rave reviews a curious moon m-o-o-n a curious moon if you search for a curious moon postgres it's a training course that teaches you postgres using data from nasa if i remember right so in terms of querying that's useful i don't have any good recommendations around managing it because when i went to use postgres we used amazon aurora which amazon manages for you which is the big reason of course that we went for it i this is gonna sound funny but when we designed the databases for sql constant care our monitoring and trending service i didn't want to be the database administrator i don't want to be on call when something breaks i would much rather pay a cloud vendor to manage that stuff for me managing things like sql server is a great way to take money off of the table like for career type purposes you you can make a lot of money doing that but it's a really crappy place as a small business to to have to worry about managing up time so i'm a huge fan of amazon aurora uh next up champagne dba says hi brent i read your five part blog on foreign keys that's actually eric darling if i remember right eric darling's post he says and you're concerned about locking uh when using cascade delete wouldn't that not be a concern when using read committed snapshot no it would be because when you do isolation level upgrades due to cascading updates or deletes they're serializable range locks as in nobody else can go into ranges of rows and that's nasty when you need to handle concurrency on a table so if you're even with though you might be only inserting updating and deleting a couple of rows you can end up locking ranges of rows in other tables thereby stopping concurrency next up pat says hi brent i'm an accidental dba my database monitoring software often alerts on os paging what's normal to hone in on i don't worry about it at all not even a little bit if the os wants to page something for some reason that's completely fine as long as sql server is the only application installed on that sql server we're not trying to run other things like integration services analysis services reporting services internet you know browsers whatever and then also that you set max memory memory appropriately according to my setup checklist where i talk about leaving at least four gigabytes of ram free or 10 whichever is greater leaving that free for the os to go do its thing as long as you do those two things the os will still scribble things to disk from time to time but they won't be significant it won't be a performance factor and let's see next up 7 says how does sampling work for statistics then he goes on in deeper rather than try to address it in a short period i'll just tell you go to our youtube channel and then go to our totally free statistics course and you'll learn way more than you wanted to know just absolutely great stuff spongebob squarepants asks do you have any recommendations for tools specific to ods's in sql server no unfortunately again they can't all be long answers david says do you have tips on how to deal with people who take other people's advice or tips as their own in the workplace here i am with a year and a half of experience in sql server teaching people with five plus years of experience but when they tell the boss they say it was them who came up with the query um that's an interesting question um at the end of the day you want your co-workers to be successful and okay let me let me say it this way okay so i'm a trainer right like i teach people all the time and i teach people for free i teach you out on youtube and all that i know you all take credit for this stuff i know you do i know you all take credit for my stuff i have seen community presenters take my material rip off my exact session titles rip off my presentations rip off my content take exactly what they learned from me and represent it as their own somewhere else and at the end of the day you have to go well all right i'm inspiring these people and they're helping to evangelize and teach others i have to be okay with that because that means i reached them in a way that they could use this is going to happen all the time throughout the rest of your career uh let it go my tip would be to be at peace with that and there are some communities where people try to hide their knowledge under a bushel like they'll be like i can't i don't want anybody else to see what i know because i want to take credit for all of this but that's not really an effective way to become known amongst your peers and and do well in your career embrace it be totally okay with that because if they're really as dumb as you apply as you insinuate because your question went on to say some of them don't even know how union works if they're really as dumb as you insinuate it's not like they're a threat to you anyway and odds are people in your office know that they don't know what those people are doing so it's not not such a bad thing khalid says i have duplicates on my primary key column what some of my customers on sql server 2014 have this weird issue in order to fix it i had to drop all the foreign key constraints duplicates on a primary key shouldn't be possible um i'm trying to think of situations where it would be duplicates of a clustered index would be but i i would want to know more about what the problem was and don't like send it to me as a follow-up but i just feel like we're probably missing something here maybe it was database corruption it could be something weird like that but it's not something that you should normally see the whole point of primary keys is that they eliminate them sequel deadwood asks how do i perform index maintenance on a synchronous ag database with heavy fragmentation without well ruining everything well start by googling for brent ozar why defragmenting your index isn't helping burnos are why defragmenting your indexes isn't helping but when you watch that you're going to you may do follow the steps that are described in the video about analyzing your top weight stats and what you might come to a surprising conclusion that it's not really going to make that much of a difference when you do go through and defragment it but if you do want to defragment it use ola holland grin's maintenance scripts and ola holland grin's maintenance scripts have a parameter for how many seconds you want to delay in between each index set that up to like 60 seconds or 90 seconds so that your network and your io subsystem have time to relax and recover in between each heavy round of operations and also let end users use the system a little better now this still isn't going to help you on the big old 10 20 100 gigabyte tables but i would argue for those you probably aren't going to be able to rebuild them without serious performance degradation in a synchronous ag anyway if you wanted to you could flip it to async over a weekend if the business was okay with it and then go and tackle it that'd be one thing i'd trent and then we'll take one more sql deadwood asks according to the trixie documentation hobbit quote there are specific but uncommon scenarios when index maintenance may be needed in azure sql database what are they in english so for me it is i'll give you a a oh man this is going to be tough to explain quickly so the there are a couple there are two kinds of fragmentation internal fragmentation and external fragmentation what the documentation is referring to is that external fragmentation is not all that important up in azure sql db but internal fragmentation is important so if you go to the search for brennozar why defragmenting your indexes isn't helping i've got a video explaining the differences between internal and external fragmentation the kinds of scenarios that produce really heavy internal fragmentation those are the ones where they're talking about you may need to do index maintenance all right well that is a good run for this morning we're coming up on about uh half an hour of questions and answers i keep putting my hands back in my pockets because it's so windy out here that my little fingers are freezing to death um i wasn't sure whether or not we'd get sun out here it's uh it's a little sunny over on that side but uh uh not too sunny over there um oh i should i should pick this up and take you around um so we have i'll just go walk over for a minute uh so i'm going to walk around the lighthouse keeper's house and then show you where downtown reykjavik is at so there we go um so that's the lighthouse keeper's house right there and then downtown reykjavik is over that way the um yeah there was the episode a little while back where i had an animal running around at my feet which we later as a group figured out is a mink that is also over there kind of at the edge of where those white uh like tanks are at so that is it for today's episode of office hours i have a friend of mine pete who just flew into town today who's from america who's going to be spending the week with us so today i got to go lay out exactly what our itinerary is we have a bunch of like he said he's going to leave it up to me to see which sites we go on tour i have to go and map out some of my favorites from the south and the west of iceland the jocas arlen uh glacier lagoon the black sand beach down at vic the trolls offshore the snakeness peninsula so i'm going to go write that up and plot out our trip and i will see you next time adios folks

Channel: Brent Ozar Unlimited

Views: 3,138

Rating: 4.9120879 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: IB2EH2irD6s

Length: 31min 13sec (1873 seconds)

Published: Wed Sep 08 2021