[Music] Hey good morning party people I probably shouldn't yell when I get started right that's probably a bad idea good morning party people I have a Braille oh I shouldn't be touching that while I'm talking to y'all of a brand new camera set up over for a camera - I got this big whole tripod thing going on here with a pen arm just to make sure that it's not on my desk because I'd lean on the boy I'm guy so much that up work to do on that light but I lean on my desk a lot and I don't want the camera vibrating when I'm talking to y'all so good morning Spitfire right off RAF good morning you're in in early as well sir lead-up somebody said is a Jim Van Allen Jimbo oh wow diligent DBA malla Oh good to see you malla that's fantastic well catch up with you sometime in person - it's been not so long well Gregg good to see you again Matt oh it's good to see you again as always Jim I wanted to say Jim Mellon Van Alen on twitch I want to say you said he was early yesterday do I have a schedule up anywhere because I had one at one point and if I do I need to go make sure it's not up anymore because I don't I don't like go any specific time it's just whenever I'm up and around and I get things to up and running and I just want to set an expectation with anybody that they show up and then I'm not here so it's always tricky yeah it has been a while it's so it's I would love to have caught up with you a pass this time around that would have been such a good thing jim says five eight I thought five am was a normal oh yeah no I'm all over the place 5:00 a.m. would probably even be late for me usually I I'm up around 2:00 or 3:00 in the morning and then I hop in the shower I'll you know pedal around the office and then it depends on what all I've got going on out in there in the office things going on with clients I had a client on so normally I do this two-day sequel critical care thing and I had a client on Friday which means I've got their findings day on Monday I got to write up their findings at some point here this weekend but so this morning I am going to be live coding fixing bugs in the first responder kit and then depending on how time goes I may also show you darlings SP Human Events and write a blog post about that as well hmm I always used to consider myself team profiler you know someone who used profiler more than I did extended events and then I just woke up one day and I was like I haven't used profiler in years like I couldn't tell you the last time that I used sequel server profiler and I realize that I use extended events way more often than I use profiler and I never start an extended events trace from the GUI I use tools but these days Eric darling has this really slick SP Human Events and I'm gonna show that to y'all ins and write a blog post taking screenshots as I go so today you're gonna see me working in a Windows VM with WordPress github sequel server management studio I thought about doing it with Azure data studio because we've talked about some of y'all said they'd like to you'd like to see me working in Azure data studio when I'm doing my T sequel work but I just didn't quite have the time to set it up this morning I'm like asker it I'll just rock and roll with my existing easy live stream setup but change the brightness on one of my lights here just to make it a little easier one for y'all to see me when I pop in and start live coding and let's go ahead and well I should have some more my tasty beverage here first before I rock and roll hmm espresso it's what's for dinner Jim says favorite VM on Windows 10 Ida I don't use Windows 10 I I use Mac's I use Mac OS flow on the draw this morning only folks now I use Mac's so then I use VMware Fusion on to in order to create VMs and I tend to use Windows Server just whenever I do sequel server just because it's what the clients use so I tend to stick with the same exact thing good morning for PETA good to see you again all right well let's pop in and go get started so I'm in github in the first-responder kit I like that Brent is 3 timezones earlier than me but I'm the one still in my bathrobe I have oh this is almost too good to be true I have a company bathrobe should I go get my company bathrobe and should i code in my bathrobe because that would be rather amusing I think I still have it let me go see so I have a wireless microphone so I can walk around as I'm doing it it's either in my office closet or else it's in our entryway closet so the story behind it when we did a company retreat in Mexico we did we got company bathrobes for the entire staff and they have the logo on them and everything well I never use it I don't know why I never use it I mean I got a pretty good idea of why I never used it I start working at like 2:00 3:00 in the morning so obviously that doesn't make any sense for me to go through and start wearing my bathrobe let me see what's going on here I do have it oh yes okay so I got both mine and Erika's let's say mine is the long one there we go okay let me go pull this out Erika's gonna be like what the hell were you doing put this back and let's come back in here and dance it in today I gotta get it around microphone doesn't snag alright so we have oh I don't have the sash on let me change cameras here total actually let me give you the even better full love so there we go Todd live coding in the company bathroom this is my life so yeah so we had these made up for our trip to Mexico and then when you think about so we had like six seven people in Mexico is six seven people my company all together like the spouses everybody got these bathrobes and we had to take these down to Mexico there were like six seven a to these things and they way like you know it was an entire huge bag it was like a hundred pounds worth of bathrobes and I was like please God let let the TSA or will whoever open up my bag and find that I have like nine bathrobes and a bunch of swimsuits because that would be so baller sir why do you have like nine bathrobes that would have been so epic amazing I would have totally loved it diligent DB a mullah says it was a time when Kendra and just around the team yeah and we did it the year after I want to say Doug got a set too I think Doug and his wife got a set but yeah all kind of bunch of people have had company bathrobes now was pretty funny let's see here don't break the rules as I just downloaded somewhere it's equal stuff on my Mac what have i done i just wrote python 3 version i still it's been for me one of those things where like I can't get into Python only because I've it languages I really suck at learning languages and I'm I don't want to go in and invest more of my life in learning another language when I'm like I'm gonna coast on CT sequel through the rest like I'm gonna retire on T sequel I don't mind being the old guy in the corner who still works on the mainframe and if the mainframe for me is Microsoft sequel server I'm a hundred percent down with that like I don't have any problems with that whatsoever it's not that I don't love to learn I love learning and I just don't like learning languages I don't like debugging oh and of course what am I gonna do now what am I about to do debug that's exactly what I'm about to do so here we go so we have in here oh would be really cool if it I'll just have to step back a lot so that you can see the little and I feel like I'm looting so I can see you can see the company logo which is hilarious okay so what we have here is we have first responder kit org so this is the github repo where we where people could file bugs for the first responder kit and I swear to god people don't ever can it's rare that people contribute their own code what they do is come here and complain and they want stuff and then they don't ever actually do it but this morning I'm feeling generous so this person comes in here parlez-vous and says hey I would like to add an Excel Chintu this rule about tables in the master database there's this table that's used to store your Microsoft Dynamics Nevisian license file you will not find it in master if you bla bla bla bla bla so he just wants one thing changed in SP blitz and I said sure go for it that pull request would be great you're welcome if you would like that change you're welcome to make that change and it's like crickets you know nothing actually happens but it looks fairly straightforward so I'll go ahead and add that for this person so I'm gonna go ahead he didn't respond so I'll just assign myself instead of Johann there and then we'll put it in the milestone for this month's release now whenever I do work on a github issue so for those of you who aren't used to github and database source control and all that so it's funny I nsx I and she I guess I have a doctor's robe - I have a whole doctor's robe I have a stethoscope I did a dr. horrible so dr. horrible dr. horrible for costume for a Halloween one year so I have a whole dr. Hollow dr. horrible get up with the goggles and all that stuff grumpy identified - figured it was spot day T sequel at the spa yes yeah it's true I do have the the whole dr. horrible outfit so I so now I got to start a new branch in github for that issue so the way the github works is whenever you want to make a change to the code you branch the code off separately you make a new issue with the name of the branch or with the name of the issue that you make a new branch with the issue that you're on so here I'm gonna go create a new branch with hi welcome to pyro cat how cool of a name is that why am I getting doubles there that's really weird hi everyone from Brighton high or Estes well we'll just get doubles up there that is what it is I'm tempted to go hit refresh the cache hold on a second here let's refresh that just to get it and then refresh the cache of the current page and done and we'll see if that fixes that that might work all right so let's see here we got to create a new brain 24:26 SP blitz I'm thinking Playboy Mansion yeah exactly only a whole lot have a grumpy day we know that's a good point let's see here exception to master tables new branch so just a new branch is kind of like hitting file save as and saving your work under a different name now I'm gonna go open that code and I'm gonna this is what this person is looking for so let's go look I'm gonna copy that out and I'm just gonna put it in a new query just so that I can see what he's looking for there then I'm gonna go open SP Blitz so see temp and go see in the first-responder kick good morning from Brazil hope you're staying safe and healthy down there I know a good job of grossly oversimplifying how it works right I have a whole presentation at some point I should do on here about how it works makes it I try to do it just get four DBAs presentation and I talk about branching and conflicts and merges and all that so this person said said exception two tables in the master database I would like to add an exception did it it really would be helpful if they had some kind of thing about what I'm looking for in the code now they didn't they kind of sort of got me close to it they have this and named not in so I'm gonna do a control copy and I'm gonna hit ctrl F to go see and of course no such luck it's kind of how my life works so let's try just plain old command log let's see maybe if we find it just in command log Edwin good to see you Wow from Singapore very good to see you so we're doing safe and well down there too when do you get to come home when you get to come back to Canada any word on when you get to come back to Canada yet so let's see here we'll copy out this is the thing that that person wants to add so that so with us people it's the way that this works when we scroll up a little and I'll show you I'm going to drop the font size down just a little to make it easier to put a check on one screen hi dad John good to see you again from Serbia so the way that s people it's works is there's a temp table in SP Blitz hey let's step back and get the full bathrobe effect right so there's a temp table that we create at the very start of the stored procedure and then every time we have a check-in SP blitz all we're doing is we're just inserting a row if certain problems match so here what we do is we check in the master database looking for tables so here I'm saying in master systems are there any tables that weren't shipped by Microsoft with these names if they don't have these names then it's a problem because I don't want people storing stuff in the master database because we rarely restore that database when all hell breaks loose so he just wanted to add an exception for that table since that's the Dynamics Navision license I'm gonna put a node in here too just in case anybody ever comes back in and wants to see where this came from I'm gonna put a note in here linking to the github issue so I'm gonna go back over to the github issue copy paste the link and I'm gonna put a note in here at the end that last one is from dynamics or is the licensing one is the Dynamics NAV licensing table so now if somebody wants to know why we did that they can go put that into their web browser and go see more information Lord knows no one ever reads the documentation but that's okay so let's execute it just to make sure she works and then we'll run SP blitz first or SP blitz I'm also going to go create a table I'm gonna go create a table I can't believe someone would actually name a table that that's a mess up so let's say create table doo-doo-doo-doo doo-doo-doo-doo doo-doo-doo-doo to what inte we'll just leave it at that there we go create the table and then let's run SP blitz and we'll see if we get an alert about that table existing and you know to be honest I don't remember where tables in the master database is where that alert is I don't see it at first glance so what I'm gonna do is I'm gonna add another table with a very similar name so paste we'll call it due and then we'll see if that that one shows up in SP Blitz it should show up in SP polat's let's just double check and make sure to doo doo doo doo doo I know right yeah I feel the same way I'm like this this there's something smells about Oracle to me with this umm let's see do we have where we don't have anything about tables in the master David oh there it is yes okay cool whoo all right perfect so the one with a different name that's perfect so let's go throughout that table to drop both of those tables just so that we don't have those still sticking around and then let's go check in our work so let's save SB Blitz because this works successfully and I can close it now let's come back over to github so the github desktop I absolutely hi welcome from Pakistan and welcome to the club so in here I mean I totally love the github desktop and the automatic built-in clapping has two peas just just so you know maybe there's clay ping going on to here but that sounds kind of dirty and I especially me and my bathrobe I would like to stay clean so in here I love the built-in diffs inside of github desktop where it'll show you exactly what you changed because especially when I work on a Mac very often I'll accidentally introduce you dual characters for windows that it doesn't work very well so here I can see that everything that I changed is fairly straightforward let's go check in our work so let's say down at the bottom left here's our check-in is so it's on issue number 24 26 SP blitz exclude nav license table don't throw a warning about the nav license table and mass in the master database closes number 24 26 it just nice little thing that if you put closes or fixes or whatever inside the issue notes and then this gets merged that issue automatically gets closes which is closed which is pretty cool so let's commit hit publish it's gonna take us back over to the website when we go to do a pull request and so now just to make sure that it shows that I have one file so I also get the same diff over here which is pretty slick so now I can say the person who's assigned is me and the milestone will be July's release this is a an enhancement to SP blitz and then create a pull request which means I would like the person who maintains that repo that's me to pull my changes into the development branch and then I'm gonna merge it so I'm gonna say is cuz now I've switched roles now I'm hi I'm back I'm the github repo maintainer and then not the DBA are looking for I will totally do that I don't usually pay any attention to YouTube thumbnails I'm a terrible creator I mean when I say pay attention I I don't when I'm creating the videos I don't usually do that and I will for this one so we've done that pull request and now the issue will automatically disappear so the issues gone thus as a open-source maintainer though I also want to have it in my blog post when I announce a new version of the first responder kit I want in my blog post to include what change this is where I keep like the equivalent of the release notes so I already have a blog post started with the changes for July's release July's release will drop like the first week of July so I'm gonna link to that issue I'll say SP blitz changes fix ignore the Dynamics NAV well so when let me rephrase up when alerting about tables in the master database ignore the Dynamics NAV license table number 24 26 thanks Johan her I got to go back and get the spelling of his name let me get the issue link first copy that in there because I always want to make it easy for folks to click on links and see what was changed yo hunt par lovely yet par level yet par level yet okay and then paste that in there and paste and there we go so there is I'll save that change and that is a github issue fixed now I should stop for a second and talk about the difficulty of that I wish that it was easier for folks to pick up github and run with it it is not easy the whole pull request and merging and like branching your own code when y'all have to go and work in the first-responder kit it's a little bit painful because you have to go first like clone the repo to your own github account I wish it was easier for people just inside a web browser to open up the SP blitz code make a quick change to it and then submit it in as if it was some kind of suggestion June bug says I wish there was a course for github there are a lot of courses like github x' built-in documentation is decently good but because it's not focused on sequel server people that makes it kind of tough because a lot of those concepts are focused on say Visual Studio code or Python it doesn't really ring easily true when you map it over to sequel server somebody said that my cup is smaller than my hand it's just that I have absolutely giant hands that's what it's not really true it's an espresso cup I really like espresso I like the taste of espresso okay so we got our first issue done oh let's stop and give a shout-out to our sponsor so this week's sponsor is bathrobes just kidding I said this week's sponsor is quest software they are doing a totally free webcast with me and panel Dobby that we're loosely calling ask the experts and I use that term loosely ace flame seer says the sound was way louder than your voice okay all right I'll fix that hang on a second here so we'll turn that thing way down we'll see what that does up so this week's sponsor is quest software doing a totally free webcast called ask the experts where panel and I get together and answer your questions there's only gonna be like an hour-long so of course there are gonna be a lot of questions that we don't get to there's also a limit of a thousand people inside there in the live webcast then you register whenever you want the registration is totally free but on the day of the webcast make sure you show up a like five minutes early because if you try to skate in there right when the webcast starts it'll be full this usually happens whenever I do webcasts over there so you can go to register for that totally for free over a Brent Ozar calm slash go slash experts so that's this week sponsor thank you for quest so June bug also says I find merge doesn't really work with SSIS packages and Visual Studio so it's confusing to use yeah Andy Leonard is the definitely the person to ask there Andy is the person in our industry when it comes to sequel now me I mean there are other people too like Tim Mitchell I want to say is I still do an SSI us work as well but Andy's one of the big huge US shining lights and he's much more professional than me he doesn't work in a bathrobe so alright so now let's hit another issue because I want to see there was another issue then there are much better looking one I want to say there was another issue I'm not as cool as Brent so let's see here there was another issue let's go back over to the github repo and go look at the issues oh yes uh yeah okay there are a couple in here so s people it's for I'll just I'll work from the top down SP bullets cash arithmetic overflow oh god I have no idea what this is gonna be so thankfully this person put in a line number but of course a problem if you've ever debugged a stored procedure or any kind of T sequel code often the line number that comes back isn't really the line number where the problem is happening so let's go take a look and see if we can see around line thirteen forty and see if there's something that changed recently now this also gives me a chance to show you something kind of cool inside of a shot-down fantasy you're awesome thank you so let's go look so we're gonna be looking at s people it's cash around line thirteen forty oh gee Barnsley good to see you so let's go over to code and then let's go down to SP Blitz cash and we're gonna look at the code directly inside of github you see how it's got line numbers in here well I can go down to thirteen forty so let's go much freakin thing not rendering right so let's go down to line thirteen forty as people its cash is a little bit on the large side so when you're looking at code this is how you know you're like come on now there is no arithmetic overflow here in terms of creating a table with trace flags so you know that that's not it so what's happening at line thirteen forty what I bet that it is is that SP blitz cash creates dynamic sequel and it is entirely possible that the dynamic sequel is thirteen hundred and forty lines long or longer so what I wonder about is in the last release did we change anything in terms of line lengths because me finding where thirteen forty lines in is in the dynamic sequel forget it is just gonna be a giant pain in the rear because the vert the string that gets built is different for every version of sequel server depending on what DMV columns your version supports you're gonna get more or less lines inside the query there's no way in hell I'm troubleshooting that so what I was gonna show you was blame this blame button do you think I'd be able to click it just a sine blame to someone but if you click blame what that does is it shows you the history basically of every line in here and it tells you the last time that it changed so if it was truly a problem in s people it's cash at line thirteen forty if that report was right about the line number then when I went down to line thirteen forty I'd be able to see this doesn't exactly render quickly more cacheable I'm not taking a general Q&A inside this session so if you go down to come on down a little bit yeah sitting better your thirteen I'll try and keep quiet then for your sleeping kiddo so here you could see line thirteen forty was last changed three years ago so you can be really confident that this isn't the thing that changed in the last release or two and I love how you can see inside here you know who changed this area last and then you can go in and click on their pull request and you can see what they changed around that time that isn't what's happening here though what's happening here is that there's something else at some other line so let's look at if I say that um that what changed in s people its cash since the last release what I can look at is I can go over to the master branch so there is I'm going to stop for a second here and because this is kind of interesting so in today's day and age there's raised sensitivity around github calling things like the master branch because obviously in the year 2020 we should not be using the term master and slave so there was a movement last week about getting people to rename their master in github as something else like main or production I want to do that I want to rename the master branch as something else but it's gonna break stuff downstream because for example there's an azure data studio plug-in that fetches the last version of the first responder kit there's a DBA tools command lit that pulls the latest version and I'm gonna have to go look at those things and see whether or not renaming the master branch is going to affect those things so that's why I haven't done it yet but I just want you to know that it is right at its you know in my mind that we have to do that so let's come back to the code so if I go in here I want to see what changed in SP blitz cache since the last release there is this commits thing here where I can see everything that was committed so here I can see what was committed like the from the dev release from the release prep release and so forth so if I go in here and start clicking on this stuff I can go in and look to see what changed Barney if the rebel says you can change it and then have an alias and then track what it affects that way yeah Barney if you want to put that in an issue cuz I just won't take notes on it from here obviously Richie says Richie do you have you Richie has a bathrobe - as well snorosaurus was on our team as well I say our team it's me Joris and my wife that's that's the entire team and we all have bathrobes so you can see in here say says like shows 15 changed files you can go down through the list and you can see which files changed like if i do a control left on here for SP Blitz cache I can get down to SP Blitz cache and I see what changed inside this release diligent DBA says Brandt could you make the fonts a bit brighter just a wee bit the screen is too white I'm not sure how I would do that in github I'm not sure if you know how I would be glad to do it I just don't know how to do that okay so now because so coming back in on that issue so here I don't have enough to know what changed quickly and I am not gonna fish back through every change that was made to the release so here I'm gonna tell him that I need or him or her I need more information so howdy I just checked line 13 40 and it hasn't changed in three years what I bet is happening is that this error is coming from the dynamics equal not from SP blitz cache itself can you try using the debug equals one parameter and then going to the messages tab there will come a day when I can type on a web cast without talking that day is not today tab of SMS pulling the dynamics or components pulling copy copying the dynamics equal into an editor finding line thirteen forty and then pasting that general region in here like the thirty lines before and after to help me track that down Thanks comment I'm going to not assign it to anybody but I'm just gonna throw in that it's a bug and it's an SP blitz cache and I'm not gonna set it in a milestone yet because I don't know when we're gonna get the information back in there alright don't change reading while typing CUC er to listen to you then reading the small text I believe it also you need a larger monitor okay so that's that one that's s people it's cash arithmetic overflow next up we have SP Blitz first is reporting queries with ten thousand cardinality miss estimations I love this bug so what this is is in SP Blitz first when we're checking to see which queries are having a terrible time with performance something that we can do with fairly recent versions a sequel server is that we can go into this DMV and I forget which one it was but it gives us like a live execution plan of a query and Eric darling wrote this code that says look at estimated versus actual look at estimated versus actual of each operator and when any one operators more than 10,000 Dex off throw a warning well what's happening to Greg and it Greg is the person who did the issue and me because this happened to me on a recent session is when SP blitz first is running at the same time that it's running in another session we get this error so the the first thing that I'm gonna do is I'm just gonna go track down where this is being done in the code and I'm just gonna make sure that we exclude our own session just as a really fast fix for this if someone runs SP blitz first across multiple sessions they have bigger problems anyway so let's go pop open s people its first and find this so let's say good loser Winnie whoo let's move that volume up just a little I like that just a little bit higher there so sir when he says guess who had two things and got approval for his life class season pass yesterday congratulations especially because it's on sale for like three thousand dollars off through the end of this month so very nice dressing down that excellent I believe that you would have that to thoroughly dip I handy so good morning all right good I guess it's afternoon in the Netherlands I'm not quite sure what time is it five eleven it's probably like midday in the Netherlands I would guess so let's see here so let's go find that in s let's first we'll go do another branch surly toughs my girlfriend is the best that's pretty cool I mean that's really all about what personality is like right we Star Wars crocks Star Wars crocks Star Wars crocks you are quite the character so let's go back over the dead branch oh get-ups telling me he wants to restart so let's go ahead and restart just to get the new whatever the new hotness is there and then come back over here not get back over there pull the origin again to get the latest changes that I made now I need to start a new branch and I need to say new branch 24 20s P blitz first ignore own session new branch and then see what I did there surly dev says I posted a picture of fun may the fourth be may the fourth be with you nice yes I think I did that for one of our first responder kit releases - so now let's see here now I got to go open as people it's first and I got to find the line that says 10,000 X cardinality miss estimations so as people its first now a second to go when let me shall shrink this down and I'll explain what's going on a second ago when we were fixing a bug in what the how is that all grayed out how our Bo they're both oh okay it's one okay nice so a second ago when I was talking about s people it's I said at the start of s people it's what we do is we create a temp table and then we do a bunch of checks that are all just inserts we look for a problem happening in sync well server and if we find it we add a row to the temp table and we do this hundreds of times well I do that exact same thing here in SP Blitz first I'll go through and now I'd have to click on that right I mean it's like legally required can I get it from let's copy that out oh that worked perfectly because it launched another oh my god I didn't need to see your feet that large so that's kind of interesting so used so the people who are approaching you see Darth Vader and it's kind of interesting because you know they have to look down like they have to look down in order to see that and then at that point you can lightsaber their head off which it feels really odd to do lightsaber emotions while I'm wearing a bathrobe you know this is like jumping the shark in an amazing way oh I can follow you too now I can stock you over on Instagram any perfect so now let's come back over here so I'm gonna insert rows into this temp table based on what we find and the check that's involved is this right here right see you and me both the check that's involved is this right here insert into blitz first data data data where the hell is the select oh it's further down cross applied and edited at an outer apply all right so here's here's where the query kind of finishes up right here and you can see that this here's the thing that I need to look for is I need to make sure that the session excludes my own session this is like the bad fashion version of our webcast right like this is this is alt completely bad fashion and he says one unread email people who maintain inbox zero make me ill whew inbox zero baby so I need to exclude my own sessions so let's do that let's say and session let's see here and s that's a join though yeah oh let's go let's go let's do it up higher because I see another session up here our wrote this sequel it's not god bless it's not formatted right see Ritchie now I feel how you feel when you open my queries and our OB o and B session ID is anything I really need a where clause I'm just trying to be like I'm trying to be lazy and I'm like doing bad sequel because I really I really should add it after this this is exactly what I'm going to do here so let's be nice and we'll say a copy-paste plus in the where be oh hell if it's a if it's a table variable why do I not just if it's this up here why don't I just not insert rows into there for my own session the hell is my problem so let's go back up to wherever we're putting wherever we're populating that bad estimate table so it is right there so bad estimate oh it's oh man it already has anything other than my own session ID damn it I was hoping that we would have an easy fix I was hoping that Greg was only reporting issues with whenever the SP blitz first was catching its own session but he's also catching when it runs in other sessions so now things become a little bit more complicated because let's step back for a second and talk philosophically about what we need to do what I need to do is I need to say when I'm looking in the DM B's and I'm reporting on problems with queries I need to exclude a specific known query as peopleit's first well in theory you could do that by looking for say SP Blitz first in the contents of the text the text to the query that the person ran but that check we just saw running is dynamic sequel it's spinning up a new session to run so SP blitz first isn't gonna be in the dynamic sequel also I can't look for a query hash I don't want a hard code in a query hash because every time the shite down you're welcome my pleasure every time we change the code every time we changed the code in this stored procedure this is going to change the query hash so that doesn't work either I don't know if I have a really fast easy scaleo spell gonna Club this is these are our company bathrobes so I don't usually do this but Andy Malan made me cuz he was in a bathrobe and he felt underdressed so I don't know that I'm really gonna have an easy fix for this so I'm gonna leave a note in the issue and I'm gonna see if Greg wants to pick it up and run with it himself unless there's one other chance I might be able to look for strings whenever I'm doing this check against bad the bad sessions let's go and look I don't have hi so in here here's the part where we're getting out the bad estimates and we filtered out our own spit we don't have the text of the query right here so I don't have a really easy way to do this so in here I'm gonna copy out the part that matters and I'm gonna come back over to the issue and I'm gonna give Greg the bad news so ouch I popped open the code and I see what's happening this is only happening when SP Blitz first is running simultaneously from different sessions and so that's true Ritchie and I have I could pull off a lot of that and I what would be perfect as I would sit over here in my Eames chair and that would really like totally jump the shark I would totally be a you know Don Draper kind of character they're running simultaneously from different sessions when we populate the bad estimates I want to say it's called bad estimates it is bad underscore estimate the bad estimate table variable that's used to find in accurate find these issues we already have this filter and ape 83 you're totally right I could do that but then the problem is that I also have to join out to the DMV's that have the text of the currently running query and I'm wearing a bathrobe so you can tell that I'm not actually up for any kind of hard work so Barney of the rubble says when you say simultaneously is it a sink no it's simultaneously two different people are running s people it's first at the same time so we already have this filter so that means you're seeing the SP blitz first running from another session like the one like the one you so cleverly oh goodness boi-oi-oi-oi-oing set up to run automatically every 15 minutes I would love to exclude thank you Andy I would love to exclude that query but to do it I would need we we and by we I mean you I should also say to I know because I've seen this same thing happen when I've been teaching the mastering classes I would love to exclude that query but to do it by we and by we I mean you would have to examine the contents of the query itself and at the point where we feel the bad estimate table we haven't joined to the text of the currently running query if we and by we whoops and by we I mean you wanted to do this we you get the point would need to add a join to something to get the text of the currently running query examine it for yeah examine it to see if it's the SP blitz first I'm a pay T three no we want to let it run at the same time it's okay so sequel server is really cool it's a database server that lets you run multiple queries at the same time I know right it's amazing multiple queries at the same time so one of the reasons it's so expensive I'm being sarcastic but I'm not known for my generous Gabriel down to D'Onofrio good to see you dirty expresso today I'm right behind you too as well d bogey says it's not clear if you're a miss estimate it's greater than 10,000 is the one session really significant that that's a little off-topic I'm gonna hold that one off for now I don't think that you're quite the target audience for this particular issue that's okay that's totally alright see if it's the SP blitz first query and to do that you may want to add an obvious comment to the dynamics equal sequel that's getting built like from SP blitz first and then look for that so if you're interested in doing that holler but otherwise I'm going to close this because it's more work than I want to do because I'm wearing a bathrobe put a wink in there comment okay so I'm going to assign that to Gregg in case he wants to do it Gregg is there smart ambitious person and he might actually do that I'm gonna call that a bug and it is an SP blitz first I'm not gonna sign a milestone though because I'm not quite ready to to commit to that particular one so there we go so that gets me the two issues that are out there certainly dev you could put her on like a group chat and we could all talk to her that would totally work so there we go I'm kind of okay with both of those we have a couple of others but I'm probably gonna call it quits there in terms of fixes for this morning so let's pop over here for self DD Toby says it's suggested that you don't hold your nose when you sneeze as it can cause bleeds I'm hoping to try to blow some of my brains out my ear I'm just entirely too smart and I'm hoping that if I do this often enough that eventually I'll get dumber and then I can be do a better job just obviously foaming at the mouth so what we'll do is we'll stop here I'll do a quick shout out to question big thanks to quest for sponsoring this week's web cast for allowing me to work in a wardrobe I should totally try to show up to this wardrobe art show up to this webcast in a bathroom so quest is sponsoring panel and I to do a webcast on June 24th that we're calling ask the experts and I have a hard time saying that with a straight face every time a panel and I are probably the closest thing that you get to experts who are willing to sit around and answer your questions for free so if you have totally unrelated questions for single server you can go over to Brent as our comm slash go slash experts and we'll get register for that you also get access to the recording if you're not around during the live session and it's just totally open Q&A and we'll I'm sure given the number of people we're gonna have we're gonna have boatloads of questions but it'll be fun to go through there so now meet a set of meet jitsi session alright so what we'll do is we'll stop here for a bio break I'll stop for a five-minute bio break and when we come back I'm going to show you Eric darlings SP human events because I think it's really cool and I think a lot of y'all will think it's really cool too I'm not a big fan of extended events normally I mean if they've kind of heart of a pain in the rear to set up there are pain in the rear to query and Eric's made it really easy to go through and analyze some of this stuff with his SP human events so I will go refill my tasty espresso and I will see y'all back in here in five minutes so I'll be right back [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] all right welcome back yes oh man in that great just looking at the sunrise out here so it is sun's breaking out in downtown San Diego my coffee shop downstairs opens at 8:00 a.m. and so I think dang so my coffee shop downstairs opens at 8:00 a.m. normally I would say that you have me until 8:00 a.m. but because the Sun is rising it's looking gorgeous I got all excited because I was gonna go take a trip with the car down over to the beach and just go watch the Sun rises I go I'm gonna leave early today and I remembered that my car's in the shop so it's in the shop for its very first service so I have a 911 I have a Porsche 911 love it it's absolutely wonderful got it almost exactly a year ago got it in July of last year I've been my life dream I'd always wanted one and just Eric out of nowhere decided she's like today's a day you're gonna go down and buy a Porsche 911 I'm like thinking I don't know what you're apologizing for but whatever you're apologizing for I'll take it I'm gonna go buy it let's do this and so we go down and get it when I took it in for service so is his first oil change first oil change we've had it for almost a year and it only has 2,500 miles on it 2,500 miles in a year I Drive I mean like granite I telecommute I travel a lot for work but it's usually on airplanes but we drive I mean like I go on road trips we go to Vegas we go up the west coast and all that but just because of one coincidence after another we hadn't taken but like two road trips we went up to Malibu a couple of times and that's it Oh Jim nice very excellent both excellent choices and so we just haven't been out and around it all in my service guy who I've talked to a couple times since I've had it just for you know like having old things done to it not because it was broken because I wanted improvements done to it my service guys like Brent really disappointed in you you're really letting me down so I got all excited that I was giving like oh I'm gonna go run up the beach for sunrise yeah no not not gonna happen today because the things in the shop so I had Porcia makan for which is a little-bitty SUV for for a loaner which I'll just go and drive around just to be out give me an excuse to be out and around see how that goes all right so next next what shall we talk about so next was uh Eric darlings SP Human Events so for years I've kind of said like I don't really use Bubbe bob bob bob says now brent let's go to the beach to the sun rive time and talk about sequel while we commute don't what are we doing we're already talking to sequel I'm not taking my phone with me in the car though because I tend to break lives or break laws when I'm behind the wheel of a car a lot of times the career is the new career as the new 2020s are just ridiculously beautiful but I think for me if like all I ever wanted was an 80s Targa I wanted a red 80s Targa and unfortunately that I had my mind set on exactly the one I wanted Steve Jones the person who owns or owned sequel server central.com I had a red Porsche 911 Targa and I kept telling him hey look I want to buy your Targa whenever you sell it I kept saying I kept saying it and he wanted to be fair to other people because other people also wanted it so he posted it on Facebook and set out on Facebook hey I've got I'm gonna sell my Porsche 911 and I don't read Facebook Facebook's like terrible Facebook has all this crap out there with all kinds of people's political opinions and I don't care about their political opinions so it's all this crap inside there so as a result I missed Steve's Targa sale when I happened to see it it was like two days later and another friend of ours had told Steve he wanted I'm like oh no no so it's fate and I ended up with a new one instead but it's just uh funny how life works out that way Lee I agree with you that the best Porsche is the one you can afford and and Steve Jones who has had several through the years kept telling me just it doesn't matter what you get go get one any that you can afford and you know just enjoy it while you can while you're still young enough to drive and now it's really funny because so Erica and I we bought this in the new 911 Targa and now anytime we see a brand new 911 on the road it's always someone older than me and I'm like I beat you I got mine earlier all right so Eric darlings SP Human Events so for years I've said that I'm a profiler person I think that I always used to sequel server profiler and then I woke up one day and I was like I don't actually use profiler anymore it's been years since I've used profiler I can't even remember the last time that I use profiler now and I use extended events way more but the thing is I hardly ever do it through the GUI or through T sequel coz extended events is a giant pain in the hahoo in order to set up and query successfully I know people like people like Erin stole Otto and grant Fritsche are like it's as easy as and then you see him you know hammering away like Kermit on the keyboard and I'm like yeah that's not really that easy Eric Darling has totally come up with a way where extended events is extremely easy to use and I'm so excited about it when I saw it he's got this espy human event stored procedure let me show you it to you and we're gonna craft a demo on the fly I'm gonna write a blog post as we go I'm gonna gather screenshots that will reinforce the blog post you'll see me write the blog post live basically so let's go see how it works so the first thing that we're gonna go do is we're gonna go grab his espy human events I'm gonna go open a web browser and go espy human events and so here we have Eric site on SP human events Eric loves recording videos as I do so he's got video walkthroughs in there I am NOT gonna record a video walk through the public like all we're getting ready to see is all you're gonna see me do on it he's got the video walkthrough thing nailed I want to do a couple of blog post showing people for those of us who like to read thoroughly dev says does he stream he does he is twitch.tv darling data he just started this like just 13 followers so he just started this and he is just now like getting his green screen on and all that kind of thing you can totally follow him there you can also follow him over on YouTube brand-new and I really like watching him work live he's got a brilliant sense of humor so very slick stuff so I'm gonna go get his github and then pull s be human events so that I can go get the code oh he changed it did it say yesterday it did oh he did a change to it yesterday he's early dev he has 14 now that's good so let's go get the contents of it and I'm gonna go put it into the master database close you and new query here and paste so now got it over in the master database so this thing is installed now let's go back over to his site and I'm going to show you the commands so here he has a set of instructions with different commands that you can use in order to see it sampling for stuff this is all you have to do all you have to do is run espy human events and tell it the kind of event you're looking for this is so bananas and I'll tell you the thing that I ran into all the time troubleshooting live client he was a ghost yeah he's he's still on nailing down the whole how chroma keys work um one problem that I run into all the time at clients is people who run option recompile on their queries they run queries that have recompile hints in them and as a result I don't get to see them in the plan cache they just disappear and their overhead is really high so Oh surely dev I saw your message to me about that too and I hadn't responded to that yet I gotta go respond to that I'm really bad at responding to twitch messages I just don't go in there very often so in here he has event type equals compilations so I'm gonna copy this out and paste it then he also has he got me head in my hands yeah exactly I I have to go through and actually read it I haven't read it yet I just saw that you had something about a pull request and I was like okay then he also has seconds sample so I'm gonna say oh yeah no no I totally wouldn't ignore you on that one it's just one of those where I'm really religious about inbox zero and email but I have social profiles at Instagram Twitter Facebook LinkedIn github you know like every platform out in the world and so I tend to just kind of ignore all those other stuff and I just tackle stuff via email so if you ever want to get me on email just go to the site and hit contact up top it's help at Brando's are calm and I respond to those really fast so this will go through and it's amazing what this actually does just this one line goes and sets up the extended events trace holds it open for 30 seconds then gives you the output in a format that actually makes sense to human beings now to see it work what I need to do is I need to have a query that's actually doing compilations so over on the right hand side I'm gonna run a query select star from let's go pop over into say Stack Overflow select star from dbo users you where you display name equals branch Ozar order by oh no let's do yeah sure that's fine order by you scored ascending option recompile lucky you is correct so that gets me let's not score its reputation and let me zoom in on these fonts to here to make it easier for y'all to see over online so I'm gonna run this query over on the right hand side I'm gonna go run at 50 times now the time problem that you've usually run into with option recompile queries is that they don't send up hiya thank you they're that they don't show up in the plan cache so when I go and execute this 50 times and I'll just go ahead and run it if I run typical plan cache type monitoring queries that say will show me the queries that have done the most reads for example if I go through and run an SP blitz cache query saying show me the queries that have run the most reads it's not going to show all of that stuff over in here this query that's been running over and over again it doesn't even show up in this list 4s people it's cash same thing with a lot of monitoring tools a lot of monitoring tools won't catch it I know is that go 50 cooler what's like a built-in denial of service not distributed denial of service but just a denial of service attempt so that doesn't work so what I need to do instead is I need to catch compilations I need to catch the number of times that this thing has been compiled so this time over on the left hand side I'm going to start SP Human Events then I'm going to go run my query on the right hand fifty times and what SP Human Events is doing is it has already started an extended event session it's basically doing a wait for for 30 seconds while the query on the right is going ahead and doing its thing what I love this for is that I don't even have to start any queries what I do in a client environment is I'll just go and run SP human events looking for compilations for say 60 seconds and now look at what I get inside here yes awesome it tells me how many times the query has been run and compiled I don't even have to do anything and I can catch here am I having a problem with queries that are trying to sneak in using recompiles so you can go run this in your own environment it's also really interesting to see the compilation overhead one of the problems that we run into when people run queries with option recompile is that compiling a big execution plan is hard the more things that you put inside the query the more problems that sequel server has to think about so what I'm gonna do is I'm gonna write a query that will show that will be a stored procedure it's gonna use option recompile and we're gonna give sequel server some tough choices I'm gonna say create or alter proc DB o USP search users and we're gonna say slow k ssin and ver care 100 start date date time and date date time as begin and go then I'll say select top 1000 star I'll just say star cuz we're terrible people from DB o users you inner join DB o post P on you old o comments will search for their comments comments C on uu ID equals C user ID where you location equals location and C comments creation date is greater than or equal to start date and dip dip dip and see creation day is less than or equal to and date and let's say that I will also say order by will throw in an order by to say order by show me the comments by score descending show me like the most popular comments then we will give sequel server a couple of indexes I don't know that I have indexes on this these table so let's go see as people it's index table name equals users so let's see do we have any indexes on it we do we don't have one on location though so I'll have to create that create index location on dbo users location then I also need to check the comments table to see if I've got an index on date there and I do not I have one on user ID creation date I actually don't want that one so let's go drop indexes so let's go drop indexes and then we'll go create index creation date on dbo comments creation date so we'll go create both of these indexes and then when that's done I'm also going to sum setting up basically for the blog post now so I got my stored procedure now I'm going to oh I forgot about the option recompile I'm gonna throw that and I'm gonna say option recompile then I'm going to build a couple of call stacks that will give sequel server tough choices about which index it should use first surly deafs I have a friend that would never use creator alter as he thinks it might so uh so what what some people will do what I've seen people do is just do a raise their raise roar right at the first of the at the beginning of anything that they type or they'll do a return and then they'll throw our a default raise error inside there so that that way when it they try to hit execute it just automatically bails but I like that same exact thing so now I need geek will serve a couple of choices so let's say execu SP search users I'm gonna need a location that's fairly rare so I'm gonna say near Stonehenge yes there is a person near Stonehenge at stackoverflow start date equals 20 or 2010 Oh 101 and date equals 2010 1231 then we'll also do will do a much more common location we'll say London United Kingdom and then we'll say a very tight date range Oh 102 let's see if these two get different execution plans so it always helps to put the stored procedure into production I find that that's very helpful so now let's see if we get what execution plan we get for inside here in sequel server you so crazy I'm so sequel server when we set near Stonehenge it decided to do an index econ location first now let's take the exact same query and let's run it for London which is a most much more popular location in sequel server chooses a different index sequel server chooses to do it on creation date beautiful this is the kind of reason that people implement option recompile inside their stored procedures damn my voice is cracking going through puberty in a bathrobe in public it's so awkward Barney no I don't I know Ritchie does but I don't so in here I've got option recompile inside here very common for people to use this kind of thing let's do go 50 and go 50 on this then let's do our recompile piece so let's start our sample and then let's do this 50 times holy yeah that's fair that's totally fair you know I don't want actual execution plans on for this that's gonna make this thing take a little bit longer let's go do it with the other one let's actually move these around so that that one goes faster first then goes this one so we got so now over on the left hand side when our sample finishes now I get isn't that amazing you get the parameters that is blank that's incredible so now when you have out in real I know a lot of people I know a lot of people who struggle with fixing parameter sniffing issues where they're struggling trying to get the parameters to use to call a stored procedure because people are constantly calling it with different parameters good afternoon collared people are constantly calling it with different parameters now you get the parameters you can use in order to tune a stored procedure that's the kind of thing that I used to have to fumble around in the backseat of a car and like a teenager in high school when I was trying to figure that stuff out what parameters do people usually use what parameters are fast what parameters are slow and you don't have to know anything about extended events in order to get it just happens for you automatically that is totally cool so this is gonna be the the screenshots that I end up using inside the blog post so that's pretty straightforward now that's by no means the only trick that this thing has up its sleeve you can do if I Bob Bob says if we use encrypted can it still show the parameters provided let's go find out I don't remember offhand how to create an encrypted stored procedure because let me tell you something encrypted stored procedures are bogus encrypted stored procedures can be decoded in five seconds using sequel decryptor sequel Decrypter is a totally free tool out there you can go download an evaluation version of it I want to say the real version of it cost like 500 bucks but the eval version does everything the paid version does just does it for a limited time so when people do de-encrypted stored procedures I'm like just let me show you something watch this pop opens the demo of sequel decryptor decode all their stored procedures and I go okay now any questions you thought you were hiding something from me it's totally decrypted any questions we're gonna decrypt these all in production right now because the encryption makes no difference like it's not saving anybody from anything from finding out super secret secure source code so let's do sequel server create encrypted stored procedure and then let's see here MS sequel tips let's try that one lease as a plane in the background dude when I am on the flight path for San Diego Airport and it's always kind of nice to watch how that works so let's see here to do to with encryption no come on that can't be where's the stored procedure just why is this come on that's ridiculous that's not at all what I wanted to do Pinole okay there we go that's better so stored procedure with encryption okay so thank you create procedure create procedure with encryption what a load of uselessness no I don't mean pinellas post I mean that the feature is a load of uselessness so let's slide over here and let's create it as encrypted so let's copy this and paste this down here and that's early dev I'm with you actually yeah that I've done the same thing with as an software vendor I've said I'm encrypting this just so that I can have obeyed the letter of the law with our audience with our auditors and I've also told my customers look I didn't want to encrypt it and if you need to decrypt it just go use sequel decryptor and we're out of here and that's kind of the end of that so let's go with encryption I believe that it is encryption and let's like hi hacker speak there with encryption so we'll go in here and say encrypt so now we got that stored procedure let's go execute that and see if that works that's ridiculous then let's go copy paste in our call stack so that we can call this little fella 50 times we're gonna change the name of the stored procedure there and then let's go fire open our compilations call and we'll go run this 50 times so I'm running the encrypted version of the stored procedure of 50 times while we get a sample over there from SP human events over on the left hand side my hunch I've never tried this but my hunch is that we'll still get complete info from SP human events because we're not seeing the inside of the stored procedure we're just getting the outside stuff so I think it's still gonna give me parameters let's go see and tada yep perfect sweet that is fantastic still gives me everything even though the stored procedure is encrypted so that's kind of cool now so we've got that solved now the other thing that I wanted to do back over on Eric's piece dudududu so over on Eric's piece this is the other thing that I wanted to show you is really shion's not compilations but recompilation x' what's the difference so the difference between the two compilations is when you specifically asked for a compilation you said option recompile give me a new compilation regardless of whether or not there's a plan in memory recompilation x' is a little different recompilation x' means there's a valid plan in memory but something clapping inside sequel server that caused it to have to build a new plan classic example statistics being updated when someone updates stats we have to go build a brand new execution plan because something about the table may have changed there are some gotchas inside there there are situations where it doesn't build a new plan it's really kind of neat Kimberly Tripp and Aaron still Otto have posts on this so anyway so what I'm going to do in order to demo it is I'm gonna run the same stored procedure without recompile hints I'm going to build a new version that doesn't have a recompile but what I'm going to do is update the statistics like rebuild the indexes on the user's table to force updating stats which will then trigger a recompile because the stats have changed so let's go write that demo so let's copy that call stack so that is recompilation x' move this over here welcome to the club corrupt so now we have recompilation x' then to trigger that I am going to need to run build us another version of this stored procedure but without the recompile so we'll copy this guy out and come all the way down and we'll call him stable and then I'm gonna take the recompile out and then go get my version of the stored procedure copy and come down here and then I'm gonna say alter table DB oh users rebuild go so what this is gonna do is in the middle of after this things already been put into cache we're gonna be forced to build a new execution plan for it because the stats have changed even though the parameter I'm using hasn't changed it's not like it's a parameter sniffing problem here I'm getting a recompile you know what I'm gonna always tricky as an instructor like how many different things I want to try to teach you inside one blog post you know drawing the line of where I'm gonna stop versus what I'm gonna explain I think this is gonna do enough that it's gonna explain to people what the concept that I'm dealing with so let's create or alter the stable stored procedure let's change the name of this down here and then let's go start our sample for recompilation x' then let's do all three of these so I am right now over on the right hand side I executed the stored proc I rebuilt the indexes then I ran it again this will trigger a recompilation because the plan was up in RAM buts server said timeout change stats on this table we better go through and rebuild a new execution plan so over on the left I should see a new execution plan having been built inside the span of that 30-second time span and sure enough right there so and the recompile Clause he shows me the recompile cause as well this thing was recompiled because something about the schema changed and Isis schema change it's I'm gonna have to go through and Google read in the source code to see what's going on what the different options are cuz that's actually the only reason that I've ever asked seen that so yeah I'm Lee I'm with you i think that that's extremely cool especially when you're trying to find out what's causing plan cache instability this is a really cool tool to have in your disposal so that is eric darlings SP Human Events I'm really only showing you two of the things that it's good for there are a whole lot more if you go through and read his YouTube to his YouTube channel he has demos on these and he's been really excited to show these to people and to give you improvements too as well it's really slick I just really like it a lot so that's everything that I wanted to show y'all around SP Human Events I'll go grant gather the screenshots for those so I want to thank again Quest Software for sponsoring this week's webcast so if you go to Brenda's our comm slash go slash experts panel and I are doing a session on June 24th where you can ask us whatever performance tuning or database administration questions that you want and we'll go through and answer those and some of them will probably say for blog post too as well very often in these open QA kinds of webcasts we get all kinds of great questions that we don't have the time to answer so those are always fun so you can register for that at Brenda's our comm slash go slash experts then also if you can't make it to the live session you can also register because whenever they get the recording they'll email you a copy of the recording for free this is one of those that won't be up on Twitter YouTube you have to be registered with them in order to get it so that's everything that I wanted to show y'all today hopefully y'all had some fun I am now going to go change out of my my company bathrobe here and then go go for a morning run go grab some breakfast I say run I don't want you to think that I'm actually doing exercise good God what I'll be doing is just taking a run with the car and then ODB Auggie uh yeah okay cool um is I'm guessing Auggie as your nickname I guess is probably how that came about but so yeah I'll go uh don't go for run in the car and go rock around and grab some donuts or whatever and I will see y'all out and around no you know certainly thoroughly enough I could actually the people at the coffee shop know me really well the problem is it's still two hours before my regular coffee shop opens and if I just go to some Rando coffee shop wearing a bathrobe I don't think that's this funny I think it's funnier when the people know me and then they go oh it's Brant yeah I know he's just wearing a bathrobe today I think that would be absolutely hilarious so I will see y'all the next time around thanks everyone and see you later adios [Music]
