Code Reviews in sp_BlitzIndex, sp_BlitzWho

good morning party people so got a lot of our uh good regulars in here i see g surgeon from the netherlands big mike the dba good morning richard cooper good afternoon from the uk jedi mind gorilla good to see you dbloggy as well this is a problem streaming first thing in the morning before i go in and drink anything i always forget that if i haven't talked just yet welcome to the club lt lt subscribing over there on youtube so yeah i always forget that if i haven't taken something to drink this morning i immediately start coughing whenever i go and speak so let's be smart and get the cough out of the system and off we go and of course for those of you who are new around here um no i i don't have coronavirus knock on wood it's that i have asthma and when i suddenly start talking uh especially having been quiet for a really long time like just waking up first thing in the morning then of course i run into all kinds of coughing so goodness gracious i also have water here uh first thing as well yeah that's true jedi mind gorilla you well i say that but i mean i get up first thing in the morning and then it's around one two local time that i start going 1 2 in the afternoon by then i usually start to get a little uh grumpy so today's agenda i'd be curious to hear what y'all are working on today so what's the thing that you're working on today at over at the office or of course for some of you in europe you're already done for your uh day hi from the philippines howdy so what's what are the things that y'all are trying to tackle this week in terms of the office the big thing that you're up against andy good to see you sir writing boy i know that feeling so today is actually my off day before a bunch of classes start i have fundamentals of parameter sniffing and mastering parameter sniffing on tuesday through friday and then next week i have mastering query tuning so today i'm actually updating demos for next week caching oh yeah read-only sysadmin type account for dev people oof permissions is a pain in the rear failed jobs from last night on a dev server oh now converting oracle for to t sql for a data warehouse load i always love how sometimes you'll see people say that uh oh all you have to do is change your back end you know just forklift all the code over move it from sql server to postgres and you'll save all kinds of money and i'm like you really you really think it's that easy to just move something across plus the faster that your code goes the more you tend to leverage uh tricks that are only usable on one particular database platform bob the lobster uh welcome to the club over there on twitch thank you for subscribing um and uh mr huttmacker i see that you're doing your vacation very nice uh hopefully staycation probably working at home stuff peta being a cluster oh god so me personally i would just always rather build a brand new server from scratch rather than p to being stuff like a cluster but i guess if it's a really old uh version i might be okay with it but otherwise oh mike automating a backup uh mike check in uh when you're talking about automating daily backups i would just check into uh dba tools because you're going to be doing this for the rest of your life automating backups and doing things like that dba tools is just such a cool framework to go do that uh jaded bailey today's agendas troubleshooting an ugly stored procedure with a merge statement that's deadlocking let me show you something uh jaded bailey just because it's relevant to what you're looking at if you go google for aaron bertrand merge so aaron bertrand merge if you search for that you're going to find an article with a whole list of problems with the merge statement and this article if i could sum it up in a noise it would be and that's exactly how that would work because one of the things that it's a known issue is deadlocks so like here basic merge upset upsert causing deadlocks if you click on that it takes you to uh it's microsoft connect which is sadly gone um but that that was by design i don't know if you can find this over in connect or over in uh the internet archive but at least that that way it gets you uh started on that journey that that's a known common known issue with the merge statement sucks really bad but there it is so to find that search for aaron bertrand and then the merge uh richard said a client accidentally restored their backup from last night trying to recover their data now jeez you should have like sad music happening inside there oh bob the lobster just finished proving that it wasn't the database with the consultant toolkit very nice i like it very nice um i should fix looks like it's giving me just a little bit of fuzzies over there on the camera let me go tweak that real fast just so that it doesn't give me that little overlay problem there let's go oh not quite that much let's try oh you know why it's because i got a i got a shirt with green on it today that's that's part of the problem now notice too how my shirt is kind of transparent as i move around you can see things from behind it there that's kind of funny how that works uh let's see what else joe came up with uh adba hey adba good to see you uh adbas is picking a part of poor performing query which has a ton of parameters passed in oh yeah oof uh and then jedi mind gorilla we will build a new server after we kill the cluster but getting a dev team to migrate we still have a sql server 2000 server due to that ouch ma'am so what are we going to do today so today what my job is job job's kind of a weird word i have kind of an odd job not like the james bond villain but odd job in the sense that i want to ship a new release of the first responder kit i try to ship a new release every month because you all do so much code oh my gosh i'll just contribute so many pull requests it's crazy uh that was kind of almost went into clippy territory there but you all contribute so much code and so there are three or four pull requests uh no md definitely no not if you search for brannozar no lock you'll see why the answer is no um i have a video teaching you why it's known so i'm going to go through those three or four pull requests and in last couple of months we've had some quality problems last couple of months i have taken pull requests from people and i haven't really tested them that bad i've been kind of like oh they probably know what they're doing this will work and god bless them they knew what they were doing but there are all kinds of edge cases whenever you ship something we get something like 100 000 downloads of the first responder kit per month so whenever you ship it to that wide of distribution you just end up having people who use it in really weird edge cases so i'm going to try this month to do a little bit better a job of testing especially given that we've got some interesting pull requests out there that have kind of ambitious functionality so we're going to take a look at those three four pull requests and work through them i'm going to try to build a test case to reproduce the problem that they're trying to solve so we can see whether or not it works successfully i haven't made up my mind yet as to whether i'm going to ship a release tomorrow or whether i'm going to wait until mid-month because see the thing is i have like three training classes in a row back to back i have one tuesday uh tuesday i'm doing fundamentals of parameter sniffing wednesday through third wednesday through friday is fun as a mastering parameter sniffing then next week is mastering query tuning will if i'm in during those classes i hit the first responder kit scripts hard we use them all throughout classes so it's the kind of thing where i may just do testing during those classes i may just use the brand new dev version live during the classes so that that way if something blows up i'm more likely to catch it rather than the people out in the world uh trying to catch it so let's we're gonna see how the quality looks and then i'm gonna make a game time decision near the end of today's webcast whether i'm going to ship an update this uh today or whether i'm going to ship it like two weeks from now so let's go take a look at the pull request so the github repo that we're working on here this github repos over at firstresponderkit.org so firstresponderkit.org or if you search for the sql server first responder kit github repo in here there have been a few pull requests we're already back if there was more on one more this morning adba actually thank you gave us a pull request this very morning for this thing uh so i'm gonna try to i always try to whenever i'm doing code start with the easiest one first so let's see here i think aids might actually be the easiest one to go yeah probably is going to be the easiest one to go start with so let's go see what's going on with this so aide reported a bug and i'll go pull the uh the link over here to show the bug parallel blocked processes weren't showing a blocking session id so if i had a query that was blocked it wasn't showing me which session was causing it or it wasn't showing aid which session was causing it so in order to do that i thankfully aid even put in a repro script so i don't have to write a test case for this that's just absolutely fantastic so i'm going to go set this up i'm going to go set up the problem so we can see the one that he's talking about here so let's do this and then new and move u over here and i'm going to start by dropping my indexes i'm going to switch over to another stack overflow database just that's easier to restore i'm going to drop my indexes i'm going to make sure that this blocking query is going to or this blocked query is gonna go parallel to do that i'm just gonna lower my cost threshold for parallelism to five by default i ship with a cost threshold for parallelism of like 50 just so that tiny queries don't go parallel but i just want to encourage the scenario that adrian's talking about here so we'll say okay now we will go through and start the begin tran so we're going to lock all of the users or some of the users then over on the right hand side i'm going to go run a query that should get blocked while it's blocked because he's not making any progress while it's blocked we'll go start a new session and we'll say sp blitz blitzu now the problem that aid was concerned about was the session that was getting blocked wasn't showing a blocking session id and well cheering is probably the wrong thing i should probably make an angry noise there this session is getting blocked but it's not showing in a blocking session id so that's that's where our problem is so aide submitted a pull request so aids pull request one of the things i just love about github is that they have up at the top of pull requests they get these hi good morning masimasi masamos has up here the files that are changed i'm going to go click on files that are changed and it'll show me what code was different oh man sometimes fixes are easy sometimes they're hard and i have so much respect for people like adrian who actually go through and work on this kind of problem because as you can see here there is more to do with it also as you can see here my camera got dragged over just a little bit now let's see if that there we go that's perfect okay uh so holy smokes when blocking session id is anything other than this then this then this then this i have no idea what the sam hell this is doing uh that's interesting wow all right so let's go take a look and see what's going on let's go get his code and let's see what it has sp blitz who and let's get the raw version copy it out and i'm going to go put it into my sql server go deploy it into our dev there um he said i did a typo just looking for black oh extra nonsense okay let me go back i'll take another look at that paste that in oops paste and execute so he says i did a typo come back a little so is there like a plus or minus oh because you minused out oh okay okay i gotcha um so you took the the code here and it says that it's taken away but it's not actually taken away it's the same code i'm gonna guess that you did like different spaces or different tabbing so uh if i look at those i hate how github does do this does when the code is really essentially the same they'll say that you took it out and then replaced it but it's the same thing yeah this is accidentally doubled up on the column the first time okay so in this then let's go see how this works so i put it in and let's give it a shot let's go run sp blitz who again and all right perfect it works so now it works but let's also just check to make sure because we may have also a different slight problem put out a pullman says it should be possible everything should be possible right with god all things are possible unfortunately they are not always possible on a live stream when you're trying to do code review so let's let me tweak one thing here to do do do switch this over here and fix that why are you doing that who knows okay i knew you'd catch that andy uh so it that works for parallel queries now let's check to make sure that it actually works with single threaded queries as well let's say option max dot equals one and execute let's also turn around and say instead of equals let's just say max stop because that's how queries are supposed to be written and then let's try it again with sp blitz who and it looks like it's working in that scenario as well so despite the the weirdo kind of plus minus type thing uh i looks like really if i'm gonna i'm gonna put myself in the way here because i'm gonna do code review here for a second uh plus minus anything other than you're okay you're the same thing you're the same thing here the same thing okay so he just added lines inside there so i'm okay with that this is really how it should have looked in terms of the code review it's just github got a little wonked up in the top part so i am going to say reviews just so that it's my butt on the line i'm going to say i review the changes and looks good thanks approve submit the review and then i'm going to merge the pull request i'm going to go up here and make sure that it is on the current milestone that it's on the august milestone then i'm going to go over to the github issue and i'm going to say no that's okay that's totally all right i know how it goes it's the kind of thing that i wouldn't expect people to go through and catch what what i'm usually more worried about is somebody will submit a pull request and they'll have like all kinds of other changed files inside there like they'll change the documentation or they'll change other unrelated scripts and i'm like yo dog why don't you just change one thing at a time uh i don't have to say that very often so thankfully both yo dog and telling people to change their github things uh looks good thanks sir it's merged into dev and will be in the august release along with credit for you in the release notes so now let's go put i gotta designate this issue as actually being inside the august milestone and then we have to add this into the release notes as well so i have over here in the background the blog post that is all set up for the august release so now i'm going to put in here changes in sp blitz who fix parallel blocked sessions weren't showing the blocking uh spid uh number 24 it's might not even 2480 i think it was thanks and here we'll just copy this out i don't know why i still make that noise thanks adrian buckman and there we go and let's get these the 24.89 that's what it was 24.89 24.89 and then paste that in paste and save the draft and we are done one pull request down out of four that only took like 10 minutes in order to do so now let's go back over to the whoops get out of here pull request list and so now all right now we're down to three more um so now let's see what we want to work on next so out of the next oh all oh no two of them two of them are on s people it's index no thank you thank you adrian i appreciate it cheers so now on here we have two remaining i got two on s people it's index greg has been on a wonderful mission greg's a very smart uh person in the community as well just like adrian yeah i kind of need a haircut like my wife is going to try to polish me or you know clean me up here a little around the edges so i kind of need a haircut so i've been doing hats i even was doing hats yesterday with the client i was like look really no you don't want to see me with my hat on and i put product in my hair and everything getting ready howdy david i put product in my hair and everything this morning and then as soon as i went and like turned on the cameras i was like not today satan um so uh show all missing index requests so greg had a couple of really cool things inside here uh and both of them i really like a lot both of them i'm interested in getting in and i'll tell you the back story on each of them when we go through and do those but because both of those are on the same stored procedure i think i'm trying to hit this one first which is uh implement skip check server skip check server on sp blitz i'm all edges [Laughter] that's pretty good that's actually really good i like that a lot i might gorilla so uh implement skip check server this isn't actually what i thought it was i thought it was something different um wow he's gonna try to do a linked server wow okay so look at that okay this is amazing so let me tell you what's going on here a long time ago a long time ago when i was a babe in the woods and i didn't really understand how open source development should work when i was a babe in the woods i was building sp blitz and i said it's very important for me to have the same parameters for a stored procedure i never want my uh oh howdy um i never want my sure blame your oversleeping on the twitch notifications um i never want my stored procedure parameters to change i want from the time that i release a stored procedure i always want the parameters to be the same because people will implement agent jobs that call them and they'll use pr stored proc uh parameter names well so early on i was like i'm going to have output server name output database name output schema name and output table name so that you can call sp blitz and then write the data to a table because people want to track their server's health over time for example they'll say they want to see what the server looked like last year as opposed to this year because i know as a database administrator it was always tough for me to get a raise because my boss would say okay so it's time for your review what did you do this year and i'm like uh i'm not exactly sure and he'd be like we'll go through the help desk tickets and find and i'm like no help it has tickets no so instead what i started doing was taking sp blitz and i would run it once a month on a scheduled agent job to a table and then that way i could compare how things looked at the beginning of the year as opposed to how things look look at the end of the year well what didn't work when i brought out sp blitz was it didn't work across linked servers there was a parameter called output server name and i thought someday i'm going to code that spoiler alert i never coded that for like 10 years for like 10 years people gave me uh support requests and they were like hey brent output server name doesn't work and every time i was like yep read the documentation there's a there's a a github issue for it you're welcome to contribute the code if you want but it's not the high thing on my priority list right now so i haven't coded it and this happened over and over and over again and seriously i swear on all that's holy i swear that i was going to pull the output server name out of this month's release because i was like i don't want people to keep thinking that it's going to work and burning their time trying to get it to work when it says in the documentation that it doesn't work but they don't freaking code it or they don't freaking read the documentation all of a sudden out of nowhere i'm sitting just on my own just minding my own business and out of nowhere i get an issue pl and a check a pull request request saying oh it's yours oh merlin oh well that's a different name i don't know if that's a different uh he says by marilyn that's not the pull request we're looking for i don't think unless you have a totally different name than pierre letter which is possible your name says merlin and the avatar kind of looks like a merlin but so anyway so like all of a sudden out of nowhere this pull request comes in i'm like oh my god someone's actually going to do the work and it's not me so uh in order to do this i'm going to need to set up a linked server now that's something that i don't have easily right now oh my real name and my gaming name look at you well in that case thank you i appreciate the uh pull requests you're my only hope obi-wan merlin um so now i need what i need to do is i don't really want to add another sql server right now i do have link servers that i could go hit over in things like the cloud but what i'm going to do just for starters is i'm going to try output server name with a linked server pointing directly at my own server so i'll show you a trick about how you create linked servers pointing to the same server you're probably never going to need this but god knows that i've needed it over the years so let's start with that first now i'm going to go install the latest version of sp blitz and i'm going to show you what breaks before i show you what works and then we'll go into pierre's pull request and see if we can go in and fix it merlin said i didn't fix the output server somebody else did that already i just completed it with skip checks yeah and skip check even if you can get skip checks to work i am frankly merlin slash pierre pierre merlin letter i i coded that i didn't even code the skip checks i paid someone else in the community to develop the skip check stuff lori edwards who's now in the past board of directors lori had some spare time and i'm like let me hire you to go do the skip checks thing that i've always wanted and i've never had the time to code i didn't know if anyone was using it or not pierre i am so thrilled that you're actually using it that just warms my cold ice cold heart so it's really cool that you would even use it let alone submit a pull request for it i would hug you if that wasn't against the law in most states right now um so i'm going to go show the people on the stream will be learning how the skip checks stuff works i think most people don't even know that so it'll be an educational journey for all of us so let's talk about what skip checks is and what it does first let me go grab the latest version of sp blitz and then i will go install it on my little server on here close u close this deadlocked or blocked query because that isn't going to make any sense roll back and close you let's go open up sp blitz so that is on c temp first responder kit sp blitz and it's in the master database perfect all right so here are the parameters that we're talking about let's say that there's a check in sp blitz that you don't care about that for example let's say database snapshot online you're like or for the purposes of this webcast i'm going to say backups let's say that you're on a development server and you don't care about backups how do you get sp blitz to skip the warnings about backups what you do is you create a table with the list of checks um uh that's a good question if you search for brentosar github i actually have a video on it but just kind of a primer but that's a really good question and other people are welcome to answer because i know i struggled with learning github too so what you're going to do is you're going to create a table and you're going to pass in the location of the table here people think when they read this they're like oh this means i'm going to skip all the checks on one database no there's a table called skip checks and where does it live like it's up to you where the thing lives and inside that skip checks table it's going to have a list of check ids databases servers etc that you want to skip so first thing that i got to do is i got to go create that table second i'm going to run sp blitz and passing into that table so you can see how it works so if i remember right i've got it documented over on the code i'm going to go to in the readme and search for skip checks so here it goes so skipping checks are databases this is on the home of the the documentation page so let's go copy that out and let's go start a new window so we're going to create the table of checks to skip in order for me to be fancy i'm going to put it in a different database i'm going to put it in the dba tools database because that's just going to give me a little bit extra level of testing so this ski the checks that i want to skip to find it i'm going to go run sp blitz and i'm going to find the check for backups on the stack overflow database alone very nice wow you use it too david oh my god i got to write i got to write a blog post about it because i didn't think anybody actually knew about that feature that's fantastic so let's say that i want to skip the check across this row right here i want to skip the check on backups of stack overflow but only stack overflow so what i'm going to do is if i scroll across over to the right hand side here there's a check id every check in sp blitz has an id so that this way you can skip just the specific checks you want i'm going to skip check id number one for the stack overflow database so up here it says when i insert into the table and i'll move this in so that you all can see it just a little bit wow a bunch of you uh use it that's just oh my god it just warms my heart that's so fantastic i'm not going to pick a particular uh database or i'm not going to pick a particular server name but i'm going to say the stack overflow database and check number one so let's go insert that oh did i create the table i don't know that i did let's go create the table in case it doesn't exist and let's insert the one row good morning santi good to see you so now let's go run sp blitz these when you see in here that i have did i put in the name oh good it's even the same name of the table so now i'm going to run sp blitz again this time i'm going to run it with that skip checks parameter and if it works correctly if y'all hadn't said that you were using it i would actually doubt that it was going to work successfully the first time that i executed it here because that's how rarely i use this so in here see now there's no warning about the stack overflow backups that's been skipped fantastic i love it so that works but what doesn't work or i assume doesn't work because i hardly ever use this is skip check server so skip check server if i pass in a server name and i go to execute that well it also server i don't even know that it's not server name is the parameter i don't even know if it's doing error handling or anything like that it's just keep it on checking right on working oh it actually wow wait what here oh you know what it's probably just completely getting disregarded because i bet if i put in uh pierre letters i bet it's probably still going to work it's probably just ignoring that thing that's exactly what it's doing it's just simply ignoring that parameter altogether great brent golf clap real smart so uh now what piers change is let's go over and look at the change request that he put in so let's come back up to his pull request whoops that's issues i need pull requests so in here he says implement skip checks server it's not used at all aren't you ashamed of me uh uh merlin isn't that just absolutely terrible so here's the the call uh the oh look at you you even changed the documentation oh you're just so fantastic so it up in the documentation it says really anal retentive users like y'all will notice that the skip checks parameter isn't used yet we figured we could uh uh avoid changing the pr surface area later even took that part out that's so just magical um so in here he has oh you're just doing four part naming okay all right that that's cool that works so he's not checking to see whether a linked server exists with that name so if a linked server doesn't exist with that name sp blitz will crash i'm fine with that i'm totally okay with that and uh if this was like customer facing production code then i might want to do better error handling or whatever but frankly this is for us database administrators i'm fine with this so let's go see if it works uh well i expect that if i run it as is with the way that pierre has it right now it should crash and it should throw an error on mine then i'm going to go create a linked server with that exact name and we'll go see what happens so i'll switch into the master database so that i overwrite the same one then i'm going to execute this again i expect this to crash oh you know why it didn't crash oh oh no okay all right yep yeah there we go okay good uh so here is the warning i'm fine with that i'm totally okay with that that works beautifully so now let's go add a linked server and make it work to add a linked server i'm going to go into server objects i always forget where linked servers live i'm going to go add a new linked server and then under the linked server one of the tricks that you can use is you can just use the period just a period and nothing else and that refers to localhost i am your local host so it's a sql server in terms of security i don't actually know uh let's try use the current logins current security context i don't actually know what if what this essay password is on this because i shouldn't have to know right if worst case scenario i'll just go make a new login so let's see if that works now here pierre letters won't work but let's just try a plain old period and let's see what happens [Music] and there's no backup warning on stack overflow pierre merlin you are awesome that works beautifully um so love it i'm very happy with that absolutely works well we will take it uh okay so that works i'm gonna ship it off we go so let's come back over to the higher pierre so true story i'll i'll tell you a true story only because it's kind of funny and then i'll accept the pull request uh so the way that i met uh i didn't meet richie but uh this way i don't i think richie and i had met each other before that but my employee my let's gonna say my only employee but my wife also technically works with me notice i didn't say for me uh the way that richie really started coming on board with the company was one of the first or the first training class that me jeremiah and kendra taught that jeremiah kendra and i taught in atlanta georgia we did our very first training class ever in atlanta georgia rented out like banquet space at a hotel did developer focus sql server training and richie rump showed up was in the audience as well and he saw us doing a lot of copy pasting of statistics i o output and yeah you absolutely should assume that yes because i do tell a lot of fictional stories like trying to change the names for client type stuff um but uh richie shows up in the audience and you know we just knew him from online or whatever and looked at stats io i was constantly copy pasting things out of stack io or statistics io running calculators against i'm trying to sum the total number of logical reads and as he's sitting in the training class he's like oh i bet i could build a site that would do that in javascript and he built it like the night after the first day of class and shows it to us and it's like hey check this out i built this to make your life easier and that's statisticsparser.com statisticsparser.com that i bet a lot of you have used even today and he just built that whole thing in javascript and we were like wow damn and so from like kind of like that moment forward i was sealed the deal for me that he's the kind of person who recognizes real world problems and builds appropriate solutions without having to be micromanaged and that's the kind of person that i really want to work with and hire because i know that's the kind of person that i want to be i strive the kind to be the kind of person who sees a real problem out in the real world and then build solutions to match it without having to be given very specific uh specs d sanchez welcome to the club so now that's a good point merlin i should also update the documentation saying that this parameter works so let's go over to the pull requests and we will accept that magnificent accept that magnificent pull request so implement chess skip check server so review uh reviewer was me and i probably should have put that in as a review so let's see here i'll go over and say review works on my machine because we know that's how we all test approve submit oh you know one thing i didn't look at is does this still work even if i don't put in a skip check server if i run it without a skip check server does it still work i don't i didn't see if he had a coalescent side there oh it looks like he might so let's go back i'm just going to look at the files changed again uh and say does it have yes yes he checked and see if it is null or not he has an if null check inside there if skip check server is not null uh go add it in um so i'm gonna i'm gonna take that as is i may change it to an anything other than empty string just because empty string will break if i do pass it in because this is technically a valid uh input that won't work or if it does work the space definitely won't work yeah so an object name is missing or empty so i would just need to have make sure that it's anything other than an empty string so i'll do that as well so let's go take the pull request we've got that accepted and now let's go in and tag it so it is assigned to merlin uh yeah yeah very nice nicely done that is pierre and then we'll say label is an enhancement for sp blitz and then the milestone is the august release we will say that this is merged confirm and thanks for the code works great merged into dev we'll credit you in the august release so let's comment that and then let's go back over to the issue itself i don't know that this had an oh yeah good it does have an issue okay cool so then let's assign pierre to the issue as well because i always like it to be able to be tracked later if somebody wants to go in and see who did something or like for their github profile something like that an enhancement on sp blitz and then milestone is in the august release and put this in here and comment and now let's see i'm now i'm going to add a branch on my own machine because i want to make a couple of really small tweaks i need to change the documentation and i need to check to make sure that it's not an empty string that they passed in so let's fetch the origin let's get the changes let's create a new branch 24.96 uh what's it called skip check server thank you road to reform let's not do too much politics inside here though thanks for your understanding um that's like you know politics and religion i try to stay out of in this uh tech channel except when you see me do it uh skip check server uh null checking or space checking and new branch and create branch and then let's say in here the changes that we got to make we have to [Laughter] well that escalated quickly uh first responder kit so in sp blitz ah do you want to reload it yes and let's close to close the other ones close this close this so in here skip checks server let's go in and find it so here it goes so if it's not null this is what pierre originally did which is really cool it's not bad but i'm also going to say if l trim r trim and technically just plain trim would work here but i try to maintain compatibility with older versions when it's easy is anything other than all right so i'm above politics so is anything other than an empty string also just for bonus points i'm going to tab that out just because it's a little easier to do oh skip checks oh uh oh no okay we're doing skip checks oh set string to execute plus sql um string to execute plus equals oh because we have already started setting it haven't we have we not set that yeah we uh set string into yes okay there we go that's cool uh and as long as we're in here uh not okay good point i'll add the semicolon as long as we're in here i think string to execute is an envera care and i just want to be kind of good about that so yeah it is an envercare so i'm gonna say [Music] uh i'm also gonna set these as plus n so let's throw the ends in there for unicode just to be kind of good uh that that and that should do it okay all right good uh so let's put that into production and then i don't know why i put that into production now let's save it close it i also need to change the documentation so the documentation there's a readme file so a quick thing on the way that github works which i really kind of love this is neat how this works if you go to the main page of the first responder kit repo and you scroll down down past all the files here's the readme file and in here is the documentation that no one ever reads i do wish that they would give us the option in github to put the readme up above the code because people just see a bunch of code and they think there's nothing to read here but the documentation is down at the bottom so down on here i'm going to do a control f for skip check server and see oh good it's not even in there so check for that okay good so there's nothing in there about skip check server so i don't think i even need to change the documentation that's kind of okay now pierre also wanted me to change the um output documentation i'm gonna go look at that output server name um not it says not functional yet and that's true the only one it's functional in is sp blitz it's not functional in the other stored procedures because the other ones use xml they do xml like sp blitz cache for example dumps xml in and xml can't go across linked servers no robert we do not no so output server name i'm going to change that he says you should add it at the bottom there with that wrong statement yep absolutely so i'm going to say in on the read me i'll edit this with notepad just because it's got a little a bunch of fancy formatting in there that kind of makes some markdown editors choke um works not functional yet or works in sp blitz but not functional yet in the rest of the stored procedures all right so there we go save i'm just writing down you missed it i was i was i was uh saying good things about you earlier it's the same we were talking about the time when we met and you did statisticsparser.com okay so we've got that uh let's look on the github desktop and we have so the one things i love about github desktop is it gives you these nice little diffs that show you what was changed so there's a change that we made in the readme then over in sp blitz here are the changes that we made over in sp blitz i always just kind of want to double check to make sure that some typo or something didn't sneak in when i wasn't looking looks pretty good so let's say number 2496 sp blitz tweaking skip check server implementation uh checking for empty spaces did i miss a space hold on let me go let me go hit that let's go and greg your stuff is up next uh skip check server uh and let's see here so we got that that oop yeah good catch greg oh yep it's right over here good catch uh so n right there save that and then close and come back over here so there we go checking for empty spaces adding ends in front in front of strings of strings closes number 2496 commit do a publish do a pull request come on big fella do a pull request and assign myself on this one and do a label we'll just stick it under sp blitz and then the milestone is this month's milestone and then create the pull request i saw your note greg and i and i'm gonna see because i may we'll see if i can repro it too as well this this is uh uh and we and i'm also like um with as much work as you put into it i'm like okay so now i'm gonna pick it up and keep going because if you did so much work in there i want to make sure that it gets across the finish line so if i can uh repro it i set myself aside enough time this morning that i could go dig in and figure out what's going on okay so that works uh the issue number now i gotta add that to this month's uh release so i gotta go put in so this is it was 24.96 so sp blitz improvement skip check server is now implemented so you can pass in or you can have sp blitz check a centralized skip checks table for a list of checks databases and servers to skip across your entire what's the word that the brits call estate estate i think that's just kind of cool because also because i like estate cars that is number 24 number 2496 thanks pierre letter and i don't know if it's letter or letters i'm going to go over and take a look at that to get that completely shoot i hate it when i copy instead of paste uh paste let's go see pierre's appear letter okay pierre letter appear letter and paste that right there paste and there we go so let's save that and i'm gonna have to do kingdom all right so save that all right there we go and we're off to the races now we are down to pull two pull requests left uh realm that's not bad either so before we go on i'm gonna uh do a quick shout out to this week's sponsor so this week's sponsor is quest software quest put together a an ebook with the best of sql server query tuning tips from me panel and janus from our recent webcast for quest software you can go read about those over at brentozar.com go slash optimization totally free ebook you just have to sell your soul in the form of putting in your email address and then they will spam you until the end of time right but that's what we all do that's why you're here with me is because you bought into my spam as well spam well you know i'm kind of like spam is the price that we pay in order to get uh this the free education type stuff i totally get it you know like i wish that every company would pay for all of their uh employees to get free training but it's just hardly ever the case that that happens and instead we end up doing free training just because vendors pay us to do it okay so we got we've done been going for about an hour straight now that marrow showed up don't worry we're still going i'm going to be doing like three hours straight continuously today doing uh testing of code and all that um i have never surfed in the ocean that's not true i did in um uh cabo we did during one of the company retreats this is before you got hired but we did um uh a company retreat in cabo mexico rented out a big house and i did body surfing out there and by body surfing i mean i took one of those boogie boards and got out there and managed to get my butt kicked inside the ocean it was not not pleasant and i mean it was pleasant but it was not pleasant to watch i'm sure okay so we're going to stop here for a five minute bio break i'm going to go refuel my espresso and we will come back in five minutes and continue working into the pull requests i'm not doing open q and a's uh today but if you search for foreign keys subaru on our blog you've got all kinds of stuff over there so we will be right back in five minutes and then we'll dig into greg's pull requests on sp blitz index so see y'all back here in about five minutes [Music] [Music] [Music] [Music] [Music] [Music] so [Music] [Music] [Music] [Music] [Music] all right welcome back uh so over the break got an email in my daily email from carsonbids.com one of my new obsessions so i've always liked to bring a trailer bringatrailer.com is where i bought my audi rs6 well thanks royce ginger i appreciate it and now cars and bids is my new obsession because they focus just on like 80s and up uh collector cars so they have a car that ends today oh the car that ends today 1986 bmw 635 csi oh oh it's so beautiful so the photos on here are just epic fantastic i just absolutely love this thing such a nice job of maintaining this thing just a beautiful look to it i really love the two-door style just big coupes not particularly fast it's got an automatic transmission it's just like a nice big cruiser and i love how they even have the stickers from hoonigan on there so over in the corner there in the rear window it says respect your elders oh i just love it it's so beautiful uh but yeah what a bunch of just really just a beautiful looking old car so when i bought the audi rs6 a long time ago my wife was like oh it's i'd say like three or four years ago um about three or four years ago about the rs6 my wife was like okay it's it's cool and it's fast but it makes noises it like creaks when you're you're in sitting in the car and i'm like oh come on now because we've had really screwed together cars for a while we've had like a volvo then what do we have after the volvo an infiniti and then the the porsche and they were all brand new and so she's like i don't really like cars that make noises like creek and grown and i was like oh and there was a wine in the driver's seat belt like it would wiggle a little bit in wine every now and then she's like i don't want to make any sounds and i'm like oh come on um super cyber all says how expensive are to maintain our german cars it it's less about how expensive they are to maintain it's more about did you pick up a car that has what's called deferred maintenance whereas the people thought it was expensive to maintain so they just didn't maintain it and they drove it into the ground not doing any oil changes or anything like that those are the ones that are going to be expensive it's the there's a common joke that the cheapest merced or the most expensive mercedes you'll ever buy was the cheap mercedes like if you buy a mercedes and because it's got a low cost to it that's because somebody beat it into the ground and they never did their maintenance on it i see i used to want a triumph or an mg or an alfa romeo or a fiat one of those small convertibles from the 60s 70s but man i mean you get in you're like there's no air conditioning in here you know there's no defroster the defroster doesn't work and i'm like ah after enough of those i because my dad had mgs and triumphs growing up and i'm like i don't know so uh yeah exactly three thousand dollars um so oh i just love this thing it is just stellar just a beautiful old car so anyway uh that's cars and bids it's really neat they have a lot of modern collector cars so there's a fox body mustang up there which i just love this thing too and it that ends today 6 000 bucks it'll probably go up to like 10 or 12 but if you look at that's something from our childhood that's like super rare to see now they looked cheap at the time i mean not a lot of us really wanted this particular flavor but now you look back and you're like oh that's actually kind of awesome now cars and bits has a lot of those interesting kinds of cars then they also have modern stuff too like i'm who in their right mind is buying a brand new used uh amg yeah the crash regulations pretty well put a stop to that one not a lot of not a lot of kit car makers could do air bags royce ginger says i picked up a house with deferred maintenance you know what's really funny one of my favorite channels on tick tock is a home inspection guy there's a home inspection guy on tick tock that takes videos of the horrors that he finds when he uh goes and inspects houses squirt bottle um yes madame mike yes i've been on here for uh quite a while now i mean not today i mean today i've only been on for like an hour okay so when last we met we were before the bio break we were getting ready to work on two pull requests for sp blitz index so on sp blitz index now let's figure out the changes that we need to make inside here so greg has two different pull requests um i don't know if one's got dependencies uh let's see i'm gonna look at the show all one because i think it's gonna be easier and i'm gonna look at the the changes that greg made inside here greg is in here inside the chat as well he's watching us from inside which is kind of cool there's four pull requests that i'm going to tackle today and all four of the authors were actually on the stream y'all are just amazing um no uh toby i can't take that much credit we have 11 issues but we had four pull requests so four pull requests we've got we're getting it we're gonna get it down to zero today but i'm still gonna have like seven issues up and running and homes on homes is also awesome i really enjoyed that dtla good to see you so on here here are the lines of code that great so let me let me phrase let me explain what the problem is first that greg is trying to solve when am i going to load up quake quake no if anything i would play either grand theft auto or uh the truck race or truck driving simulator and i forget uh which i made tick tocks i have like a dozen tick knock talks or really like 30 or 40 tick tocks i think it's just i only make them when i travel like the iceland trip and thank you sniper demon for renewing as well that's fantastic a second month here thank you i appreciate it so the problem that greg is trying to solve with this pull request so when you run sp blitz index one of the things that we do in sp blitz index that's the point db augie as it blurs out the background because if you're looking at the background my eyes are up here come on just wanted to say that because it's kind of funny um but that's a point it's called bokeh it's a fuzzing out the background on purpose so that you focus on the thing that's important you squirrel um you will you say i don't i don't update it that often so it's it's not too bad and i promise i never dance it is never going to happen i'm never going to make a dancing tick tock i'm waiting for him to follow me then i'll make lots of tick tocks the code is back there if you're trying to look over here we're not doing testing right now and if you're trying to look at the code on the monitor you just need to get a better monitor like switch to 1080p or 4k so the problem that greg is trying to solve greg being right there the problem greg is trying to solve is that with s people it's index we give you your missing indexes like indexes that clippy has been complaining about but we filter them the table has to be at least a certain size the indexes have to have been used at least a certain number of times and that number is based on how long your server's been up because i don't want to bother giving you index recommendations for something when you've only run a query three times but the server's been up for six months so what greg did was greg said i want to be able to show all missing index requests because maybe my server's been up for a really long time and a queries by our queries been burning up the charts but it just hasn't hit the thresholds overall due to our uptime yet so greg wanted to add a parameter that shows all missing index requests regardless of how many times they've been called so let's go see how that worked so if i go over here so here's a parameter that greg added show all missing index requests default to zero i love it because greg's being considerate of the existing code base out there you don't want to break functionality that people have been expecting to get you just want to have a new parameter that people can opt into if they want the new functionality so here he's doing filtering for normally the math that we do is here the magic benefit number which essentially is the number of times the query was the number of times the index was asked for how big of an impact the index would have had and what the queries cost was and the latter two are estimates by clippy so they're not terribly accurate that's why we call it a magic benefit number because it's kind of real and kind of not uh so magic benefit and then based on the days of uptime now yeah for right now i'm just double checking submitted code here uh today so here is where we do our math he's saying either where the missing index uh filter has been turned on just show me everything or where this math matches so that's absolutely perfect i like it also this is very easy because i don't have to check greg's math here this math has been in place for years so it's not like there's anything tricky going on here i'm going to scroll down a little further okay so the next one mode 4 is also when we're examining and doing like the psychological tests on your indexes so uh mode four i'm going to move this down just a little to get it out of winnie's tweet there winnie's message so mode four same exact thing we're saying either mode four and they wanted everything in or show all missing indexes is turned on okay i like that let's go down a little further and then same exact thing here checking the thresholds okay i love it so now i need to be able to test greg's code in order to test it what i need to do alan welcome to the club from youtube um in order to test it what i need to do is i need to create a missing index hint and have it not show up inside of sp blitz index because i like it only runs one time kind of thing so let's start by saying we'll flip over into stack overflow i'm going to run sp blitz index and then i'm also going to run it sp blitz index mode equals 4 and sp blitz index table name equals users the reason why i'm going to do this is i want to make sure that the before and after because greg changed things in a few places i want to make sure that it's consistent in all the places just to double check so right now we don't show any missing index requests and let's look in closer at the users table the users table also shows no missing index requests now let's go run a query that triggers one so i'm going to say select star from dbo users where display name equals brent ozar and let's see if this triggers a missing index request sure enough hey buddy oh you didn't think you're going to see me on this webcast did ah you won't lose me that easily so hey buddy i see that you're querying my display name sure would help if you had an index on the display name table but i only ran the query once and the queries relatively inexpensive the query only costs 38 query box so does it show up right now over in sp blitz index when i go look at the user's table it does not there are no rows inside here and technically there's a bug because it doesn't say no indexes i'm going to pretend i didn't see that now if i go over and look over at the sp blitz index the overall version just to make sure that that doesn't say so that doesn't show any problems either it says no major problems found nice work and let's go run this one yes i did i went to malibu and to mexico for uh like a week each so none of these show index recommendations this is working as designed i do want to check the dmvs just to make sure that there's something in the dmvs that reports a missing index i'm going to go select star from sys dmd dom fx you can read the documentation for that one i'll let you go ahead and do that dmdb missing index details and let's go see if there's any missing index recommendations and there are there's an index recommendation up there on the users table on display name so that's the one that greg wants to show in his new version of sp blitz index so let's go get greg's new version of sp blitz index let's copy that out sp blitz index copy it out get raw that sounded a little weird copy that out and then go paste it into ssms it works as desired works on my machine execute and then let's try it again now with greg's change let's go in and run sp blitz index oh no no no no no no it's okay i didn't run the parameter that asks for all missing index requests it actually works well so far let's try this one and that still works as designed that one that one still works as designed now let's pass in greg's new parameter show all missing index requests i think that's what it was called let's go see and [Music] look at that greg yes there we go that's true i totally need that that would be kind of funny um so this now shows that we have one missing index on the users table it only would have been used one time so that's perfect let's try it with mode four so let's copy this out copy and paste and then we'll paste it down here while we're at it so we'll try it under mode 4 and that also shows up then let's try it on the users table and the users table shows up so there we go yes absolute cgs i absolutely am i absolutely am cgs what you'll learn over time is that as you give free software back to the community you become really lazy about cosmetic bugs i'm like that's a cosmetic bug if someone wants to go and fix it they're welcome to file an issue it's been like 10 years and no one's noticed that cosmetic bug until now including me and i will leave that as it is yes um so if you turn on your parameter so it'll hide the bug only if i think there are missing index requests but if there are none then i think the bug is still going to be present okay so speaking of which i'm going to go back to the main one so i'm going to say that greg only i'm going to say this because you're in the webcast so in here see how it says high value missing index in an amazing world what i would do is instead of saying high value missing index i would say rarely used or new missing index this is not that perfect world i am shipping it i am totally down with exists exactly the way it is the reason why is that i know some yo-yo in five or ten years is going to come in here they're going to run this and they're going to be like why'd you call it high value it's not high values only used one time but we'll all at that time we'll all look back at this moment and we will laugh um yeah and that's that's cool i know i'm down with you i agree 100 all right so i'm gonna take that exactly as is that works for me let's go over here and take that little fella so let's come back over here to the pull request uh review that's true uh looks great thanks for the richie you say that but we've got two bugs open for it right now and you know what's really funny richie is that greg this pull request is related to that bug in sql constant care we are recommending an index that already exists due to the way that we evaluate them yes greg like i could put the two of you in a room together and you would suddenly bond over richie has to go fix the bug that greg found thanks for the pull request merged into dev will be in the august release with credit for you in the release notes oh i also got to add this to the documentation approve submit and then so i'm going to put this in in the august release assigned is greg and then labels [Laughter] works as brent's works as brand suspects that's what you should put because i think in my specs is the reason that that bugged this uh cgs no it doesn't and i'll talk about that in a second here too once i uh merge this pull request because that has an interesting thing along with uh greg's uh bug that he found inside there i feel like a valued customer you are you're part of the team now because you're contributing you're contributing issues uh so that did i merge it i merged it yep okay good so then let's come back over to the item and we'll say assignee is greg the label is a enhancement for sp blitz index i think i called it in a bug at some point whatever and then 2018 release paste in my note over here and close and comment so now over on the release notes we'll say sp blitz index changes improvement uh yes absolutely santia can um added new show all index index what was it was it was it called show all missing index requests requests parameter to show um even if the query was only run a few times um or had low impact number 24 24.98 uh thanks greg dots and i don't know if it's dot is it plural dots well here let me paste it in first uh and i'll see it over on your github profile you don't have to type it in there greg dodds and copy that in oh it doesn't have it on the github oh it's only singular it's only singular okay cool uh greg dodd there we go thanks and of course now i got to put it in there valued customer greg dot so we'll save that and now i'm going to talk about uh the question that i'm gonna talk about who was it that asked uh cgs cgs said does constant care get updated monthly like the first responder kit one of the things that uh that i wanted to be able to do with constant care is track what's happening over time and then make recommendations based off of that with stuff like sp blitz index all we have is the current moment in time we don't have all of the history so the whole reason that this pull request came around is that greg started getting missing index requests in constant care and we started having a conversation about how we track them with sp blitz with a constant care we track that a query is being run like days in a row we want to make sure that the missing index request keeps happening if it only happened one day because some data analyst just ran an ugly query on their machine we don't want to trigger a missing index request on that we want to see the exact same one going up for days in a row so that's the kind of thing that we need metadata over time for and then you know we also want to go d dupe indexes we want to check and see that clippy's indexes aren't too wide that they actually make sense in order that we're not indexing on the wrong kinds of columns so that's the kind of brains that we put into constant care because we can afford to spend more computational time on that and track history over time the release schedule and the logic for what we do in constant care is totally different than what we do in the first responder kit so we tend to not announce things ahead of time because who knows we'll find bugs and we'll wait a while so we just released the constant care changes when they're ready we don't do them every single month at the same time of the first responder kit because it's just totally different code okay so there's one for sp blitz index um uh no it's our uh changes in constant care are totally independent from sql servers releases we have a whole uh like oh my gosh a ton of github issues in there that for ideas and things that we're working on uh we just i like rick talk with richie and slack we make decisions about what we're prioritizing we tend to do a milestone for every month or two and then richie works through things i'll submit pull requests and then as we get them tested then whenever it's the right time we go into a deployment rich you can probably answer how frequently we do them i'm going to guess it's every two to three months ballpark i'm not sure off the top of my head i've never really thought about it because i also i tend to not micromanage that kind of thing i'm like ah whenever we got a bunch of stuff and it looks like it's ready we'll do it uh we'll do it all at once we don't do continuous deployment or anything like that we probably don't know we couldn't because we have database changes too as well that we're not great about automating in terms of source control okay a quick shout out to this week's sponsor um before i go on to the next pull request so quest quest software sandy i'm not going to do training during this class for training we've talked about you the training classes that you can go into but this i'm going to have to focus on the thing we're doing here this week so every two weeks damn uh so quest us bringing you these webcasts this week if you go to brentozar.com go slash optimization you can go download a free ebook on query tuning compiled with stuff from me janice and penal from webcasts that we've done recently for quest software we have more webcasts coming up in i want to say september october november i've already inked them liking got the contracts and everything done and they're on my calendar i just don't remember off the top of my head the public registration isn't open yet as soon as we get the public registration open i'll publish it on my blog as well for that also if you go to that site and get the free ebook you'll automatically get emails for the upcoming webcasts that i'm doing with uh i don't know what i'm doing with i think those are just me for september october november and they're all of course recorded so if you miss the live ones quest will send you the recording links as well okay and we have so many oh there's so many interesting things at some point we should probably do an open q a about uh constant care and the infrastructure behind it just so that you all could see because i have no idea what kinds of questions you would ask we used to blog about it but at this point like the infrastructure is kind of stable enough the only changes that we kind of would have coming down the pike the two things one um i'm kind of intrigued by aws aurora serverless so which is a serverless database that'll automatically scale up and scale down in theory we're perfect for that because we have real bursty workloads we have really uh ugly demands for just a few hours a day when people all send in their data so in theory serverless would be good for that right now it has a whole lot of restrictions that make it a no go um i'll just stop there it has a whole lot of restrictions that would make it a no-go the second thing that i would love to be able to do from an infrastructure perspective is be gdpr compliant but unfortunately since the edu knocked down the privacy shield laws for us to be gdpr compliant we would have to have a whole separate infrastructure in the eu and right now just the cost benefit wise it doesn't really make it okay so now let's look at the next uh pull request or really the last pull request from the public i think there's there are going to be a couple of other things that i want to change inside there that i may want to work on so this one greg has been working on for a while this is kind of cool i love this one so here is the thing that i would love to accomplish um no uh royce i wouldn't even come close to that that's we're not even gonna bother with that it's really when you think about building an application it's like do you want to build for today or do you want to build for 10 years from now i'm really building sql constant care for 10 years from now where i firmly believe that the majority not all but the majority of clients will be out in the cloud not everyone but most will so yeah and plus two we aim for affordability and if you want to do it on premises oh my god the support workload is unbelievable so if you want to do it on premises your best bet is to go buy a conventional monitoring tool and then you're gonna be like but brent that's a thousand to two thousand dollars per server that i wanna monitor yeah and that's why they're so expensive because y'all are idiots you can't figure out how to install anything or configure it or keep it running so it costs a lot to pay all the support people to hold your hands and make sure that you don't shoot yourself in the foot costs a lot because you're incompetent anywho my wife brent how do you still have customers i'm not really sure uh so people stay in here until they get i have to feed in one bullet and good news my boss can't buy real bullets so they're blanks sp help dba that's called the consultant tool kit so you can go buy the consultant tool kit if you'd like to you can go buy that so in here with sp blitz index sp blitz yeah exactly sp blitz index will give you index recommendations like that's shown up there it says you uh clippy is recommending an index on location and then it includes about me well what i would really love for it to say in s p blitz index not in the missing index creation script but i would love for it to say how big the columns are because right now what i have to do is i have to look at the index recommendations and then switch over to another spreadsheet or another file to figure out how big the columns are let me show you my use case so i'm going to go back over to the change that we just made and see how in here and i'll zoom in to show you so you see how in here it says hey high value missing index it's on the stack overflow database on the users table and you're doing an equality search on display name well how big is display name and then if clippy wants a whole bunch of includes how big are they in my work as a consultant sometimes people will have to send me the data like out of sp blitz index and they'll have me re-analyze and re-juggle their indexes disconnected like i never get access to their production systems in these scenarios well when i do that i kind of need to know how big the columns are so i don't go and recommend an index that's on an nvarcaremax because you know how some how it is with column naming sometimes somebody will have a column called is deleted or is employee and you'll be like oh i bet that's a bit and then you go in and look at it and it's xml and you're like what in the sam heck is going on here so that's the goal that we want to achieve and then i cracked open the code and i went oh sweet potato this is going to be a lot harder than it looks well that does not scare off people like greg so greg says i'm on it and he's added stuff like this so right now the way that s p blitz index works is it generates a bunch of temp tables generates temp temp tables temp tables and then it works off those temp tables instead of working off of the index dmvs directly we just pull a bunch of stuff out of the index dmvs and then cycle through them in the temp tables so that we don't have to hold locks because you wouldn't believe how often some people create and drop objects on the fly i've seen people that are creating dozens of tables per second so in here greg says we're going to create two columns in the temp table instead of just listing equality columns on the index recommendation we're also going to go create equality columns with data type and then same thing with inequality columns and it looks like he didn't do it on included columns that's okay i'm going to start there we'll start there and see how it goes so then let's see what else let's try first running it because i've looked at this code a long time ago and i don't want to make y'all sit through as i go through and examine it again let's just go first see if we can get it oh he says included columns in the next line and it is oh yes oh that's fantastic look at you go so let's go get the code because i've reviewed it once already and i was quite happy with it but we just had a bug in it that around nulls so let's see let's get the raw version of it let's copy paste it out and then let's get paste it in over here paste execute so we've got his new version of sp blitz index in there now i actually have an index recommendation already in here so let's just start with this let's see it probably won't work because it probably doesn't have the show all missing index okay that's cool no problem so what we'll do is we'll go generate a missing index request like we had in the issue let's come back over here because i have a script in here that will do it copy and paste first let's drop the indexes that we already have just to be careful then let's go run a query 10 times and this query generates a missing index request then let's go run sp blitz index table name users [Music] so it's empty string so we got a problem in here where it's not actually generating the right missing index request it's not saying what columns it's on okay time to go spelunking now greg's already heard this lecture but i'm going to give it again to folks who aren't here when you run an open source project uh sometimes you'll you'll have different standards for code you may have like if i put in the issue hey here's the problem when you run this this comes out if somebody submits a pull request and the code doesn't work at the very first time that i test it i'll often push it back and go well there's a repro set of scripts in there right on the blog post itself go take a look at those and see if you can make it work here though it didn't it immediately failed the very first test that i tried but you know what greg has put so much work into this and i want this code so bad that now you're gonna go see me spelunking so now we're gonna go way deeper into this one than we did in the previous pull request because we have some work to do now i have to go see where the string is being built what contents are exactly greg is like works on my machine i have to go see where the string is being built and see why it's showing up as null on my machine see what's going on with it i don't know if it's a null that's getting inserted in there somewhere or what the deal is let's go find out i need to change the setting on one of my cameras so there that goes and off we go microsoft yes it's uh it's excellent living i truly enjoy it so let's go back to greg's pull request code because i don't know that i have uh oh i can just script it out again from the master database so let's go back over into the master database go into sp blitz index and script this little fella out so now um now i got to figure out what's inside that missing index recommendation table at the time that it's being built so greg's code does let's go over and take a look at what he changed inside here so let's go actually do this and we'll look at the pull requests you can go fix it the right way i well for me it's i want to make as few changes as possible in order to make it work the right way is probably a little bit of a stretch i don't know if it's where the equality is with data if it's in the problem with the equality columns or the inequality columns i'm just going to copy this and then i'm going to go look in the code for where it's being set uh so control f here's where we're creating the table now let's go down to where it's being set all right so case when do do do case when equality columns is not null else empty space oh so it's probably being it's probably not set let's see here let's see where else so this oh we're doing it inside dynamic sql um i didn't make that face for greg no no i mean i made that face work because i'm going to have some work to do here so it's we're inserting stuff into where's the table that were missing indexes so the table's name that has all this stuff is missing indexes so what i'm going to do my very first step in troubleshooting is i'm going to go select out the contents of that missing indexes table and see what we're working with i'm just going to do a plain old select star for missing indexes so yes i should also say i think sp blitz index has a debug parameter let's go see if i'm going to say come back over here debug equals 1. if we get lucky let's see if the debug outputs the missing index recommendations and it does god bless whoever wrote this because it wasn't me this was originally written by kendra little so if i scroll across on here oh yeah there we go [Music] now i don't know yet why it's null so here's the first strike i can see there are a couple things inside here the about me is populated the with data type is null these are also nulls so now i know that the reason why the index definition is null is because for some reason these columns haven't been populated okay so here's the deal i'm actually going to accept greg's pull request because i want to work on it and start making changes i know somewhere out there somebody's going to say brent there's a way that you can use github and you can work inside greg's pull request before you accept it in the development and i'm sure you're right but i have no freaking idea how to do that right now and i'm not about to learn when i'm trying to get across the finish line for something else yeah yeah because you don't even have to worry about data accuracy over in nosql nulls are expected so i'm going to go ahead and accept greg's pull request and then i'm going to go create another branch over in github where i'm going to continue to work on greg's stuff i will check in the fixes by the end of the day this is something that i wouldn't uh recommend people do out in the real world but because it's a hobby project sure why not so let's take greg's pull request so i'm not going to do it over from github desktop i'm going to say do you want to pull request into a different branch no greg i'm going to knock it out right here today oh i'm going to make this work i'm going to be like tim gunn tim gunn on project runway or now making the cut who i'm a tremendous fan of and true story i had a dream about him the other night where i bought his used mazda miata which tells you some strange things i don't even think tim gunn has a driver's license so i'm going to approve this isn't quite working yet but it's close enough for government work and horseshoes so i'm going to accept it and start working on a tweak to it to it copy approve or submit and then let's go over to our let's assign greg to it just to make sure so reviewers assignees that's all good let's put it under a milestone of this the issue is 2227 so let's link this 2227 and then let's merge the pull request confirm the merge put a comment in there and then come back over to the issue itself so the assignee will be mr dodds oh why won't it let me put oh that kind of blows all right so in that case i won't i'll put myself on there no spicy food before bedtime uh why is it so spicy i don't know how many other uh how many of you other than me and richie watch tick tock andy are you on tick tock yet andy leonard are you on tick-tock uh i bet your kids are on tick-tock that's probably how that works and it's uh once you get in there it's really really addictive okay so i'm going to reopen the issue um uh thanks for the pull request it'll be in the lord there's stuff in there you would like i promise you uh it'll be in the august release with credit to you in the release notes so put that in there i'm going to update the release notes just because i'm in here uh i'm still catching up on twitter was idiots for for closing that product so improvement um added uh data types to missing index requests number 2227 i think that was yeah 2227. uh thanks greg whoops put this in over here uh paste and then copy this over here copy and paste this oh shoot here we'll just put that in there and then i can backspace over it thanks value customer greg dot so it's in there twice they must be a really valued customer we'll say really valued customer all right so we'll save that now let's go in and figure out what's going on on this thing so i got to go create my own pull request or i got to go create my own branch so let's switch back over to the dev branch and get the latest changes from the development branch then i'm going to start a new branch for 2227. missing or sp blitz index missing request missing index data types new branch yep i'm sure and now let's go open that up into s people it's index so now we have the new version of sp blitz index i'm just going to close other other things just to make sure that i've got the right one and make sure that it's in master i'm going to go put it into production and then i'm going to run it again just to make sure that we're still having the same issue just because every now and then you you know weird stuff happens and things just suddenly work so in here what was the linus torvalds quote uh debugging is not poking it randomly until it executes successfully i forget what he what he calls it or like pushing randomly until it compiles i just loved that quote um so missing indexes so right now we have the sad trombone problem where this stuff's null now the reason why i wanted to take this first as it is is jiggling code until it unbreaks yes i love that that's exactly what it was jiggling until it unbreaks god that was awesome um so uh the first thing that i wanted to do was i want to do just a little bit of defensive programming i bet that there are edge cases where this code is going to break and it's going to produce nulls inside these columns so what i want to do is i want to first be a little bit defensive and assume that it does produce nulls sometimes and use a coalesce i want to take a non-null value if the equalities the the ones with data types aren't somehow populated i want to defend myself just a little so to do that i'm going to go into the code and i'm going to say i'm trying to hide the dang results column there we go i'm going to do a search in here for where we use it so in here it says uh here's where we're producing the column for missing index requests what we're saying is if this is not null then use that column but instead of just using the one with data type i'm going to use a coalesce here so i'm going to say when whoops when coalesce i want to use greg's first but if it doesn't work i want to use plain old equality columns so here what will happen is is if we have this error again in the wild if they passed in this is what the coalesce command does is if this actually has non-null value use it if not try to use this instead so then i'm going to do the same exact thing copy that out over here where i populate the string where i go and put i i'm not doing jack around here where sql server populates that string describing whoopsie it populates the string describing what the index is so let's go put that in over here now i have to do the exact same thing with the inequality piece so i'm going to take that exact same copy paste and i'm just going to change this to inequality and inequality columns copy it again i could replace the whole case statement with coalesce oh greg oh i like that oh greg you're right i absolutely like that and i will absolutely take it that's i like that even better so we'll say as coalesce this and then empty string so that's perfect then i'm going to put a space inside there because i also need to add a space i think after the list of equality columns okay that there we go he says you need the word equality oh good catch yes so we'll say an equality let's let's see how we did it in the original version of it i'm going to scroll across to the inequality ones to see how it was done okay good it was done with inequality and then semicolon in a space okay i love it so this works now i'm going to take this paste it in twice plus and plus out here this is now going to be inequality this is going to be rose welcome to the club from youtube inequality columns and then this is going to be included columns with data type okay yep included columns with data type and included columns and then this is includes all right nicole says it will always return not it will always return not null what did i do here oh nice nicely done good catch everyone yes so what this needs to be is moved whoopsie daisy hold on uh remove this needs to be moved over to the beginning so cut and then paste then same thing over here cut and then paste and then cut this is why i do live coding on youtube because it include improves my code quality because y'all are way better than intellisense you're only marginally better than intelsense so it looks like we've got all of the stuff from inside there just looking across the end um at the that should he says that should give you a different result is that good uh to do to so greg is this what we want now um the way that this is set up what's intellisense okay uh if you have no inequality then it'll still oh good greg that's so fantastic yes i love it okay so uh how do i wanna do that [Music] um case when damn it then you know really what i should do is i should really do the case i really don't want to do it this way um what the hell yeah assumes that string plus null equals null um so you know what i'm gonna do i'm gonna go back to the case i'm gonna go back to the case version and i know y'all aren't gonna like that but i like the case version better so i'm gonna close this without saving it and i'm gonna go back to the case version because i think it's going to be more intuitive for people to read when i die and they have to inherit this code god i hope i die soon just kidding so what i'm going to say is so here i hit ctrl z right so i'm going to say case when coalesce and then this comma comma is not null so this way i'm going to be able to say it'll just be a little bit more intuitive for the readers this way so we will be harvested when the aliens richie i'll leave you in my will so we have when this is not null then now i'm going to dump the coalesce inside there coalesce and paste this back in and then end plus and then now i can why are you fuzzy underlining oh because i'm missing a parenthesis okay that's cool there you go all right so now i can take that exact same kind of thing um and i can do it twice more for whoops twice more for the inequality columns which is fair because it's not my technical skills that are keeping you tuned tuned in so now i can do the same thing with inequality columns and i can say in in in in and then do it in the label as well in equality then i can switch it to includes includes includes includes and while we're in here i'm really tempted to change this it always says includes and i'm really tempted to just change it to say include because that's what the uh sql server syntax is so i'm actually going to change it to that that's technically a breaking change but roll okay so we got that now i don't need a plus at the end of that little fella then i think we're good i'm going to give y'all a moment to decide whether or not we're actually good with that [Music] we'll see what you think of that looks like clippy is okay with it um i think we're good i'll give you a uh a little countdown there to find bugs no pressure no pressure [Music] okay so if clippy is happy then who might argue all right i can't i think greg had to do this at several places inside the code so what i'm going to do is i'm going to do a control f first i'm going to copy this uh just so that i can reuse it again later actually i'll copy the whole shebang i'll copy this whole thing and then i'm gonna do a ctrl f to search for equality columns of data type to see if it happened anywhere else in the code and that's where we insert it into the table and then we'll go down further that's okay that's okay okay perfect that looks good so now all i've done here at this point is i've just done defensive coding i haven't even fixed why it's null yet one battle at a time so we'll go ahead and save that put it into production and then try to run it so anyone can get a beta copy so the nice thing with github is because all the code's public it's out it's a public repo anybody can go get the dev branch whenever they want so let's go execute sp blitz index again now with this the first check that i'm just going to go do is just to see that we still have the defensive null problem and we do this is good this is what i want to see location is populated but this column is not this is where our defensive programming comes in so did it work so down here if we go further down yes so the good news is is that now the index definition is populated the bad news is it's still wrong so uh when i say wrong i just mean that it doesn't have the data types included so now we climb the first hill now let's climb the second hill second hill is going to be how do we find out why that's null and how would we get it correct so to do that let's go back in and look at the code now we've got to take a second pass in here we've got to go back and find where that's getting populated and we have to go see why it isn't getting populated correctly so in here we're saying get this stuff as equality columns with data type so we're what the heck is this so if i go it's coming from there's no from no it is column names with data types so that's going to be a cte up here i bet column names with data types here it is so collect column names with data types as holy potato this is ugly you know what's good though is it's not hitting a temp table it's not hitting a temp table which means i can get that dynamic sql and run it separately now maybe the dynamic sql is coming out in the messages tab of sp blitz index already i don't honestly know let's go look at messages and see how lucky we are so that the miss the one that i'm looking for is column names with data types he says it's in the messages so let's go copy this out into a new window column names with data types all right here we go so here's the query that's running let's copy paste that out and copy paste there's a little bit of a bug inside there so we'll just pop that out and let's see what this produces i don't even know it's actually going to compile execute yay okay so incorrect syntax near the keyword into well you know you so there let's do that and then this let's try it again that that is fine let's just try um null as and then you let's try you [Music] [Applause] um but on the flip side okay so at least now we have dynamic sql that we can go quickly reproduce the problem without going all the way through s people its index next now we got to find out what's populating that and why that's null the journey continues so the one with data types select column names replace column names so what is up in this cte here let's see what's going on so where t y name is blah blah blah inner column name max length so let's see it looks like we're doing a case based off of t y name let's get t y name and see what it is just to see what the contents are because maybe there's something different about mine same thing with we're not doing any null protection inside here we're not doing any coalescing uh so if one of these is null we're going to be screwed so let's get out uh ty name co scale co precision co max length let's see if there's anything else that we're using in here to do this generation that looks like it's probably it now i have fuzzy underlinings great the hell you mean you couldn't be bound you're right there um what on my life uh select oh because it's inside is there another from inside here where's ty all right so let's go see control oh assist types cis types as user type id okay let's just start by getting users why are you not being able to be bound oh you know what let's just hit execute oh couldn't find stored procedure what the what what the what could not find stored procedure it's the select at the end of line two there's another select stuff select but i added the stuff in in the beginning let me let me cut it back out just to make sure what we're dealing with here oh my oh goodness that's wonderful okay no problem um j james goldfield oh it's an inner query james goldfield welcome to the club from youtube okay so ah okay so i have an inner query is the inner query only producing one thing it looks like it is let's see if we get lucky and we can do this because it could be no that's the stuff isn't going to work [Music] okay so instead of getting all this stuff i'm going to have to write a new query i'm going to say from missing index details go over to the types let's go move this down here so let's say uh go select he says i think you can just copy the inner query out oh that's not a bad idea let me try that uh skip this select and the select and [Music] i'm gonna guess there so oh you know what why don't i be smart and why don't i hover my mouse over these so i can see where the don't you just love that when you can't see where the ending parameter ending parentheses is all right brace yourself i'm gonna have to go out to 100 i'm going to i'm going to maximum suck she's going from suck to blow all right there it is copy and then paste uh uh uh okay we just won't do a filter for the indexes we're not gonna we're gonna take that out completely execute oh take out the for xml path now i can get select ty name let's see if that works no it does not okay there should okay yep uh we're not gonna fix that quickly okay so instead i'm going to take a different tack i'm going to zoom back in so that you all can see it better instead of trying to go through that query what i'm going to do is i'm going to jump out and write a new query from scratch to see what it is that we're dealing with so the inner query says from missing index details cross apply some other stuff for the like equality searches alt f4 i will i will give you that so let's do a quick just select to just go see if i can get stuff inside here whoops select so let's see what we get here uh uh where uh i don't know that i really want that um god i don't want any i don't want to deal with any of this crap today uh i'm not dealing with any of this crap today uh so it's oh man okay so and join where i d inner id equals i'm not going to do that i'm not going to do that and i'm not going to do that let's delete all three of those let's delete all those out and see if this produces any rows and it does not yep okay so and cgs you are correct so i am i am not gonna get this shipped today i'm looking at this and i'm like ah so okay so let me be fair though okay so one of the things that i said is i think that uh there's no null protection inside here so let me step way back so there's no null protection inside here when we stuff like these cases so what if i go i do some basic null detection just to see when some of these are null if it's null then replace it with whatever the column name is so let's actually try that um it's still gonna suck it's still gonna suck but i'm gonna just try it to see what happens um there's one other thing that i could try to do as a quick check just to see if i could get it to reproduce i'm gonna us make sql server request another missing index and see if we have this problem with another missing index so let's say because i have a hunch that it has to do with an nvaricare max column so i'm going to say instead of select about me select display name and then i'm also going to say select display name and about me so let's go grab both of those the reason why i'm doing that is that display name is a short column it has a known length about me is an invercare max which has an a length of negative one and i think that might be what's screwing the column type detection so let's go run it again this time i'm not going to bother with the debug equals actually i will bother with the debug equals one um so greg says i don't know how it's working on your server um yeah so it's it's still not working here even and i should have known that it still wasn't gonna work because it doesn't work with location either okay um let's try what else could it be so i'm not in uh 2019 compat level as far as i know i'm just trying to think of like real fast things that i know that might break something or produce a different execution plan i'm just going to throw it into 2008 compat level i did but my compat level i have a step in my jobs whenever i do restores every time i do a restore i automatically oh i'm in 2013 though every time i do a restore i reset the compat level so that kind of helps we're going to pop into 2008 and say okay just out of curiosity and i don't know that i may have to redo those queries let's redo those and then redo sp blitz index and then let's see what we've got in here coming down a little for the right says you're the man so nope it still does not work so it doesn't work in 2008 combat mode okay we ruled that out this isn't a case sensitive database let's try creating one new table so i'm going to create a new table from scratch just in case it's anything related to the stack overflow tables clippysitebranozar.com because i just published a blog post where i have clippy go create a bunch of missing index recommendations very funny video highly recommended that'll be all throughout my social media stream this week too as well i'm putting through a bunch of uh best of type pieces so let's go and i'll even create a new database just to to make sure it's not that create database clippy and then we'll go use clippy and then we so there's one other edge case possibility which could be that i have a copy of sp blitz index in that database if i go in and look at stored procedures i do not so that's not it so we're in clippy now i'm going to go build a table with a whole bunch of columns and insert data i say a whole bunch six columns and then after it finishes i'm going to go have clippy uh build a bunch of missing index requests uh so we'll do go 10 there so i'll take a second to go put in 10 million rows and then execute so this should produce 10 missing index requests now i'm going to switch over to clippy and i'll say the dining room table boy it would be really cool if this worked and [Music] still no dice so we still don't get equality columns with data type there okay so i don't know off the top of my head why that isn't working and it's one of those agamon says wasn't there a join to a database named stack overflow in there i'm not sure oh when you saw the dynamic sql the dynamic sql is based on whatever database i'm running it in but that disappears whenever i switch over to sp blitz index somewhere else okay so i'm at a tough point there i don't know like when i look at that code i'm like oh it would be really cool if it did work um but i can't get it to work so and it's one of those where i just got to make a judgment call about how long a depth i want to go in there so the next layer to which that i would want to go is i would want to put in coalesce checking inside of each one of these to try to figure out which column was screwing us up i'm going to take i'm going to zoom out and i'm going to look at the code just to see if there's anything that jumps out at me just to see in an edge case um agamon says i just got the code from github the join is there okay so let's go c let's go get the so this is the original code from github if i go look at stack overflow what what all right so hold on here now this is one of an interesting i don't use this feature very often but there's a feature in github called blame i'm not really a fan of blaming people we're gonna blame somebody today so we are going to go open up and see in github where that is and who did it because that is some bf so we're going to go look at code and then we're going to go look over at sp blitz index and then we're going to go do blame over here on the right hand side is blame and when you click the blame button github goes and generates a list of every line in the code so i can do a control f in here for stack overflow and now i can see here's the line stack overflow over on the left hand side we can see who did it if i scroll up just a little bit i can see the pull request where this was done greg that's fair that's fair i'll take that i will totally take that so ah this is why i really should not have merged it into the dev branch i should didn't i start this whole live stream by talking about how the code quality has been kind of sketchy lately and how i needed to do deeper review well this is us doing deeper review and catching these kinds of things so who was it that caught that stack overflow now i got to scroll back in the chat and i got to see who caught it because i want to see who it was because they deserve a very agamon agamon meraki agamon i'm going to move the chat thing over here just so that everybody can see it so right here agamon meraki agamon i want you to send me an email if you go to brentozar.com and click contact up at the top agamon i'm gonna give you a live class season pass as a thank you for catching that because that is heroic you deserve a big round round of applause it's just absolutely fantastic so big cheers there for agamom you just won yourself a live class season pass which is kind of cool because i have classes tomorrow too i have fundamentals of parameter sniffing tomorrow and then wednesday thursday and friday that is absol a concur entirely so very good job there absolutely fantastic so now let's come back over here and let's go fix that because that is terrifying um so let's go fix that and you notice there's there's a stack overflow one of one okay thank god it was only in one place so now let's go back over to the code that makes me wonder what it was before it was stack overflow i'm guessing that it was just looking at the stuff below it is stack overflow sys columns and so let's see what it was before that so i'm going to copy this out and then i'm going to go get the mask i'm going to go get the a prior branch of this code i'm going to go back over on my mac just because i happen to have it over here on my mac and then go into sp blitz index so i am going to go copy that on my desktop just to go drag it over there and paste this in over here so that i can open it on my desktop and then i'll open it with a text editor just to put it over there and then ctrl f search for that string uh otherwise let's try this otherwise or let's just search for d sql uh cuts it out maybe oh it's all your new code oh oh oh okay all right in that case in that case uh so it doesn't say that we're checking oh this is this doesn't have a database prefix on it so i think we're okay with this um uh i don't know if greg has a live class season pass i don't know greg do you have a live class season pass because if not we're going to give you one too one of the two um so i i would open up the membership site and go look if you have a live class season pass except then it would be up on the screen everyone's live class season passes and we wouldn't want that but greg if you don't have one shoot me an email uh and i'll get you hooked up with one as well and the only reason i'm telling you to send me emails just that i don't forget during the live webcast work got you one in the last black friday sale okay excellent okay so we've got that so i took out cis columns this is so awesome that's so cool that we actually uh figured that out um so wiring says anyone tell me how to ask questions we're not really adding question answer inside this webcast unfortunately we i have enough questions on my hand as it is all right so we have this new change to sp blitz index that now no longer has the stack overflow database so cisco most people would uh google but i i like how you just want to ask the presenter to drop everything that they're doing and tell you the answer the answer is it's for people who know how to google so if i go look over now and try it again if i go look over at sp blitz index table name dining room oh big money no whammies [Music] it worked um if i go so now let's do the users table and let's see users and i'm also going to pass in database name equal stack overflow 2013. so let's go see how that works and oh okay that's all right that's all right that's all right so what isn't happening here is what happens if you have to switch databases because i i kind of had a hunch that this was gonna happen so i went to welcome vincent from twitter from youtube so now it works when you're in the same database as the object you're examining but it doesn't work when you're in a different database like right here oops i am in the clippy database but the database that i'm examining is the stack overflow 2013 database so that's okay the reason why i wondered about that was that when i was looking at greg's code down here it doesn't prefix the database name and it when i saw stack overflow hard coded in there i was like oh that's true i bet we have to prefix these with the database name that we're examining so this just gets just a little bit harder not bad what we have to do welcome to the club james from youtube what we have to do is we have to prefix these objects with the database name that we want to examine i think we can just use the database name variable but i'm not bulletproof sure so what i'm going to do is i'm going to scroll down or i'm going to scroll around and look for is there yeah check this out see how we were doing this over inside the other objects we were putting in the database name i just need to copy paste this out and go put it next to the system tables my guess so this is just a guess but i think if i remember right these dmvs always reflect the database that can't be right either like i think if you have a use up at the top it's not so bad but i may have to fully prefix them so what i'm going to do good morning robert i'm just going to take this exact code copy paste it up and i'm going to spin out and go up to the other places where we're hitting things like sys columns so up here so up here we're going to go dump that in and then put the period inside there i also need a period let's copy that out and then here and let's see if there are other things so up here there's missing index details but down below we're not fully prefixing that so i'm going to kind of leave that as is it's kind of like what's that called um damn it what's the pro cargo cult cargo cult debugging so i'm going to use some cargo cult debugging here and if you've never heard of cargo cult debugging that's a fun thing to google as well so i know these are some of the things that you pick up when you're just watching somebody else's code as you see some of these kinds of tricks that they use and security things too for dynamic sql so okay so let's try that so we've got that in place now let's execute that to go put that code into production then let's run this again and see the thing that i'm looking to see is after we fixed it now can are we going to get the data types in our uh miss recommended missing indexes and as we scroll across [Applause] that is fantastic uh guard we're not really doing waring we're not doing questions and answers inside this webcast unfortunately i got a job to do so i'm kind of focusing on my job in this one now we're inching ever closer we got a little bit of a tiny problem here it's a little bit of a tiny problem we need commas on every column that's included after the first or i could just be lazy and put a space because after all it isn't like this is code that's actually going to get compiled by someone for my own evil purposes i'm going to say i'm oh i can see how included columns has a comma i'm going to kind of want to have that same technique here i want columns in between multiple columns commas in between multiple columns so for me to do that i'm going to go look at the technique that was used over on the included columns one and then go see if i can reproduce that exact same thing over in greg's new code so let's see what we got here so where did we populate the included columns list so this is column names with data types where are the column names without data types all right let's go let's go google for it atik welcome from uh youtube uh let's say uh equality columns and then uh drop table employee says leave it without the comma so the code doesn't compile on purpose it won't compile anyway because it's got the data type names inside there um greg's this from memory clippy does the comma oh you're right greg you're absolutely right um so clippy automatically furnishes the comma so i'm gonna have to get a little bit more complicated than that with data type okay so for me to figure out coalesce data okay so where are we getting oh i got to figure that out let's go back up top higher there it is okay so where we're doing this is here with this stuff i'm going to zoom out i'm going to have to zoom out in order to see all oh i don't have to really inequality name that's the data type what i'm going to do is at the end of the data type i'm going to shove a space i know i know i know i know i know but this gets me close enough that i'm going to deal with it greg says this is why you get the big box box so i'm going to get close enough by dealing with it by just putting a space inside here it doesn't have the comma but you know what i'm just going to deal with that i'm going to live with that and rock and roll and call it a day so now that we've got that let's double check it by putting in i'm going to do a couple of missing index requests with multiple columns in them so let's say i'll do the the users table so let's say where location equals antarctica and display name oh let me zoom in for y'all there we go from dbo users let's switch over into the oh we'll stay in the we'll fully prefix that's what we'll do stack overflow 2013 dbo users uh where location equals antarctica and display name equals uh jorus 23 because he's 23 on twitch first let's make sure that this actually produces a missing index request and it produces it yep good it produces it on two keys so that's good um and let's go that ten or uh ten times just oh i didn't really need the execution plan every time and now let's try our missing index check i'm gonna take off the debug i'm just going to go look at the regular result sets and execute that and so there are a couple things that i want to make sure that work so over here on the left hand side i want to make sure that the indexes are done with the data types inside here i know people are going to yell because they don't have commas and i'm going to be like suck it up buttercup if you want the commas you go in there and put them in because i would rather have the data types on there then if i scroll across i also want to make sure that the create t sql is still correct that it doesn't have anything about the data types and we have winners across both of these so so far so good let's also do sp blitz index with just uh mode equals four and then execute don't don't be fooled he's not really paying attention regardless of whatever work he's doing then if i look at over here so this is the database wide missing index recommendations yes because a fill factor of 100 is the right thing to do then if i go across in here this works beautifully it shows the right data types if i come across here and look at the create index statement let's go fill factor 0 is better let's go look at this so beautiful the missing index recommendations do not have that data type in them that's fantastic last thing that we're going to do is mode equals three mode equals three gets you the list of missing index requests and okay so what's going on inside here all queries combined with a union intersect or accept operator must have their equal number of expressions damn so close but yet so far so we've been going at this for a little bit and i think i'm still going to fix this because i don't think that this is going to be hard i think that this is just going to be a matter of somebody did a select star somewhere inside of mode three and i i didn't even flag this earlier for greg that there's a mode three what mode three does in sp blitz index is it lists out all of your missing indexes and just puts them in one grid so that it's easier to digest i will fix this but we're going to take a bio break first so first a quick shout out to this week's sponsor so quest software has put together an ebook with advice from me janice griffin griffith and penal dave now knowing you see me coding all the time live so you may not actually want to take my advice so that's kind of how that works uh but you can go get that for free over at brentozar.com go up slash optimization i should have said too when rishi was joking about this shirt i i don't surf but i did go to north shore in hawaii so i kind of stood around and watched other people surf oh my god those people are insane that is absolutely bananas those waves will kill you it's incredible how large the waves are we happen to be there right after a storm and so we got to watch some really 10 15 20 foot waves roll through and i'm like you people are suicidal that is just insanely hard uh so we'll take a short five minute bio break here i'm going to re refill my espresso well we're down to decaf though uh decaf espresso for one more shot before we go in and do the final crunch to go fix mode three and then yeah i know i haven't had to write ui this is actually one of the reasons i switched over to sql um so we'll do a five minute bio break we'll come back in five minutes and then we'll finish up mode three and then i'll make the decision as to whether or not i'm going to do a release for this month now um yeah so and then after that two at the end of the webcast i'll give away another coupon i'll let y'all decide and y'all tell me over in chat which class you would want to get the coupon on we're not going to do a live class season pass coupon but pick any one of my classes and i'll do a coupon when we come back from the break to say thanks to y'all for helping debug my code and all that um yeah i am gonna do a second breakfast too the coffee shop downstairs opens at eight o'clock i am totally going down there and getting myself a lox and bagel because i we've done some good coding today so i'll see y'all back in here in five minutes [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] so [Music] do [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] all right party people so now i said before we uh go further i'll give you all a coupon code here's the deal um you tell me which class you want a coupon for and i'm not gonna do the live class season pass or the recorded class season pass but if we get up to 50 people chatting in here like if in twitch specifically i love youtube but the youtube chatters don't count i've always wanted to hit a 50 simultaneous chatters thing inside twitch if we get to 50 chatters inside twitch and i'll look at the little border cgs says what happened to the streaming quality it's okay here you might have really it went blurry for y'all wow oh it must cgs it might just be on your end it seems to work okay um so if we get to 50 people chatting and when you're in there tell me which course you want it for and we'll do i'm going to do 75 off just because why not because y'all are going to go like crazy 75 off and if we hit 50 people in here chatting i'll do that for the recorded class season pass otherwise you'll have to pick one specific class so let's go look over in twitch i'm going to pull up my little dashboard to see how many of y'all are chatting because i think i can see it inside here somewhere god only knows exactly where creator dashboard let's see here insights channel analytics maybe stream summary uh that doesn't seem to say it stream manager maybe stream manager number of chatters nope it doesn't say number of chatters so we'll just see if we can get uh i don't see anything if it says where the chatters are well we'll just see we'll we'll watch yes immediate mike says you think you got 83 we have 83 people chatting all together but only a few of them have been chatting simultaneously so that's where the the chatting numbers uh come in so uh i'm going to either let's see here we should look and see a couple people remember i said no chatting over on youtube because that doesn't really help oh sac theory wants a database administrator uh kurt says what about those who already have a season pass you should be very happy and thankful right you should be cool d sanchez says the senior class cargo cult debugging that's very good royce i wish i could give a countdown i'm trying to figure out where i can go in uh twitch to go find out to see if it shows the number of simultaneous chatters um let's see here it's starting to look like a game stream it's indecipherable which is fine i mean you know that's kind of okay roy says twitch so twitch is the other place where i do streaming that makes a big difference there let me see if i can check how many people are simultaneously chatting uh how many people are chatting now in twitch in my channel see if we can just google and find it oh edie says mastering query tuning and the mastering classes are okay too you can choose a mastering class if that's what most of y'all choose so let's see here understanding viewer account versus viewer list how to manage harassment in twitch yeah no i like it when y'all are harassing me in terms of high chat counts it doesn't seem to show me so i'm just gonna go with the recorded class season pass let's go for it let's get y'all a coupon for the recorded class season pass and i'm gonna do 75 percent off so here we go mutant says show more clippy please um so let's see here let's give you a coupon and let's say thank you twitch chatters will be the name of the coupon it's going to be recurring product 75 off it expires today and it is for just the recorded class season pass let's go put that in there uh season pass make sure that that works uh and then is that everything i think i need to put that in recorded class usage limits yep just one per user i think that should do it and let's see so uh let me and i'm gonna go try to use it myself first i'm gonna start up an incognito decision no she asks questions when i give doubt discounts because true story we have a banner hanging in our house because i love to give stuff away like i do open source work i do all kinds of streaming like i don't charge y'all for any of this stuff um we have a banner in our house with the name of the vacation house that we want and i'm not going to say what it is because i don't want you all tracking down where i am but it's the banner says can't buy blank with free as in i can't do free stuff and then you know buy enough to pay off the vacation house so there you go so now let me try and buy it with a coupon code so that was why i wanted to say that does she likes when i give away stuff for free not terribly let's go try and i'm doing it anyway because i like y'all y'all are good people especially agamon or wow holy smokes already gave him a live class season pass uh just a second ago so because he emailed in so thank you again for that code uh let's go view my cart let's try to apply that coupon and make sure that the coupon works it does all right sweet so uh the coupon code is thank you twitch chatters so the coupon code is thank you twitch chatters why for some reason it's not showing over on the screen let me go pop into my twitch channel and then go drop it off into code so coupon code thank you twitch chatter so that's down there on the bottom coupon code is thank you twitch chatters and that's good for my recorded class season pass if we go into brentozar.com and then we go to up at the top you go to training and then down near the bottom there is a recorded class season pass so this gets you access to all my fundamentals classes the senior dba class the dba fundamentals class dba job interview q a it doesn't give you the mastering classes because those are kind of like my cadillac i don't know why we still say cadillac cadillac isn't necessarily the best anymore but it gets you how i use the first responder kit fundamental all the fundamentals of performance tuning classes including tomorrow's the new updated recording this is when we say life is not fair fundamentals of server tuning and all the dba classes there so that is thank you twitch chatters uh you're welcome again uh agamon that was uh fantastic of your uh your stuff they're the camaros of training um so the one thing that i would ask is just to hit my twitch 50 chatters number that i've wanted to hit for the longest time if you use this say thank you say thank you over in chat and this will expire in one hour so you have one hour to get it and it's going to end up being like 125 bucks after the 75 off you're welcome jedi mind gorilla thanks y'all it's always fun uh coding with lots of y'all in here so cheers all right and i'll give you all a moment to go put that in and i'll talk to the europeans who unfortunately uh aren't able to pull this off so it kind of sucks for that i would love to be able to do sales into the eu it's just that damn privacy the shield privacy shield thing that they just struck down i was working really hard to get to the point where this year we could actually do sales to the eu um gao bunga says can you take a vpn no because our credit card processor is even programmed in to say that if the credit card's billing address is in any eu country or the economic area it won't accept your payment so it's not even just the address that you put it mike oh thank you very cool i appreciate it it doesn't count but it's always good to see you in here anyway so that's kind of cool um oh thank you robert i appreciate that too as well so you're welcome y'all i hope you enjoy that one we'll see how that goes so next [Laughter] put it on put it on my papa's tab um uh so yes so there you go uh so now let's go see we did we were working on greg's pull request for sp blitz index uh you're welcome sac theory good glad y'all are enjoying that and my wife doesn't wake up for another hour and a half so that's why i said the coupon expires in an hour because as long as y'all are all done uh buying it then we're simply you're like on i'm sorry i i didn't know that i gave them a coupon for 75 off i'm gonna have to work on my excuse a little bit no cgs we do we do actually you can do that during checkout we unfortunately just don't give you access until we actually get the money and if it's from the eu then we just kind of have to send it back all right so the bug is supposed to be 7.5 that's actually a good one oh not the dba you're looking for that i might actually i might be able to get away with that one so when plus she's really happy because i worked a weekend gig this weekend our gen our rule is whenever i work a weekend gig she gets to keep the money because i'm like if if i would work seven days a week if she passed away i would just work continuously d sanchez it includes that that's included in there too so you can get that also inside the recorded class season pass mike you're on twitch dang holy smokes you're on twitch as well um does she watch the twitch sessions she does if it's something like clippy like i'll tell her because she worked on the clippy costume with me helping it helping me get it all set up um so she's jewelry she did get jewelry this weekend she's very very excited about my weekend engagement she had the piece picked out that she wanted and all that stuff so when last we met we were working on sp blitz index and we started i know yeah well with the sun i started right before the sunrise so it ended up working out kind of okay um in my coffee shop downstairs opens in 20 minutes and i thought usually i quit streaming around 8 a.m so that i can go downstairs and get coffee and locks and bagel i can't do that today because yesterday we ordered pizza and we're always really good to the pizza hut delivery drivers really to any restaurant staff this is going to sound crazy but we tip 50 to 100 right now because of the pandemic because i'm like restaurant people are basically first responders right now and i want to make sure that they're taken care of so our pizza hut delivery driver we generally have the same driver every time we only order pizza like once every two or three weeks but we have the same delivery driver and he we only ordered two pizzas and he brought us four pizzas yesterday he's like thank you for taking care of us so well you know the staff wanted to say thank you and i'm like damn for well it's because it was open early on during the pandemics like a lot of our local independent kind of shops weren't open uh just because of all the problems with coming back up um so good good gov dba i'm a huge believer in it you're for for right now those first responders so they brought us four pieces so we gave one pizza to the desk staff at the building and but now we still have another two pizzas left over in the fridge so i got i got a lot of cold pizza that i need to eat i adore cold pizza it's just absolutely wonderful not here they take the door so that's what they're they're they're trained with the app right now they're trained to do no contact delivery by default that they're supposed to leave it on the door and then bail um but i pre i and i'm i would be fine with that except we live in a 40-story tall high-rise and you can't get in the front door without authentication so okay so now let's go in and fix mode 3 and sp blitz index we're so close to being able to ship this thing i still don't want to do a release tomorrow because we keep looking in and finding a little oddball bugs inside this thing let's go in and fix mode three so we added this new feature we greg greg dodd added this brand new feature inside of sp blitz index uh that lets us go through and add oh g surgeon has already figured it out damn g surgeon is the netherlands is representing tomorrow today all right so in around line 5282 let's go down there so it is is this the one that's mode three nope it's probably a little further down uh yeah here it is no that is that is mode three in missing index detail and he says add before zero as display order so oh this is one is display order so it's probably up higher or lower it's probably down here here we go so zero as display order we need three more nulls no no no okay so this would get it to compile i want to just make sure that we're not doing something oh greg i bet you greg look at you greg dang i love that you are just the bomb i missed a comma okay let me go back and check that oh good catch everyone good catch good catch oops i gotta put it in the right place okay so now let's see if this works so let's save it and let's execute it to go put it into production and let's run it again for mode equals three now the reason why this works is greg added a couple of new columns in here i love how he did this this is just cool greg put in a couple of new columns here with uh the old equality columns with their with no data type and then our new equality columns with the data type greg i love it i love how you did this because also this is going to work beautifully in the consultant tool kit you just did wonderfully all the way around so proud of you greg even though i say proud but i have nothing to do with your skills okay that's beautiful i like that that's fantastic i it shouldn't have any impact whatsoever on mode one but i'm just gonna run it on mode one great okay we're done okay so i like that sp blitz index change let's save that close it let's check it in on github so the changes that we made i'm just gonna scan down to look to make sure that my changes have their pants on okay i pretty well like that that looks good we're going to say we're going to go check in put in our check in request down here so this is 2227 sp blitz index missing index data type tweaks continuing work on 2227 commit and zoom out on that publish the branch do a pull request guy just love it that's fantastic i'm so glad we got that code in because i wanted that really bad uh this is reviewers where everyone on twitch uh assigned myself as the coder and then this was uh we're just i'm not even gonna say bugger enhancement it's just in sp blitz index it is in the august milestone create the pull request and uh how did it pop over to there did i accidentally click on something uh i did all right there we go merge the pull request confirm and let's go back over to the issue i think i had it i put this in the release notes already i think [Music] yep yeah good it's in the release notes so that's good i can close that let's go over to the issues list issues so now we are down to no open pull requests yeah i'm laughing instead of clapping it was supposed to be the clapping button all right um now uh we also have new issues that have come in including this right here uninstall does not remove a table now normally i would say to someone if this is important to you you should consider go coding it yourself uninstall have hasn't been modified in five months so of course it doesn't delete the new versions table okay cool if this is important to you you're welcome to submit a pull request to do this here's the contributing guide and go get the first responder kit org pull contributing contributing request contributing copy and close that out close that out paste if you don't want to do it no problem i'll close this in a few days thanks comment assign this person who wants this i do i should sound more passive-aggressive you're right the only reason that i don't sound more passive-aggressive is just because i'm so happy after having just done that incredible bug that is really cool i don't think we have any other uh labels on there no milestone there we go and that's done all right so i'm happy that we're right at 10 issues um i try to these days i'm trying to stay below 10 issues some of them are really hard to do like some of them we've been working on for quite a while but i'm happy with where we're at there right now that's pretty awesome actually so that is everything that i want to go knock out today i wanted to go close the four open pull requests and get me close to the point where we could do a release if we wanted to i don't think i'm going to that's that's true i don't think i'm going to do a release today i think i'm going to what i'm going to do is use the dev branch in my training classes on wednesday ah edie uh uh thank you welcome to the club i appreciate it so um that's so interesting in terms of a name edie dorou now i'm curious where that comes from um so i'm not going to do a a new deployment for a new release for it this week i'm going to use it use what we've got so far in my training classes on tuesday wednesday thursday friday exactly well not even you just me live on screen i tend to use the dev branches whenever i do the mastering classes so if it explodes i'll be able to laugh at it live during the classes i love finding things like bugs during training classes i think it's kind of fun so i'm good with that i'm going to stop here in terms of this week's stream and close that out and then i'm going to shift gears into my day job i kind of have a day off today quote unquote but i'm doing i'm not working client facing i teach training classes the rest of this week and then i teach mastering query tuning next week there's a demo that i need to update for mastering query tuning next week you're welcome jedi mind gorilla always good to see y'all github is it's really intimidating when you first uh use it uh cubelet yes absolutely i do tons um yeah github is is really worthwhile it's just also a little bit intimidating if you're not a developer if you're a sql server person it's really intimidating to use for the first time hey well and you know hey you've made the first twist jump if you've switched over to twitch you're already doing well so is github like jira no jira is more of a help desk tracking thing github is for source control so i can't remember if it's atlassian that does source control out of that jira company the company that does jira so thanks oh you're gonna live code oh i get to step in and watch you then too so thanks y'all and i will see you out and around andy leonard is also on twitch as well he does live streaming with azure data factory sql server integration services so if you want to learn about things like business intelligence and etl work go check out andy leonard andy leonard over on twitch so thanks y'all and i will see y'all later i don't i'm going to not do any other live streams through the course of this week because i have those training classes through the rest of the week but i'll be back on saturday at my regular scheduled time so thanks y'all and i will see y'all later adios [Music] you

Channel: Brent Ozar Unlimited

Views: 2,123

Rating: 5 out of 5

Keywords:

Id: i6lJmxIDEv8

Length: 168min 44sec (10124 seconds)

Published: Mon Aug 03 2020