Live Coding T-SQL in the First Responder Kit

good morning party people how's it going necroduck says why are you streaming so early i'm usually up around two or three in the morning pacific time and so then i just stream whenever i have my like email and background type work going all that kind of stuff done um so uh howdy dboggy jim van allen a bunch of our regulars inside here vomit says do you have a youtube channel where i can see all the videos man if there was just only some kind of place that you could go on the web and you could search for things maybe some kind of like giant place where we could go and find things we could maybe call it like a search engine and you could put in terms and you could go find the terms that you were looking for it's always amazing to me who's like oh can you tell me exactly walk me through something go search for it that's what it's for um cgs is where you've been the last two mornings oh that's a great question so i had an emergency performance client this week and so they you know paid me to come in and do all kinds of stuff performance tuning-wise so i worked with that for that was thursday and friday mornings so thursday and friday mornings and then i just goofed off in the afternoons i don't know um why i don't usually feel like streaming in the afternoons like i like stream you know i guess some of it's probably because my wife isn't up yet you know that my wife isn't awake uh now so i don't mind streaming now but then in the afternoons when she's awake i would rather go hang out with her if i'm gonna do nothing open all good to see you good to see you here uh absolutely [Music] so let's see here uh santi good to see you again as well santi good to see you from india greg good to see you as well cgs said did helmut make it up to portland yes he did i i assume he did i didn't hear anything back from the truck company now comes the nervous part of waiting for i wish there was like a live webcam in the company shop so i could watch helmet being worked on and i have two different things coming back so i have a helmet coming back eventually probably in like two weeks ballpark they were supposed to start working on him not this week but the week after we'll see how it goes um santi said so you'll be moving from oh and abec thanks glad you like the shirt um santee says uh so will you be moving out soon from this house no that's interesting i wonder why you would ask that um my lease is up closer towards the end of the year i want to say our lease is technically up in either december or january and then uh neil it's working okay here i have the most unbelievable internet it's just absolutely crazy i should turn off my wi-fi though hold on just a second let me just make sure that the wi-fi isn't on so that i don't have to worry about just in case anything going wild and crazy with that necroduck says who is helmet helmet is my porsche 911 he's i named him helmet and you spelled the name beautifully too that's exactly how he spelled um santi says i think i read somewhere when you wrote your shifting yeah so when our our lease is up at the end of this year we're gonna leave this apartment i'm going to be here for another like month or two we'll probably go month to month for january and february but then right now touch wood right now the game plan is for us to go to iceland for three months we've got the house all picked out we've got a full gig ethernet going at this house in iceland so we're all ready to go there and i'll be teaching my training classes from there i'll do continue the live class season pass work uh so we'll teach for one week a month and then the other rest of the month we'll be driving around iceland seeing all kinds of sites abec says please say hello to my wife from kazakhstan hello to your wife from kazakhstan so yeah so that's uh so what's going on with me personally there so i had a good week work wise uh cgs says i hope you have snow tires for helmet for iceland we are definitely not taking him uh because especially it'll be in the middle of winter we get a toyota land cruiser when we're over there johnny yeah you're right we've been over a couple of times we spent a week in 2019 and then a month this year in february march and then we're going back for three months necroduck says are you going to stream at 2 a.m iceland time there's a very good chance for that because i'm just like hardwired for 2 a.m 3 a.m when i usually wake up so it does work out with my with my uh can you use complete sentences you sound like you're my drill instructor their agenda today please just because you asked that way i'm gonna say no you're on your own um tioni asked a great question any possibility of getting constant care for european uh companies so in the news this week the eu decided that american companies don't have the right to yank eu data and bring it on in america without doing all kinds of complicated legal stuff i'm a tiny company there's me my wife and richie rump our developer and so we just simply don't have enough money to hire all the attorneys and accountants that it would take in order to be bulletproof legal with doing constant care in the u.s what we would have to do is we would have to thank you glad you like the streaming designs neem neil what we would have to do is host the constant care data over in europe we would love to do that we would love to do to host it over in europe but it's just that we're going to have the fixed costs of having to run the entire constant care infrastructure over there it's not something we're quite ready to do yet if we did it it would most likely be in 2021 the first thing i'd like to be able to do is get our streaming sales done for eu customers do the streaming stuff but with this uh this week's eu ruling i can't even do the streaming stuff i can't put your your email address and all that kind of thing inside databases in the us i would be violating the gdpr and so i'm just like not even close to interested in it dvd tovi says streaming courses please what do you mean by streaming courses do you mean uh courses on how to do streaming i'll never do that just because i'm not a pro at it but i do write about it over on the blog neil says please stream while aurora is visible you know it's such an interesting question so i had to make a decision uh years ago oh in the uk for a sequel yeah no i can't because of your gdpr rules sorry dude go talk to your uh politicians but it's just way too expensive for me to put that together um yeah teonis is we used to have it before gdpr yeah it absolutely was a lot of fun hydra dimitri good to see you again um but neil about the question about streaming with the aurora so it's funny i used to have all kinds of nice camera gear and there came a point where i was like i'm done bringing cameras on my vacations because i just want to absorb the the place around me i will still take pictures with my camera with my phone but i don't i don't take anything more complex than my phone um so when we went and went looking for the aurora i'm like okay i'm not even gonna bother taking a camera because i'm not gonna bother you know trying to get everything set up to shoot the aurora but now now that we do this streaming stuff now that we do this streaming stuff now i'm like well i'm gonna have my camera gear with me i'm bringing it all to iceland so i might actually it's interesting that you say that i might actually stream uh with uh watching the aurora so that's kind of fun uh okay so let's see here did i catch all the comments going up on here oh abec says coupons abec sure why not because i can do that all right let's whip you up a coupon here real quick uh let's say and i'm not going to show the the blog ui while i do it just because it's going to show other coupons like other customers have for stuff um and says let me go put in so what do you want a coupon for who asked for the coupon it was abec abec what do you want a coupon for let's give you something fun just to give you one let's go add coupon or what is it that you're thinking about buying and santi says better to have a shot on the phone instead of all the luggage with the camera gear can stuff some goodies in there yes goodness gracious i got a mute button now i'm so excited with that um but uh uh santee says uh you know it was better to shoot it on the iphone yes but you don't get a picture with a damn on the uh on the iphone so it's kind of tough but the aurora it's so tough to get because it's all night photography from what i've seen i think the android phones do a better job of uh night photography but the aurora is kind of faint i've seen it on boats on sailing trips my wife has never seen it in person though so abec says fundamentals of index tuning all right so let's say uh here you go a back so we'll give you let's see here that is a recurring product discount i think it's a recurring product we'll say 90 percent off expires tomorrow and that is for fundamentals of index tuning let's go find it fundamentals of index fundamentals of index tuning not the live one where's the recording one fundamentals of index to shoot fundamentals of index tuning get your credit card out fundamentals of index tuning where the heck is the replay only there it is perfect uh so there we go and usage limits um we'll say to the first 10 people limit to one one per user publish so abec says or for abac so for fundamentals of index tuning use the coupon code here you go ibec so use that coupon code here you go abec and that'll be 90 off fundamentals of index tuning i haven't tested now it's just the replays it's not the live class version i haven't tested it so you'll if you get any errors with it just shoot me or you know like say over in chat it's good for the first 10 people only so there you go you're welcome enjoy if you don't ask you then it's not you know it never hurts to ask right so i might as well like give you something just for asking david asked is clippy in the house today so i'll show you something only because i mean we're kind of like goofing off here before we right officially start coding so i'll show you something i'm going to go get something out of the closet that you'll get a laugh out of now i have shown y'all in the past my green screen suit i have a whole green screen suit that i'm working on for a clippy costume where i'm going to appear as clippy live on camera well i have something else that i've got in order to be clippy because clippy always seems like he's a little cartoony so i have something else here let me get this on so that i can go walk on camera with it you know how clippy's always like hey buddy hi hi i heard you're querying the user's table hey maybe i can help what can i do for you ah maybe you should create an index that's the thing that you should do so i have these hands so i'm going to be totally decked out in a green screen suit so it'll disappear when i shoot it from my main camera over here when i shoot with me on the background it'll just be me and then my hands you'll just see my face and then my hands so that'll be a lot of fun ritchie says our kelly better not be in the closet my it's true my that is my bathroom over there so inside our kelly thing um what else did i miss uh any starbucks cards left i don't i do have a giant starbucks card balance um dimitri says this is the way i want to spend saturday um yes so this this will be a lot of fun you say it'll motivate microsoft to uh bring clippy back it's probably going to be the opposite of that they're probably going to really hate my guts when i do that these are warm these are this would actually be nice to wear in iceland is like winter gloves so let me go put these things back uh so yeah i think i've got totally everything that i need for a clippy costume i think i've got my green screen set up uh looking forward to doing some more wild and crazy stuff with the streaming oh i should also say too so going out on the blog on monday morning i'll be releasing a post where i'm talking oh uh so see sipa or cape master master moss make sure you choose fundamentals of index tuning only the replays not the live classes just only the replays uh neil says typing might be it might be a small challenge you're absolutely right so i have to rehearse i have to rehearse when i'm doing that pulling the the gloves on and off off of camera like i've got to be able to do it on my desk without looking like i'm all kinds of jumbling around all over the place but i definitely cannot type while i'm wearing those gloves they're huge i thought about cutting small holes in the bottom of the glove so that i could sneak my fingers out but i think that might be beyond the my sewing capabilities not that i have any sewing capabilities catalina wine mixer okay that's an interesting name on twitch that's not bad all right so what are we going to do this morning um so what i'm gonna do this morning is i am going to be live coding in microsoft sql server there have been three or four pull requests uh three or four pull requests in the first responder kit covering sp blitz lock sp blitz index i think yes people it's cache there's an error that somebody filed recently on s people it's cash so i'm gonna spend probably 45 minutes working on the first responder kit take a bio break oh so abec says the coupon is not applicable to the selected product it might let me go try one thing let me go change one thing uh we'll see maybe cart discount fixed cart no not fixed cart discount uh fixed product discount let's try that and see what happens update uh okay so abec try it now about give it another shot now and let's see what happens i actually i'm gonna go do a test order here real quick uh let's fire open uh incognito mode brentozar.com training and then let's go try to buy fundamentals of index tuning when i say it was here you go abec i think it was uh brent ozar brento at brentozar.com uh don't number my email and i think it was here you go a back let's see here here you go a back and apply coupon yep yeah now it works okay oh wow what now it's no it's totally free screw it yeah why not there you go abec it's totally free it'll just be the next 10 people only that's funny how they're working i thought it was a percentage discount but now it's a dollar discount so there you go so that is totally free i just tested it so i know it works so the first 10 people there you go knock yourself out uh let's go back and now over to my posts list just so that i got it richie says it's not going to make it to the uh arctic and neil says it's free alright so let's get started on the first responder kit let's go see what's going on over here so i have let me go uh pull open the first responder kit github repo so we got the first responder kit github repo oh and you notice how i'm wearing a black lives matter t-shirt and their logo the three stripes are in green but you see how it kind of disappears the three stripes kind of disappear let's see if i move around see how you can see stuff behind me kind of weird how that works um okay so we have these three pull requests let's see which one should we start with um the top one is sp blitz who the second one is sp blitz cash the third one i don't says that's people it's index oh i know what that is and then the fourth one is s people it's lock holy cow we got fixes for like four different stored procedures inside here i'm gonna do an easy one first so i'm going to do this one ah good glad it glad it worked out for you there i'm going to do uh sql no greg says can we do number three before i have to leave sure so number three is sp blitz index so i'm going to go pull open the issue so that i can show you the issue first so in sp blitz index when there are when clippy our friend clippy files missing index requests he gives us a list of columns to go index but right now sp blitz index isn't showing us the data types on those missing index so let's go back over to uh to sql server management studio and i always like to try to reproduce the issue first to show you the problem that people are trying to solve and i don't think i put oh good i did i even put repro code inside here so that i can show it to you let's go into the smaller stack so let's go i'm going to run a query 10 times that should generate a missing index request and it did not let's try popping over let me change one other thing about my server just to make it more likely that i get missing index requests and then give that another shot just to see if i get a missing index request beautiful okay so let's go make sure my indexes are dropped do this 10 times and then go run sp blitz index so in sp blitz index right now the first result set gives you the list of indexes you already have this right here is the part that i'm looking at that i'd like to fix this part right here says whatever the missing indexes are what are their data types as a consultant very often i'm examining the output of s p blitz index from people and uh clippy's out there going hey buddy sure would like it if you indexed customer name and i don't know what uh what columns the customer name is like i don't know if it's a varicare max and nvarcaremax you know whatever so i would really love it if that included the data type because i often don't have this results you're welcome eamon glad you liked it so i don't have these results very often so i said can we put the um index or the data types inside there so that was the the request for enhancement there and i said here's what it needs to look like so greg greg dodd has contributed a fix for that so it looks like he's added three new columns to a temp table um wouldn't mind having another crack i'll may make this compatible so whenever i'm trying to review somebody's somebody's work i like going over the files changed tab and this gives me a nice list of the original version or the current dev version of the code is over on the left over on the right are the changes that greg wants to make so it looks like greg's adding a few columns to tables okay that's cool let's scroll down now what am i looking for inside here when the first glance when i do a code review is i want to see if things pass the sniff test like does it make sense that someone would code it this way and the two things that people often screw up are one case sensitive servers often they'll do upper lower case variables back and forth or they won't make the the column names exactly match the system data types in sql server because most people don't have case sensitive servers but sp blitz index needs to work on case sensitive servers so i'm looking for waco cases and then i'm also looking for things that won't work on earlier versions of sql server when i'm doing rapid fire testing like what you're about to see me do here i usually only test on sql server 2019 however i just want to get a quick glance and identify did they put code in that doesn't work on 2008 we try to be good and still support sql server 2008 because it has a really huge market share 2008 and 2008 r2 are way higher than 2019. so i'm trying to support as many versions as i can it's okay if they put things in here right yeah exactly [Music] it's okay if they put things in that only work on newer versions it's just that i want to make sure that it fails gracefully that it doesn't throw out an error on 2008. so let's see what we got inside here so he's adding beautiful so it looks like up there he's adding whenever we're listing out the columns he's trying to add those columns with the data types um he's added on oh he's got a check in here for version type so he's looking for the version number if it's greater than or equal to 13 13 is sql server 2016 if it's greater than or equal to 2016 then he's adding in this other stuff and i'm just going to take actually i'll move this around a little on the screen so i don't have to keep stepping side by side here let's move over to that piece right here there we go so let's see let's just glance oh that's still not even here we'll move it over even further now look windows quit being a dork there you go uh so let's see here what else you got uh is in invaricare care i that that makes me a little bit nervous um just because it's also not saying text and end text and tech not that people use text and end text very often but they're still technically supported they're deprecated they're just not technically supported um so got a bunch of ltmr trims which i'm fine with who all kinds of stuff cross supply okay i can't remember if there's a problem with that on 2008 but he's got this under 2016. string split which hit in 2016 so that's good i can see why he's he's set 2016 as his minimum version there then cgs says we have lots of text in sql server 2012. so there's a lot of code inside here and it looks like he's trying to fail gracefully on earlier versions so let's go see what happens uh yeah mr franklin you couldn't order the course from uh cause you're from germany well maybe you should rein in your politicians and maybe not pass laws that small businesses can't comply with because it's very expensive or you know do your thing um so let's go see if uh let's see how this works with uh g.c's version so g dot c's version let's go get his sp blitz index get the raw uh greg oh that's a good point greg says i don't think we need text because the code is just trying to show the length of the field that's a very good point you might be right i like the way you're thinking there so let's go get that let's get the contents of his query and let's go put it into production and let's run it and see how it f how it works so now i'm going to rerun the exact same stored procedure what i'm hoping to see is i'm hoping to see that the location now shows the correct data type and for bonus points about me is actually an nvercare max so that'll be interesting to see how he handles that so let's go execute it all right there wasn't an error my standards are really low and then now let's look and see oh man damn missing index is null okay so he's got a bug inside there so this is where it's a little bit tricky being a maintainer for open source stuff so when you're a maintainer for open source stuff you're trying to balance two goals one is that i want to get as many pull requests as i can of quality code that's good enough that i can at least take it across the finish line and run with it i want to encourage as many people as i can to submit pull requests on the other side i'm also have to balance my own time like how much time i put into testing other people's code to you know it's where do you draw the line i'll give you the dba tools project those of you who use powershell the dbatools.io team they do a phenomenal job of catering towards the side of taking as many pull requests as they can i have submitted pull requests for dba tools and i don't even know how to use powershell but at least i can go in and tweak some small things for other people i don't test my code though because i just don't even know how it works so in here with the our whole virus like um so any that that would be one way is going over and aboard they'll take your pull request and they'll go do all kinds of work they're like refactoring a pull request that i did from scratch doing all kinds of stuff the other extreme is going like it's just me here trying to maintain this uh open source repo to the other extreme would be me going i'm sorry greg this doesn't produce the results that i want or it doesn't produce the results it just doesn't it doesn't even show anything it shows a null in this case and it's the i'm using the exact same code that i posted in the github issue this would be like the minimum quality that i would usually try to accept thing is greg usually does really good work i've seen a lot of greg's pull requests so i'm going to take a couple of minutes to just see if i can see something obvious inside there so let's go take a look now the place that i'm going to go look isn't in the code because sp blitz index is absolutely ginormous i'm just going to go try to look at the things that greg was doing inside that github diff that we looked at inside the website and see if i can spot the bug there and maybe some of you can spot the bug i'm going to guess that if there's a null i'm going to guess that greg's code has a problem appending nulls to something so i'm actually going to write another test for it real quick well writing a test is a strong word because i know when i say write a test that has very different meanings for me than it does for richie now you might remember that when we looked at the output earlier it was an index on location that included about me i'm going to guess that because about me is an envercare max i'm going to guess that greg might have a bug interpreting the output of uh and bearcare max so yours are absolutely although i will say i have apple homepods and you don't i'll also say i'm not sure if that's an advantage or not so let's say instead of getting um an invaricare max column i'm just going to do a missing trigger a missing index on location which is just an enviro column but it doesn't include the unbearcare max let's go see if sp blitz index works correctly on that and unfortunately nope nope no luck there okay so in there then i'm going to hop back out and we're going to go look at his pull request and i'm gonna look to see if i can spot whatever it is that he did that was an accident there so i don't think it okay so he's putting stuff into a temp table one thing that i could do is i could do a select out of the temp table and go see what the contents are of the temp table maybe he didn't get the stuff quite right inside the temp table would be one way to go and look let me keep going down all right one thing that makes me nervous is any time you append any time you append values to a string if one of them is a null sql server will set the string to null so what greg's doing here is he's saying case when equality columns is not null then add in this oh what's going to happen here well what could happen is that if equality columns is populated but equality columns with data type is not then it'll say what hey this thing isn't null over here so let's append something else and then that's what's going to break a problem there abhishek says can't we use concat normally i steer people away from things that aren't supported in 2008. you could play with that here though since sql sense greg is using is already filtering for only a specific compat level i want to say it was 2013 or 2016 he was searching for so in here this is going to trigger the problem of if we're if we have a null inside this one down here but this one up here isn't null that will trigger the bug that we're looking at all right so i'm gonna rather than me fix it and go deeper because i i just have this general rule i'll tell you what my general rule is if i give you a bug report if like i give you an assignment as a developer and lord knows i did not assign anything to greg greg's being a wonderful volunteer he's picking things up and running with it um mel go apply thanks i appreciate that i'll check into that some time so if i give you an assignment kind of as a developer and before ritzy's listing in on this and all of a sudden perks up uh richie it's different with you um but if i give you an assignment as a developer and i say when this happens i get this problem you know like i give you a tight repro and i say if you re if you run this it reproduces the problem and then you give me back code and i run the exact repro that i gave you and it doesn't fix the problem my work is kind of done here in terms of testing that's kind of as far down as i'm going to go neil says you know go change this to two places that might do it maybe it might but then here's the next thing i worry about i haven't even checked to see that he's populating the contents of those columns correctly it's just the flat out null handling that's broke so i'm going to hit the breaks here in terms of there are only so many things that i can do in the span of the two hours or so that we're going to work together trying to merge pull requests in so i'm just going gonna go do a code review instead right sql uh sql greg says i don't think it's oh sequel greg is the greg dodds oh welcome oh you're wonderful it's good to see you greg says i don't think he's checking column one but getting data from column two is if one is there the other should be there i know they should be just like i should be attractive and wealthy and on the you know south coast of france right now but that's not exactly true and richie's absolutely right i do constantly break our unit tests like i don't even test my code in postgres half the time someday when y'all hire a developer you can lock him into a closet and make him write unit tests as well like it's probably not what you would do all right so let's say in here let's do a code review so we'll say review changes uh and we'll do it line by line so i'm going to say uh in here we're checking equal checking uh one column for nulls but using another a couple of things might be a problem here um if one well actually i'll just say it as uh greg saw this on the stream so he's going to dig into it add single comment and then review changes um request changes didn't work whoops the this didn't work with the repro that uh was on the issue so let's give her another shot submit review so now i've got the code review done inside of there i'm not going to delete the pull request because greg may be able to just make a couple of changes and then push his changes again and then we may be able to use it exactly as is so there's that one um so one of you i can't remember which one oh maybe it was greg oh that requested me to go do that one first so there we go um things that i would so whenever i'm working on an issue like that i also try to to dump in here are the things that here are other things that we may need to look out for i i totally believe that um so i'm going to say in here other things we may wanna test to make sure it'll work okay uh sql server 2016 but the database is in 2008 compat mode missing index requests with each of these in equality inequality and includes uh invaricare you know 100 and bear care max uh varicare 100 varicare max xml json text and text and then int smalland or small ant big and big ant and dates that should do it um sequel races the limit for 2016 was using string split do you have a workaround for that which i could look at adding as well i'm okay with that i'm totally down with that i'm uh i'm fine with you using string split that's totally cool for me i'm i'm okay with that um let me go look at what you did with string split because i might let me take a quick look and see what's going on with that that was in here and i'm just going to rename this just so that people know sp blitz index show data types on missing indexes uh welcome not the dba you're looking for good to see you again let's see here string split uh from string split inner equality columns oh no i love that i'm totally down with this i'm totally okay with that i think that's fine yeah thumbs up for me i'm down with that um i'm i'm of the opinion that i'm okay when this is a new feature like this if other people on prior versions can't use this this is just nice to have it's wonderful to have and um i'm thankful to get the pull request if it doesn't work if someone wants it to work on earlier versions they can figure out how to get it to work on earlier versions as long as we fail gracefully i'm totally okay with that okay so there is the first pull request out let's go see so the next one i'm going to stop here for a second where the hell's my button there it is i'm gonna stop here for a second to give a shout out to this week's sponsor so redgate just finished their uh state of the database monitoring industry report for 2020. so in there they asked a whole bunch of database administrators what are the kinds of databases that you manage what are the problems that you run into how do you monitor for those kinds of problems you can read about that over at brentozar.com go slash read it totally free anybody can go grab that and learn how your peers are monitoring their databases as well okay so we've been going at this for a while we've been going for 37 minutes let's stop here and take a short bio break we'll take a five minute bio break i will go refuel my espresso i should warn you that it's going to be a little lively today probably because i ran out of decaf espresso so i'm only using real espresso that's fairly unusual for me that's a very unusual for me so it should be maybe a little bit more sassy than usual as well um so we'll take a five minute bio break and we'll be back in here in five minutes and we'll go on with the next pull request see all in five [Music] hmm [Music] um [Laughter] [Music] beef [Music] hmm [Music] um [Music] if [Music] beep [Music] [Applause] [Music] all right welcome back um not the dba we're looking for says i see sql constant care gets a shout out in red gates monitoring paper at five percent of the people using it i was i was excited when i saw that too it's kind of cool to to see it and to see it continue to get market adoption when we really don't push it that hard i don't blog about it that often i don't uh push it on the webcast that much but we're diabolically working on the next round of improvements that i think people in this kind of audience will find very interesting if you're the kind of person who likes twitch live streams gaming likes seeing other people do gaming i think there are going to be some improvements that you're going to like in constant care and i know that sounds completely bizarre so all right so next uh what are we going to do next let's go take a look at the remaining pull requests and go see santi says how many cameras do you have there four altogether i have this one that you're looking at here i have one directly in front of me that shoots with the green screen behind me so that i can use the uh like the fadeaway looks behind me yo adam saxton welcome and adam's one of the people who got me inspired on using multi-camera setups there as well and then also in my in your setup is bananas though because you use higher quality of cameras then on my pause and i'm going to put this on and it's going to look like i'm going away for a second but i'm not on the pause i have two cameras i have a logitech brio pointed out my office window so that one usually aims directly out my office window but because it was dark i aimed it over here and then i also have an iphone pointed out my desk that also shows up on the behind the scenes uh cameras so two of them are kind of crappy um but then the two of them are really good so coming back over here to this setup so there you go um cgs says i'm considering constant gear because i'm tired of fighting with the other developers make my network guys over whatever make our companies go faster yeah that for me is a lot about what constant care is designed to be is to get a very fast get everyone on the same page and because it comes in via email because the results come in via email you can put whole teams on it you can put distribution lists on there and then everyone's completely transparent about what needs to do what needs to happen next santee asks do you always stand and work i always stand when i'm doing webcasts but then as soon as webcasts are over this is a motorized standing desk so i'll just push the buttons to so you can see it start to drop down so it drops down then into a sitting position and then from there i go back and pull a chair back out i have a chair just on the other side of the camera so all right so there we go neil says my setup is only a three camera setup sad face well but the nice thing with stuff like iphones i mean just using an iphone as a camera you can get a used iphone from like woot uh refurb iphones for like a hundred bucks and then with a mac you as soon as you plug them in they appear as a camera source so it's so nice because they have a screen built into them so it makes it much easier for positioning backs uh uh backs behind the scenes type cameras it's really useful there botsco it says is it from cameras what do you mean tell me more about what you mean is it from cameras cameras from outer space okay so we will go take a look now at our next pull request and go see oh he says two rows above um i'm not sure what you mean two rows above um so plus two richie um and adam's absolutely right you really only need one camera and a light and a microphone and that's like it people care about you and what you're broadcasting bosco i don't see your earlier questions i have a ui that shows me a whole list of stuff you may need to answer your question ask your question again um i'm not i don't see the like the twitch chat i use restream no no don't apologize you're a wonderful person and i like having you around you ask really good questions so uh and i know sometimes i go on a hair trigger but i love you you're good people i i mean i don't really know you but um so yeah it's uh oh cgs so why didn't you go live from mexico because in cabo mexico i'm gonna show you the map so if you look at a map of cabo mexico cabo is in a very interesting place two turntables and a microphone cabo's in a very interesting place so this is where i love to go in mexico it's right down at the very tip of that peninsula and the internet there is terrible it's just really bad it's either satellite which has really bad latency or else it's landline but then it has hardly any uh throughput at all so when i go there i don't disconnect i still do things like oh that's cool of google to put the thing up there on the top um so i love it it's a beautiful place my one of my favorite places in the world but ironically you get much better bandwidth at iceland iceland there's gig fiber freaking everywhere to house houses get gig fiber symmetric gig up and down for like 30 40 u.s per month it's amazing how good the bandwidth is there okay so okay though jeez get it terrible okay so here's what we got next so we did 22 27. now we've got this one's super easy so i'm just gonna go knock this one out so what this one was i'll go open it up and show you the issue oh he didn't put the issue inside there let's go link it over from lewis says i guess i'm moving to iceland the problem is it's really hard to get work permits there it's spectacularly hard to get work permits there which number did he say it was 24.82 so the bug in this one here sp blitzcash takes a sort order parameter so when you want to find your most resource intensive queries do you want to find the ones that are the top 10 by cpu the top 10 by duration the top 10 by reads by execution there are all the ways that you all the kinds of different ways you can sort the plan cache there's a sort order parameter that makes this magic all work well when adba adrian i believe his name is who's a wonderful contributor also just like greg when uh great our aide was using it on a case sensitive server he didn't get the results that he was expecting and as he drilled down into the source code it's kind of funny how this works so and i'll show you the source code for sp blitz code so that you can kind of get an idea so we'll go open up sp blitz cache and with sp blitz cache let's go down to where the parameters are at so here's sp blitz cash oh good luck uh richie try not to break the hip or anything uh today um oh and i wonder if you're doing it inside or outside because that would depend whether or not it was hot yoga so sp blitzcash has this parameter sort order and depending on what you pass in we do different stuff so in order to do that we have to examine the contents of sort order so i'm just going to do a ctrl f for sort order here and jump down to where we start using it let's go back a little is there a quick easy way to find the previous one in here there is he says so here's an example of how we handle sword order so depending on what you pass in we send that into the order by inside the query well you notice that these are all lower case there's probably a reason for that right because if i go in and i look [Applause] fine previous find previous find previous let's go find up it's got to be way up inside here actually let's go all the way ah see how it says lower inside there they're going to be places where we have to worry about the sort order and its case sensitivity if i go way back up here up and around line 2 000 i believe it is lower and i'll see if i can do it this way sort order ah see how we're having to handle all these cases well you know what we should do is we should just set the case set the uh order or set that sort order thing to be lower case right from the get-go why don't we just set sort order to be lower case right at the beginning of the stored procedure and then all throughout the case of the store all throughout the contents of the stored procedure we can do handle uh examine it no matter what it is as long as we're smart enough to use a lowercase inside here some of you may remember not the dba was just typing that some of you may remember a few weeks ago when i was doing a stream i went through my blog post queue the things that i need to write at some point in the future and one of my blog post ideas was everyone in their life needs to work on an sap database sooner or later in your life you need to work on a database that hosts stuff for sap because they're terrible oh my god they're awful they're so bad they have thousands of objects in them object explorer doesn't work the names don't make any sense they're letters and numbers they have nothing to do with the contents of the the data itself they're case sensitive atoms like absolutely it's awful but the first time that you do it you'll have a new respect for what it takes to build stuff like open source yeah it's equal to fdba you're absolutely right that's uh true as well not the dba you're looking for you're absolutely right there as well so we would lose a lot of people there out of that now adam you said is your bane and you spelled it in an interesting way the bane of my existence is b-a-n-e and i'm guessing that you don't know but you are actually making that cool joke that b-a-i-n is the batman thing which is the tie-in they're saying bane of my existence bane was batman's bane of existence so it's kind of funny how that whole thing worked in yes oh it's just terrible write a passage is another good way of saying it so that teaches you when you go to write stuff like sp blitzcash oh you knew oh very nice then nicely done so it teaches you all these things that you have to do in terms of defensive programming i like that sure ain't pretty that's probably true so in here you might say well why don't we just at the very beginning do a lowercase of sort order well a long time ago somebody actually did but look at the line number the line numbers all the way down at a thousand ninety-one well that's too late yeah hey back i talked about that in the beginning it's because this uh black lives matter shirt has green stripes as the logo so it ends up kind of showing me uh kind of being uh uh transparent which is kind of funny and i i'm gonna use that to my advantage with a green screen suit here one of these days i've got the green screen suit already in the closet so the problem is we just put this in the wrong place we just put it way far down and we probably should probably should have put it way higher up um so let's go see so that's what the bug is and this is what uh um i almost said bain uh what adrian stumbled across is that he went to go use query hash but unfortunately we start handling query hash way too high up in the stored procedure before we've said it adam says yes floating head i have because adam's here i'm going to show this because adam's here i'm going to show this i'm going to go grab it out of the closet because he's going to find this amusing i have already got off of ebay not ebay amazon a whole green screen suit you can get these surprisingly inexpensively now obviously i'm not going to put it on but i'm just going to kind of i'm just going to kind of hide behind it so totally floating head and what it is oh let me switch over and show it over here what it is is it's a lycra green suit even complete with a hood that goes over your entire face so you can just cut out the parts that you want and so i'm gonna have just my face uh you know just the face part peering out out of here and then use those uh white gloves that i showed on earlier very fun so uh the things you know you should see the things that i had in my amazon suggested purchases after i bought that it was like oh my god i want to oh well adam the first like five years of my green screening were terrible it's just awful it was really hard to do but then also my audio sucked my camera sucked you know it's always it's a growing and learning process it takes a while to get right but oh i just love that elgato's dropping green screen this is just phenomenal to mount on the wall or ceiling it's wonderful you're invisibility cloak yeah exactly shampoo that's exactly what it is so coming back over here a random guest to find it in your house i have my one of these days i need to do a tour of my office closet it is unreal the stuff that i've got inside there just bananas um and my my wife really embraces it too just my interest in buying oddball things for the stream so what a decide that he wanted to do in order to fix it let's go look at his code and see what he decided to do so he said let's move the sort order earlier in the proc let's go look at the files change i just love how github makes this so easy which is right here in the web i can go see what he did so he moved it all the way up to she is she is good people she's she embraces it and runs with it in a hilarious way um so he moved it all the way up to line 756. just to double check i'm gonna go look at line 756 and do a find for sort order and see if it shows up anywhere earlier so we'll say sort order or i'll go all the way up to the top we'll go straight up to the top of this hit control f for sort order and then go see where the first one is now obviously it's in the beginning params that's totally okay here it's the help content where if you pass in a parameter for help we tell you what the instructions are then down here that's also still in the help 756 there it is so there's the first place that we're handling sort order so i'm okay with this this looks good as a fix in this i'm not even going to test this code i'm completely okay with it what i could do for bonus points is i could add i could go find all the places where we're doing lower sort order and i could just remove them because they're not really helping us at all at other points inside the code but screw it i'm just going to ship this and roll with it i'm going to say review changes looks good approve and submit then i'm going to merge that pull request i'm going to assign aid uh adrian i don't know why i call him aid because i'm certainly not that we're not that on that good of terms that i would know what his nicknames are this was a bug it was a bug in sp blitzcash uh and then we're gonna put it in the milestone for this month for august's release i'm gonna merge his pull request and then i'm gonna go back and to the issue itself and so assign him there as well just because i like people to be able to track which issues they were involved in the pull request that was linked here's the pull request and then over in the notes i'm going to say great job uh merged into dev will be in the august release with credit to you in the release notes closing comment now i have to go update our post with the release notes so i have over here in wordpress i have the release notes for next month's release i start a draft post in the beginning of the month whenever i'm going to go work on uh first responder kit stuff and so i'll scroll down to sp blitzcash so here we go oh i've already added it in oh i added it in when he when i saw the pull request so there you go oh shambill thank you i appreciate it welcome to the club chamber all right so we've got that in our release notes we are good to go let's go see what the next one is let's see what our next pull request is so the next pull request uh aid we'll do aids next one too just since we're uh in here so aid says do not accrue wait time for sleeping spids this is very interesting so uh let's see here the issue number is that the issue so he says and i'll i'll take a sip of my tasty beverage while we both read those notes okay cool i love that he gives a little repro script there let's go ahead and assign him not me and then we'll say this is a bug in sp blitz who and then we're gonna stick it in the milestone for this month's release now i always like to see the bug first before i go check the fix not always i didn't in the last one but let's go see the bug first so adrian said go start a transaction and sp blitz who will show weights piling up so let's write a transaction let's write some blocking we will say go make this bigger make it easier to see begin tran update dbo users set reputation equals reputation plus one where id equals 26 8 37. go so i'm taking out a lock to grant myself a reputation point now we'll go over into another window and we'll go run select star from dbo users where display name equals brand ozar let's go see if we can select our data we can't we can't because i'm being blocked the other session has a lock on adding a reputation point to me so now i have two sessions that are open here over and i'm going to move these out so you can see them independently and scale down the font just a little bit here so over on the left hand side that query isn't waiting on anything that query started a transaction he hasn't committed but he's not waiting on anything the query on the right is waiting for a lock so in terms of performance tuning for weight stats there are no weights really happening in the session on the left there are weights happening on the session on the right so what aid is saying is that when we look at sp blitz who which shows us which queries are running right now sp blitz who goes in and shows you which queries are running the top line is the one on the transaction that's doing an update he shouldn't be waiting at all but if we scroll across to the right here oh that's the bug right there it shows that we've been waiting on a lock it's actually got a couple of bugs there you see how it says 47 607 and it doesn't even show milliseconds that's kind of goofy too as well so let's see what aids fix is so we've verified that it really is a problem and let's go see what aids fix is so let's see what his pull request difference is and i have no i i haven't looked at sp blitz who's contents in like a year maybe more i don't even know where those numbers are coming from so we'll be both learning together as we go through and look at the changes in aids code let's move it around a little bit so we can see both of them on here without me blocking anything i always love that it's derp inside here left hand side is the original version the right hand side uh thanks cav tv welcome to the club over on the right hand side these are the things that he changed i like it where status is anything other than sleeping then put in the weight types right now my transaction that was over on the left is sleeping so he wouldn't shouldn't be incurring any weights okay is that the only thing that he changed looks like there's another one down here so that i'm fine with both of those those both look good remember earlier when i was talking about code reviews i said i'm looking for things that cause case sensitivity problems and i'm also looking for things that don't work on other versions it looks here like we're safe on the board across both of these i don't see at a first glance anything that's going to cause problems across other versions or case sensitivity issues so let's put his changes in place to do it i wish this is one thing that i wish was just a little bit easier in github i wish there was a jump for me to go get his raw file straight from here i wish that i could go into here and say like view or edit file but when i view file if i remember right it takes me to mine not his if i do the raw oh no it does oh raw yay oh that's fantastic so we're gonna copy that out then we're gonna go put his new version in place on my server i'm going to put it in the master database to override the one that's already there and then let's run sp blitz who again the thing that i'm looking for is that that lock weight shouldn't show up on the update let's see how it goes and yes that's fantastic that is not i'm in the wrong window i am a let me go over to that window and there it is that is phenomena and of course i end up with cowabunga's uh galbanga's thing right on top of it let me move it around a little uh there let me figure out how i can there we go so that works beautifully that is fantastic we will take it and it didn't seem to break the other one too which is kind of good all right good let's take it i will go stop my query and roll back my transaction roll back no that wasn't your bad that was me not knowing uh i'm using zoom it live for the first time like the the one that lets me continue to type while i'm zoomed in um and so i'm still trying to remember how all the shortcuts and like moving the screen around type stuff works so that is as they say on tv magically delicious looks good and approve us americans we we probably use too many exclamation points um ill will that's a great question and i'll answer that in a second let me do the pull request and then i'll answer that and we'll talk through it i'll show you a demo to show you the differences between them uh so let's change this over to be assigned to adrian then let's call it a bug in sp blitz who and then we will say the milestone is this month's release and then we will merge the pull request confirm the merge and that worked let's go say thank you to adrian looks good thanks for the pull request merged into the dev branch will be in the august release with credit to you and the release notes close and then we'll go over here to our release post and so the changes maximize this here changes over here we'll say changes in also got s people it's lock changes coming so i'll add another one sp blitz whoops sp blitz who changes uh fix um sleeping sessions no longer show as accruing whoops accruing wait time number 24 camera was 24.65 is that the poll 24.65 24.65 thanks adrienne b and i got to go look at adrian's last name spelling because i don't remember so let's get his aid and that is adrian buckman okay buckman there that goes paste and there we go okay so let's save that and now i'll go answer uh ill will's uh question so ill will ask are there any differences between sp blitz who and sp who is active a long time ago in a galaxy far far away when adam first released at a mechanic first released sp who is active it was copyrighted and technically if you wanted to obey the licensing which i'm all about if you want to obey the licensing you couldn't install it on customer servers you had to have them install it well i needed something that i could just bundle inside a download that any customer could go grab and then start running and i also needed something that i could install on customer servers and all my stuff is licensed with mit license so you can do whatever you want with it so eventually adam ended so we ended up having to write sp blitz who we tasked eric darling was the one who originally wrote that so we had had him write that as like a drop in replacement for who is active we we made it much more simple because of course adam had a lot of years invested in sp who is active he also performance tuned the daylights out of it to make it insanely fast even under high concurrency situations well years later adam open sourced sp who is active and i forget which license he used but he no longer works with sql server much these days he works with other database platforms so he open sourced sp who is active now i use both i use both sp blitz who and who is active the things that i like about sp blitz who is it's better for real time query tuning who is active for me is better for real-time emergency troubleshooting because adam has performance tuned that thing like to within every inch of its life whereas we haven't really done that much performance tuning with sp blitz who the things that i like out of sp blitz who is it'll show you things like resource semaphore memory issues way easier it'll show you parallelism issues way easier who is active shows you blocking issues better in my opinion so i think they're both good i end up using both of them now i won't do a demo for that that pretty well sums up the differences between the two for a while there we don't really do that much active development on sp blitz who anymore it does the work that it needs to do and adam's the same way with who is active he doesn't really do active development on there either um you're welcome uh evatar all right let's see what the next pull request is see what we got inside there so the next pull request let's uh we'll close that window we'll go over here and then the sp okay so we hit that one this one uh the sp 2452 sp blitz lock object names with periods this is so funny so sp blitz lock will show you dead locks and it uses the extended events default trace the extended events default system health session which there was a short name for that sql server by default logs all kinds of deadlock info inside there well unfortunately the built-in deadlock info that comes out of that event session shows table names incorrectly when they have a period in them and i should show i should put that or the issue up over here so that you can see it if you have table names with a period in them or are database names with a period in them then the built-in deadlock report shows things incorrectly this isn't a bug with sp blitz lock it's actually a bug with sql server and you can pretty well guarantee good morning you can pretty well guarantee microsoft is not going to fix that anytime soon going back through all the different versions the sql server just introduced too much risk there's not really a reward to it the only time that you care about it is when you're trying to parse those deadlock reports programmatically like what we're doing uh here abec no we don't i don't work with azure sqldb at all and nothing against it i think it's wonderful it's just that the problem is microsoft can yank support for dmvs and columns at any time and they have so i i put a lot of work into supporting some of the the queries like s people it's cash and s people it's index on uh azure sql db only to have them fail or unsub you know just out of nowhere because microsoft changed something behind the scenes i can't drop everything and go make fixes for stuff like that i'm like when microsoft publishes a change log when they publish a list of things that they change and when then i'm in a hundred percent but since i don't know when things change they won't tell us they won't give us any kind of preview and they won't give us a recap after it's done i can't invest my life in supporting that unfortunately but i encourage other people to do it i'm like if you want to do that you're welcome to um stupid these equipment yes oh that post is so awesome that's what i do things like tables with uh databases and tables with spaces is the name so that's very good that was a ton of fun so let's see so i've reproduced this bug but now unfortunately i'm gonna have to reproduce it uh traveling snail correct you're absolutely right so if i want to reproduce it i'm going to go build a fix for it let's see here so let's see has he let's see if all the way down wish to hear what you think of the solution okay so it's a slightly different solution because of the data all right let's see what the solution is let's see what this file change looks like okay so the things that i'm looking for is case sensitivity and stuff that's different in different versions i don't know what this is associated object id i don't know if that casing is right we're going to have to go in and take a look at some of these variables let's page down okay paging down again paging down again paging down again and select distinct select distinct from deadlock oh okay all right that's cool so this looks okay it doesn't look like it's gonna blow chunks um across different database versions this all looks okay there is an ever so slight risk that someone doesn't have access to um a particular database because see how it pops in and does a use uh execute sp ms for each db and then it's using the databases that are involved inside there um ratnala says there's not much clarity on the screens well you may not understand how sql server works so just kidding no you can also zoom in you can use a higher definition screen i'm broadcasting at 1080p i actually use 4k on my desktop i just can't stream that way obviously okay so what am i going to do i'm just going to go ahead and take this and go c i'm going gonna go take his script and go see if it works on mine let's go take it view the file get the raw contents of it go put it in on mine oh before i put it in i need to demo the bug to make sure that i can see what the bug is now i have a blog post on uh deadlock sitebrandozar.com i have a deadlock uh and let's see here how to create deadlocks and troubleshoot them so i've got a little blog post here about how you create and troubleshoot a deadlock let's go copy this out and paste it in inside here let's close all of our other stuff let's actually close everything no don't save anything new query now this bug is all about database names with a period in them so we're going to have to create one we'll say create database test blocking and go and then use test blocking go and then let's create the table let's start a transaction then i'm going to start another window and i'm going to do something else over here copy begin transaction begin tran go and execute then over on the left hand side i'm going to run this this is blocking right hand side i'm going to make the deadlock happen i'm going to count down from three to one and then i'm going to hit execute over on this side within five seconds we're going to have a deadlock so three two one execute don't know when it's gonna happen but within five seconds we'll have a deadlock and boom one on the right hand side was deadlocked so he loses now let's go look into sp blitzlock and see what we get so let's start a new window sp blitz lock and execute there's a chance oh good yay it actually showed the deadlock so that's good so now what's the problem with sp blitz lock here so here the problem with sp blitz lock all comes down to this piece right here the database names and object and schema names are all screwed up because we've got this database name with the period in it so here's the problem that we're trying to deal with sp blitz index commands here show the wrong database name obviously the database name isn't dbo the schema name is an l and the table name isn't fd so let's go try uh adriano it's this isn't adrian's who is this i should uh know their name say my name i want to say it was def con that uh submitted the pull request let's go see so philip oh yes phillip oh he's had a couple of good uh pull requests recently so phillip let's get phillips code and then put it in and see what phillips code does put it in the master database and let's see big money no whammies [Music] and yes oh well wait no oh so close so close so close table name l dot fd and r dot id that might have been from another test though see look at that that's a different database name that's actually a different test that's not the deadlock i just did that's where i had periods in the table names oh so he might still be okay now you'll notice that the one we just did though isn't in there that isn't unusual sometimes sql server doesn't flush deadlocks out into the extended event session for some time and sometimes we have to go do a couple more deadlocks in order to trigger sql server to go do it so let's go do a couple more deadlocks let's move this over to the right hand side so let's do this again we'll say over here commit and commit our transaction let's start it again begintran begintran update update there we go so now we've got another deadlock in there let's go see if sp blitz lock shows it now and come out over here and it's looking good and it looks magical that is fantastic i love it it's absolutely perfect so here it shows the correct fully qualified database name it's also working beautifully with periods inside the table name as well i am happy with that as they say ship it now i didn't test it on a case sensitive server and i didn't test it on sql server 2008 so a note here about my testing when i'm accepting other people's pull requests what i'll usually do is just uh get a very quick rough idea of does the code pass the smell test and will it at least work on 2019 and if it passes both of those if it works on 2019 and passes the sniff test i'm like i'll just merge it into the dev branch i do another round of testing on 2008 2012 2014 2016 2017 named instances default instances as i get close to release but i just don't do it day to day as i'm working with each individual pull request i am a terrible developer or open source maintainer so in this it looks good jim welcome to the club thanks for subscribing i appreciate it so in here let's take his pull request i'm gonna say review changes uh i only did a cursory look at the film i know you mean like every one of these platforms has a slightly different way of subscribing i only did a cursory look at the code and i didn't check to see whether it works on case sensitive servers but i did test it on 2019 with databases and tables with periods in the name and it works and we'll then approve it submit our review and then back over on whoopsydaisy we were right there we will merge the pull request confirm and then we'll go back over to the issue and we'll say oh i think i was assigned to that thomas welcome to the club too as well man all kinds of y'all are jumping in here today thank you and it's always nice to get subscriptions too those of you with amazon prime if you have amazon prime you can connect your amazon account to your twitch account and then you can get free twitch subscriptions there as well when you subscribe to channels on twitch they get a monthly revenue too doesn't cost you anything at all if you have amazon prime it's totally free but then it like gives us something back to encourage us to keep streaming frankly i'm going to stream whether you're here or not so this is we're going to set this as the milestone i'm going to thank philip i'm going to say looks good i merged it into the dev branch and it'll be in the august release with credit to you in the release notes thanks for solving this i know it couldn't have been an easy problem at least it wasn't for me and come on comment now let's add this to our monthly release notes over here on our post with the release notes we'll say fix now databases and tables with periods in the name i'll say before databases and tables with periods in the name uh wouldn't show correctly in the detail results uh number 24 what was it 24 [Music] 24 52. close that guy uh 24 52 uh thanks phillip and now let's go get his last name paste that in there as well lady melville thank you appreciate it welcome to the club thank you very much now phillip uh let's see here phillips last name is uh cornell listen corn oh good it's up in the title bar uh cornelius son and then link to him over there paste and save that all right very cool life extinguisher oh that's a funny name that's cool okay uh so that is that all of our pull requests other than the one greg's working on it is yes oh that's fantastic love it that's wonderful that's a perfect breaking point um okay so we'll stop here for a minute we'll go take another bio break and when we come back i'll tell i'll do open q a if there's anything that you all want to ask me about sql server we'll do you know any subject is totally fine we'll do an open q a and then after that i'll go look at my blog post queue and we'll go figure out what blog post i'm going to write it is 6 42 a.m here in san diego you've got me until 8am so you've got me for about another hour and 15 minutes because my coffee shop downstairs opens at eight o'clock and i'm gonna go down there especially since i ran out of decaf espresso go down there and get myself my regular bagel and uh coffee yes i cough because i have asthma don't worry about me catching uh catching corona i you should worry about me catching corona lord knows i worry about it we're totally paranoid in this house i am masked up like nobody's business i wear gloves when i leave the house even for anything i am super paranoid about that kind of thing because i do have asthma so it makes me more vulnerable should i heaven for forbid catch uh covet 19. jakel what is the purpose the flavor because i've already had by this point in the day and i wake up this energetic strangely um but i've already had four four shots of espresso so it doesn't make sense for me to continue to get more of that but it does make sense to continue to drink something uh tasty and i would drink like espresso all day continuously if it didn't uh continuously just tear your stomach up and make you a you know angry dinosaur i used to when i telecommuted out of houston i used to work live in houston commuted or worked for a company in dallas but i lived in houston and i telecommuted i would work out a starbucks every morning from like 6 00 a.m till noon and i would drink four quad venti americanos and they called the mocha valencias so they had like six shots of syrup in each one oh my god between the espresso and the sugar it's just unreal and then one day out of nowhere i was like i'm really angry by like 11 or 12 in the morning i'm like not a happy camper and i'm not friendly to be around which is really weird because i'm a super laid back guy i'm like as mellow i'm like the jimmy buffett of sql server totally mellow laid back anything goes uh but then you get enough caffeine in me and i was like i was a real jerk around 11 a.m 12 a.m and all of a sudden i understood that i might have a problem with caffeine consumption at that point holy moly something must have been broken around the following piece because there's all kinds of stuff coming in through there all right so we'll take a five minute bio break and then we'll come back in here and talk about blog posts and open q a so we will be right back [Music] [Music] [Applause] [Applause] [Music] [Music] so [Music] then [Music] um [Applause] [Music] [Applause] [Music] [Music] [Music] all right welcome back so a quick shout out to for redgate for sponsoring this week redgate introduced the 2020 state of the database monitoring industry report you can go download that totally for free over at brentozar.com go slash read it where they talk about what kinds of database platforms people are using what their administrators face in terms of challenges and how they're monitoring to make sure those problems don't strike and like cause all kinds of ugly problems uh darren davis says thanks bro hybrid i'm enjoying your content great thanks glad you like it i appreciate that and then right as i was leaving a whole bunch of uh of uh following alerts came through from bots and i was like i'm watching i'm like oh damn something's gone terribly haywire so i just turned off notifications for new follows in here so that'll just make that go away they could they could even still be happening who knows uh david asked a question recreating the new clustered index of a 50 gig table when i'm recreating a clustered index of a table what does the sql server need an extra 50 gig of data space or 50 gigs of space in both the data files and log files one of the best pieces of advice that i ever got was from paul randle paul randall's an icon in the sql server community paul used to work for microsoft on the storage engine team it was i don't know if he started as a developer but worked his way as a developer up into management one of the nicest smartest people you will ever meet and paul gave me a great piece of advice paul said i don't know if it was me or just that i heard him giving it to somebody else but paul said when you ask a question the first thing that you want to think about is could i reproduce that myself and find out for sure like how hard would it be for me to reproduce it because you're better off doing a demo than you are asking the question and it's nothing against that word i'm going to do the demo to show you but i off the top of my head just whenever i get questions like that my first thought is can i build a demo to prove it and what would that look like well so to prove it i'm going to need a table that's fairly large and i'm going to need no or not much extra space inside the database and not any extra space inside the log and then let's go see what happens so let's use the stack overflow database to go find this out so in here i usually keep three different sizes of the stack overflow database on my server i keep the full-blown stack overflows like 350 gigs 2010 is just like a 10 gig database 2013 is like a 50 gig database i'm going to see just how much empty space i have left in this database i think it it's probably got a lot of empty space so it's probably not going to be a very good example for this demo so in here yeah see he's got like 30 gigs of space available so it doesn't really make sense to use this database i could restore it from scratch it would just take some time for me to go do that but i'm not going to bother playing around with that so instead what i'm going to do is let's go look at the 2013 database and let's see how much space he has and he's got four gigs left let me try restoring him because he's small enough that i can restore pretty quickly and even if there is space left what i'll just go do is i will go create an index in there that'll burn up the rest of the empty space so what you're going to want me to do is you're going to want to drop the clustered index on a table and then recreate it so let's just start typing while this thing goes and uh does the restore and the restore is probably going to take about 60 seconds i'm going to type out the kind of project plan for this so as a reminder is the question is if we redo a clustered index and you specifically said let me go back and make sure that i got it right you're say recreating a new clustered index so i'm going to say if we need to create a new different clustered index on a 50 gigabyte table do we need 50 gigabytes space in the data file do we need 50 gigabyte space in the log file all right so let's let's find it out let's see if the uh the restore is finished nope okay so the first thing to find it out the first thing i'm gonna do is i'm going to uh pick the table uh then two i'm going to go create a new clustered index and this is going to be a little tricky because everything in the stack overflow database oh i know exactly what i'm going to do i'm not even going to pick a table i'm going to create a new table i'm going to say hi sharon create a new table with a clustered index and then create a different clustered index for it oh that's that's that's going to be even more elegant oh i love where this is going okay so my restore has finished i'm going to go take an existing table and let's let's find out what are the biggest tables inside this database um and i'm not looking at how much empty space is in there you're about to see why here in a couple of minutes so to figure out what the biggest objects are we're going to say sp blitz index mode equals two uh yeah darren davis you're a little late we talked about that in the beginning of the webcast actually it's not your fault not everyone can show up to work on time some of us are real busy of course i'm giving you a hard time it's the weekend there's there's no such thing as being on time uh so if we scroll so sp let's index mode equals two what this does is it gives me an inventory of all of the objects in the database so i'm going to pick one that's large i don't have one i'm sure that's exactly 50 gigs i'm just going to choose one of the larger ones that's inside here i'm going to scroll across over to the size columns so that i can see because of course for this question size matters oh it's so terrible so i'm going to look for one that's fairly large so up top we got a 20 gig one there i think i'm gonna go with the 20 gig one up there so we've got a 20 gig one up there let's go see what he is so that's the comments table let's go see the comments table then um so let's say select into mobile do a create table let's go look at the structure of the comments table so the structure of the comments table is um it's got an id oh it's beautiful it's nice and short so we'll say create table dbo comments for who asked the question let's see it was david comments for david and this is going to have an id int creation date date time post id int score int text and varicare 700 user id int then we're going to put a clustered index on it i'm not going to do anything like unique or primary key for the purposes of this demo i'm going to say create clustered index cl creation date on dbo comments for david p that's gonna get old after a while uh creation date so we're gonna cluster the table on creation date then we're gonna go insert all the rows into it insert into dbo comments for david p the id creation date post id score text and user id select get all those columns out copy paste from dbo comments so let's go load that up that's going to take it's going to take a little while because it's going to insert 20 gigs worth of data so while it does i'm just going to keep typing what the rest of my comments are going to be so the next thing that i got to do is i got to go recreate the clustered index i don't remember off the top of my head if i can create with replace existing i'm not exactly sure if that works on clustered indexes we will find out so we'll say create oh he's gonna choke and download speed there clustered index cl creation date on dbo comments for whoops uh for david p id uh with drop existing i forget what the name of that is drop existing equals on um and let's see oh santee oh that's funny oh santy good catch santi is one of the very sharp people around here santi i need to make sure to have like a special filter for you when you talk because i need to pay closer attention santi always asks good questions so let's go look at sp blitz index mode 2 on the 2013 database uh just to see so on the 2013 database you know what i'm going to go ahead and use comments even though it's only eight gigs because it'll still teach us this story so let's go create the table in here and then let's go insert and actually i'm going to change my clustered index name just a little bit drop table dbo comments for david p i'm going to drop this just to make it a little bit more intuitive cl and then there we go execute do the insert and then while that runs we're going to go do this i don't know if it's with drop existing equals on i bet santi knows because santi is pretty smart like that um i always kind of like to challenge myself to see if i can get something to work without or see if i can get the syntax right without looking at the documentation i love looking at the documentation but just in case so let's see when that finishes then the next thing that we're going to go do is create the clustered index before i do that i'm going to want to look at the database so look at free space in data file log file then look again oh so i really need to look at several things what do i need to look at here i need to look at at uh data and log file size and free space across both of those so let's copy those and we'll look again at data and log file size and free space so how do we go look at the file sizes if i remember right there's an sp space used i can't remember if space used i can't remember if that shows both the file size and the free space let's go find out and of course my sql server is going to be uh chugging along super slowly because it's trying to do this big huge insert at the same time that i'm doing streaming and all these other things so and that's not quite so good that only gives me the database size and unallocated space i really want to see on a file by file basis now i could use the shrink file ui but i'm going to try something else instead let's go google so let's say dimitri says why not use the data usage from reports dimitri i always forget about that that's not a bad solution at all so i can come over here close this guy and no i can i hope i didn't leave this thing in a 16 core vm because if i have a 16 core vm that's going to cause me problems i'm going into task manager just because he's so spectacularly sluggish that i'm wondering if i don't have another problem oh yeah i did i left this is a big monster 16 core vm and so i know i'm gonna have cpu problems when i go and do big queries like this i'm running this on a pretty beefy mac pro desktop i forget if i have 24 cores or 32 cores or what this thing is but when vmware builds a really wide vm vmware fusion builds a really wide vm you have cpu usage problems um so it's because on a table this size it should have been done long ago it should just it shouldn't be that big of a deal um so let's see here so we're going to look at reports because dimitri pointed that out let's go look over at reports and then standard reports and disk it's not that disk usage uh yeah exactly dimitri every now and then it kind of backfires usually i just leave it as a four core vm but i totally forgot before i did this webcast to go back and change it um disk space used by data files that's pretty good i'll take that um so this oh auto growths are also going to cause me problems well it'll be good enough to shoot it doesn't show the log files all it shows is the it does show the log space usage up there which is kind of cool it only shows the percent though it doesn't show the size that's good it's just not quite good enough um okay so let's change it then let's go run a query and let's google for uh sql server file size empty space query and i i always have a guess that usually penal is going to fi file up let's see if pinal has a good one here how much free space do i have in my database don't worry pinal it's not your website that's responding slowly it's my poor vm that's just absolutely getting murdered um i love it yes pinal's absolutely perfect here that that works beautifully so let's copy the code from pinal like we all do from production uh i'm at you that's cool thanks greetings from turkey uh you're not even the first person from turkey there have been one person already and thank you and all for uh doing that so here this gives me um this is one of the things that i really love about pinal's blog is when i'm looking for code that i need to copy paste in quickly and find the problem i usually go directly to sql anytime i see sql authority in the google results that's the first place i jump into because i know it's going to work just like here it did so here it gives me uh for each of these what my total data file size is and how much space is used across each of these that's exactly what i want perfect so when this d weeby says this stream is entertaining thank you ah boop hill uh good i'm booping boopin good to see you in the club thank you appreciate it [Applause] so now david's original question was oh good so my table finished growing so my my uh my for the plot so david's question was when i go and create it am i going to need the space both in the data files and the log files well right now let's go back to pinellas query and go look right now and i'm going to rerun it because the transaction finished right now i can't run the test yet as is the test would work beautifully on the data files because i don't have much space left but it wouldn't work as well in the log file because the log file already grew so to fix that i'm going to shrink the log file down as tiny as i can i don't usually do this but just to answer david's question i find it interesting so let's go shrank say shrink files and let's try to shrink the log file down as much as it'll go i'm going to script this just because i may need to execute it a full a few times and then let's go back and run penals query and look at that perfect now my log file is down to just 250 megabytes so now we're all set for the exercise now what's going to happen well first off i don't even know that my query is going to run the way that we just wrote it we're going to find out the hard way so we're going to go run this and see if it works i don't even know if it's going to work let's go execute it it's working yes it's working oh yes that's fantastic so now the question is going to become when this finishes how much larger did each of the files grow now what i need to do is i need to save these results because these are the before results so i'm going to hit save and i'm going to call this the before window then i'm going to copy all of this out paste it in a new window and we'll put this over in a new vertical tab group so that we can call this after so the left hand side is the before right hand side is the after now i always like to guess before i hit execute on things oh sharon says can you show the locks it's going to be an exclusive table level lock because you i didn't do it with online equals on or anything like that so i always like to guess before i hit execute what's about to happen now in the data file i have to grow it because if i'm going to build a brand new copy of a say eight gig table i have to read from the old one and write to the new one at exactly the same time there's just no work around for that that's the way that has to happen there can be edge case shortcuts if there's uh off-road stuff that doesn't need to move off-road data sql server may not pick it up and move it somewhere else i don't know it's been forever since i've done that i don't think the the comments table has that much stuff off row so i don't think it's not going to be impacted in the log file but i don't actually know we'll go find out but then after we do it i'm going to show you something else a new feature in sql server 2019 accelerated database recovery and how that may perform differently but let's go see so if we come back over here over on our initial query our one that recreated the clustered index that has finished so now is the moment where we find out right inside run it and well it's kind of hit or miss here so now we have eight gigs nine gigs worth of free space in the data files the data files have had to grow you see that before that they were 14 gigs before now they're up around 16 gigs you're gonna need double the size you're gonna need the extra size of the object over here the log file is not that bad of a story the log file we had 250 megabytes before we only needed an extra gig in order to do it so that's not quite so bad i'll take it so there you go if you're doing a large object in production like a pradeep just asked about a terabyte database for example if you're doing a large object like that in production one of the best ways to go see it is to go do a staging copy of the database on another server and both time it and look at things like space utilization so now we have our answer but i said i wanted to look at uh zeus says did it use tempdb to shuffle the data it didn't because i didn't ask it to but you can use sort in tempdb as well sort intemptibia is a switch that you can add david says thanks you're welcome now there's another feature in 2019 that affects this mix as well what i'm going to do is i'm going to go blow away that log file again did i save that i sure did let's try shrinking the log file down and let's recheck our space usage so now our log files back down to 250 megabytes i'm going to do this same process again but this time before i do it i'm going to go into i don't think i can hit it via right click i don't think i can hit it via right click so the new thing accelerated database recovery let's go see sql server enable accelerated database recovery there's going to be an alter database statement script and i don't know off the top of my head what the exact syntax is and because and of course microsoft doesn't remember how a minute ago i said that i always go to penal site first that i always click on the sql authority links this is why i always click on the sql authority links because even in the official documentation even on the big main page for one particular version they don't always give you the scripts so in here i'm going to try clicking on the management link just to see if i get lucky and yes okay so here we go so we'll copy this out and paste it in it all says today i'm so happy me too sir uh alter database current which i love that shortcut and i wish more people knew about that we're going to turn that on for sql server 2019 and good morning jitendra good to see you again so let's turn on accelerated database recovery i wonder if i have to be the only user in the database i probably do let's switch out our other databases microsoft quality is job one [Music] takes freaking forever [Music] come on come on oh you suck so bad i'm gonna have to use a use command god microsoft i swear oh finally perfect timing okay now after getting everybody else out of the database i'm able to change it to accelerated database recovery is on okay so good um zeus you have great uh memory i don't even remember doing that so now we've got it over in accelerated database recovery on let's go back over into our demo pop it back over into the 2013 database and then now let's go recreate our welcome to the club nittan thank you i appreciate it um so now let's go change it again this time exactly exactly uh j cole you're exactly correct we're going to re-cluster it on creation date so then let's recreate it and what i'm looking for after i recreate it now so this new feature accelerated database recovery what this does is it keeps versions of your rows inside the user database not necessarily the transaction log you'll notice i'm being a little vague here because i haven't done that much research into it other than to just run a few quick demos and go yeah okay it works so what it's supposed to do is it's supposed to keep versions of your rows inside the user database not in the transaction log so that did i get that right no i i said that incorrectly i said not inside the transaction log i don't know that it may fully still log the transactions i think it probably does fully log the transactions inside the transaction log it's just that the transaction log space is available for reuse as soon as your transaction even before your transaction is committed because sql server can use the copies of your rows over in the user database rather than having to pull them from the transaction log so what we're about to see is does that impact the size of the user databases and does it impact the size of the transaction log so here's where it was before we did our rebuilds it was we were at like 250 megs now let's go over and do the new version let's switch over to 2013 and see the new version now well it's look it's not good it's a little bit of a hit or miss story so in here now our transaction logs still grew by like a gig strangely there's 155 megs still used i don't understand why there would still be space used because i don't have an open transaction whereas before we had no space used we'd shrank it down to 250 megs but did our data files grow they did not okay so that's kind of cool so i guess in this case it isn't really going to help you that much ah let's see here we had what other questions came in during the um santee said so should the same also be happening for non-clustered indexes true yes how could we not use the temp db for creating the same well the problem is is you have to have still both the old and new versions of the object in at all times so you can use sort in tempdb it's going to reduce some of the space required but you still need both copies of the objects uh pradeep says what is the best way to shrink the database in production if the size is more than a terabyte go read my blog because just this week literally just this week i published a blog post about that exact topic if you go to brentozar.com and click blog up at the top scroll down there's a post this week about shrinking multi-terabyte databases so all right uh coming back up let me see if there were other ones because y'all asked a couple other questions along the way pradeep asks hi brent regarding parallelism does cost threshold of five only refer to cpu cost i don't know off the top of my head i've never heard that before i don't think it's possible um i i don't know off the top of my head the thing that i would say is if you have a sub tree that has a cost that's large enough to go parallel i wouldn't be as concerned about cpu versus io it's just the big picture of do you want that to go parallel or not like i wouldn't try to micromanage that let's see here is there anything else that i missed in terms of questions i think that's it in terms of the questions okay perfect oh moshiko asks do you use c-sharp with sql i do not i know that you can use c-sharp extended stored procedures inside user databases to me that's just an incredibly in unbelievably stupid idea because sql server costs two thousand dollars a core for standard edition it costs seven thousand dollars a core for enterprise edition application servers which is where most people run c sharp cost let me carry the one let me carry the other one oh that's right zero application servers cost zero dollars per core so if you're going to write c sharp code i think you should put it in the place where it costs zero dollars per core that's just kind of the way that i do it um says what a nice view in the back yes that is downtown san diego too it's very nice uh this morning the camera's white balance is a little weird let me play with that here for just a second because i have to make adjustments in between uh day time and night time that the balance gets a little different um david says so when dropping a 50 gig clustered index making the table into a heap do you still need the extra space in the data file that's a great question i'm not going to redo that demo just because i know that i'm not going to do that demo just because i know that with this thing being at 16 cores my performance is going to be pretty horrific to go through and do it but you would that same exercise you just saw me use that's the exercise that you would go and work walk through okay so we've been going for about 30 minutes uh let's go shift that's probably good for the open questions and answers let's go ship oh um uh oh that's interesting do sleeping sessions reserve or hold worker threads no dimitri have neil ask his question again then because i didn't see it up in the in the restream notes as well i'll give you i'll give neil a second to re-ask his question if the question has things like links i may not see it you want to keep it as short and clear as possible don't like put t sql code in there stuff like that ha knew how to transfer sql jobs to a new server use dba tools dba tools so if you google for dba tools transfer jobs there's a whole set of powershell scripts to help you do it neil says what's better in performance uh table one union index and so general questions like that you never want to say um with neil's question you never want to say which of these two design patterns is better for queries because it'll change so much depending on your exact query i'll tell you a great story someday i'll do a webcast around when someone said which one of these two is better and i said you can't tell and i had to write two different demos to show when each one of the two was better so rather than saying which pattern should i always use what you're better off learning is how do i measure a query and the common ways that i'll measure a query i'm going to go type it out so the common ways that i'll measure a query when i'm trying to decide what's better just to pop out here how to measure a query so set statistics io and time on tells you uh logical reads and cpu time not really duration those are the most common ways that i'll go and measure a query and if you want to watch me doing it go to brentozar.com go slash tune queries and there's a totally free webcast there's like two webcasts there where you can watch me comparing two queries against the other so you can see how i measure whether something's getting better or worse uh mashiko asks does sql server work smoothly on a mac you can technically run it in a docker container i've had really bad experience with that it's not sql server's fault just docker on a mac has been just an absolute hot mess for me so i don't do that i tend to use a windows vm like i'm doing here i'm actually running um uh i have to learn from you on how to yeah use watch those webcasts a life extinguisher says is it safe to fail over availability groups using powershell so let me zoom back a little and i'm going to say is it safe to fail over availability groups at all like when can you tell if it's safe the weather and whether i'm using the gui whether i'm using availability group manager inside a management studio whether i'm using t sql how do i know whether or not it's safe to fail over the first thing that i want to know is is the target server ready like before i go do a failover i want to know that the other server is actually in a safe place where i could actually fail things over to it because sometimes it's broken sometimes it's broken and people didn't even know there's a memory problem it hasn't allocated any memory etc second thing that i want to ask is is there anything happening on the current running primary that i need to wait to finish has someone for example started a big ugly transaction that's going to take forever to roll back is there a backup running is there a check db running is there a data warehouse job that's running etc so those are the first two things that i'll look at is the target server ready and is there anything that i need to wait for now that i've said that now you kind of understand why it doesn't have anything to do what tool you use it has to do with you being the tool you've got to go check those things first whether you're using t sql powershell the gui or anything like that nothing like that is built into any of those tools it's up to you as a database administrator to go check those kinds of things that's also why you don't see a lot of database administrators using automatic patching like any kind of system automated patching that will automatically patch servers when they're in a cluster and availability group because you want to make sure that there's not a backup running while somebody's trying to do a patch rolling that stuff back can be terrible all right there we go uh good so we've hit uh zeus says this 2019 is still coming out with cus for corruption yeah out of f out of the last five cumula of all five cumulative updates so far for sql server 2019 three of them have fixed corruption bugs so i think i might want to see and i've just got literally just got done advising a client yesterday no two days ago two days ago that they should stay on 2016 rather than move to 2019 yet just because i love 2019 a lot of cool features but there have been a lot of corruption bugs and i'm like i'm not so sure about that cgs are you confident in cu5 yet i'm going to take a step back and i'm going to say something that's going to piss off my friends at microsoft i'm not happy with 2019 i'm actually very unhappy i am very unhappy with the code quality in 2019. this the cus have had two problems the cus aren't documenting clearly the problems that they fix and they've even put out cus with one line fixes and then removed the fix they've said i'm sorry you know like just totally vanished that fix from the cumulative update i have less confidence in 2019 i think than i've had in any other version so far now i don't have anybody publicly who's my client right now who can talk to the code problems they've had with 2019 but i will refer you to stack overflow at stack overflow they went to 2019 early on and it has been horrific in terms of code quality like if you ask their database administrator taran pratt on twitter she's taran pivots on twitter she'll she goes on every time they run into problems with sql server 2019 they can't still even enable scalar function inlining because it's having so many problems jack glad you like it um so i've been very very disappointed with 2019's code quality so far i have hopes that it's going to get better 2019 is the coolest release in a long time there's a lot of cool things that they've implemented but the code quality is the thing i'm a little nervous about um yeah bobby failover talked about his powershell scripts um code quality in the covid area ill will safe walking your job um pradeep i'm not wild about doing it as scripting because usually when you're talking about moving jobs from one place to another you're talking about prepping a failover uh process where you're gonna migrate from one server to another and you want to catch all the jobs at the last minute and you have so many other things going on powershell just makes that way easier jack says what about tempdb fixes in or tempdb contention in 2019 i haven't seen that as an issue um i wouldn't be surprised just because they made those huge changes to in-memory metadata for temptb in 2019 so it's possible i just wouldn't be able to speak to it i know that the the tempdb metadata changes have had a corruption issue in those first five ceus so i would just be careful with that okay i think we've caught up there i think we're good there i think we're good on the questions uh you're welcome neil absolutely all right so now let's we've only got 30 minutes left and i'm probably not going to be able to uh i'm probably not going to be able to do a blog post so i'll just keep going with open questions until you all run out i'll stop here and bdg i'll hit your question in a second i'm going to do a quick break and shout out to the redgate team so redgate sponsored the webcast this week and they just released their 2020 state of database monitoring report you can go read that totally for free over at brentozar.com go slash read it and it tells you what kinds of databases people are monitoring the kinds of problems that they're running into and how what they look for when they're monitoring those problems so bdgs how about sql server and windows containers i don't see the problem that it's solving i don't understand what the problem is that you go oh the answer is to put it in a container i just don't see that problem for production servers i totally get it for dev servers dev servers is another thing altogether but for dev servers you wouldn't run windows containers why pay the windows licensing costs just go run linux containers if you want to run sql server in a container for things like devops continuous integration etc but i've never seen a the problem where i go oh the answer to this is to get containers and windows involved it just did i don't add that up if you think you have a problem where you go the answer is when can sql server containers in windows containers then by all means put that inside there um the uh next question was uh masimasimas says uh uh that's what he knows uh call him or ki or her what they what mazi massimo says should i migrate to 2019 or wait a few months to go to 2019. what i would ask for is what's the problem you're trying to solve with the new version like how urgent is it that you need a new version in place and generally speaking if you could hold pat for another four five six months i would probably do that before going to 2019. if you had a gun to your head and had to go live on a brand new infrastructure now like you've bought the servers already you're ready to go and you're trying to choose a version i would take 2017 all day before i would take 2019 right now um zeus says could we just switch back compat level to 2016. no unfortunately like we talked about those five things inside the or five out of the five cus three of them had corruption fixes you may not be able to choose which storage engine is writing your pages so to speak neil no that's a great question but no do i recommend transactional column store indexes columns or indexes and transactional systems no and the the place that i'll go i'm going to show you a webcast you should watch if you search for um uh nico nuge bauer column store uh youtube you're going to find a bunch of uh webcasts that you can go watch so search for nico nugebauer comstor youtube and there are a bunch of webcasts you can go watch where nico's like the world most uh form most well-known column store expert talks about where it makes sense and where it doesn't make sense also worst practices for column store indexes and to kind of tip you off there one of the worst cases is transactional systems uh mark no unfortunately you can't migrate a database backwards you have to pull all the data out and then insert it over into the older version david you're welcome my pleasure uh let's see here was there and oh moshiko asked do you do any other coding stuff but uh but sql server like web development or app development so i used to i was how i got my career uh start in well so i originally got my career started back in hotels worked in the hotel industry like front desk night audit managing hotels and then switched trends switched over to uh development developing apps in the hotel business and just generally like big picture really big picture in terms of life goals i decided i want to make a lot of money i know don't judge me i decided i want to make a lot of money and in order to make more money i don't know a lot of developers who make a lot of money they do okay they do not bad but if if you want a million dollars as quickly as possible development is probably not the answer unless you're going to work for yourself and you're going to build your own app that's a little different but it's also one hell of a gamble development you can work the rest of your life with a secure salary as long as there are you know keyboards that take input you will find work as a developer the world will always need developers it's an amazing job it's like set for life but if you want to get to the point where you're making a million dollars a year development is a tough road to hoe to do development you'd have to work for somebody like facebook google and you got to have really high-end development skills in the database industry it's more common it's not super common but it can be done and so i just decided that i wanted to go down the database realm so i left the the whole web development and app development world behind there are a lot of things that i miss about it i miss being able to build something with my own hands and have users run it database people we don't get that experience we're just empowering other developers uh sanjay asked hi brent how can we calculate the baseline performance of tempdb and sql servers any proposed tools that you can propose well sure there is a company that happens to make a tool like that and if you go over to brentosar.com go slash read it you can read redgate's study on the sounds like i planted this question you can read red gates like survey of the entire database performance monitoring industry and you can see what kinds of tools people use and that'll give you a good head start on seeing what the big tools are that are out there so those are that's exactly where i would go uh bobby tables uh may i ask for a quick favor how do i quickly find unused indexes and the worst performing indexes oh sure okay so yeah going back and says you're going to reply to fake questions uh yes over there in chat phyllis asked how are you so handsome um okay so for how you find indexes that aren't being used so if you go to sp blitz index mode equals two get all databases equals one what this will do is it'll give you an inventory of all the indexes across all of your databases in a format that's really friendly to copy paste into excel and when you copy paste it into excel you can sort by ta-dah reads so there's a total reads column you can sort by reads you can filter for only objects that have zero reads but do have rights so this is a really easy way to go through and grab that as people it's index mode equals two get all databases equals one another thing that i love doing with this is i also love to go over to the rows and reserved megabytes column and i'll sort by that as well because often folks have no idea what their largest objects are how many rows they have and how much size they have and often it's things like caching tables that people didn't even realize are old archive type tables i wish that i could just click on this thing that says rose or click on this thing that says reserved megabytes you would think year 2020 that i would be able to sort by that but evidently that's a little too much to ask for in the database product so i have to copy paste it into excel i love excel excel's just magical it's just utterly fantastic i can't get enough of that um little bobby table says so tables with primary keys with zero reads how do i tell the bi guys um in terms of how do i tell here's my favorite thing for that if you search for brent ozar consulting lines garam masala the answer is yes absolutely you can if you search for brent ozar consulting lines i've written a whole series of blog posts and this was like five ten years ago about lines that i say all the time as a consultant in like how you deliver good advice to people how you deliver bad advice to people all that kind of thing so if you want to know how you tell them this is how you tell them i just there's so many lines in there that i just absolutely love uh all right so there we go so let's see if any other questions come in otherwise i will go hop downstairs to my uh my uh office that they so are none are rude that's it depends on how you interpret rude it's uh the people i try to be as politically correct as possible cgss is erica technical or computer person also not even a little bit not at all she's insanely smart she's one of the smartest people that i've ever met and she'll figure things out really easily but she doesn't want to be a computer person or a technical person at all when i met erica it was at a company that i went to work for she was the receptionist and i've told that story on another stream about how the whole company would go out and party together um but uh she was the receptionist and then we ended up getting together she has been worked for an airline as a crew scheduler and then worked her way up into the faa she worked for the federal airline administration i think it's called or federal aviation administration as an air traffic controller worked as a manager or office manager for a medical malpractice law firm and then eventually when our company became large enough i was like okay can you come work for us and be our office manager so she manages things like our contracts insurance invoicing but it's just nice because we both live together we've lived together since what 2000 so we've lived together for like 20 years now we live together and work together out of the same apartment we've been together doing that for like maybe i don't know five ten years now and it's it's really i know erica comes with me obviously erica was the one that got we decided to go to iceland it was the whole iceland thing was really funny uh erica says one day she's like i'd like to go to iceland i was like well that's odd like i i don't know that you know i've never really thought about iceland but okay and she goes uh she goes we should check it out i'm gonna move this a little so that my white balancing gets a little better or my lighting it's a little better she said we should check it out and so we're watching like national geographic traveler specials on iceland and i'm like oh this is interesting you know we'll have to check it out at some point that weekend that weekend i get an email from sql saturday iceland and they're like hey would you like to come speak with us i am a really big believer in fate like when things happen just at odd coincident times i'm a huge believer in fate and so when that happened i was like we're going to iceland that's it you know these these things all happen in the same time together we are absolutely going to iceland and dev uh debonair sage says why iceland it's gorgeous oh thanks mom cynthia hines is my mom everyone cynthia hines that's my mom my mom who loves to tell everyone that she taught me how to read which is true mom was i should say especially because she's in here mom uh was a stay-at-home mom who took very good care of me and my sister uh didn't do homeschooling we went out to schools but got us really started in the whole love of reading and learning type thing uh and made really good granola as long as i'm embarrassing mom i should just go on and on right um so let's see here uh fate aka mike was that a google app yeah right exactly uh and uh dimitri says you you live a dream it does feel like i'm living a dream it's pretty awesome right now we're doing well uh touch uh touchwood um so but why iceland um another one of you asks if you're welcome mom thank you mom um why iceland it's gorgeous it's just jaw-droppingly gorgeous when this webcasts finish go to youtube and search for iceland 4k and there are tons of uh films on iceland people shot with yesla that's right i remember that that's funny um the and you had the little story of when we were building granola it was like a whole pile of hills it was like building the oats and all that kind of thing building up our hills ah little bobby tables thank you good welcome to the club mom does have stories to tell yes absolutely i'm trying to think of what would be the funniest story uh that mom could tell at some point i'm going to bust out all my like high school and college photos i used to uh have oh that's okay little bobby tables you can unsubscribe and re-subscribe again i'll thank you every time that's totally okay i used to have like a shoulder length hair i used to do i was i thought in my mind that i was going for kurt cobain in reality i ended up looking like a mushroom um i don't know what else uh is uh uh uh would be too interesting on sure it's always tough to think of things right away um but okay so let's see here that's probably good i think i've caught up everything on the questions all right well i will go place my order with my coffee shop downstairs they take actually i'll go do that now in case uh any of you all come up with questions let's go place my orders uh let's see here i will go get and you can't i guess you no i don't want to show you my order because i think it's going to pop my credit card confirmation up here at some point uh square and achilles oh no sanjay absolutely not and i'll tell you why i'll tell you why in a second angie let me put my order in and then i'll tell you why i would never make it paid so achilles coffee roasters and we'll get a large pour over we will get decaf because lord knows i've had enough espresso already this morning and then let's go get our bagel and locks there is that sandwich and add i want an everything bagel and i want heaven forbid anyone come in on the stream at this moment uh heirloom tomato and cucumber i will get two of these because my wife will want one as well and then change the tip i'm a huge believer in huge tipping right now restaurant staff are basically first responders so i've been tipping just absurd amounts of money there we go and place my order and just so that that way they start working on it when the place opens at eight o'clock and there we go okay cool so back over um sanjay asked any plans to make the first responder get paid in the future never i it'll always be open source 100 open source and it's funny if you're going to uh if you're going to build something and you want lots of people to use it the first ugliest barrier to adoption is making them pay if you want to make them pay all of a sudden you have a long road uphill if you're going to build tools like the first responder kit just give them away same thing with dba tools like the powershell stuff if you give it away even when you give it away people won't use it it's really hard to get people to adopt something to get them to use something but one of the cool things about scripts stuff like the first responder kit dba tools etc is that they really serve as advertising for your brand if you give them away free tools like imagine carpenters don't have the ability to give away free hammers and saws but imagine that they did imagine that there was a carpenter shop in your city where you could go and get a free hammer and a free saw any time you wanted would that make you a good carpenter no the part that's hard about building stuff isn't the tools any can go down to the hardware shop go buy a hammer and go buy a saw but if you look at the furniture they build they would look like just butt wind i mean they look absolutely terrible but if there was a carpenter shop that gave away those tools for free maybe they were lending them like a library or maybe they just gave them to you permanently you would remember that's where i go get the hammer and saw and after you build enough crappy stuff you'd go you know what that those people they also sell carpentry services maybe i should talk to them and that's exactly how the first responder kit works i get a ton of consulting revenue based off of the first responder toolkit where people run it and then they go go oh my god i'm overwhelmed with the contents of this i should go call someone why don't i go call the person who makes those tools and same thing with training with training as well businesses often go our our staff have been using your stuff for so long we should just hire you in for training so i will never make the first responder kit paid everything that i do that's a tool i'm going to give you you're free to use it and you know what's really crazy so you know sql constant care i'm going to tell you something really bizarre sql constant care is our kind of paid monitoring tool i want to get to a point where that's free i want to get to a point where sql constant care you install it it starts sending you advice via email i don't even want to charge for that because i can run and host it inexpensively enough that adding on additional servers doesn't really cost me that much i want to be able to have a free tier where y'all can go in and install this so that it alerts you when you don't have backups when you need a patch and all that kind of thing so that i want to get to the point where we're there we're still probably a year to two years away from that but that gives you an idea of how aggressive i am with the free stuff the more that i can get the brand out there and get people using the tools the better uh curtis brown asked are you and curtis it's always good to see you it's always great to see you um curtis says are you taking your car to iceland you know it's really funny um so iceland if you're only going over there short term if you're only going over there and i want to say it's three months i can't remember if it was three months or two months but if you're going over three months or less you can take a car over and keep your plates like i could keep my california plates on the car and you don't have to pay a registration but you got to pull the car back within three months the cost of bringing the car over is so high and the fact that we're going over in the winter where it's not really that fun to have a porsche 911 over there in the winter anyway because it's really winter over there we're not gonna do it but what if we live there longer iceland doesn't let you work remotely long term like you can you can show up for three months on a tourist visa but you can't work for anybody while you're there we would love to stay for a longer period of time we would love to stay for six months 12 months uh and see it out we love that country what's just totally then i would love to take the car in because it's a beautiful place to drive stuff like porsche's in the summer but iceland charges you like 70 of the cars cost to bring it into the country 70 of a porsche 911 oh my god no i mean like i could buy a porsche boxster or a cayman in iceland for the cost to bring a helmet over so unfortunately no um bobby table says why 911 and not a tie can because i like a convertible then they don't make taikan uh or taken convertibles yep oh let's see here what was there other uh things that were inside there uh neil says do you give a coupon every time no no just somebody asked for it i want to say it was uh was eamon or uh forget who it was that asked for it but one person asked for it so i gave one away uh and that was it i think oh inca says do you still have a puppy no ernie passed away two years ago i think uh passed away two years ago and we didn't get another dog because we loved to travel two reasons one we really love to travel and having a pet makes it harder to travel because a lot of hotels don't take dogs for example the other reason we i'd love puppies but the thought of toilet train or um potty training a dog i don't know that's a tough one because we live in high rise we really like high rises and uh it would be really oh no that's okay inca it's totally all right it's not not a problem at all um but uh yeah we ah the thought of tr potty training a dog in this day and age is really tough and a high rise we get poop all over the kitchen and we're cgs we still i still have not seen hamilton i am like i don't know how many people in the united states or world or whatever uh haven't uh seen hamilton yet i still have not seen hamilton yet and one of these days i need to having the sub isn't the problem we actually have the subscription i just haven't gotten around to watching it yet because there's so much good tv on right now and i have the worst taste in television well last night we watched alone which is one of my favorite tv shows about us being stranded out in the arctic i we really like reality tv shows and it's a reality show where a dozen wilderness experts are stranded alone out in the arctic and they move the location around from time to time stranded alone and if you can survive for a hundred days you get a million dollars you have no food you have to go figure everything out on your own amazing show so caught up on that uh and um holy moly holy moly the mini golf special and and i don't watch hamilton i know right um says how old are you uh in my 40s i'm in my 40s um rengar rangarajan says hi brent what's your advice on max memory or where should we set the max memory go to brynozar.com and download my first responder kit and i have a setup guide in there that explains exactly how to set max memory so that way you can it'll tell you all kinds of other things too like how to configure tempdb and all of that uh and well mom loves alone too yeah it's especially oh this this week's episode was another uh great one not enough people i'm surprised more people haven't tapped out yet that they're still had like seven people on in last uh two days ago's episode that was pretty crazy uh raldo says is weird i've used your tools before and just now finding you on twitch i've only been on twitch i don't know what do y'all think it's been it was like january or february i think that i got on here uh so it hasn't been that terribly long so i really started because of the quarantines when we came back i was in iceland erica and i were in iceland on vacation we came back when the quarantines hit and i'm like well if i'm going to be here you know at home for several months i'm going to learn to stream because i when twitch came out i'm like who would ever want to watch video games you're going to watch someone else play a video game and then i watch someone else play grand theft auto and i was like i'm in i'm in 100 because i love grand theft auto but i suck at grand theft auto so i love watching people who are good at something actually go do it j cole asks a good question is this every saturday when i'm able to stream i have a blog post going out monday where it's actually going to be starting every week saturday and sunday every week saturday and sunday i'm going to stream from 6 a.m to 8 a.m pacific my time the two hours before my coffee shop opens so i'm going to do that saturdays and sundays 6 a.m to 8 a.m pacific i'll have an ical in the uh blog post on monday morning so like you can set up recurring reminders and all that and i know so like y'all are in here y'all are okay with the times obviously because you're here live i know i'm going to get a lot of pushback from people who are like i want you to stream during the week i want you to stream nine to five but i have a day job just like you do and so i can't always stream monday through friday nine to five whereas i can do it on the weekends like first thing in the morning um says thanks for working on saturday and sunday i don't know that it feels like work sure feels like fun to me and i would do this even if y'all weren't around i'd be in here just talking to myself as i work on the first responder kit oh bobby tables it's absolutely true i get way more people inside of this on from uh australia england um the the uh south america uh indonesia kind of areas philippines and all that then i get darned for americans because the americans are asleep most of the time during this so bobby table says it's almost 1am here uh all right so that's probably good it is 8 a.m pacific i'm gonna call it a wrap here and go downstairs and go pick up my coffee and my bagel thanks y'all for y'all really make this fun i really enjoy streaming because it's so much fun to hang out with y'all see the same names again and again it's uh lots of phenomenal change your sleeping habits bur where are you based out of because i see you doing uh like activity at odd times too as well i'm not sure where you're at i don't know if you're you got one of those names it's tough to read whether it's a uk name or whether it's an american name my pleasure y'all thanks for uh coming and hanging out with me it's always uh fun matt gamer says what about canadians i love canada it's just that they don't usually say where they're at on the stream unless they're from either toronto or vancouver sometimes they say uh when they're in oh you're in laguna hills oh very nice uh very cool it's a beautiful part of the country i really love aliso viejo the whole orange county thing it's gorgeous haha j cole says when is pinal starting his stream that's a good question too because he is one of the funniest people when he streams when he's on live and webcast he's hilarious he's so fast in terms of a sense of humor and we should ask him when he's going to start his stream all right well thanks everyone for hanging out with me and i'll be on tomorrow i'm going to be on tomorrow from 6 a.m to 8 a.m pacific as well and uh but all you're scared of what what are you scared of everything seems scary until you jump in but all right well thanks for everyone and i will see y'all tomorrow morning or on next weekend whichever can't match the quality geez well if you look at most the other streamers the most the streamers suck if i'm honest and i scared the first like i sucked i suck the first uh two months all right so i will see y'all tomorrow adios folks you

Channel: Brent Ozar Unlimited

Views: 1,793

Rating: 4.9111109 out of 5

Keywords:

Id: 2e1fKTzGQtE

Length: 161min 22sec (9682 seconds)

Published: Sat Jul 18 2020