Office Hours: Ask Me Anything About SQL Server at Silver Strand Beach

hello party people and welcome to silver strand state beach in uh beautiful san diego california mexico is i think about 10 miles that way and downtown san diego is about five miles that way we're out on coronado i think they call it an island i mean the coronado island but it's not really an island it's more of a peninsula it's just that it seems like an island from downtown san diego gorgeous day outside figured i'd take a run down to the beach and then go uh do some questions out at the beach for uh office hours so let's see what y'all have piled up over here in office hours let's see dylan asks why does an update on a second key column in an index cause a bad page split the first column is a hundred percent unique oh okay okay so what what i think you're asking is when you update the second column of an index if the first column isn't changing why is it that the row is moving if the first row is unique why would it cause a page split one thing that could do it is if that second column was nullable if it started as null and then there wasn't any space left on the page to put in its new expanded value let's say that it was like a big old envercare 100 and there wasn't 100 characters left of space on the page or 200 bytes whatever you want to call it then sql server may have to do the page split if that isn't what's causing it like if it wasn't a nullable column originally post an example of what you're seeing over at dba.stack exchange and include the definition of the table and some inserts and updates so that other people can see what you're doing because that's my guess as to what's happening but it could be something completely different next up we have maddie asks hi brent why do having non-aligned indexes on partition tables cause inefficient query plans and cpu pressure the really two parts to that question one is why are non-aligned indexes bad and it doesn't usually have anything to do with query plans or cpu pressure it has to do with the inability to do partition switching to do sliding window loads that's usually why people get upset about non-aligned indexes the second part of that question is can indexes cause bad query plans and cpu pressure yes if they're not good indexes for the queries that you're facing that can absolutely cause it but that usually doesn't have anything to do with their alignment that's just bad index design when you put these two things together when you say why do non-aligned indexes cause query pl bad query plans and cpu pressure i think you might be going down the wrong trail there i think it's just that the index design is bad not the fact that they're misaligned but if you're seeing warnings out of sp blitz index or similar tools about non-aligned indexes we're not complaining about query plans or cpu pressure we're complaining about your inability to do partition switching next up we have these glasses aren't bifocals so i mean i use progressives these days you know one prescription for up close i don't need any prescription to see things up close but these are for distance for driving which is when i usually use these things i drove here and i'll show you a helmet at the end of the video bill says we have an application about to come online and i want to baseline the sql server what's the best way to create a baseline do you have any recommended tools or articles yes i talk about it in my mastering server tuning class but that isn't a sales pitch for that class when the application's about to come online you can't really put any faith into those metrics at all so when an application comes online people are just at first getting used to it they're running all kinds of queries that they're not usually running they haven't settled down into a usage pattern yet so often for the first like 30 days of an application the performance metrics that you see there don't really have any bearing on what you're going to end up seeing in the long term now what i would say is this is why monitoring tools are so good you could go attend by mastering server tuning class and learn how to do it but you could also just go buy a third-party monitoring tool i'm a raving fan of third-party monitoring tools because they can quickly and efficiently gather all kinds of performance metrics that you would never think about or never have the time to gather so either go to mastering server tuning or go buy a third-party monitoring tool or just wait 30 days for the first 30 days to shake out because those metrics aren't usually that good anyway next up we have let's see here oh that one let me go move this around bob says hi brent what is your database maintenance recommendation for very large databases vldbs oh for that one i have a totally free answer on our youtube channel go to the fundamentals of database administration class i have a playlist on youtube for the fundamentals of database administration and in there we talk about things like check db for very large databases backups for very large databases and much more so that's on our youtube channel fundamentals of database administration playlist next up ah postno asks is it hard to adjust being back in the states if so what's the hardest part oh buckle up i'm gonna step on a soapbox here for a second uh one thing so i move for those of you who don't know i was in in iceland for the last nine months and then moved back here uh about two three weeks ago moved back to san diego two three weeks ago the one big thing that was different between iceland and here is big highways so here of course in in san diego we have four to six lane highways with all kinds of traffic on them whereas in iceland even the biggest national road only has two lanes on it so that was the number one big thing was fun a fun shock to get used to driving on highways again the second thing for the love of god americans don't understand that masks prevent the spread of coronavirus i don't get it i am shocked i'm stunned at how many people won't take this simple free precaution and people get into elevators with me people get into rooms with me like into close proximity and they don't have a mask on and i'm like my god how do i get that maybe you don't care about your bad breath and what's coming out of your mouth but there are others of us who are immune compromised have asthma or vulnerable to those kinds of things it blows me away how political americans have made it about masks has nothing to do with politics it's not like your chosen presidential candidate is going to save you from some kind of virus it's not like they're running interference on your bad breath put a sock over your cake hole it's just not that big of a deal so that was a big especially coming from iceland where people were so courteous and so mass using mask usage was just a no-brainer indoors it was just completely obvious next up woodard asks i keep getting questioned on why i only let veeam backup software do copy only backups and i insist on native transaction log backups outside of veeam i don't trust any system this is woodard speaking i don't trust any system where i can't touch the actual log backups at will and we use those backups every day he says is this uncommon it's not uncommon for people who have been database administrators for a long time it is uncommon in larger enterprise shops very often here comes the lifeguards oh they went the other way um uh it is more common in enterprise shops big enterprises to split up the duties between a backup team and a dba team and a security team so the larger company you go into the more common it is to have some of those responsibilities taken outside of your control same thing with administrating secure administering security managing clusters you start to see those things peeled off into specialized job roles in a perfect world i would love to see database administrators have fine-grained control over all their backups it's just that i've been through so many gigs i can't even tell you so many consulting engagements where i walk in and some full-time database administrator was sure that he or she had control of their backups and they did not and things had been failing for a really long time and i'm like i get that you think you're good at something but this is not one of those things why don't you leave that to somebody else so that i can have you focus on the things that are really broken in sql server rather than trying to micromanage something you don't even do well so if you're the kind of a person who tests your backups on an automated basis i don't mean you click through some gui i mean you have automated restore testing happening then i would give you more credence to keeping it under your own control next up we have oleg oleg says for a brand new database that is in development would you recognize would you recommend to create indexes ahead of time based on guessing or would you apply the death method after it's been in production for a while oh oleg that's a great question i would recommend when i'm working with developers and they're building a new application from the ground up i'm like look all i really need you to do is put a clustered index on every table and call it a day create a primary key on every table make that the clustered index call it a day and we're out of here when we go live then we'll start revisiting what indexes we need to add but that's really easy to add in post-production and you can't predict what parts of the application people are going to use and which parts they're going to ignore so you can end up spending way too much time overthinking all kinds of features that you never even end up using and the indexes just end up wasted their kimberly tripp likes to recommend that when you create foreign keys you should index every foreign key by default i agree with that i'm totally fine with that if people are going to the trouble of creating foreign keys then index those because they're often the kinds of columns that you join on anyway but just know that you're probably going to have to come back and revisit that pretty soon after the application goes live next up we have pseudo dba pseudo dba says hello brent you made an interesting point about writing a blog about what you know about sql server when you're picking topics to write about write a blog about what you know without worrying about whether similar content is available online or not he says what blog sites do you recommend to post my sql server related issues that i have faced and resolved get your own domain name don't blog somewhere else don't be a sharecropper go buy your own domain name it's like ten dollars get a web hosting package it's gonna be like ten twenty dollars and my personal preference is to use wordpress but there are a lot of other people out there who are using static blog sites these days i'm totally fine with that just pick a tool and roll with it but make it under your own domain name so that you own it forever i'm old i get it i have gray hair but i have seen so many blog sites come and go over time myspace media there have been all these sites that were trendy sql blog all these sites that were trendy over time and then eventually disappeared when their business model didn't work out you want to own your home on the web it is an investment in your career if you don't want to own your home on the own your home on the web and you're just trying to share your knowledge go post a self-answered question on stack overflow or stack exchange because those sites have really good seo and it's search engine optimization so it's really easy for people to find your question and your answer and it is considered totally fine etiquette there's nothing wrong with posting your own question and an answer and during the process when you're posting a question they even make it easier for you but there's like a check box that says do you intend on answering this yourself so if you just want to share knowledge that's a really good way but if you're doing this for your career go on your domain next up we have greg greg says we have a two terabyte production db with seven years worth of data we wish to reduce this to a few months but we still need to hold six years of data in order to meet compliance would you build an archive strategy based on production or would you trust the upstream data warehouse will have your back when you say compliance so here's the catch with that when someone wants to check compliance do they want to use the existing app to do it as soon as you move that data somewhere else regardless of what your archiving strategy is whether you put it in the same database whether you put it in a different database on a different server then you have to modify your application in order to get that to work and that's often a non-starter for people people want to check compliance by just going directly into the application and seeing all the history if that's what the requirement is you can't move the data elsewhere without modifying the application but if you don't need to query the data then i would start archiving it somewhere else one way you could do it is you could keep backups peep companies will often keep monthly backups going back several years then that way you don't need to do anything special ever all the data is just built straight into the backups and when people want to check compliance they can go pull the backups and get whatever data that you need you can also guarantee that that data hasn't been modified because you're not in there writing anything with the backups but if you do have to have one database with all seven years worth worth of history in it then it's going to be up to you to build some kind of loading tool like an etl tool an elt tool that moves the data into its longer term home but that is an architecture question of course that's way bigger than what we could answer quickly here hopefully that gives you the high level overview of what your choices are well there's a handful of questions that we went through and covered i am going to now go take a walk up and down the beach there are people doing uh parasailing out here either out of their ginormous kites of some kind uh depending on how this looks you might have been able to see there's an aircraft carrier out here i'll move this around just a little bit there's an aircraft carrier out here in cruise ships of course san diego has a big old military presence and marine presence and after that i'm gonna go uh take the car up go take helmet up and go run around uh the coast so i don't think i'll go down to mexico i you know as long as we've lived here i've never been that driven that close up to mexico but it's just a fun car to go take around and go drive up and down the uh uh the area you know what else i should let you do i should let you see how the top goes up and down on this thing because that's kind of fun let's go move this over here and i will put the top up and down because if you've never seen this on a targa before this is really a big part of the fun is how this thing moves up and down we'll set this right here and then move this down just a little bit to focus on it and let's oh i forgot i can do it via the remote of course too all right so let's see here i'll get out of the way so that you can see it this thing's like a ballet i just love watching that i love hitting the button and walking up to it and watching that happen it's just so much fun oh just absolutely love that thing it's great as long as the car never gets rear-ended if it ever gets rear-ended it's probably never going to work the same exact way again so thanks for hanging out with me at office hours and i will see y'all next time adios

Channel: Brent Ozar Unlimited

Views: 469

Rating: 4.8666668 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: qTocbvnQgFs

Length: 18min 14sec (1094 seconds)

Published: Thu Oct 14 2021