Office Hours: Ask Me Anything About SQL Server

good morning party people howdy and welcome to another fun weekend it was fun for me at least i have worked for seven days a week for like on and off for like the last couple of months and so it's been kind of weird like i'll go through sprints and then uh none sprints and then none um and when i say none i mean like i disconnect and go somewhere else like malibu california or whatever good morning mossy massimo and so i've had like today i was kind of excited because i had a weekend emergency client scheduled and they cancelled at the last minute so now i get time off oh which is kind of awesome although i have work that i need to do that i owe richie richie and i are working on sql constant care and i owe him a bunch of things that i have to to add and like plan out for the next round of improvements that we have to do so now today i get to go sit by myself on my balcony and like go sketch out things that we need to do for the next version of sql constant care oh lee good to see you again as well um and mossy masimas it was funny that um i was thinking of you this morning because i was like eventually all of this quarantine thing is going to be over and eventually we're going to have user groups and conferences again and when we do we are all only going to know each other by our twitch and youtube names and we're not going to know good morning we're not going to know anybody by their real name so we're gonna need like t-shirts with our uh twitch names and youtube names just because it'll be easier to recognize people but like i knew when surly dev and i met in person i knew him from his shirt like he had said that this is a shirt i'm gonna wear so i'm like oh i know exactly who you are uh morning neil good to see you again uh welcome uh kavishka yahora says how do you wake up so early on holiday i am hard-coded to wake up at like three or four in the morning at the latest so usually even when i'm on vacation it's kind of funny we often go to the same place in mexico and there i will wake up like two three four in the morning in mexico when i'm on vacation i'll go down to the the there's like an oceanfront restaurant i'll go down sit down there with my ipad and the people uh actually recom or actually recognize me because i'm down there every morning and the cleaning staff will bring me out coffee uh so so it's always fun to to you know like be that early in the morning for stuff uh but then on the flip side is i'm dead at one o'clock in the afternoon one in the afternoon i want to take a nap um sql dev dba one of the things i really wanted to do for this year's black friday sale was i really wanted to get it so that when people bought a live class season pass or a bundle that they would automatically get a shirt without knowing about it like i just purely wanted to surprise them but then i realized the sizing thing is such a pain in the rear trying to get a t-shirt size right like this the shirt honestly if we're being serious it doesn't really fit very well but i love it because it's from the national spy museum in washington dc so 2 am yeah but so then i was like oh what could i do could i do coffee cups and then we've tried shipping coffee cups a few times lycra oh my god is that terrific is that a terrifying thought or what good morning sir um no i said yes too we got chris in here this morning all kinds of people the man who knows things unfortunately the things that i know aren't usually terribly useful most of the things that i know are pretty much uh worthless uh sql dev dba says the shirt is actually pretty true to size it's not bad it just feels a little awkward in terms of how it's it's uh just not quite cut quite right it's not not terrible but uh nice um these do stretch a little bit these are uh kind of a little bit uh stretchy but i love the national spy museum in washington dc is absolutely amazing it's really cool uh if you ever get the chance to to use it i or go to visit out i highly recommend it um thanks steve uh very cool i like i always usually like to have them on my desk when i'm working with clients and drink from and they're like where when am i gonna get one of those uh one of those mugs um so yeah so now i get to kind of goof off this weekend so originally i wasn't gonna stream either originally i was supposed to work usually with my weekend emergency gigs i have to do some prep right before the gig and then we get on the camera together at like 8am pacific so that's true i haven't gotten one of the tumblers yet i have to go uh buy one of the tumblers i also have to get the jacket i don't have one i was gonna order one of the jackets and then i forgot i should actually actually i shouldn't take a note of that i was gonna take a note of that so i did it right after the stream but i think my wife is a little angry with me for the amount of shirts that i bought over the last couple of weeks because like every quarter i like to go buy a whole bunch of t-shirts for the streams mostly for the live classes because i always think it's kind of funny when i show up with uh different uh no exactly chris you nailed it um that i'd like to show up with slightly different t-shirts and uh i've already bought a whole bunch of t-shirts and my wife gets kind of upset when i get small packages repeatedly sent to the house because she disinfects everything because with whole coronavirus thing so we'll we'll leave the t-shirts we'll leave all mail it gets piled up in the window out in the sun so then after the you know sun kind of soaks it in for a day or two and then you know virus has a little bit of a chance to die off then she goes in there and you know scrubs everything down and disinfects stuff so the more small packages that i order the more pissed off she gets about having to disinfect all those things which is fair because she's really just trying to keep me alive and i can't get i shouldn't you know i totally i'm totally sympathetic i get it she's wonderful for doing that for me i love it but i gotta stop buying additional small things there's so many little tiny things i want to buy i need another camera i need so many little things but my wife will get upset sadie says everyone has that corona package corner i wonder if i was wondering if we were the only ones who did that or if everybody else did that too like leave it somewhere in the sun for a while to just kind of disinfect and at first i told her i was like you realize that it's just sitting inside right that like the if the virus is airborne if the thing gets moved around uh oh merlin says does new car qualify a small new thing i was this close i'll show you something so i was this close to buying let's pull it up so cars and vids is doug demuro's new car auction channel tehran wow very cool that's awesome um so let's see cars and bids is this awesome auction site and there was an auction this week this be still my beating heart i used to have an audi rs6 this same year mine was gray and i don't reckon i don't um regret a lot of things in my life but one thing that i regret is selling that car i had a gray audi rs6 i had no need for it i to be honest i don't have a need for one car i work from home i don't work as even a stretch but this car my goodness it has so much power it is ridiculous at the time that it was released back in 2003 it was the fastest sedan on earth so you'd hit the gas pedal boom 186 miles an hour around 500 horsepower 500 horse pound-feet of torque so much power that it was infamous for trashing its transmission used a tiptronic early version of the tiptronic transmission it would trash the transmission this thing sold for 10 grand i have no business with one of that now cgs is who outbid you i talked to my wife and i was like so look here's the deal i really want this thing bad and she's like but you know we're leaving to iceland you know we're going to iceland for three months minimum at the beginning of next year she's like so you would have to put this into storage to mdbs the rs-6 savant just hit the u.s go now so i'm allowed to buy one my wife and i have talked extensively about this i'm allowed to buy one but i would have to sell helmet she says you can only have one of those two you don't need both i'm like but so i can't do that but oh what a beautiful beautiful uh car and far she says we have to pay several billion dollar uh several billion to months to buy this not only is it it's it's even it's not expensive to buy i mean it's not terrible to buy but the service is awful so the service on this because the engine is so crammed into the front the engine it's so big and they've stuffed so much under the hood because this thing is so stuffed under the hood if you want to have any maintenance work done on it they actually take off the front end of the car they take off the bumpers all that stuff in order to get to uh all the components inside here so the expenses is are absolutely insane merlin says that over here the eco tax alone on a car like that is 40 000 pounds yes yeah oh god crazy so uh air eriker erica erickson says which i just love that icelandic names are just so incredibly cool with like the erickson and you may not be icelandic but that erickson thing is you know of course how they do it over in iceland um but erika says over on youtube what are you doing in iceland for three months as little as possible um but what i'm going to do is get a so we're working on a work permit and i'm going to work for a company over there plus continue to teach my training classes uh and just go see the country again so we went for one week then uh two years ago we went to iceland for one week then last year we went for one month then this year we're trying to go for three months so i'm really interested in playing around with that vtol freak says uh i have a small hot hatch in the workshop for transmission repair i feel your pain also i have a company car the same way i really have no uh need for that kind of thing and uh see chris says will you adopt a cool icelandic name i had not thought about that but somebody said that bert or brent translates to burnt in icelandic so i'm like oh that's kind of cool uh so sukvere asks a question actually and i'll answer this one do column store indexes help on update queries no they're actually terrible update statements are one of the big problems with column store indexes under the hood column store indexes really treat an update as a delete then an insert so now that that's not really that big of a deal because with column store you can do your own delete and then insert and it's not really that bad in terms of performance but updates for some reason the way that they implemented updates are terribly slow so when i talk to people about whether or not it makes sense to put column store indexes on your tables one of the things that i'll say is if you're doing updates you probably shouldn't do that again over on youtube says when do you start with the column store indexes videos the course the first live course is tuesday if i remember right i got to actually go look at my calendar i'm not even sure um so my calendar let's see here it is yeah tuesday um so tuesday will be the first column store class then the instant replays of that will be for sale on brentozar.com andy says sometimes i drop column store indexes then update then re-add the column store index yeah absolutely because it's it's actually faster for some kinds of tables and the number of updates that you're doing it can actually work up uh quickly eric says you can help with the icelandic oh i i'm gonna need that so badly uh because icelandic santa says are you learning any icelandic at all it's so interesting and several feel free to ask technical questions what i'm going to do is i'm going to put those into a queue and i'll answer those two we'll go through and all the technical questions or non-technical questions you want to ask today is totally cool so with icelandic here's the deal right now americans can't get into iceland we can't get in on a tourist visa but we can on a work visa so i'm going to get my we don't even need a tourist visa we can just show up over there but problem is you can't do the corona virus thing so what i'm going to do is get my work visa and error if i get a work visa as an expert which is i always have a hard time saying that with a straight face i get a work visa with an as an expert and then i can bring erica along with me so we're going through this background check process and all kinds of cool stuff the if you're in iceland for four years straight on a work visa then you can apply for permanent uh residency now normally four years straight in another country is not something that i can really do because of course we have clients conferences trips i want to go on but you know what i'm not going anywhere for a while so it might not actually be that bad so it's kind of crazy to think that i would go over there and if the situation with the coronavirus continues in the united states which i have no reason to believe it wouldn't it doesn't look like we're really close to a vaccine anytime soon doesn't look like we're going to get out of quarantines anytime soon state of california doesn't even have a timeline yes it did so it's there's a funny story there too andy i'll tell you that one privately but my mind check passed okay erica's did not um which i knew about ahead of time which we all had a big laugh which is funny because she's been an air traffic controller she has all kinds of federal clearance and stuff but she's like that there's the thing with the place but if you're over there for four years and you want to apply for permanent residency you have to learn icelandic you have to take 150 hours of classes on icelandic so i go and read or i'm starting to read about okay so what is icelandic i don't really like learning languages but i find so much about iceland charming i'm like oh let me go see if you know what it's like to learn icelandic oh my god it's terrible it's so hard they have like 14 different tenses it's insane it's one of the i was like looking at it and it's the most complex language i've seen and it's not really that useful outside of iceland but i don't do anything halfway like if i'm going to do something i'm going to own the bejesus out of it so i'm like well if i'm there for a year i am going to learn icelandic but we'll wait and see how that goes to see if i actually stay there for a year and ganesh says are you going to iceland for work or holiday yes so i'll because i can work from anywhere we'll do we'll work like two weeks a month and then do the rest of the two weeks a month running around the country because it's just gorgeous it's the most beautiful country i've ever seen just spectacular and i'm not going to get out of the united states i love the united states it's amazing it's fantastic the people the the geography the opportunities the food the cost of living you know the space for cars i love the united states it's fantastic i would recommend anyone if you get the chance to visit it's magical the problem of course you can pick the wrong cities to go visit you wouldn't really want to be in some cities for an extended period of time some spirit cities are less tourist friendly than others but damn it's a it's a wonderful place to live um so it's not like i'm going to get away from the united states it's just that we really like iceland we like all kinds of places and gov dba says living there will help with the language a lot of people just speak english i've we've never we've even gone to a very tiny lots of very tiny places in the middle of nowhere and we've never had a problem so detobi says don't you think europe has a greater variation than the u.s no having been to both i think that europe in europe there so much has a really long history it's been there for you know two thousand three thousand years in different pockets so it it's very similar in that the culture has has bred through the area people who are in europe are very used to and tolerant of other cultures so there's a lot of mixing and matching whereas you come to the united states and it's so young and certain pockets of the country were only inhabited by certain cultures that it's just dramatically different between mississippi louisiana florida washington new york just spectacularly different ariel sure absolutely by all means go right ahead it's uh that's how i make my living all right so let's go copy paste some of those questions into the queue and then so let's see here i'm gonna copy these in you're about to see all kinds of stuff uh show up in the chat uh let me go copy some stuff in here and copy copy paste and then next up let's copy another one from over there oh eriker said what company will you be working for i'm not ready to announce it just yet but i am so excited about it because it's just so neat i just love this company so let's see here copy that in and then copy this in and copy this in and this and what else did i get everything else is that everything i think i got them all uh oh mazimau says yesterday i finished reading one of the books you recommended t sql fundamentals and today i started with t sql querying yes that is uh itsec gone stuff absolutely wonderful i really enjoy that okay so erickson says it starts with an m no i almost guarantee you've never heard of it that's the other thing that's really neat about this so i had a bunch of icelandic subscribers and i just emailed them all and i said hey you know if you're interested here's what the engagement would look like you know work with you this many days a month and so forth and it was totally uh the one that emailed me i was totally surprised by i had never heard of the industry before and i adore it it's just it's eco-friendly and it's all kinds of really neat things uh so let's see here let's start going through we'll add that yes so uh so sfd i did get yours that's good so i've got that there i'm going to copy paste that in and then we will go start running through these so first up we have i should actually take a sip of my tasty beverage here before we start because i have a terrible habit of making espresso and then not drinking any of it because i just get so excited next our first up we have how do i remember how this works it's been so long cti geek on twitch says i've heard never to use where not in and instead use where not exist do i agree you know what's really funny i had never thought of where not in like it that never even occurred to me to write that is so weird because i've never s i'm surprised that i've never thought of that or seen that that's kind of intriguing to me but where not exists will bail as soon as it finds the first row that matches i'm my guess and i'm just guessing i'm guessing that where it's we're not in will execute the entire result set before it checks for existence i am just literally guessing though that's probably why um so that would make sense to me about existence checks there next up sfd says what are your thoughts on using merge in 2019 is it safe no sadly merge all the bugs that you've probably heard about from merge are still present i'm gonna go pull the post just so that you can see so if i search for merge aaron bertrand aaron bertrand out of new england has this blog post over at ms sequel tips where he goes through all of the bugs inside the merge statement and the current status of those bugs and the short story is it really doesn't make any sense to use that particular statement so when you want to find that search for merge aaron bertrand also folks only ask your question once please don't put it in multiple times if you put it in multiple times i'm just going to ignore it all together so all right there's the first one let's come back over here next up we have kavishka over on youtube says hi brent i work as a junior dba for a year and i love performance tuning i invest a lot in learning is it a bad idea to bet my career in a niche field so early on oh that's such a good question buck woody and i know it's not his original line but he's where i heard it first buck woody likes to say that in a mature market in a mature product you want to be a specialist so like a product that's been around for 20 years like sql server it pays to be a specialist to do a very deep dive in an immature market things that are newer and changing a whole lot azure amazon web services etc as your data factory power bi in an immature product be a generalist like be able to do anything so because you're working in sql server it totally pays to be a specialist if you're able to establish a reputation as a performance tuner it's fantastic i would highly recommend it and it's also one of those careers that continues to pay off as people move to the cloud the cloud is not self-tuning the cloud is almost self-harming and it is extremely expensive to get high performance in the cloud so it that's a great career if you love doing it you should totally go for that i can't go for that no can do um so andy links to my my uh the book that i co-authored i almost called it my book um someone said uh i didn't catch who it was but i saw it going by brent would you ever think about writing a book the problem is that books make less money than minimum wage so for me i like money i like money a lot here's to money so the thing is people just seem to kind of pirate books these days they just copy paste and send them around so it's kind of tricky all right next up in the queue next one barris says from over on youtube brent what would you do if you can't use instant file initialization on sql server other than upgrading to a faster disk um some people uh will or some companies will not allow instant file initialization because it's a security risk in the kinds of shops where you're not allowed to use instant file initialization due to the security risk you probably have all kinds of other handcuffs and barriers too as well people think they're like well i have to be able to use this feature you know when the security department says no this is what enterprise development is like all over the world that enterprises constantly say no you can't have that cool feature because of the security risks and you just have to learn to deal with it by going with faster hardware just that's all it boils down to now one thing that i would be aware of is the restores are slower because restores have to write out the entire contents of the data file log file restores are slower so one way that you work around that is you pre-restore databases ahead of time using things like log shipping so that if all hell breaks loose you've already got a restored copy ready to go next up dtovi asks i would like to alter a dev database and see if the changes make things better or not is query store the easiest way to correct collect production queries to replay in development that's a really good question for me i don't usually collect queries from production i i talk because of my weird consulting role because i have to do this exact task a lot what i'll usually do is say to the client give me five variations of this query like five sets of parameters that you want me to tune for what are the ones that are most important to you query store collects things that users may or may not care about for example they may be things that are run by a back-end service things that that aren't user facing and that aren't critical in terms of performance so if i used query store i would use it but then i would sit down with the users whoever the performance stakeholders were and say all right out of these which ones do you want to see me focus on because you're always going to have crappy queries the thing with query store is it can bring a server down if it's not used correctly if the workload is bad so if you use query store just make super sure that you go watch aaron stellato's course on pluralsight if you search for sql server query store pluralsight she has a course over there and that's really the only good resource out there in our industry right now on the breaking problems with query store i've had clients bring servers down with that repeatedly so i'm to the point where i won't recommend query store unless i can be there to be involved in the workload when the thing gets turned on other options my personal favorite for that is the plan cache so i just use sp blitz cache which has no additional overhead on the server your server already has the plans up in cache anyway so you can just grab them anytime that you want next up jignesh says hi i have a hybrid environment with azure what connection method can i use i'm getting lots of odbc connection failures so generally if you have yours your database servers in one place and your application servers in another place that's going to introduce a lot of latency you're going to have a hard time connecting between those because the connection will drop all the time you'll have a contention on that network pipe you want your applications right next to the database server like uncomfortably close there's no worries about coronavirus in between the application servers and the database servers you want them holding hands making out in the parking lot you want them that close so generally i just wouldn't try to do connections between those two and expect them to always work let's see here uh i'm an angry gumball i love that name that's so awesome i mean angry gumball says have you ever had a customer refuse to move their databases off of optical drives so i think what you might have meant was magnetic drives but optical drives are generally read only you know you can get rw uh drives but true story i did have to actually deal with that very early on in my career we actually ran stuff off of big cd jukeboxes we had giant optical jukeboxes with yeah exactly worm drives where we could write uh records and then never worry about someone erasing them so we we did have to deal with that and i can tell you horror stories about the time when we only had four drives in the entire it was like this jukebox the size of a fridge and pioneer big monster jukebox and we had little robotic arms in there that would move around and swap the drives in and out we actually couldn't get the drive arms to move fast enough pulling the little worm drives in and out what a hot nightmare that was but the ceo got a really good deal on the drive so whatever next up etz says is there a big performance difference between having the default incrementing primary key like in an identity integer versus a string primary key like username and sql in general the reason why people usually like small columns like integers is those clustering keys are included in every non-clustered index that you have so the wider your clustering keys are the more space that they take up they're going to take up that same space over on all your non-clustered indexes too so it makes your non-clustered indexes larger now is there a big performance difference you'll find armchair architects people who don't really build anything these days who just read about it in books are like well it's a it's really important big ginormous difference but i tell you what i've never had a case where i went to a client and said you know what your biggest problem right now is that you're using the wrong data types on your primary keys and this changing the data types on the primary keys is going to be the solution that gets you across the finish line because changing your clustering keys generally sucks pretty bad so i'll say is when you build a new table from scratch yeah you want to pay the attention to the clustering key designs but hey let's not ask questions twice remember we're only going to ask them once so just generally get it right when you go build a new database but don't try not to go build uh or try not to change it on an existing database just the overhead generally isn't worth it and generally you'll get more bang for the buck by chasing something else next up um vtol freak says any risks any risks touring like alan turing test query optimized error fixes on server wide with 41.99 so are there any risks anytime you turn on a trace flag you're going through a less documented and less tested path through the sql server code i'll be honest i don't even trust sql server's defaults these days i don't think they're doing a good job of testing what comes out of the box let alone when you turn on trace flags great example of it is how they recently last month had to pull cumulative update seven they were like okay everyone you should patch with this immediately two weeks later oh you know what i did it it turns out we didn't test that we need you to roll that back immediately and i'm like uh that how about you get your pants on and start testing things first so the the gotcha with that with turning on trace flags is that they're not testing those worth the damn so for me i don't want to turn on most trace flags across the board i'd rather turn them on for specific databases where they desperately need it 41.99 is a great example of that i would want to make doggone sure that it's going to make things better instead of worse so for me that's on the database level all day long and then next up lee says i set up my first sql server failover cluster recently i assumed that there would be an event for ags i was surprised to learn that nothing like this events how would you recommend alerting for an fci failover a third-party app if something is important enough that you're going to put in a failover cluster go buy a monitoring tool go buy quest spotlight idea a sql dm sentry one performance advisor redgate sql monitor like all those people have figured out how to do not just that event but so many more that sql server doesn't handle by default so just go do one of those otherwise trying to build your own monitoring you're always going to end up behind you're always going to end up behind the eight ball on that you don't want to look stupid you want to look smart and the easiest way to look smart is just to rely on somebody else look for stuff like that it's only like a thousand bucks a server leave it to the consultant to say something like that but if it's important enough to cluster when i'm sketching out the cost for a budget for a new project i just include the monitoring software as part of that and if somebody wants to to bend on uh like worry about uh costs on the project i'm like how's about you take off one core of licensing so that at least i can just get the monitoring software working because i would trade one cpu core of licensing for a complete monitoring solution all day every day so there we go all right let's grab uh the next round of questions here actually while i do i should give a shout out to that's the wrong button that's the wrong button give a shout out to this week's sponsor this week's sponsor is the data platform virtual summit so this is a conference that's happening in december that's got a different kind of time schedule where it's actually friendly to people all around the entire globe and they have pre-conference workshops and post-conference workshops right now they are 50 off if you use the coupon code up there on the screen sql so over that's over at dataplatformvirtualsummit.com i'll leave that up for a second while i go copy paste in your questions from over in youtube to get those things all queued up so you're about to see a whole flood of questions come through here so let's see here let's copy paste this stuff inside here add this add this and add this add this uh copy this in and then next up let's grab these over here hope that no that's twitch let's see here let's copy this in copy buckle up because now the stuff is going to come in like crazy to do ah copy this in and this and then this and this and so that dtla that was a good one actually let's get make sure that that's in and [Music] copy this in copy that in copy this in and that people are like how boring is this um oh ltgf i didn't copy this one it says thoughts on sql server serverless uh yes i like it a lot for dev databases that can just power down overnight so that's kind of cool let's copy that in boy oh my goodness i don't think we're going to need any more questions today we have so many questions in here in the queue that i may not need to hit any more of them actually copy this in and paste and i think this is it let's make sure whoops paste this in any back up on youtube oh wow oh yeah so just has a good one there uh and that's it okay perfect there we go and then add do do do do add add add add add add add add i should have music on when i'm doing this oh andy leonard says i'm speaking at dps yeah that's absolutely true um there's a good lineup of speakers too all right let's come back over here for [Applause] this there has to be a theme there has to be a name behind an idea behind that e or b go beep beep that's such an excellent you are b b b go beep says i tuned a query in which the reads and writes dropped cpu at 95 and deb and then execution time increased and the dba is refusing to release it should execution time ever be considered when run in a radically different environment oh totally gap so i'll give you a great example so i was tuning one query and it ended up after i got done tuning it it ended up using way less resources but it went from going multi-threaded like going parallel across a whole bunch of cores down to going just to one specific core no it's certainly dev show up oh there he is oh awesome no i didn't know he was in here welcome sirleydev thank you um so the query that i was tuning went from going multi-threaded down to just single threaded because sql server believed it was so much less work so because of that the it actually made more sense to go continue tuning it to figure out how to make it go multi-threaded so that i could still continue to get the time down and have it use less resources so absolutely because at the end of the day execution time can be the customer facing experience sometimes we have to make them both better not just one side but it's a great question i'm glad you asked and surly dev i want to give a shout out to surly dev who manages the questions in the queue thanks to surly dev for being uh today's moderator here that's ash that's awesome yeah adam's absolutely right there completely true next up peter says should views which have a big amount of joins let's say 20 plus be split into temp tables or ctes so with views you can't use a temp table so that that doesn't really work what i would sometimes say is if you're having performance problems with a query that hits a view sometimes you need to refactor it into using things like temp tables but what i'll say to users is yeah by all means go ahead use the views as starting because most of the queries that you write don't actually need to go that fast but when you start to hit a performance wall bring me the queries that are having the problems then we'll talk about re-architecting those in a way to make them go quick but by all means if you want to use the view as a starting point go right ahead if you find yourself going this one view always sucks then that's when it's probably time to step back and go okay how could we either change the indexing on the underlying tables or maybe even consider an indexed view which materializes it to disk next up a long 1231 twitch says hi brent some of our tables are rep some all our tables are replicated that's interesting here we go when any column changes we have to manually drop the indexes and the replicated tables is there a better way to do it i hate replication i recognize that it is something that the world needs and that lots of people use it but i can't stand the damn thing because it's really a pain in the rear to manage for things like this for deployment changes uh changes to tables and columns so i don't actually work with replication at all i think the last time i worked with replication was 2008 maybe so i'm not a good person to answer that question but i'll tell you that it is a good question that people struggle with that all the time with replication and you said is there a better way to do it usually for me the better way to do it means switching to things like always-on availability groups but i get that that's a problem because readable secondaries are enterprise edition only that they copy the entire database across that you can't have different indexes they come with all kinds of challenges but they work so next up uh ima says if the server's been up for long and we don't see any reads in dmdb index usage stats can i say the index is not used for me for long is usually 30 days if a server has been up for 30 days then i'm reasonably confident that the index isn't being used you just have to be very careful that if someone has an index hint in their query that query will fail when the index isn't there but generally as long as the server's been up for at least 30 days you're good uh sukvere says good to see you again sukveer says we have a table of less than a million rows and we have a process that does upserts on the table which locks it right now we do a synonym swap is there a better way yes search for so you're doing upserts i would do it in batches of say 2 000 rows or less if you stay below sql server's lock escalation threshold which is about five thousand locks on the table if you nibble through it in smaller numbers of rows like two three thousand rows then you can avoid lock escalation and you don't have as big of a problem there it plus it's not going to require the schema stability lock it's going to not require the schema stability lock that the synonym swap would have next up uh kevin dba says do you have clients who have troubles running the consultant toolkit i think it's straightforward but when we end up having to do a screen share with them to get the output you know i'm gonna tell you something funny having clients use the consultant toolkit is kind of the minimum barrier for entry for me if someone can't get that to work i kind of go you know what you're not really a great client for me because that utility is so relatively easy that i go if you can't pull that off i'm probably going to get frustrated working with you because you probably made other really unbelievably large mistakes so i feel you kevin that you're generous enough to do the screen sharing time with them if there's something that you think would make it easier shoot me an email and we'll definitely see what i can do but like i don't run into that that often because also my prices are kind of high um dtl says downtown la says what brent what auditing product do you prefer for tracking changes to data for me the question becomes do you need to stand in court and defend the data that's inside the audit so some clients who do some clients they have to keep legal track and i'll give you a great example hospitals if you're a hospital you have to track everyone who sees george clooney's medical records because if they go and sell them to tmz you have to be able to prove who did it and it has to stand up in a court of law if you have to stand up in a court of law i punt it over to the security team the security team should own the auditing appliance the database administrator shouldn't have any rights to it whatsoever and there are appliances that sit in between the sql server and the rest of the network and they capture every network packet if it doesn't have to be legally defensible i don't really care what we use i'm a big fan of triggers just because inside a trigger i can pick and choose which kinds of changes i want to capture whereas with tools like change tracking and change data capture they're going to go grab all kinds of stuff that i don't really care about and sometimes when i'm doing it for just my own diagnostic purposes a trigger is much lighter weight next up from youtube sajeeth says do you recommend turning on accelerated database recovery for highly transactional uh databases so that's a new feature in sql server 2019 and since it came out there was a corruption bug with it i get a little nervous any time see microsoft changes something involving the storage engine inside user databases so i don't recommend turning it on unless you're having a performance problem that that's the only way you can get past it if you're hitting us a problem that you can't fix any other way then sure but the rest of the time yeah exactly adam says anytime anything goes magically faster i wonder what the trade-off is and here this is a brand new piece of code now microsoft is using this up in azure so i'm sure they're finding and fixing the bugs just as quickly as they possibly can and shipping those back into the product but now and i i'm a huge fan of the feature i'm a huge fan of the idea i love the way that it was implemented but it's just risky anytime you put in a change change equals risk so don't put it on unless you have no other choice steve says is there over on youtube steve says is there are there any places for failover clusters today or have availability groups made them redundant so imagine for a second that you're a software as a service provider and every client gets their own database because this makes sense for a bunch of reasons for some industries like when every client needs to be able to grab their databases to do a restore or you need different flexible performance and indexes for different clients so if every client gets their own database it's not unusual to see sql servers with 500 to a thousand databases per sql server availability groups suck at that kind of thing because they use uh cpu threads in order to move data across replicas plus you just doubled the size or tripled the size of your storage depending on how many replicas you have so there's still a huge place for failover clustered instances uh yeah there are there are a whole lot more cases for it so that's the answer you know on that one good question though um this mossy moss uh asked this one and i wanted to go into deeper on it so i copy paste it into the cues masimasimas says did you think about writing a book something like irrelevant irreverently fast t sequel not from scratch but i'll tell you what i do have in my back burner i've got a big long list of things that uh that i want to do someday when i have the time it's like someday maybe kind of thing one of them is i have an editor that i've worked with in the past that will take a whole bunch of blog posts and turn them into a book i want to do that with like with brynosaur.com is coming up on 20 years next year like the 20 years worth of blogs now obviously the stuff in the first five years is not very good um but after that i mean uh there's plenty of stuff in there that i'm like oh i'm really proud of that or other people jeremiah kendra doug lane you know all these people have written eric darling all these people that you've known and loved richie rump joris so i want to make sure that i get those back out there i would do that i would do a best of brenozar.com but i would totally give it away as cheaply as amazon will allow me to give it away because i don't want to get rich on that kind of thing there's no money in books these days i have no idea i don't even know what that is next up raghu says i haven't seen you talking about latches and spin locks for query tuning do sp blitz cover them so i have never personally hit a problem where spin locks were the root cause so because i haven't hit it i haven't needed to build any of the code into sp blood splits cache etc blitz first whatever latches i absolutely have and sp blitz first starts to dig into those and i'm not saying spin locks don't exist spin locks totally do exist i just have had the weird random luck where i've never had to uh problem solve those gumball says what happened after five years that instantly made the blogs better i want to say it's stephen king that says when you write you part of the work that you do in writing is you have to sit down at the at the typewriter a keyboard every day and you just have to push stuff through and the more that you write the better you're going to get i got a lot better after the first five years but i just didn't know what i was doing or why i was blogging for a lot better and i haven't gotten any better since then but whatever samiro on youtube says is a query performed using sp execute sql going to perform the same plan as just running the query of the string if not why not oh that's an excellent question so generally speaking it's going to be the same there are edge cases where it'll be different but generally speaking it's going to be the same so i'm going to give you an example of why they might be different whenever sql server builds an execution plan it's like opening a choose your own adventure book you remember those from your childhood you would open up the book and it would say if you want to slay the dragon turn to page 14 if you want to rescue the prince turn to page 21. so when sql server opens up this choose your own adventure book it's up against the clock it needs to ship an execution plan and start running your query well depending on things like statistics how much time other users are burning on cpu you may hit a query that gets different execution plans depending on when you execute it like how heavily overloaded the server is and i'm not talking about the query having different the the lights are actually on they're just not bright enough in here so the so the query will get a different execution plans depending on uh how much resources are available to sql server at the time of compilation that is a very edge case answer but it's i feel like i need to say that because there are times where there will be a difference next up alan says hi brent i watched your live tuning video the other day and i was surprised that windowing functions performed so poorly could you shed some insight into why the easiest way to do that is go read itsek ben gan's book t-sql querying so search for t-sql querying itsec ben gone it's a wonderful book that dives into how you craft windowing functions among other things all kinds of other stuff for best performance i want to say it's like 860 pages so it's pretty pretty in-depth and thorough obviously i don't want to go into that on a q a webcast like here but i want to get you at least a place to go started um next eriker from iceland says have you looked into the scalar udf inlining issues in sql server 2019 when that was my very favorite feature in decades when it came out i was so in love with how microsoft tried to do it is so ambitious but also it was kind of doomed because it's people will put the most nasty stuff inside their scalar functions so i dug into it but the problem that we've been hitting is that with every cumulative update microsoft has been pulling functionality out of that feature so i have a hard time recommending to clients that they put something in when 30 days from now the part that made their queries faster could be yanked back out so if you can use it and it helps your queries go faster great i just wouldn't bet the farm on it right now i'm kind of holding out for version two i think whatever the next version of sql server is we'll have a better idea of whether this is a feature that microsoft will continue to invest in and fix the bugs in or if we have another merge statement on our hands where they brought the feature out and then they went oh actually as it turns out not so much gopi says hi brent are 10 indexes on one table okay what you want to do is you want to check out my upcoming fundamentals of index tuning class and it's totally free i'll show you how to sign up so what you do is go to brentozar.com go to brentozar.com and then right up at the top it says free live thunder god that guy's big a free live fundamentals class coming up click on that and you can go attend an all-day class completely for free that will teach you how many indexes on your table is okay and how you go about working with those also have a day-long fundamentals of query tuning class and a class on how i use the first responder kit all of that is completely free i'm doing free training during a lot of the quarantines as a spoiler alert that's mostly going to go away after november because i i'm going to go to iceland it doesn't really have anything to do with that but i want to work as little as possible work sucks so oh i'm strange next up santa asks uh do you have an opinion on peer-to-peer replication as in do not use it unless absolutely necessary yeah so my thing is if you really want two active sql servers that are both hosting inserts updates and deletes into the same table i don't like peer-to-peer replication as a solution because you kind of have to work within its framework what i'd really rather have is just a bunch of separate sql servers and we roll our own uh like conflict resolution so that i can have help desk people do the conflict resolution uh for me instead of me doing it myself to give you a rough idea i worked with a team that did that on back when i was a real dba on seven to eight thousand sql servers servers that were installed on salespeople's laptops and we had to do with uh deal with resolution conflicts so for that anytime i'm going to go start building peer-to-peer solutions at scale i'd rather roll it myself than rely on what microsoft has built in not that what microsoft built-in uh socks it doesn't suck it kind of sucks but they're just not improving it they just haven't done anything to improve it in 15 years because it really does work well enough it works well enough for most people but if you're going to go push it at scale that's where you're going to want your own complex conflict resolution now let's see here next up oh eriker said from over in iceland eric i'm probably going to say that every time now said i've seen up to 30 difference when changing from big end to int in very large databases what you want to do instead is just implement compression if you implement i can't i want to say it's row compression if you implement row compression then sql server just automatically only uses integer sized columns if it's integer size data like for all the ins that you have you're only going to use i forget if it's 4 bytes or 8 bytes it's going to use the smaller size and only when your data crosses into bigint territory does it use the rest of the space so the reason why compression is so much better of a solution is you don't have to change your data types you don't have to alter the existing tables and all you have to do is do an alter index rebuild and you've immediately got it okay look that the drawback is that you have to be on enterprise edition to get that index compression if i remember right but when you say very large databases they're probably on enterprise edition anyway so that's a really fun trick there next up gma's dba works for good morning america i don't i don't really know that i recently received an offer to a database administrator oh that offers adult entertainment website services how is having a resume in that scene in the fields should i try and hide it no no no here's the deal i say here's the deal a lot with adult entertainment website businesses you're going to learn about very high volume oltp with interesting challenges like they want it to be highly available they are handling e-commerce security and privacy are a big deal so depending on if it's like a name brand adult entertainment website it's gonna actually have some recognition in the field of oh that is you know a high volume big deal i say that because gambling is the same kind of thing i have clients who are gaming companies usually they will make you sign an nda that you can't name what the company is adam says yeah there are multi-billion dollar companies at this point in those industries some of them at least now of course they also get hacked from time to time and you don't really want your resume attached to the company that got hacked but what i would say if you don't want uh people to look down on it on a resume is say come up with a say i worked in media i'm not allowed to name the company because sometimes people will have ndas where they're not allowed to name the company that they worked with i'm an angry gumball says yeah you don't really want to see how the sausage is made though there are multiple meanings of sausage but it's it's really that can give you wonderful experience and everybody needs i.t i mean it's the same thing for a while cigarette companies or tobacco companies were looked down on in america because you know technically the product kills people you know it doesn't really have a whole lot of positive uses but everybody needs database administrators so if you need it and it lines up with your career i kind of jokingly say that if i ever retired in the mafia like if i was sitting on the beach in mexico and the like one of the drug cartels came up to me put a gun to my head and said uh you have to do our database administration i wouldn't like it because i don't ever want people to get hurt at the same time i'm also kind of interested because i'd be like all right so how do they use sql server and how does that all go on that's very true too santa says that everything is all gaming now these days richie asked why am i here not watching european football that is a very good question i think erica has that on her schedule to go watch uh today oh that's a good pa pa points out look at all the marijuana dispensers and growers that's true because sometimes things will be illegal and then morals will change and then suddenly it's legal and everybody's into it i'm not into weed i'm ambitious i have things to do i don't really have that many things to do ashish says all our tables are replicated with custom replication nice we have primary keys with goods which causes paid splits and not so nice um can we add date as the first column with good to avoid page splits so the problem is whatever your clustering key is that column is going to get added to all of your non-clustered indexes too and if you're just adding a date column for the purposes of having a date column it's just going to add more baggage to all your non-clustered indexes so i'm not really a big fan of that i don't really have a problem with most page splits because after all you probably have indexes on columns that are going to have page splits i'll give you a great example customer name when your customers sign up they're not signing up in alphabetical order they're signing up with names all over the place so it doesn't really make sense to like desperately avoid page splits when you're going to have them on all your non-clustered indexes anyway it's not really that big of a deal also a quick shout out to this week's sponsor so this week's sponsor is the data platform virtual summit you can go learn about their conference lineup over at dataplatformvirtualsummit.com all kinds of pre-con and postcon workshops so you can get a day of live training with instructors from all over the world check out that lineup over at dataplatformvirtualsummit.com and then right now you can use code dps sql to get 50 off the cost of conference admission this is one of those deals where i don't know how long that thing runs for but if you're going to use it go talk to your manager on monday about how you can go get into there because i don't actually know when the expiration date on that thing is all right so let's take a bio break here we're going to take a five minute bio break and when we come back i'll keep working with the question queue because you all have so many good uh question uh questions out there today so i will see y'all back in here in five minutes [Music] you [Music] [Music] so [Music] oh [Music] [Music] [Music] so [Music] so [Music] [Music] [Music] [Music] [Music] [Music] so [Music] so [Music] [Applause] [Music] so [Music] [Applause] [Music] welcome back party people uh yes i am totally in the cloud today this is funny we have a total fog in san diego we had this yesterday too in san diego and it burned out fairly early on it's the air the flip side is the air quality index is amazing it's like 38 right now which is kind of odd because it's you know you'd look out it's a fog but it's it's at least it's not smog which is kind of cool uh over on youtube and aniket asks i like that name too you always have cool names aniket asks can change data capture cause incremental backup sizes to be large um you know i have no idea um i haven't used when i went through the microsoft certified master program in like 2008 at the time i had never used cdc or change change tracking or cdc um and i learned what i learned about it just inside that class in like a two-hour module and i don't think i ever used either of them again i don't think that they're bad there's nothing wrong with them just i don't have any experience in the intricacies of it so i don't actually know i'm just going to say off the top of my head anything that that writes to the database can cause your backup sizes to be larger so the more that you track things like change tracking and change data capture are logging things but i don't know if that wouldn't be my first guess though that wouldn't be the first thing that i would guess would be causing it so next up oops bw merlin berlin says tips for non-dbas who look after sql server by far and away the biggest tip that i can give you is go run sp blitz and i'll show it to you so if you run sp blitz this is an open source totally free stored procedure you can go google for it and i've got a download link to it's all open source licensed under the mit license so you can use it at your day job it is a totally free health check in priority order so you can walk right through and see here are the priorities that i need to work on first the most urgent stuff pops up first and if there's anything that you don't understand adam says i'm afraid to run sb blitz because there are some nasty things going on that's that's what you call consultants in for and you know what the consultants do they run sp blitz and they just copy paste it into our port i know because i get emails all over the world from consultants who say thank you for doing this because i make a living just running sp blitz and it's true and to some extent so do i so if you then if there are any warnings that you don't understand you can copy paste inside here and you can go learn over in a web browser here's what that means generally i kind of jokingly say that stuff from priority one through 50 oh with the numbers over on the left-hand side priority one through 50 is generally the reasons that people get fired now granted you're not going to get fired for tempdb on a c drive but when that thing fills up and the os won't boot that's when you start to run into problems so just the stuff from priority 1 through 50 go learn about those and start to fix those i'll tell you what merlin if you do that you're better than most database administrators i know i would think i give this away for free it's all open source i would think that if people are going to hire me they would google my name and if you google my name say my name say my name if you google my name this is one of the things that comes up i would think that people would go copy paste this they would go run it and they would go start working through the stuff but you know what most of the time when i go to work with a client i run this and they learn stuff and they're like oh my god i didn't know i had some of my databases uh not backed up i didn't know that we're not doing checkdb chris is what are you doing in that company it is unbelievable how often i run across this even from clients who i've worked with for years that i come in once a year or once a quarter and they are surprised every single time i do that i'm like it's this is free it doesn't even cost anything because i don't want you to pay me huge money and all of a sudden i show up and all i do is run sp blitz and i'm like yo your pants are unzipped so i don't really want to see your junk it's smaller than you think it is that makes me sound like i see a lot of junk i guess i kind of do um so next up on the list next up kavishka says if you could give yourself advice back when you started your career what would it be don't spill espresso on yourself while you talk what would it be i think i like i'm overwhelmed with the number of things that i would want to tell myself as i was younger for example when you have an opportunity to learn more early on if you get the chance to to work somewhere fun where you can learn things don't worry about the money when you're young the time to make money is in your later 30s early 40s later 40s don't worry as much about money in your 20s and early 30s that's the fun experimental time of your life when you're gonna have enough energy that you can do all kinds of crazy things but later on as you get through life you're going to have more commitments to other people you're going to have more sampling angry ball remembers that story that's that's a very funny story um yeah oh mike it's good to see you um as you and my mike will identify with this as well as you get older you're going to have more commitments and you're going to want to commit yourself to people not to companies like the older that i get the more that i want to spend more time with the wonderful folks who are hanging out with me hear me on uh hear me see i can't even string three damn words together the nice folks who hang out with me here on twitch um it's more about uh people that's true too turns out that man's a murderer there are there are things that you have a lot of time for when you're young don't feel guilty about that so like i grew up during the dot-com days and i had friends who went off and became billionaires and i felt guilty i felt guilty for not doing that like there was a time in my life where it's like i suck i want my my grandparents specifically my grandpa on my mom's side who retired when he was i want to say 45 like he was 45 years old and he'd retired and i kind of looked up at that and at the time i knew my grandpa on my dad's side he was also retired i kind of looked up at that and i went wow these people are doing it right i need to do what they're doing and to do it that way i would have to jump on a treadmill and start running like hell i didn't do that i chose to goof off a lot i chose to to do a lot of interesting things that didn't make much money i traveled all over the u.s when erica and i got together we traveled all over the world and i felt really guilty about that because i wasn't making a lot of money if i could go back and tell myself my younger self i'd be like you go girl you know you do uh all of that that you want to you know right exactly man i get so many interesting uh technical problems that i made from a career perspective what advice would i give myself i would say go work for companies where you can learn and don't feel bad about that just because you're loyal to a company that that a company has good people doesn't mean it's good for you personally you may need to be challenged more i stayed with some companies longer than i probably should have because i i loved the companies what they did and the people that i was with but i wasn't learning a damn thing so i was kind of treading water for like a decade next up oh i would also say i dropped out of college don't do that i dropped out of college three times and then don't do that next up santa on youtube asks query runs okay not great when compatibility is level is taken up to the max possible the cpu blows up the query uses row over any thoughts yeah compatibility level especially when you jump from uh a jump into 2014. so as soon as you get to like compatibility levels thing i want to say it's like 120. um as soon as you go to 120 or higher you're getting a different cardinality estimator which gets you differently shaped execution plans you have a choice of either you could leave the database on compat level 100 which is still available in sql server 2019 or you're going to have to tune queries differently when you get to compat 130 at 120 130 you know on a 2017 2019 all that stuff so if if for some business reason you need to change your compat level just change your compat level and expect that every query that used to be fast is now at a risk of maybe going slow so and don't don't change the compat level for no reason if you don't need to change it leave it exactly where it is if it gets you the performance that you need today leave it and there's nothing wrong with that people think that they're missing out on something magical all you're missing out on is bugs microsoft hates it when i say that um next up let's see here ian says what are your thoughts on open query versus querying a view on a remote server in either case when you have one sql server talk to another performance is going to suck one of the reasons that it sucks is the sql server doesn't cache that data locally it assumes that something might have changed on the remote server so it'll go wow that's a nice name there you thought i was a queen psych so the sql server will always go and fetch that data from the remote server that's a recipe for a bad time so if you're a developer and you're writing something that needs to go get data from a sql server go connect to that sql server like if i wanted to ask you a question i wouldn't write it down on paper and hand it to a friend of yours the friend of yours is going to take longer to get the data just go connect to the data server that has the data that you need that's the kinds of things consultants say alex says i read great things about sql server 2019 and kubernetes have you seen anyone using kubernetes and consulting gigs oh here we go i sure have in order to make more money have i seen him use it to solve a client's pain no that's probably where i should leave it at that luco says any recommendations of sql server free courses yes if you go to brentozar.com and search for free training i have all kinds of them or if you go to our youtube channel so you're on my twitch channel right now if you guys that's true yes if you go to my youtube channel search for brentosar youtube i have hundreds of hours of free video on uh how the engine works statistics tuning queries you name it a lot of the streams are and i break stuff into categories a lot of the streams are me answering questions don't watch those to learn things because it's kind of shotgun scattered all over the place watch the ones that aren't q a and there are hundreds of hours cycling says glad i stumbled on you because my husband uses sql daily and this husband or this channel helps him learn new things and i love collecting knowledge my wife kind of jokingly says that she could be a database administrator now because she's overheard me talking on so many training calls and consulting engagements so it's kind of funny oh thanks adam i appreciate that that's very cool next up it says i need to store a resource key from an external api you know what my audio isn't going out y'all aren't hearing the sound effects let's go fix that sound and then change that to loopback audio and let's try that again yes yeah there we go that works better that says uh so how do i need to store a resource key from an external api how would you recommend designing the tables using a composite key or the key given by the api as a primary key any so this goes true for apis human beings any database you will ever design this is a big important career lesson talk about things that i would have told myself when i was younger when someone tells you that something is going to be unique they are lying incorrect they just don't know it yet they don't know that at some point they are going to send you duplicate redundant uh exactly the same copied data that was me trying to make a joke on duplicate and redundant at the same time so i would never use a key from an api as a primary key somebody's going to change that api somebody's going to have a bug in that api where it sends you dupes never anytime someone tells you something's going to be unique don't believe it for a second unless it was generated by your system by a system that you own and even then it's probably going to give you dupe someday uh oh santa says over on youtube if someone was to migrate from sql server 2016 uh would you rather andy i knew you were gonna like that that was really funny uh how would you would you recommend 2017 or 2019 2017 for me and i i haven't written a blog post about this yet and i'm kind of torn about it but for me the the code quality on 2019 isn't there like the the number of updates that are coming out are fair i don't have a problem with that the bugs that they're fixing are fair i don't have a problem with that either but the quality of the updates has been so bad and there have been so many questions about it that at this time i can't in good faith recommend sql server 2019. 2017 is bulletproof like a freight train that thing is fantastic if i was going to pick a version even between 2016 and 2017 i take 2017 all day long it's pretty awesome it doesn't have that many gains over 2016 in terms of performance type features it's just reliability it's kind of cool next up roman asks over on youtube what would be the best strategy to keep table variables in memory as much as possible what's the problem you're trying to solve tell me more about the problem you're trying to solve because i think you're jumping into the wrong tactic there's this this thing called an xy question i only learned about this recently so i'm going to tell you because you might find it interesting too there's a thing called an xy question where when someone asks about x what they're really asking about is why but they just don't know that yet the reason why you're asking about something how to keep table variables memory a i don't think you should be using table variables b if you're trying to keep them in memory remember that you're shoving other things out you're shoving out cached data oh very good oh nice i haven't seen that one i'm gonna have to look at that you're shoving other things out and you're thinking that you're outsmarting the sql server engine about what needs to be in cash ericker is exactly right they're not memory bound almost nothing in sql server is guaranteed to be memory bound and i know that someone's going to pipe up and say you should try in memory oltp hell no you should not because the problem is then you need like twice as much memory as you have in memory otp data so i get really nervous about that kind of thing so let's zoom out a little and tell me the business problem that you need to solve what is what are the users coming to you saying i need help with this because i guarantee you they're not saying table i need my table variables to be in memory zoom out what's the problem that they're trying to solve ask that and i may be able to get you a better answer uh gumilari says i need to copy database backup files to multiple servers can you uh uh can you suggest to me the best possible way do i would because copying files around is something that windows does really well sql server doesn't really do that kind of thing what i would do is i would write my backups to a network share and then have all the other sql servers pull directly from that network share if you need to copy that network share to multiple places you hand that off to your windows team and you say i need every file that gets landed here to be copied over there and your windows admins are used to doing stuff like that they have to do that same problem for all kinds of servers so they'll use tools like windows dfs rsync xcopy jobs they'll have all kinds of tools at their disposal but as a data professional don't get involved with that because that's not you're not going to do a good job of that i'm not going to do a good job of that i'm an angry gumball says windows team that's a thing that's so many jokes there uh db says the server i work on has more than four terabytes of memory is that a lot it asks it's kind of comes off like you're trying to show somebody your junk in a park by opening up a jacket none of us are impressed with your junk besides we know you're just making numbers up next up peter says dumb questions that's like oh thank you thank you uh says dumb question is there a way to no lock a procedure or view rather than no lock on each and every table yes i'll show you how you do it so if i want to write a stored procedure that has no lock on absolutely everything i'm going to pull out a stored procedure that i just happen to talk about sp blitz so let's go open sp blitz and let's see what it has inside of there so right up at the top here there is this line set transaction isolation level read uncommitted this is the same as putting with no lock on every single query that you have now i know some of you know me and you know how i feel about no lock that it gets you random data that's okay for this because for this it's mission critical that i don't block anything i don't really care if i give you wrong data inside sp blitz because let's be honest you're not fixing the problems inside here anyway so it doesn't really matter what i show you you're not doing what you're supposed to sometimes i say things out loud i probably shouldn't but anyway so this is the way that you do that this is the same as putting no lock on every single table inside there next up next up says oh wow samson on youtube says in a job interview if you were asked to explain on your last project how deeply will you explain or what are the things that the interviewer is looking for what an interview is looking for an interviewer is looking for is they want to know how you can help them so what i would do is as soon as they were asking about the project i would start describing it in big terms specifically about the business okay so the business was really frustrated because the performance on the website was taking a really long time they believed that the problem was a database so they had me make the database go faster and i would immediately as soon as i'm done with that sentence ask the interview is that the kind of problem that you're facing here and the faster that i can turn it on them to get them to talk about the problems that they're having the easier it is for me to come back with oh i had a problem just like that and let me tell you how i solved that on that project the answer that i'm giving here for the server i was working with may not be the same problem that you're having but i want to tell you how a time where i had a problem like you're having and how i solved it this gives the interviewer more confidence that you are going to help solve their problems too so as fast as you can pivot into tell me about the problems that you're having and i'll tell you about a time when i solved it next up um surly devs says asking for a friend of a friend do you run check db on a database or on a restored cop copy of that database when when i can i like running on the the main original copy itself like the production copy the reason why is almost every time that i've seen someone offload checkdb onto a restored copy of the database they've had bugs in their script they had bugs in the restore job they had bugs in the check db job one of my favorite examples of that was i had a client who was doing the restore and check db thing and they'd been doing it for years and i said because i don't believe anything i'm like okay so show me let's let's go in and look at the logs because i want to see the last time check to be finished successfully and they're like oh here it is it's in a table i'm like no no no let's go look at the event log let's go see when it failed and the deeper that we went into there they're like oh yeah we're restoring this 15 terabyte database and running checkdb in two hours and i'm like no you're not two hours doesn't make sense for that database size with the contents of those databases something smells odd here show me let's see the logs and so as we were going into it they were running out of space during the file copy and restore process so it never actually overwrote the oldest copy of the database they were every day running checkdb on a database from like two years ago and of course it was succeeding because they were checking the same damn one every single time so then their eyes all get big and i'm like well just don't any time you have a process don't take for granted that it's got success messages in the log and that means you're okay dig deeper just dig a little deeper now and then and see why it's wrong so that's why i prefer doing it on the primary because then it'll actually update the last good check db and date inside the database and then we all have warm fuzzy feelings it's not that i never do it on a restored copy i just prefer it on the main copy first so manny i'm not actually going to answer that question so if you want you can google that one there's that's pretty easy to google shared says from over on youtube what's the one feature in sql server in the last few versions which has made a lot of difference in its performance and scalability [Music] um column store column store so when column store indexes came out in sql server 2012 they were a hot mess they made your table read only but this is one feature where microsoft kept investing every year and every year it got better it ain't perfect yet still has a lot of gotchas and i talk about that in my fundamentals of column store class but man i tell you what it makes an unbelievable difference on big multi-terabyte databases just fantastic next up juwan from youtube says do you prefer triggers cdc or temporal tables for logging i like if it's logging for my own purposes like for the app dev teams purposes i prefer triggers manny i'll teach you about that my fundamentals class watch my fundamentals of column store class i prefer triggers because i can tune what data i capture and log and how i log it because sometimes i don't care about logging every column sometimes i only care about logging certain columns or only certain contents of certain columns so triggers help me do lower overhead logging plus often with cdc and temporal tables they capture everything and then i have to go through some other mechanism to pull the data to the place that i want it so i prefer triggers but i don't have any hard passionate feelings against cdc or temporal tables also too just in terms of logging i when people think they want logging what they often do is they log everything and then they keep it like a hoarder like their apartment is chock full of all this garbage useless data and they never go back and fix it so triggers are just a little way that i can lower that overhead because once people put in cdc or temporal tables then they gather everything in their databases like 10 terabytes in size next up ganesh asks the sql server service stopped when we were getting data from open queries um it worked when we ran under sa account afterwards can you share info so let me read let me generalize your query a little bit the sql server stopped what should we do about that so anytime the service stops i want to jump out and ask what were we doing at the time is there a better faster way of doing this my worry with things like excel and open query is that somebody's trying to pull the entire contents of the database out and they may run wild and crazy queries while doing it in a lot of shops they actually forbid using things like open query because it is such a performance pig so if i was going to share info the first thing i'd say is let's not have excel connect directly to a production sql server let's find out what data the users need in excel and let's use a tool like ssis or ssrs to pull that data out on a fast efficient way and leave it for them on a file share so that they can go fetch it from there but generally i'm not a fan of having tools like excel connect directly to a production database because they just don't perform very well next db asks from youtube a great question that comes up all the time does it ever make sense to create an index on a temp table this is kind of political most of the time when i see people creating an index on a temp table it's because they think they need it not because they actually need it so the times where it makes sense for me to create an index is when i have a big long stored procedure i'm gonna say like a thousand lines long and it repeatedly hits the same temp table on the same columns my first question is why are we doing it that way is there a better more efficient way of writing that code but if there's not if we need to over and over again hit the same temp table with the same filtering columns or joins then it makes sense to do an index but if you just load the data once and pull it back out again you don't really need an index for that in most cases it's not really going to help that much i just got to pick my battles with clients so sometimes i'll go into a shop and i'll i'll see people with a bunch of indexes on their temp tables and every stored procedure and i'll just be like okay i'm making a note they're not wearing pants or their pants are on their head but i'm going to pick my battles because usually winning that battle is kind of tough and it involves breaking people's hearts next up to bibi says is there ever a good reason to restart sql server other than for applying patches yes so if you make a big dramatic change to your application you deploy a new version of your application for example and you want new metrics across the board including index usage right now the only way to reset index usage statistics is by restarting the sql server instance taking the database online or offline so if you want everything to start again from scratch after a deployment a restart is one way to do that there have also been a lot of cases where sql server had a memory leak so that would be another one if you can't patch to fix it you would restart sql server rather than patching it do i understand spanish mira el diablo estas in miss pantalones uh surly dev says uh the server i work oh we talked about that one so i'll close that little guy next up ariel said uh hi brent in the last webcast i asked you about min server memory equals zero i didn't find a good reliable source do you know any sure so as i told you in the last webcast go here to brentozar.com and up at the top of brentozar.com i want you to click on scripts up there at top click on scripts and then right here there's a download of my first responder kit and it includes things like a sql server setup checklist and in there i tell you how to set up your sql server so there you go you can go get that and that's a source i kind of like a lot cause it's my source next up let's see what we've got here next up um oh monty says uh will a column store index be efficient when a filter condition is there absolutely that's what uh why some of the things that i teach over in my fundamentals of column store class and next up kurt says a friend asked me to ask you about your thoughts on dbcc optimizer what if to see how hardware changes might impact execution plans so my thing is usually uh it's hard for me to get clients to make hardware changes unless they're in the cloud but if they're in the cloud it's cheap to go build a giant monster sql server quickly and it's it's stunningly fast so i'll give you a couple of examples so if i go to ec2 instances dot info ec2 instances dot info is where you can get the costs of different uh vms you can choose what columns you want to see inside here so for example i'm going to say don't show me the linux costs just show me the windows on demand costs i can say i want to see servers only with at least 60 cores and at least 200 gigs worth of memory that have at least two terabytes worth of storage on there and then i can sort by the number of cores the amount of ram all that stuff and you can also change the cost to say daily so just to pick one i'm going to pick the i3 metal i3 metal has 64 cores and 512 gigs worth of ram and a ton of local solid-state storage and it's only 200 bucks a day so if somebody wants to run an experiment to see how much faster their queries will go i just go all right here let's just go spin up a server in ec2 really quickly and for 200 bucks we can run a day's worth of experiments and i can show you exactly how your execution plans will perform differently and you'll feel it because you'll see how much faster the query goes for me optimizer what if doesn't do anything because if the client's just looking at a different plan and they're like okay the plan looks different but whereas here inside here when i can actually spin this thing up and they see the difference in speed that's how people really love and appreciate the difference in hardware so much easier now with the cloud all right next up let's see here oh it says my deck is currently empty oh all right uh let's see here we'll add a couple of into here to do i think that's it let's go pop that one in i am an angry gumball says uh what's your favorite fast food joint and what's your favorite item from there i have so many uh i really like fast food i'm a foodie but there are a lot of fast food things that i like and i'm gonna give you just like a mental highlight reel of some of my favorites carl's jr their six dollar burger i love that because it's called the six dollar burger but it's usually like four dollars because it's just kind of funny uh jack in the box they're patty sandwiches are absolutely fantastic i didn't hit the clear cue button i'm just working all the way through it might be eventual consistency but i'm not seeing anything in the queue there um uh the so the the jack in the box they're patty sandwiches like sourdough patty sandwiches checkers they're french fries checkers french fries are fantastic chick-fil-a aka christian chicken their breakfast biscuits are awesome and so are their chicken sandwiches popeyes i know that there's a bunch of buzz around popeyes in their chicken sandwiches i don't think they're any good i think they're terrible i don't think they're uh good at all burger king i like their fries a lot i also like their whopper it feels like they use better vegetables than a lot of other places mcdonald's a double quarter pounder love that my wife likes the french fries the best from mcdonald's i'm not actually they're okay they're consistent at least but i think if you had me name my favorite french fries they were going to be checkers sonic uh sonic drive-throughs they're tater tots see like i have this just ridiculous encyclopedic knowledge but you know what i won't say is in and out burgers i think in and out which is huge in california in and out i think it's terrible i don't think i think their burgers taste like cardboard i don't think they're very good at all but so there you go wendy's uh wendy's has a whole bunch of good stuff jack-in-the-box egg rolls are fantastic they're breakfast tacos their taquitos is absolutely fantastic it's just absolutely amazing oh eriker says have you tried the icelandic hot dogs yes and also um so there's so many things that i like about iceland iceland food the lebowski bar lebowski bar in downtown reykjavik i absolutely love it i know i'm i'm in trouble for dissing in and out i just don't think that's good i've tried it like five times it's taco bell okay so taco bell they're seven layer burritos i loved their seven layer burritos they're off the menu now but uh oh so good why is this thing not showing any because i know surly dev is putting uh stuff in the cube but it's saying your q is currently empty that's really weird nothing showing up is on deck that's just odd how that goes um so if you want surely dev if you want to try putting stuff into the ticker give that a shot and let's see um let's see if uh if that works because it's not showing anything up on uh on my side there oh well we do it uh uh i'm an angry gumball says my choices for lunch didn't get any easier oh uh cure bell says oh see there we go now all of a sudden they're showing up so now i'll go erica said did you see any difference in performance between ec2 and azure yes for me uh amazon typically has better vm options quicker amazon will offer better vms faster cpus better storage bandwidth more quickly than azure like they get there a year or two ahead of azure azure eventually catches up but for me it i never get to pick the cloud provider anyway the only just vms because really at the end of the day everybody's got vms that's not that big of a deal but if you want something special like and i'm going to give a couple specialized examples if you want a redshift then you're going to be an amazon customer if you want azure synapse analytics you're going to be a microsoft customer if you want deeper integration with visual studio you're going to be a microsoft customer if you have an enterprise agreement with microsoft you're going to be an azure customer if you prefer a lot of open source stuff like aurora postgres you're probably going to be an amazon customer yeah i i don't really care either way i'm fine either way to me it's kind of like the old dell versus hp versus lenovo type server discussions i'm like whatever you buy them i'm cool uh shirley dev says that in and out burger sounds like an indictment on the quality not a brand and yeah i kind of have that feeling it's kind of they have a sharp sign but it's just not that good you watch this is always you know as a consultant you know always always have to be mindful that surely there's probably going to be someone who sees this and plays it to people who are the database administrators that in and out burger and i'm never going to get them as a client that kind of sucks but at the same time so does their food so prakash says hi brent how can one have a sense of humor like yours with sql server makes learning it more exciting and interesting than the current approach thank you i appreciate it that's uh very cool um a lot of it comes with i got started in the the hospitality business i got started working in hotels and restaurants and if you want to learn customer service and talking to people there are few jobs that will make you better suited for that than working the front desk of a hotel let me tell you a story so when i was a fairly young and working the front desk of a hotel i worked for a hotel that was so bad one wing of the hotel was basically condemned that the city wouldn't allow us to rent that wing of the hotel it had some of the rooms had no working bathrooms none of them had any air conditioning whatsoever they didn't have screens on the windows so if you open the windows you know hello bugs but our city small city that i lived in at the time had a really big tourist event once a year and once a year was the air show in muskegon once a year the city was packed so many people would come in with their families and they wouldn't know that there weren't going to be hotel rooms available so here's what our boss told us with a slimy boss at the time the boss said whatever you rent those those condemned rooms for out of that wing of the hotel i'll give you 50 you can have half of the revenue but if they get a refund you're not getting any well i tell you what nothing lights a fire into you like when you work in the front desk because i knew i had to get people to sign for it and they had to stay so i had to say things like all right here's the deal we have no rooms left there are no rooms left in the city you're welcome to call whoever you want i have a room it has no air conditioning it has no bathroom there's a window you can open but there are no screens and there's no fan you're going to be hot you're going to be pissed but if you want it i'll sell it to you for 150 bucks but i'm going to need to say we're going to write it right on this piece of paper that we're that are writing on the checkout you have i understand that this room has no air conditioning no screens on the window and no bathrooms and i will not get a refund and they would sign it they would take it and sometimes it was more than that sometimes it was more than 150 bucks and this is like 20 30 years ago so people would go to that room and if they came back and they wanted a refund i'd give it to them because i don't really care i don't you know at the end of the day i don't want them suing or something like that but a lot of people would go there and they would be pissed but they would deal with it because they had no other choice there was like no other rooms for 50 miles if you want to learn how to talk to people putting yourself in a situation like that exactly there was no travago and even if there was there's nothing you could do there were no rooms available for you know 50 100 miles so that will really teach you how to relate to people and that was uh that was just a wonderful experience and you can laugh and have fun about it when you know that it's coming it's when you have to do that as your job and you don't have a choice that's where it kind of sucks dorman says over on youtube what are your thoughts on a six terabyte database with premium disks should we take the effort no it makes no difference whatsoever premium discs are junk they're just garbage you have to stream or you have to uh stripe 10 20 30 of them together in order to get the throughput that you want you will make no difference whatsoever with the 4k versus 64k allocation units the easiest way to test that is to go provision another azure vm with the same exact uh performance it's only going to cost you a couple hundred bucks to go do it for one day restore the data you know format the drives of 64k restore the databases onto it run a few queries you'll notice no difference doesn't make a bit of difference scott says hi brent i have a 10 terabyte database with 10 000 tables do you have any guidance for partitioning check db to work and avoid the last check to be never yeah so what you can do is check out minionware's checkdb so if i search for minion checkdb jen and sean mccown have this open source free product that will help you restore your database onto different servers and break out checkdb into different parts it doesn't have a gui it involves t-sql in order to set the whole thing up and uh like it's all table driven for configuration as a disclaimer i've never used it because i just i haven't needed to i do things slightly differently with clients in terms of that long story but i'm okay i like i would dry char that in a bubble i would try it in a heartbeat if i were in your shoes so that's minion check to be totally free it's uh knock yourself out next up let's see here uh curable says how is the market for a sql server dba right now in usa the easiest so i can't tell you personally because i have a weird job as a consultant like everyone knows my name i can get work in foreign countries quickly um ariel we're not going to have that discussion again thank you for understanding so it's inside that that checklist if you ask me again i'm going to ban you on the channel so please understand that and hear me hear me once and believe me well kiera bell says in terms of how is the market for a sql server dba right now in the usa the thing that i would say is uh if you if you talk to any of my clients who are looking for database administrators for the longest time for like 10 15 20 years people insisted on the dba being in the office that they had to show up in there and be personally accountable and of course the virus changed all of that so now companies are really shifting gears like crazy trying to change their hiring practices because some of them are totally embracing the remote only uh work others on them are still trying to think well we're going to hold off on hiring until we can get back into the office but for every one of my clients who's embraced the remote only work and they're willing to hire dbas from anywhere they're still having a hard time finding the best candidates i literally got an email yesterday from one of my clients who is hiring a dba right now they have a stack of 100 resumes and we're going to have to figure out how to get through that stack to find the right one so they're overwhelmed with applications but they're just most of the clients or most of the applications just aren't any good they're people who want to get their first dba job for example next up manny says over on youtube if multiple sessions run my stored procedure it takes 30 seconds if i run it for a single session it gets executed within three oh that's such a good question um i oh there are so many uh so i actually do so only for my existing clients so there's so many things to go and look at it could be a memory issue it could be a cpu issue it could be a storage is overwhelmed issue could be a tempdb issue go to brentozar.com and click on at the very top i have a free set of classes running the week after next that'll teach you the fundamentals of query tuning and how i use the first responder kit go through that and you'll start to get a handle on getting the answer to that question but very good uh fabiano near zero just absolutely near zero db says is it safe to install all your sp blitzes on every server can i get in trouble by installing them yeah you can always get in trouble for installing anything because some companies have security standards like where they'll forbid open source i've seen clients where they say uh they don't allow any open source stuff on their sql servers so of course you'll want to check with your systems administrators your security team etc next up benjamin says is it dangerous not to encrypt sql tds connections how often do you encounter non-encrypted so if someone gets to the point where they can sniff your network traffic going between the sql server and the application if they can sniff the network traffic you generally have bigger problems because there are probably other unencrypted routes between the two however i'm going to give you a link to a video so if i search by groupby.org pedro oh what's pedro's last names um there is a it's not pedro lopes let's try secure encryption and security let's see if nope it's not any of those let's try youtube.org or youtube.com and let's see group by uh oh dang it he's from portugal uh encryption let's try that um it's not that it's not that oh there's a really good dang it um portugal sequel server uh encryption video oh this is gonna drive me crazy uh oh that's oh no that's not it um oh it's gonna drive me crazy not knowing that but i'm of course i'm not gonna find it on uh quickly um but there's this there's a great video from a guy out of portugal andre andre andre batista andre it's not andre batista anderson oh man there's a big guy from out of portugal who talks about sniffing the network connections between them and even modifying the packets so if you want it's not that but if you want to learn more about it that's the kind of thing that i would go and search for next up let's see teasy coder uh lowell how are you are so skinny i am not that skinny i am sadly i am sadly not that skinny as my wife will happily tell you that she has to kind of keep me uh reigned in very much in terms of food uh sac says uh frequent conversations what can cause a conversation to air i'm not sure if you mean service broker or what you mean there i don't actually use service broker at all so if that's about service broker i can't help you there and then we'll take one more roman says have you ever seen undesirable consequences on the primary enabling readable secondaries yes andre malencia that's it that's it that's it right there um so all andre mellencia if you search for andre melon see a group buy he has a sql server a video on that exact thing nice catch there lona and it's early enough exactly you got it um so the the undesirable consequences of enabling a readable secondary so i'm going to go through a few because there are a few the one that i hit the most often is people will take a sync secondary a synchronous secondary that has to stay up to date with the primary they'll enable that to be readable and then they'll throw a bunch of crappy workloads at it like a bunch of ugly readable reports and then the secondary will slow down to the point where it affects the primary's performance that is by far and away the biggest one that i run into that people think that they can just enable reports on a sync secondary without affecting performance and it does so i will generally tell people that if you want a reporting secondary it should be async so that we don't have to worry about the overhead on the primary another one that i hit all the time and you nailed that with isolation levels is that people who want a schema stability lock on the secondary can actually so if they're reading a table that structure of that table can't change the table has to say have the same number of columns in it well if somebody wants to do a deployment on the primary and they want to change the structure of the table blocking on the secondary can actually stop the activity on the primary can cause blocking on the primary so those are two common instances that it runs into so that's a good starting point there all right uh uh sal or sake says where did you get your shirt from that's from the spy museum in washington dc love the spy museum and they use sql server that's all i'm allowed to say about that um so thanks everybody for hanging out with me this week i hope you had fun i'll do the same thing tomorrow actually so anything that i didn't get to today feel free to ask it again tomorrow and i will go through there uh through the stream tomorrow so thanks everyone and i'll see you all tomorrow [Music] adios [Music] you

Channel: Brent Ozar Unlimited

Views: 3,421

Rating: 5 out of 5

Keywords:

Id: DHcNecEfJjU

Length: 118min 14sec (7094 seconds)

Published: Sat Oct 17 2020