How to Read Someone Else's Temp Table - then Watch Brent Tune Queries

hmm hmm i am who i say i am so hmm inhale and exhale all right good morning party people yes uh it is saturday morning here in san diego a nice gentle quiet right up until i played that track uh i love that freaking track it's so i use the pretzel music player um which plays stuff that i can safely stream on uh streams with uh like licensing is included and stuff like that um and that one i absolutely love uh brian says morning brent good glad good to see you uh belgium formula one belgium very nice uh greg good sql dba good to have dba good to see you again niraj good to see you again kurt uh kurt asks have you been up all night with sql saturday oslo no i woke up at 2 uh pacific time but then i didn't go i went in briefly to see gianluca sartori's stream on or his session on benchmarking he does a phenomenal job both just talking about benchmarking and doing it he's got a set of open source scripts that he's published it's just really really good stuff so i watched him talk for i don't know maybe about half an hour and then hop back out because i wanted to rehearse my session and then i realized i really wanted to just write a new one from scratch because very often when i'm doing watch brent tune query sessions i always feel like a lot of y'all have seen some of those queries before so i mean it's funny sometimes when i'll teach stuff i'll ask for a show of hands like how many people know a certain concept and then they actually don't know it very well but so this this morning i was like i bet some of the people in the audience have seen watch brent tune queries with other varieties of queries i might as well do a brand new query from scratch so i did that this morning yes greg i put my 911 target i've been meaning to paint him i you can buy uh chalk colors so my my 911 is chalk you can buy spray paint like for touch-up for chalk and so i've been meaning to mask him and spray him i just haven't quite gotten around to him yet one of these days i'm gonna do that mr huckmacher good to see you in as well so this morning this morning i wanted to write a blog post about um how you can read other people's temp table contents i had uh how you could so clear that's kind of beyond what i can teach quickly inside a stream it's not stupid at all it's totally not stupid but it's really just that's the kind of thing that you go buy entire books for if i was going to recommend one i would say itsec ben gone's books it's sick ben gone if you search for like t-sql fundamentals or t-sql querying on amazon uh they're just uh fantastic fantastic books i love nardo gray too nardo gray's a little bit darker but i absolutely love it and honda's are even doing a version of non nando or nardo gray now uh yes i will be feeding my stream simultaneously to sql saturday oslo at the top of the hour when we get to the top of the hour i'm going to shift gears we'll do like a pause break here the stream will stay live but then i'm going to go log in to go to webinar and then i'm going to hopefully if everything works right i'm going to stream both to here and to sql saturday oslo i won't be able to do the fancy like multi-camera type stuff or transparency stuff that i do on uh it's equal saturday y'all will still get that you want i won't switch sideways to cameras or anything like that but i'll be streaming via go to webinar for watch brent tune queries and i'm going to be recording it here locally so that i can then go upload that up to the cloudy i've had so it's funny richie uh uh has constantly sees my queries on the uh costs of everything for our aurora for sql constant care richie i would just like to point out that i have never written a query that was as expensive as your delete query was um station yes it is still happening remotely i'm doing the whole thing from the glorious comforts of my home uh and then the attendees get to watch via private stream so it's actually the same exact kind of plumbing that i do with these it's just that i'd use a different one for uh the private ones like sql saturday oslo yesterday and then sql saturday gotenberg next friday so how to read somebody else's temp table contents i can't remember where i initially saw the question if it was like stack overflow or deviate a stack exchange or somebody's blog post going i want to be able to read other people's tempty contents howdy arthur and the the short answer is no you can't do it but the real answer is yes you can kind of do it with statistics so oh you'll be recording the session or moderating session very cool uh the with statistics what you can do is spy on other table statistics even if you don't own the table even if you don't have permissions to see the table you can still pull the statistics for it so let's i needed to beat you in something so to do that what i need to do is i need to explain i saw you doing like smoking this morning and i saw the pictures out on twitter and i was like oh yeah it's uh that is an all-day kind of affair looked good it looked very good so to do it i'm going to write the blog post live sometimes i only do the demos for y'all like we craft the demos together here today i'm actually going to write the whole blog post live doing the demos and screenshots and all that start to finish so let's go get started with that so first what i need to do is i need to go create a temp table so i'm going to create a temp table i'll say create table secret plans and we'll put in there well here we'll zoom in a little too so you all can see a little better secret plans phase int task varicare 50 insert into secret plans phase task values one um collect underpants those of you who know that joke two to be determined and then three no i can't make that i can't make a joke in on that like there's nothing funny about that so that for those of you who don't know the star of black panther died i want to say overnight over colon cancer after fighting a battle for four years wow it's impressive uh profit and so here now we've got that select star from secret plans so here i've got a temp table and i put a few rows into it and if i want to read it from another session if it's a regular temp table like with just one pound sign if it's just a regular temp table i can't hop over into another window and say select star from secret plans if i try that i get invalid object name okay that's fair it's someone else's temp table well the next thing that we'll go try is we'll try to go switch over to the others to i'm sorry i was gonna say switch over to another session switch to tempdb so i could say use tempdb go select star from cis all objects and then if i look in at all objects it shows me that there's a user table and that what it's secret plans is the name of it when sql server builds temp tables the little number at the end of that is the uh number of the temp table that you've created like the first see first temp table we'll have under under under under 1 second will be under under under 2 and so forth not of the same table but just as you continuously create any temp table that little incrementer goes up by one each time setting aside reusable temp tables so like paul white series on reusable temp tables is pretty cool so can i get access to that let's try copy pasting that let's copy that and then let's say what happens if i fully qualify it select star from boom like can i use that exact same table name and of course the thing wraps around there's the very end of it let's see what happens when i go to do that and then execute sami yes it's reset on sql server on the instance restart not necessarily the windows os restart but just even a sql server instance restart resets that so useless trivia right with brentos are so that doesn't work i'm not able to directly query someone else's temp table and i can't do it even if i put in a prefix like dbo because it's it's not really dbo owned if i try that same thing no go if i try to put brackets around it i also get a no go okay so first you think all right i give up i can't read someone else's temp table contents but temp tables can have statistics and statistics of course can include the temp table's contents so the first thing that we need to do is we need to go find out the stats name normally when you use commands like dbcc show statistics you pass in the table name and the statistic name i don't know if there are any statistics on here i'm going to have to go spelunking through sql servers diagnostic views in order to find out oh semicolon was in brackets yep sure good point there uh roger or holliger i'll uh go do that see you get the same thing invalid object name okay so i need to find the stats so if i say select star from systats and execute that there are stats in here i don't know the object id but i can go get that object id would be for the table or indexed view that we're looking for so let's write our query a little bit more rigorously oopsie let's say from cis all objects uh sukver uh how is what useful can you be more specific oh inner join actually we'll do a left outer join outer join snap o object id equals s object id where o name equals and so we're going to paste in our big ginormous whoopsie paste in our big ginormous table name let's go get that copy paste so i'm doing a select star whoops i got to do an odd yep good count andy good to see you good to see you brother um hope you're doing well uh as well as can be in these times so i have so i've got some results here but then all of a sudden i get nulls out over here so uh jennifer that's kind of uh jonathan that's kind of off outside the scope of uh what we're talking about here xp i'm not even sure what you're talking about with xp let's hold off on that so uh here let's uh reading someone else's temptable how is this useful in case you need to read someone else's temp table yeah um so in case you need to do query troubleshooting is the classic one this is how i've run into it in the past when i need to see what a session is doing so that i can figure out why things are going wrong so here there's stuff that's coming out is all nulls what is it that's coming out as all nulls let's see if that's the statistics and go execute that yep yep that's it so there are no statistics yet on this object now that kind of makes sense because when i wrote my original query you can read the channel's description to see what i'm doing today there so when i did my original query i didn't do anything in the query that triggered sql server to go build automatic stats on the table i did a select but i didn't have a where clause so if i'll go in uh no i i bet i'm not going to do that and in fact i'm going to block you from doing further chats just so that you don't go too far off topic there i'm not here to serve you as much as it might look like i am waiting on you hand and foot i am just a digital avatar i am not really here to do your bidding you are not the only person watching the stream even though it's also my mom my mom is here i i say that she might not actually be here this morning sometimes she is sometimes she's not um so i didn't do anything that caused sql server to build statistics on this is it's a miracle that anyone ever comes twice isn't it it's a miracle that people ever come to this stream twice um so i didn't do anything that would cause sql server to build statistics so let's go do that select star from whoops from secret plans now everybody's scared to ask questions which is probably true where phase equals uh well here we'll go where task i hate you hate yourself i don't play yeah you're glutton for punishment like uh the drunkard says i'm a bit of an acquired taste kind of like espresso it tastes terrible the first 10 times you have it and then it's addicting so if i can just get people to stay for 10 sessions then they'll probably stick around where task equals profit so if i go through that's true i am i put the lippy end clippy i still love that at that line i just can't believe that i hadn't used that years ago i put the lippy and clippy um so the so what i'm doing here is i'm running a query that's going to cause sql server to have to stop and guess how many rows have profit as their task when i do this this is going to trigger sql server to build statistics on the task column so let's go through and do it i'll execute this query now i'm going to switch back over here and i'm going to run my select stats query again three two one execute and now all of a sudden that's absolutely true too and champagne for me as well now all of a sudden i have statistics on here and if i have statistics i can read them one way oh good to see you going bunga one way that i could read them is the command dbcc show statistics that's how my grandfather taught me in order to read stuff out and it's true it works but this is the year 2020 and we should probably be a little bit more modern when we go and write blog posts and demos so instead of grandpa's way i'm going to say take this same exact query and i'm going to say left outer join cis histogram now there's this brand new dynamic management function dmdb stats histogram and that i can pass in the object id and stats id your next question is going to be when did this come out and which oh it's also not a left outer join it's a cross supply because it's a function cross apply oh jennifer that's interesting i don't know that that's oh that's a really tricky question i don't know if i could force a create for stats that's kind of tricky that's a great follow-up question too uh so this histogram i have to pass in thank you intellisense the object id and the stats id well i can get those from the two above objects so i can say oh object id s stats id and now now i i don't really want the info from the stat what i really want is the info from the histogram let me go put in a prefix for that or an alias for that we'll call it hist and then i'll get his star and run it again and that is how you read the contents of someone else's temp table when they have statistics on it [Music] so the next thing that readers are going to want to do is okay cool let me go look and see what's going on on my server but if you were going to just run this query you would have to know what the name of the temp table is and i know my readers are entirely too lazy unambitious in order to go figure that out so i need to give them another query that will just loop through all of the objects inside tempdb and show you the statistics for all of them so let's write that next what we'll do is we'll take that exact same query let's come down a little and now this time i'm going to give them say for example oh name now i don't know yet we're going to think about that one uh name thanks glad you're glad you like it uh oh name i don't need that twice as temp table name and i'm probably gonna wanna i don't know that i wanna keep the whole entire thing in there because it's gonna be extremely long but let's see how i should get a netflix special netflix and pill jack that's not really related to this so i'm going to hold off on that when i do open questions and answers then you can ask it then otherwise it's kind of like right now me asking you what you're wearing and i know you would love to tell me not really interested uh hissed star now instead of saying where own name equals sql plus secret plans i want to filter this for just temp tables so i'm going to say select star from sys all objects and execute the things that i'm probably interested in are user tables i'm probably only interested in seeing statistics on user tables so let's filter for instead of filtering by object name let's filter for where o type description equals user table i could also filter by just type type is like a shorter version of that it's just that i like being kind of clear and explanatory when people are reading my code i'm only saying that on a stream anyone who's ever read my real code knows better than that the hell are all these one-letter aliases um so now we have oh i love it so now we have a list of all of the temp tables and these uh statistics elvis does does this work for table variables no because there are no statistics very good on you for catching that there are no statistics on those that's good most people don't get that so you're above average even despite the elvish kind of title on there i don't know if elvish fiend means that you're a fiend but you act elvish or elvish means that you're of the the species elf and you are also a fiend that's an interesting question if you're short of stature or you just feel short of stature so we're getting closer i don't have an order by in here and the other thing is kind of tricky is i don't know what column this is i know what the contents are i don't know what the column is ah okay there there's me and there's a good word play joke in there and i can't quite make it worse work i had an ac phase in it so right exactly that's what i was thinking that's what it could be genuine fiend only acts elfish that's what i thought it was or at least i read that way at first so now i really would love to tell them what column the stat is on so to do that let's look at select star from sys stats i love uh just using autocomplete because it gives you a quick idea of what's available to us to go query in sql server and i bet what we're going to find is stats columns so stats columns if we join out to that that's that's normally information schema is one of my favorite go to's as well but stats columns let's go see what's inside here trowel okay so now we have the stats id and the stats column id so let's go get that left outer join now cranky cranky says will this work for a large number of rows for those of you who are statistics pros you may remember that statistics only have a maximum of 200 steps often i'll find even when there are less than 200 rows you won't get 200 steps in the histogram because sql server doesn't need all 200 steps to accurately portray your data but that's such a good question that i want to capture that for the blog post so i'm going to make a note over here cranky old mustard says uh third because i think you have the yeah perfect uh uh a number of steps in histogram large tables so there we go so it's okay sukvere uh you know it's some day the system utilities will work for you as well for you as they do for me that's a vicious slide they don't work for me very often either i just got really lucky so left outer join sys stats columns c on o object id equals oh who wrote that o object id c object id and s stats id equals c stats id so this is going to give us the column of the table and if i go c star hist star and then no postgres is a totally different uh product totally completely different um so this now gets me the column id but i don't know what column it is so now i got to join out to something else in order to get what the name of that column is because my readers are surely not bright enough to know what column is in each table especially for things like temp tables that are constantly getting created and dropped so now we got to go through and join out to get the columns let's see left outer join are these at this point these could probably kind of be inners well i kind of this will show me if there's a temp table with no stats uh left outer join cis all columns we'll call this sc then sc and uh c uh is it valid in any stickle version in flavor no only in sql server chocolate edition only in chocolate uh do you need to check individual stats where the new function is not available those of you who are running antiques roadshow versions of sql server i don't give a rip about you you're dead to me that's not true i don't have a lot of viewers i care passionately about you but you're welcome to do it another way with like dbcc show statistics me however i'm in the future this is the year 2020 so i'm using sql server 2019 because that's that's all that's available if there was 2020 available i'd i'd probably use that uh c on i don't know what's insist all columns let's go take a look uh so sql select star from cis all columns and see what we get inside there it looks like we have object id and column id the future is now there's a robocop joke joke in there somewhere too uh object id equals c object id and sc column id equals o column id so now at least i know what the column names are so let's go get it is c name c column id i don't know that i'm going to get i'm not going to bother with joining out to types to show what kind of column it is like if it's an integer or whatever what about sql azure that's future looking yeah okay good luck to you then let's see here so i think that gives me everything that i want i do need an order by order by i'm going to be a terrible person no i won't oh name uh dinesh dinesh dinesh that's 15 years old that went out of support like 10 years ago dinesh do you need a hug i bet you need a hug i bet you came here looking for friends dinesh we love you we hope the best for you that's rare that i am nice on a stream but there you go i really do feel for you column id [Music] f is the appropriate reaction there he came to driest tears there you go uh invalid column id oh it's over there oh it's not o column id you it's c column id there we go uh so then let's go through and execute that all right cool so now we have what oh okay so it's uh it's doing the blitz cash procs which is a global temp stored procedure uh there we go and there's our secret plans so secret plans and the task it is kind of actually interesting um it is kind of actually interesting that it shows the global temp uh version so that's kind of cool so this achieves my objectives oh this gives me everything that i want for the post so now now that you've seen the demo let's go write the blog post and this is usually the way that i go and write blog posts is i will go write the demo scripts out that i want first and then i'll switch gears and go back and write the blog post because often whenever i'm doing the demos i run across something that i wasn't expecting to write about or i go you know what that's not really the best point for this blog post like that i learned something else and i go i should really blog about this instead it's probably a good time to take a quick break and thank our sponsor for the week so my sponsor for this week is quest software and they put together an ebook for query tuning advice you can go get that for free over at brentozar.com go slash optimize in blanky i like your or just plain text just put it in like a word document and hire a bunch of college interns to go find the stuff that you want iostream i am going to block you that is not okay to spam other people's uh streams thank you all right so quest software at brentosar.com go slash optimize now i'm gonna go write the blog post now to write the blog post um normally i would i kind of do it inside of a vm i do everything inside the vm and i use the windows screenshot tool and all that crap i don't have the the everything set up right quite that way to do that in this vm that i'm using i'm using a vm from our mastering query tuning classes so it's going to look a little bit kludgy as i go back and forth and i write this thing so for me to write it let me go grab another vm real quick let me go grab a vm or go grab another window move my browser around and say move this over here just a second pay no attention to the man behind the curtain move this over here and there we go so now let's come back over here so now i've got a browser here where i'm going to go through and write my blog post i'm going to go fire open brentozar.com yeah i use use doctor inside a virtual machine for maximum sadness um posts and i think i had it started it over on another window let me go close that window whoops okay so save that draft just so that i've got it and then close it come on through close that little guy and minimize him and we'll come back over that's not the key that i wanted to hit so let's see here the new one that i'm doing is how to write how to read someone else's temp table contents there we go and now so let's say so now is when i get to put in the justification as to why we're doing this whole hot mess so let's say you need to troubleshoot someone's query performance and they're using temp tables tables you want to peek in at their temp table contents from another session while their query is running sql server makes that a little bit challenging go on realities maybe you should shut up and read i'm literally telling you out loud pay attention so so in here the sql server makes that a little bit challenging let's start by creating a temp table in one window of ssms then let's go switch over to my remote desktop and we'll go back over to our original demo over here and copy come back over here and that's not where i wanted to go at all i wanted to come over here there we go so i use a little uh code highlighting tool inside of wordpress there we go so then open another tab in ssms meaning another session and try to read the contents of that temp table of that other sessions temp table temp table all right there that goes and then we'll come back over here and we'll do a win screenshot of this little fella and come back up here select star from secret plans and we'll show the error that we get i'll fire up a little screenshot here and there's that and uh king julien says do you use restream yes uh brent are you not affiliated yet no i don't want to be a twitch affiliate uh or a partner i actually dropped out of those programs because you can't live stream to both youtube and facebook at the same time and i'd rather help as many people as i can so uh select from temp table now let's drag that in into our blog post and i don't want to center it let's set the right session settings on here link to the media file insert into the post invalid object name said trombone what if we try to uh sad trombone let's try another trick let's try another trick let's use the system tables to get the full exact name of the temp table whenever you create a temp table sql server actually creates it with a unique name suffixed with the hex equivalent of the number of temp tables that have been created since the last sql server instance restart i appreciate that i used to make money on it but it's just one of those where i'd rather i'd rather do the evangelizing to the public you know to teach more people reach more people quickly so since last instance restart so let's go now let's pull the query that we were using to get that to do and run that little fella grab a screenshot of that and do do do and there that goes i think you're welcome thanks uh thanks sally okay so there is get object name let's pull back over to the blog and drag and paste that image in and there we go insert into post uh armed with that table name you know great that's a that's an interesting question so i love how pinal pinal dave constantly posts uh interview questions at the same time it kind of feels like some of them are trivial pursuit you know it's like i wouldn't expect if i had a full-time database administrator who was sitting around trying to read other people's job content or temptable contents like i guarantee i don't want to guarantee because there are some smart people inside this audience i'm not talking about you i'm talking about daniel huttmacher but there are some smart people inside this audience who may have tried this before but i would like guarantee that 99.9 of the audience has never tried this before um and i would guess that because someone had that stack or dba exchange question and people are like no it can't be done and i'm like oh yes it can you just it's so even when p smart people that i respect said it couldn't be i'm like ah it's not entirely true if i want to violate hipaa or pci or sox requirements i can totally do that because sql server is not nearly as strong as it thinks it is arm with that table name let's now try querying it by fully prefixing or by fully by using the super secret uh full object name and then we'll throw that into the blog post just to prove we can't do that either the old way don't tell me what i can't do um execute and we'll uh i'm gonna do enters in here just for the screenshot's sake um so that's not what i wanted to do where's my screenshot key that's not my screenshot key and then and now okay and do this too execute just so that people see that it's not about the object prefix copy that too there we go and come back over to the blog post and drag you in select attempt one two so here's select attempt one and come in insert into the post uh it's not a matter of not fully something monitoring i heard about that somebody else one of my customers like i totally respect century one but any time that you and any monitoring vendor tries to to capture something new we run into problems like that so i totally i totally get it you can just never believe what kind of crazy stuff people are doing out in the field it's not a matter of fully i'm not fully qualifying the schema either because it's not in the dbo schema and somebody out there some rocket surgeon reader is going to say why didn't you try querying what the schema was and i'm going to be like why didn't you try querying what the schema was i don't know you're not my real dad um so so it this would be where normal people give up but you know how i am dear reader not normal uh so no you uh let's save this draft here so now then comes the next thing um now let's tell the tale of uh statistics so because i like putting in headers every now and then so people understand where i'm going uh let's try reading the contents of the let's try reading the temp table statistics like any table sql server i don't want to say any table because table variables somebody's going to say like tables and user databases sql server will automatically create it's it's true david letterman used to have a gag where he would say and great you're right this is a brand new microphone that i shipped this back to dpa because the hair the earset was broken and they shipped me back a new one and when they shipped it to me the pa the company that makes these microphones sends them left ear and i flipped mine around last time to make it right here which is real easy to do but this time around i was like you know what i constantly turn to this microphone over here or this camera over here why don't i just leave it in the left side so it's not so obvious so it kind of worked out well uh the david letterman gag david letterman used to say folks if you're any if you're like me and i have a feeling you're not and i always laughed at that i used to be easily amused i'm still easily amused uh do i want to include the select star why wouldn't i well seriously why wouldn't i i okay so i have this problem with people who have a problem with select star what do you think it has a performance overhead when i'm bringing back a table with no rows like it's not producing any data what's the matter with doing a select star you people are why i drink so see guys you just ease up on the rabbit oh select star oh triggers oh cursors the more you just are flexible and open to things you'd be surprised where it's really not that big of a deal god so he's talking about the second query what second query uh oh this other thing in here okay cool so terry just be more specific we got a lot going on here now terry i appreciate you you're a good person but just maybe try and just give me a little bit more info when you go and yell out things from the peanut gallery let's see here i i get the feeling that that would be a lot of people pressing one jesus then again maybe not see i i don't criticize folks nearly as much in my paid training because they paid it's not because they're smarter it's because they paid so let's come back over here and put that select star in so and i'm also going to select 2b what's your first time in the peanut gallery welcome welcome welcome uh let's see here let's drag this in and we'll say insert in actually i'm gonna put in the caption uh shout out to terry stoneburg for improving this screenshot and then insert there we go so this would be normally [Music] okay cool let's try reading the temp table statistics like tables and user databases sql server will automatically create statistics on the columns on columns when it needs to make decisions when it needs to make decisions about about the number of rows that will match filters on that column let's take a peek in temp db cheers angel uh to drunk god i just now i just now got that uh pained paid and pained and painted uh for like my drag queen uh customers that they paint their faces that's i'm a huge fan of uh trixie and katja let's take a bacon tempdb to see if any statistics exist on that temp table and put this back back to work it's saturday it's the sabbath you're not supposed to be working don't ask me why i'm working i'm not really working i'm kind of giving back to the community although kind of not um so let's see here let's get let's run this query and then oh it's this admin life yeah no i i feel you there i'll uh drink in your in your honor later um oh shoot okay so i got to recreate the statistic now because or i got to recreate the temp table because the statistic is created that'll break my storytelling i can't have that so let's go drop that temp table drop table secret plans and then let's go back and recreate it and do the select now let's come back over now this is going to be a fun moment so what just happened was i dropped this he's he could really just we could copy paste that and frame it and it's pretty much true 24 7. so what what is about and what i'm about to see and i really don't know and i'm curious to see what happens is i dropped the temp table and i recreated it sql server will cache temp tables though and it'll even cache temp table statistics which is kind of awesome except when you don't want it to happen like right now so i'm really curious when i go and run this select did sql server keep the same temp table name and did it keep the same temp table statistics so let's go find out it dropped the statistics now what i don't know either though is did the temp table name change because it's entirely possible that the temp table name changed as well so i'm going to say where name like secret plans [Music] just to make that a little bit easier on the screenshot too so okay perfect so we'll say select o name execute all right so now we have a little screenshot that shows uh that there are no rows for systats that's beautiful that's exactly what i wanted to see copy that out and paste that and let's save this as a work in progress uh just in case all hell breaks loose and i lose something uh so let's take a see if any statistics exist on that temp table uh no statistics let's copy that in i'm kind of keeping an eye on this the timer because at 15 minutes in i'm going to or 15 minutes from now right at the top of the hour i need to go switch over to go to webinar and prep for the watch brent tune queries so we'll see if i actually get finished with this blog post or not we'll find out uh dress the person who's created or the session that temp table doesn't have statistics yet this is the first weakness in this blog post strategy to read someone else's temp table contents you're only going to get statistics when folks have queried specific columns let's uh let's trigger that by qui by going back into the original session that created the temp table and running a query running a query that will trigger automatic stats creation on one of the columns excuse me all right let's go back over there come back over here and copy or actually here we'll just run it so there we go done step three and come back over here and put you into there and insert now go back over to the window where we were diagnosing statistics and check again and come back over here and now let's check again run the exact same thing what i'm sure that has something to do with execution plan contents and i am too not going yup dinesh we already covered that and what you can do is uh go ahead and close your browser now and then when you're when we're done with the stream you can go back and watch from the beginning so that you don't continue to ask and talk about things that we have already covered thank you so let's see here so now we got that i know i'm a butthole but you would be too if you were doing this kind of thing in public and people kept doing the same damn thing um so now let's see here statistics on temp table and then come back over here and off you go to voila now armed with a few more uh filters or film more joins we can see the contents of that uh temp table statistics uh uh so let's copy that and let's say that uh and let's see there execute beautiful and now let's give them the beautiful conclusion query we're going to just copy paste that in here in a second so then let's take a screenshot of that guy and there we go then we will give that oh i should finish with a semicolon because i'm a good person only on tv not even really a good person on tv um here's the query if you want to look at what's happening right now in your own temp db paste that into there paste add and then oh i could do i should do one more filter in that i should do step number column id stats id s or hist his stats id hist stats id hist step number all right there we go and then that's in there execute just so that i can give that to the full query to them and go paste that in there and paste and save and then let's get the screenshot out statistics contents and drag that in over there over there and insert into the post okay uh well we should put a caption in on there uh voila or presto we see the secret plants hope that it actually needs to go down there that's fine okay there we go uh to do do do limitations of this technique and let's go put a header on there uh as in the live stream uh cranial master pointed out reminded us okay let's get in here uh reminded us that statistics only have have a max of 201 steps in the histogram and even then we may not get in period so we won't see all the data so we won't see all of the data on uh real life sized tables in addition even if there are under 200 rows sql server may not use all 201 steps in the histogram because it may not be necessary in order to paint a great picture kishore if you're talking about your glasses i would agree it's time to wipe the vaseline off so that you can follow along in order to paint a great picture of our tables contents in addition as paul white has so lovingly blogged temp tables and their statistics can be reused from one session to another because of that you wouldn't want to say that one specific user's query activity has produced the statistics that you're seeing in these queries however these query this query this query this diagnostic query however that's actually a good thing this diagnostic query can show you when someone session is running with temp table content temptable statistics that may not uh match up with their current workload now let's go get that blog post from paul white paul white temp table reuse he's got a couple of them i think this uh sql performance let's try i think it's this one uh yep yep that's perfect copy and temp tables and statistics can be reused paste there's another relevant actually i'll just stop it there so set featured image and there we go set featured image i was gonna say i could go on and talk about something else too as well but i love it that is perfect ship it oh whew all right that is perfect um it's not perfect but like in terms of what i can pull off in one hour that's 600 words and it's a pretty good indication of when you see someone's blog post it's like 500 to a thousand words long with with technical articles with technical content like t-sql screenshots etc an hour is like the bare minimum starting point for writing something like that like i knew going in i had in my my list of ideas for a while of what i wanted to blog about i knew that that was something that i wanted to hit i knew what the content was like i kind of knew where i was going down on that route beautiful i'm happy with that and of course people are going to pick problems with it that's just the way it is whenever you write anything um let's stop here for a second and give one more shout out to our sponsor so our sponsor for this week is quest software they've written a uh totally free query tuning book you can go get that over at brentozar.com go slash optimize it's got a bunch of tips on how to do query tuning index tuning and make you smarter because lord knows you and i both need it we'll stop here we're gonna take a short bio break i say short but realistically my session at sql saturday oslo starts at a quarter after the hour starts at 15 minutes after the hour and i'm gonna have to hop in to go to webinar so what you're really gonna hear is like behind the scenes of me going in to go to webinar to get set to to watch things may explode and go haywire i may even have to stop this stream but you can also go search for sql saturday oslo and go join in on the go to webinar there but i'm going to do everything that i can to make sure that y'all can actually watch along um so five minute bio break where i'm going to go off and do a bio break and get water and all that for the session that we're about to do and then it's time to do watch brent tune queries so i will see all back in here shortly i'll put the pause up and then after the pause i'll i'll let y'all listen to me struggling along with go to webinar see you in a few so so do my do with um so all right party people so i am connected in to go to webinar let's see if it actually works um i'm gonna play around with trying to see uh share my camera on there so you'll get to hear some behind i should probably just leave the music on rather than hearing y'all having y'all hear me fumble around behind the scenes whatever here we go let's see here so i can choose okay that's perfect listen through loopback audio that works let's see if we can is there preferences for go to meeting there is webcam looks like i can choose acceptance good morning good morning how's it going it's awesome good so have we had any sleep uh the last few days oh yes lots i am in uh all the time it's uh totally give me just a few minutes to get my webcam set up and whatnot i'll be right back sure thanks okay so it looks like i'm not going to be able to how am i going to do that it looks like i'm not going to be able to oh i could do a different webcam because it's not letting me grab the deck link it's not letting me grab my virtual camera because obs already has it taken i have a fix for that hold on a second here i have another webcam that i can put on because frankly i'm less worried about the gorgeous quality of the go to webinar webcast as i am the recording because the recording needs to be gorgeous all right here we go i have a cable that i have that let's grab this pull this up out all the work that i do for people it's unbelievable you know you want everything to look flawless for everybody involved all right let's get this oh my desk is going to look like a hot mess which end goes to the host that goes to the sink this goes to the host all right big money no whammies let's drape this webcam cable across my desk then let's put it in on a little webcam in the center of my desk it's not really high enough it's way too low i got another tripod lying around back here nothing like doing it live somewhere back here i thought i had another tripod i have a disturbing amount of camera gear but i do not have yet another tripod oh i do have one i hate using that though now you know what i'm gonna do i'm gonna no i'm not because i need that stream um there's one other trick that i can do i can put that on a box and that's exactly what i'm going to do somewhere in here we're going to have a box in this apartment that i can set the webcam on oh quality is job one no damn it we unpack the boxes there's a stack okay i had to be quiet there for a minute because it's right next to where my wife is sleeping she's still sound asleep all right now i have boxes so i can set the webcam on top of a box you thought i was joking didn't you no no no no this is how we roll here at the ozar house that's not gonna stay closed okay we have a box is that wide enough for the tripod it is perfect god this is so trashy this is so trashy we're gonna do it anyway all right screw you in screw you all the way in make you go there and oh this is isn't this tacky this is so terrible webcam and switch to usb you don't show the oh there it is good okay good logitech brio uh no malloy oh that's very sweet of you but no i should be okay there um it's a little odd it's gonna be the nose cam oh i can move this up hold on a second oh that's gorgeous oh that's not quite so good i hit the back of my computer hit the back up just a second there we go that should do it and let's see i'm going to change cameras here just there we go just because i'm going to try and catch when this thing goes too high up in the air that's it right there that's my standing desk monitor now okay perfect all right good we are set to go there and now let's switch over to hoo hoo that's perfect now is it showing the webcam it's kind of sort of showing the webcam where did that yes yes it worked okay mate pass that little hurdle all right so i got that on now i need to share my desktop uh let's see here oh i need to for that to happen i need to be the brand panelist me uh oh i can't share my oh because i'm not the presenter yet uh roon do you want to share let me share and all okay cool you've been made the presenter share remote desktop that's it right there show this window howdy hey i'll move that out of the way just so that just so y'all don't freak out marlow will be your moderator today so i'll leave you into his capable hands oh cool all right cool let's see is uh move that over there that's out of the way so it shows that i think we're good there howdy marlow how's it going hi hi bro how are you very good very good where are you from i'm from oslo nice very nice what's the weather out like there today it's a bit cloudy now starting to get a little bit colder yeah ah so let's see here so y'all can hear me okay and you see the screen okay yeah let's make sure that when i zoom in it actually zooms in yep beautiful fantastic okay cool all right so we've got like 51 folks in here oh let's see i should look at the oh i can't look at the attendee list that's probably better i shouldn't be distracted and be looking at the attendee list anyway so that's good throw all my stuff down so how do you want to deal with the questions that you want if you don't or if you want to just have me i think you have to make me a panelist so that i see the uh the questions as they come in i can't quite remember um i don't see any questions now um i don't know if uh if there are questions that have come in yet or not i'm gonna move this around just so it isn't on the screen for the recording move that over there yeah but either way or if you um or yeah it's up to you if uh right now i don't see any in there if anybody's in go to webinar go post a question oh man as soon as i say that there's gonna be like 500 questions come in um but uh if if y'all want to post a question and then and then we'll see if we get them in and how that works people are asking we're sleepy where's cliffy ah oh i have his hand go get uh clippy's hand while we're waiting let's see here i have done webcast as the stig before but uh i don't know let's see here i have both of his hands my wife is still determined to make me a real clippy costume she wants to make me a full-blown costume with plastic pipes and stuff so hey buddy how's it going i can't really pick anything up with these unfortunately but and i also have the green bodysuit but i'm not putting that on yet it's amazing they have inside these gloves there are holes for your fingers but you've got to pick and choose which fingers you want to be able to move because it's really hard to move all of them you know it's kind of tricky so i end up using the bottom two this is entirely too much fun there we go all right cool so now i can say one two three oh it's such a mess marlo's like we don't have time for that all right there that goes yeah i think we're good here i should have like music going in the background oh marlo went off camera slick i got i can't keep a straight face the whole entire time i should probably take this off it's getting warm enough here in san diego jonathan says and to think some people have silly hulk hands i never did the person who uh my photographer the person who i say mine as if i owned her but the person who brought hulk hand to my photographs session like way back years ago um never gave me the hulk hands and so uh i never actually saw them oh that's weird it just like acted like it disconnected or reconnected oh it made me the organizer and the presenter oh now i see all the questions okay there we go there we go lots of test questions hugo says thank you hugo test question there we go i'll go ahead and delete some of these questions so i don't have to see them i'm going to get that's interesting looking at the past questions there someone says scibase someone says pure awesome i'm going to go ahead and delete each of those because i don't need to see any of those popping up whenever the quest session starts get rid of that get rid of that oh and it's funny hugo was in was it hugo or irland i think it was erland that was in i did a watch brent tune query session years ago and hugo was i want to say cameron was hugo or or irland uh over on facebook uh someone says hello sir i have a huge interest on sql i'm a beginner in this field my question for you is where can i start to from become a where can i he typed way better than i'm talking let's just say that i said uh where can i start to become a sql developer like you oh this thing the autofocus is all screwy um and i i i don't know uh that you want to become a sql developer like me because i was a pretty terrible sql developer as anyone who's ever worked with me or read my t-sql can attest um but uh the the first place that i would just go and say is ask your manager uh what what problems they need solved like what do they need to know about the data what do they need from the data what questions do they have what do they need to know in order to make more money for the company whatever use that and then go learn whatever t sql that you need in order to solve that problem but that's where the where i would start from is always start from the business because what you may also find out is that the business needs something other than sql developer they may need machine learning artificial intelligence they may need someone to go get them large clippy hands for a costume you know who knows tajer webb says aren't you doing a sequel saturday today this is the sequel saturday we i'm streaming live on facebook youtube um and the sequel saturday go to webinar as well now do we have anything that we need to say for the sql saturday like is an organizing slide at the beginning or no everybody everybody who works for the sequel saturday has uh dropped off mike and it is time for me to start so i would assume just i should go ahead and pack up and start rocking and rolling so let's do this so let me get a nice clean start here i always like to three two one huh so hi folks my name is brent ozar and i'm going to be talking about in this session watch brent tune queries some sessions there you kind of have to explain the title this session it's really clear what it says on the tin that's exactly what i'm going to be doing i'm not going to even explain anything about my background i'm just going to go jump in so that we can spend a maximum amount of time tuning this query i'm going to start with sql server 2019 i'm using the latest and greatest sql server as of right now it's cumulative update 6. i'm using the large version of the stack overflow database the 201806 version it's about 400 gigs in size ballpark and i've assumed that i'm going to go ahead and use the latest and greatest compatibility level i'm going to give sql server the best chance that i can possibly give it and then i've also created some indexes in order to help my query go faster i'm gonna assume that the people who brought me this query have already done some index and query tuning they put in these indexes in place i'll just go ahead and highlight these and execute them and see it fails because the index already exists oh i should also state i'm a terrible presenter right up at the very top there it has brentosar.com go slash tune queries this entire query the demo database plus videos of me doing this at other conferences sql relay online stuff live streaming doing different queries every single time like today's query is totally brand new as well so down here my users have given me this query that they say is performing really slowly they've got this stored procedure here where we've said go find me the top users in a given location this query takes a parameter for a location a start and end date and it's hitting the stack overflow database to find all the users who live in a given location and it's trying to find the questions and answers and the comments that they've posted inside that date range seems pretty fair it's not using cursors or looping through stuff while things happen seems pretty straightforward let's go see what happens when we run it and whenever someone brings me a query to go tune i like to work off of the real execution plan i don't mean the estimated plan the sql server built before the query started because here's the thing most of the time when sql server estimates query plans and designs out which table it's going to process first how much memory it's going to need how much parallelism it's going to need etc most of the time all day every day your sql server is doing a heroic job of accomplishing that task it's doing 99.99 of the time it's doing a great job at building query plans it's one in a hundred or one in a thousand query plans that really blows chunks and those are the ones that i'm going to have to focus on tuning to make perform as well as possible when those are happening it's often because sql servers estimates and its actuals didn't line up it's like me if i don't look at the shopping list if i just go to the store my wife hands me a list and i just go start shopping i might have only grabbed a hand cart when really i need a whole push trolley because my wife has asked me to get all kinds of stuff especially alcohol because she has to deal with me so what i'm going to do is i'm going to go take this query and since it it only does reads it looks like it doesn't look like it writes any data let's go ahead and just put it into production and then let's go turn on our plans i'm going to turn on actual execution plans up here and turn on my query tuning options and then execute the query and while it executes i'm going to explain what i mean about my query tuning options set statistics i o and time give me a rough idea of how much work sql server has done in order to accomplish this query like how many pages it's read how much cpu time it's burned and well things aren't looking good i can see why users are getting frustrated now when you have a query that takes a long time to run you may want to find out what its execution plan looks like and you've got a lot of options to look while the query is running my personal favorite is using sp blitz who sp blitz who on modern versions of sql server will give you the live query plan while the query is running and that's super helpful to me because i can click on it and it's not animated it's not showing things move through the query as it works but it does show me not really the progress either because i can't really tell which operators are completely done there may be some that are still pushing rows through remember this isn't the final we're just looking at one snap moment in time when sp blitz who ran but i can see things like sql server estimated 229 rows were going to come back when in actuality almost a million rows came back if i could give you one thing just one thing to walk away with from this session and i have to give you this early because you may walk away at any time who could blame you i'm a terrible presenter if i could give you just one thing to walk away with from this session look at estimates versus actuals start at the top right of the query plan at the top right of the query plan was the first thing basically that sql server decided to do i don't always read a plan from right to left but when i'm in a hurry and i just want to get a quick glance i'm going to read from right to left and i'm going to compare the estimates versus actuals now in the old dark days when your grandfather and i were doing query tuning not together i never knew him during the war if i go through and i look sql server says estimated number of rows uh per execution so this is how many rows he thought it was going to bring back and then actual number of rows for all executions is how many rows he actually brought back now i apologize for the scatter shot look of this query plan here microsoft when they need to dump things onto a tool tip they tend to use one of two approaches either they alphabetize it which makes no sense for how you want to read it or they load up the data cannon and they just fire it at the screen they have a shoulder mounted data cannon that their developers do and they blast it right at the screen wherever the data happens to end up that's where it ends up here you can see that for example we lost the word actual in the shrapnel here sometimes things say actual sometimes they don't say actual actuals and estimated are all over the place makes no logical sense so these days instead of using the tool tip what we do these days is we just hover our mouse over each operator and the bottom number is the number that sql server expected to come out and the top number is the actual number of rows that came out so here we brought back 449 rows of 120 expected rows that's great now look i should say before i say that's great you have human expectations you believe that sql server should get everything exactly right i have really low expectations i'm happy if sql server's guesses are within about 10x meaning he estimated 120 rows were going to come back i'd be happy if anywhere between like 12 and 1200 rows came back at least it would be vaguely correct generally speaking when estimates versus actual are within about 10x sql server understands the work that's going to be involved in the query i didn't say the query was going to be fast i just said that sql server understands how much work is going to be involved in the query but if he says i think i always use a guy's voice for sql server because he's dumb and stubborn and he refuses to ask for directions he's all trust me i got this when he doesn't usually got this if sql server says i think only one row is going to come back and then five million rows come flowing through the plan then that early decision of estimates versus actuals will have a cascading effect of problems throughout the rest of the plan so what i'm trying to figure out just did sql server build an appropriate plan for the work that we're trying to do i'm going to work from right to left top to bottom just quickly quickly looking to see where did estimates versus actual go off by 10x or more so in here when you see like this 374 percent at the bottom that's how far off its estimates were 374 percent if that was your child and they were doing school work at home you'd be like we're gonna have to stay a little later and do some more exercises except you wouldn't say that anymore because during this day and age all you want to do is put your kids to bed so that you can do some drinking in peace i say that i don't have children so it's very easy for me to joke about that kind of thing i can drink all day long if i want to this is espresso as far as you know so not that i need that to be this level of amped up it's kind of disturbing how excited i get about sql server 120 rows expected 449 came back 374 percent off it's actually fine 374 percent off that's fine too a thousand ninety one percent off oh hold on tiger duh we start to have a problem here sql server only expected 121 rows to come back a thousand rows came back look what's 900 rows between friends is i continue to go across here still vaguely correct not even close to vaguely correct so now we're so far off it's like me with my expectations of what college life we're going to be like i'm going to get a hundred percent and then i've dropped out three semesters later so we have a problem here where sql server doesn't quite understand or can't accurately predict how many rows are going to come back from some of these operators like that comments operator down there sql server thought that only 229 rows would come back in in reality 895 000 rows came on back when i see this i start to ask questions i start to go what could i do in order to improve sql servers estimates in order to improve sql servers estimates it's not just about numbers because at the beginning for example sql server estimated that 120 rows would come back sql server thought and i'll hover my mouse over here so you can kind of sort of see except you can't kind of sort of see because god knows they put stuff all over the entire freaking plan when sql server was looking at one particular location like reading or london united kingdom or china or whatever it was looking for how many people did it think lived in that location so sql server believed only 120 people lived in that location now here comes the next trick how active are those people have they been asking and answering a lot of questions at stack overflow or hardly any at all let's go back over to the query and let's think about how the query works so let's go back over and the query finally finished and we'll look at the execution plan here in a second let's go back over and look at that query plan so sql server is tasked with go find the people who live in one particular location after you find the people who live in that location how talkative are they how many posts and comments have they left how's sql server supposed to know sql server just assumes people are average sql server doesn't know that some people in some locations like to talk a lot like my apartment in san diego they also don't know that some locations people don't like to talk at all so sql server had a rough idea of how many people lived in say reading united kingdom but sql server didn't know that one of those people is john skeet john skeet at stack overflow is absolutely legendary i don't know how this person did as much talking as stack overflow as they did john was the first person to break one million reputation points at stack overflow seemed like during like 2011 2012 he was just sitting answering an answer asking or answering questions and posting comments like full time he looked like kermit at the typewriter just banging away stuff continuously and his answers were really good his answers were epic so sql server didn't know that john happened to be one of those people who live in in reading so to be fair sql server didn't think that it was going to have to find a lot of people or he thought it would find a few people in reading one of them's john who was super active inside that time range well that's tricky if i look back at that plan that i captured at that one moment in time sql server knew there were 120 people but it had no idea how much john skeet loved to talk it had no idea it was going to bring back so however many comments he has statistics on the locations of users or does he let's look and see what that index says up there we got clippy up here going hey buddy oh i got an idea you know would make your query 98 faster it'd be 98 faster if you had an index on users on location and while you're at it you should include some columns on there too and clippy's even including an envercare max that that's rather large and about me stack overflow people can write their entire profile in the about me if they want to they can write a letter to grandma if they want to but clippy says that this is going to make things 98 faster so you know what i'm actually going to do it so let's go copy that out close it and then i'm going to go create that index while clippy does or while we go on and investigate more things about our query let's say we're going to call it location includes and let's go create it indexes are free right screw it who cares clippy's word is gold as far as i'm concerned let's do this now clippy said just to be epically clear clippy said this would be 98 faster the impact would be 98 so now that the query is finished let's go in and look to see his query took 48 seconds in order to run so i'm i'm really bad at math but if i take 48 seconds if i say select 48 seconds times .02 because clippy said it would be 98 faster it looks like that after clippy's index gets created this query is going to finish in less than a second great clippy you're a god among animated assistance now the index finished creating let's see clippy's amazing work now just to be fair i don't have to do this but i'm gonna because i want to give clippy every possible chance dbcc free proc cash and i'm going to do it multiple times just so that y'all can see i have completely freed the plan cash plancash was tied up in a closet it wants to be free and now let's go see how this thing works one second would be 98 faster that number's made up it's out of nowhere it's just as good as his suggestions were around your resume hey buddy looks like you're trying to get a new job maybe i can help i had to get a new job after they let me go from the office team it didn't make 98 improvement we're still going it's been going for quite a while here so let's go take another look at that live execution plan let's go see what's going on now so here if i go click on it now if i go in and look to see what sql server's estimates were what the heck sql server now you thought that only nine rows would come back damn it sql server your estimates got worse your estimates got exactly worse instead of better now you thought only nine rows would come back so you thought only 16 or 18 comments would come back come on dude really and i'm bringing back almost a million comments already clippy is just trying to help but he's kind of like siri and cortana and whatever those other people's names are hey google it's a starting point but it's by no means the finishing point and it's hilarious that the query took exactly the same length of time in fact it's slower it's 49 seconds now rather than 48 seconds let's see if we go look at the actual actual execution plan now to see what it's got yeah clippy pipe down now didn't he to be fair if i go across and look at the index that he created is it in there it is so in fairness at least sql server did use the index looks like clippy isn't going to save us this time so how can i get that estimate to be more accurate how can i get sql server to do a better job of guessing how many rows are going to come back is this maybe a problem or is there something else that i could do around encouraging sql server to understand how many rows are going to come back maybe an index would help except i already have one and sql server is already using it sql server is making a list of the users and then going in and checking to see what posts they own and what comments they own but because sql server doesn't know that reading united kingdom happens to contain the legendary john skeet sql server isn't going to make good decisions to give you another example of why this is the case if i go back up to the query itself let's come all the way back up here so up here sql server knew that there were a certain number of low rows in redding but until it executed this it had no idea who they were let's give him a shot at that let's break this query up into phases now gallium bunga says just remove john skeet just filter for where user is anything other than john skeet and so it's really funny that you say that i'll do it i'm not afraid of doing crazy stuff and u id or we'll say you display name is anything other than john skeet and let's try the query again let's put the query up in whoops no stop x puts you into cash and then put you into production and then let's go run the thing again gallon bunga as far as i'm concerned you have the answer because if we just delete john skeet if we work from right to left top to bottom our estimates here still suck the big one they're like not even close our estimates all throughout here suck pretty bad but look how much faster it is john skeet is the root of all our problems at stack overflow i wish that i could go back to users and say things like that i wish i could just say as long as you remove your outlier data you're going to be fine but there is actually an interesting lesson inside here which is the outliers are usually the cause of a lot of your problems where sql server doesn't expect what's going to happen now let's go take that back out because unfortunately we don't want to remove john skeet he is a national treasure not of my country but of another country if i go back and run it again like if this thing's going to be slow again now can i give sql server a better chance at understanding where his estimates are going to go wrong you can't see this from an estimated plan if i go run the estimated plan i'm only going to see what sql server estimated was going to happen and of course that's going to be wrong i really need to see the actual plan in order to get that when i'm looking at the actual plan one way to go get it was from that sp blitz who which will show you the live running query plans it's just that they're not animated my goal as i'm looking through here is to see where estimates versus actuals went to hell in a hand basket if you don't use sp blitz who another trick you can use is you can go into activity monitor now i know activity monitor has a terrible reputation because it is pretty clashy but over here there's show live execution plan which will show you if you're really really lucky an animated version of your query plan running in my case this has about a twenty percent uh success rate like one time in a million this thing actually works out for me that's not twenty percent not very good at math but for those of you who it does work for a great bully for you this right here what i normally run into is that i can't get the actual execution plan because the queries going by in somebody else's session or ask people it's who isn't working this has been a big problem for a really long time and sql server 2019 brought out something amazing sql server 2019 lets us get from the plan cache the last actual execution plan in order to get it we have to turn on an option oh damn it i've already deleted that from up above let me go ctrl z my way back to replacing that from where it was i have to turn on this option plan this option right here alter database scope configuration set last query plans equals on there is a performance overhead to this the more you run queries the more complex those queries are the harder of an overhead this is going to be on sql server i have never measured this but i've had two students in class who turned it on during our functions exercises functions run row by row for example and their servers went to a crippling halt i would just be careful to only turn this on whenever you're doing active query troubleshooting like for the day and i've got full instructions inside the demo script in a blog post about how you go and turn that on once you have it turned on you can run sp blitz cache as people its cache from the first responder kit gives you your top 10 most resource intensive queries and here i can see el numero uno number one is that stored procedure report top users by location we can see that the plan was created in the last four hours it has a low cost because sql server thought it was going to be cheap but in reality it has very high cpu and then if i go and click on the query plan ho ho instead of just seeing the estimates now i see the actual from the last time that the query ran it's not the worst time that the query ran it isn't the longest running of the most reads it's just the last time the query ran now also when i ran sp blitzcash just now what sp blitzcash does is give you the top 10 most resource intensive queries from the plan cache the one you're looking for may not be that one no problem you can say stored proc name equals report top users by location and you can filter out just that one queries plan there are also other options that you can use like a plan hash query hash all kinds of other stuff to find the query that you're looking for so same thing here when i'm looking to find out why a query went wrong i'm looking right to left top to bottom trying to find where estimates versus actual are more than 10x off and how i can improve them well the place where they went 10x off really here starts right at the first operation sql server don't think i'm going to find about nine rows in here oops my bad 449 came back the second most important thing that i can tell you so i said the first most important thing i could tell you at the start was work right to left top to bottom looking for places where estimates versus actual veer more than 10x off the second most important thing i can tell you is that when you see that happening think about taking like an exacto knife and going right to where that is in the plan and saying let me just cut operation right there can i get sql server to break this plan up into two phases can i do the first phase and dump that data into say a temp table so that sql server can then come back on the second pass and go oh oh now i know how many rows are in that temp table and who they are let's go see if we can do it so let's start by making a change to our query now in this case i should say two i purposely write demo queries to be as easy to tune as possible so that i can teach you as much as i can in the time that we have together which isn't very much because i don't really like you my coffee shop is open downstairs however in real life queries it's much more complicated because often the the estimates versus actual when they go way off we'll be out like here and you're like uh sql server what's happening right there at that point and i wish that tools like century one plan explorer would let me click on one operator and show me where in the t sql it is that becomes much harder i talk about that in my mastering classes so here though it's easy we started by looking to see what users are in that location let's rewrite our query just a little let's come back up to the top let's rewrite our query just a little to say first get the list of users in this location so we get better estimates create table users what we put in there i might as well do all my work for the users table and just get it done with so let's say id int primary key clustered because each user has one id i don't always put clustered indexes on my temp tables i'm just trying to be a better person whenever i do live webcasts so there you have it if you didn't know that this was unique then you probably wouldn't want to take this approach plus you can see that i'm joining on that everywhere else so maybe sorting it by that first might help me out i don't want you to think that i always do that usually i just sling something into a temp table and see what happens uh so reputation and display name and varicare 40 about me i want to say is an envercaremax not a 100 positive i should probably set a good example there and look as well users columns yep about me and bearcaremax so there we go so about me and veracare max curses you whoever put that in there and then to do insert into users that sounds awkward id reputation display name about me select uh now this this comes into something a little bit tricky here see how my query says select top 1000 can i do top 1000 in my temp table insert is that going to work no because i'm ordering by the sum of something that isn't on the users table so this strategy of mine here to dump this stuff into the users table would backfire on something that has a hellacious number of rows if it's like location equals null or location equals empty space where there are a ton of users who match so i'm doing this here with keeping in mind that i'm optimizing it for things like reading where there are a limited number of rows i may need to come back and look at say passing in india to see how that works so let's select id reputation display name about me from dbo users where location equals location now instead of going from the users table here i can go from the temp table then instead of doing the filtering down here i can simply remove that out because it's already taken care of by the filter above when i'm doing tuning i like to use another name because it's going to sound crazy but i usually have to do my work in production i know but the thing is i had one of those weird jobs as a consultant where people are often asking asking me to fix something on an urgent uh you know crushing basis where the production is going down we got to fix something as quickly as possible i don't want to alter the production stored procedure i'd rather put in like bgo at the end of it so those are my initials i don't use my just first and last name for reasons that will be obvious to fifth graders who like to make poop jokes at heart i am a fifth grader who likes to make poop jokes if you can't make temp table or store procedures in production you can also do temp store procedures so i can say for example pound report top users by location this also works well so if you want to test something and you don't want anyone else to see the same stored procedure that you do this works great so let's go ahead and do that now let's run our now i don't have to ever do free proc cache again because i have this stored procedure just that's all entirely my own just like my favorite car let's go hit execute on this three two one do i have live yeah i have actual plans turned on now we know that this query used to take like uh 50 seconds in order to run let's go hop over to our window with sp blitz who and go take a look to see what the live query plan looks like and let's see if our estimates get any better now one of the cool things about live query stats you can't really tell what percentage done something is when you're looking at percentages that's percentages of the rows that were brought back but you don't know if that's 42 percent because what if for end up from just to be clear i'm pointing at this little fella right here so what if or we'll even go to the very beginning one so the first one around users what if sql server thought 449 rows were gonna come back but in reality only seven did or what if it's only just read the first seven rows of 449 you really don't have a good way of telling when you're looking at these plans however i sure do spot a problem even though we've only read a few rows that's terrible sql server thought that 452 comments were going to come back but in reality 220 000 came back that's not so good and in fact things are getting worse i don't sing often in webcasts but that's pretty terrible right down there that's a minute and a half and this little fella is still going let's go back and look one more time at our estimated or our actual execution plan that's flying in progress so let's go in and see here sql server still only has seven of the 449 rows you know what he did john skeet it's probably number seven he hit john skeet and now he's in the sad process of finding all of the things that john skeet has ever done it's gonna be a hell of a lot of work john skeed is a prolific person this query plan represents a whole lot of work but what's missing if you're gonna do a whole lot of work in sql server what would you expect to happen what would you see on this execution plan that we're not seeing here now you might say a missing index request and that's fair because we do have a couple of index seeks plus key lookups that's one opportunity you don't see clippy piping up going hey buddy anytime you see an index seek followed by a key lookup you could immediately go hover your mouse over that key lookup so like if i hover my mouse over the key lookup on post if i hover my mouse over the key lookup down at the bottom there's an output list these are the list of things that sql server wishes it would have had over on the index if we added this over to the index the index would suddenly be covering and we wouldn't have to do all of these key lookups tanya tanya nails it tanya says the other thing that we're missing so one thing is that we're missing missing index hints but both tanya and junior dba over in the chat both say parallelism sql servers like don't mind me this query looks really cheap i should be able to execute this with just one core this query only cost 3.5 query bucks that's nothing when i was your age i carried five query bucks around in my pocket all the time so sql server doesn't think that this is going to be more work so it's not allocating a lot of course to it so there are two angles of attack that i could take now the query finished let's go over and take a look at the actual execution plan would be moving into putting in the temp table made it go to four minutes oh that's just delightful putting in the temp table made it go to four minutes if i look over at the execution plan here's what gallum bunga was asking for over in the chat gowimbunga says hey i don't see a yellow bang on the plan yeah we don't get yellow bangs all the time in the in the live query plans because it may not have started spilling yet for example now clearly it has spilled here we're spilling to temp db because poor sql server didn't estimate anywhere near enough memory in order to sort all these rows so now if i work from right to left top to bottom estimates versus actual let me zoom out and come back in here so top right sql server believed 449 users would come back they did sql server then thought i'm going to go look over in the post table to see how many posts they have he was vaguely correct now we get over to the comments table and it's become a poop show oh man i love the sql server management studio team has been on fire really doing impressive improvements that make my life easier as a query tuner they haven't fixed everything they're still addicted to the shoulder data cannon but you see the times on here it's showing me that this operator in the plan effectively finished at around 52 milliseconds in there are a lot of gotchas to this what the times mean on each of these and i have a link to eric darling's excellent blog post over on the resources for this session talking about how the time on the operators means different things depending on whether these are in row mode or whether they're in batch mode but if i'm going to go look at what the problem is holy cow my estimates versus actual went to hell in a hand basket right here now i might look at what could i do in order to improve that can i get sql server to have a better understanding of how many comments one particular user left when i'm doing query tuning out in the real world when i start my query tuning efforts i have a half hour hour glass that i keep on my desk and i try to flip it whenever i start query tuning to make myself check in every half an hour and go am i on the right track so that when i look down and this thing is empty i can then start to go oh oh i don't know that i made a lot of progress in the last half hour and that i was necessarily on the right track and is it time for me to maybe switch tracks and what progress do i think that i can make in the next half hour that will be the most effective so i know when i look at this query plan i don't think parallelism is going to help if i balance the work across multiple threads i don't know that that's going to improve things if i do index tuning i don't know that that's going to improve things either but i do know that it will reduce the number of key lookups that i have to do if i hover my mouse over this and i look at the output list if the output list is fairly small i might go in there and add those in so that sql server has doesn't have to do all these key lookups back and forth we've come to a branch inside here dear reader and i will let y'all choose do you want to work on indexing or do you want to look at parallelism because i have tricks up my sleeve either way so over in the chat or in comments tell me which one you would rather prefer and i'll give y'all ten seconds seven nine eight seven six five four three two one saying indexing santa's saying parallelism jeb says why not force the hash join you could totally work on that hugo says i want to fix the dang estimate i hear you it's tough several people looks like lots of people are saying indexing a few of you are saying parallelism so we'll try indexing first and then we'll see how much time we have left over and see whether we want to play around with parallelism and i love that hugo hugo's the only person who's like i want to fix the uh fix the uh yeah no that your name no i i rethought that the one in chat who's like hey why can't you say my name and i'm like yeah no no no not so much okay so when i'm gonna do index tuning if i'm gonna go track all the index tuning problem on this anytime you see an index seek followed by a key lookup hover your mouse over the key lookup and then look at things like the output list down at the bottom if the output list is fairly small like think not envercare max if the output type is very small then i don't mind including it in indexes generally speaking when i do index tuning lectures i talk about aiming for around five indexes or less per table and around five columns or less per index now that guideline i'm very careful not to say rule that guideline stems from the fact that i have five fingers on this hand and i have five fingers on this hand so it's really easy for folks to remember the more columns that you include on your indexes whether they're in the keys or in the includes makes less records to be available on each of your 8k pages it makes the index larger it slows down inserts updates and deletes causes more blocking more dead locking especially when you put them on columns that change all the time like score but score is a tiny number so i might be okay with that in this case let's give it a shot so let's go look at the indexes that exist on that table that is the posts table so let's go c sp blitz index table name equals posts sp blitz index is an open source stored procedure originally written by kendra little uh now works for red gate software out of the uk and in here the first result set gives you the list of indexes that already exist the second set is clippy i haven't got any idea how i could possibly make this faster if you need me i'll be over here doing key lookups then down below is the list of data types on the table so i can see for example this table is not small this table has a lot of columns in it i can see for example that score is an integer i like this as just one single quick control panel so that i can look at different columns and figure out whether or not they're safe to add instead of jumping around to like object explore if i go back over to that execution plan the index that we're talking about was on owner user id can we add the score column to that index on owner user id well that index on owner id owner user id only has one column in it so i'm down if we scroll across we also have the create t sql so i can copy that out so i'm going to say now on this index i'm going to say i want to say it's with drop existing equals exiting drop existing equals on i never exactly remember what that is so let's then say score let's see if that works i don't actually know okay that's that one now while that runs let's look at this other one the other one was on the comments table so the comments is also on user id let's hover our mouse over that key lookup and then see what does sql server also need to do the key lookup there for the key lookup that he's doing is on score so it looks like we need to add score to this index as well so this is just remember when i switch windows it's the index on user id on score let's go c blitz index table name equals comments and then let's go look to see come on big fella would be really funny if this was blocked it is it's blocked it wants a schema stability lock oh good it finished i got lucky on that one so now if we go in here's the index on uh user id looks like it only has one key and one include so i'm kind of comfortable adding a column to that let's go take that guy so create that index and then come back over to our other window where we had our indexing work and then let's put this over here include creation date and score with drop existing equals on there that goes i really like having a little breadcrumb trail of the changes that i made when i'm going about tuning queries to see whether it made a difference or not the other thing we should do is we should go back and try the original version of the query now that we've done this and maybe the temp table version will be faster maybe the original version will be faster we'll go see now when this does uh after this thing finishes take a second there oh you know what else is really cool uh if you fire open a web browser and look for another job if you fire open a web browser and go to brentosr.com go slash progress brentosar.com go slash progress solomon rutzky has a dmv query out here that will get you the progress of an indexes creation so you can copy paste that out and you can see how much longer your index creation has to go this is over at brentozar.com go slash progress you do have to know the speed that you're looking for but the thing that i find really cool with this is it'll give you exactly how much time it estimates is remaining but you see as soon as i paste this in all of a sudden sql server finishes because he's watching to see as soon as you know that he you mean business about that index getting created then all of a sudden he tends to finish his business all right now before i run this again to go see how much better or worse it is i kind of skipped something when i'm trying to measure whether a query gets better or worse i very rarely use time because time is so unpredictable god knows the year 2020 because i'm not unpredictable i rarely use time because it can vary so much depending on what else is going on in the server instead what i usually use is logical reads if you scroll down through here these are really useful numbers each one of these logical reads is one 8k page read generally speaking the less data you read the faster your query will go rather than running a calculator across all this because that's a giant pane in the rear i'm going to copy that out and i'm going to go over to statisticsparser.com and at statisticsparser.com written by richie rump out of south florida gives you a really nice user interface to like slice and dice kind of like excel so i can see here's the total number of logical reads across all my tables it's also helpful to see which table you're doing the most reads on so my before number is about 3.8 million logical reads now that we have our new improved indexes let's try our query again now remember he did take four minutes before when i had the temp table version and i certainly do not like that temp table version at this point let's go look at the live query plan now it is smaller it's more compact because we've gotten rid of the key lookups but the estimates still suck the estimates are still terrible let's see though if i go and look at the actual execution plan again i should have also seen what the total number of rows this thing ended up bringing back but yeah see he's still just at user number seven i don't think this is this might be a better solution but i don't think it's a great solution let's do this rather than hitting the the temp table version let's hit stop and let's go back and try the real version because the real original version was taken like 58 seconds it was like 50 58 seconds somewhere inside there now and i could have freed the plan cache i did create indexes well we'll see if we go look at that's true i even dropped the existing indexes so that'll work if i go look at the live query plan see how sql server did guess incorrectly still about the number of rows it would want to come back this is this in here especially the next set is where hugo really wants to jump in and start work and sql server is comically incorrect about how many comments john skeet has left uh well but looks like it finished let's go back and see i i really it pisses me off that when i try to close a live query plan sql server is like something changed nothing changed this window is read only there's no way i can change the query plan but i still have to hit no close all that crap out of there so now let's come back over here and the query finished in half the time it finished in half the time that is not bad it's 30 seconds instead of 50 some seconds okay we have nine minutes left you wanted parallelism we've got to go see if we have parallelism because we actually we don't have parallelism inside the plan it's closer it's not wonderful but it's closer well the the estimates aren't the estimates are still smoking weed i guess weed would make you laid back that's why we don't have parallelism right because people started smoking weed all right so let's see if we can inject parallelism another thing that i love that microsoft has been working on so much lately i guess i'm going to ditch that temp table version of the stored proc let's go back over to the real stored proc so it was report top users by location stored procedures report top users by location nunubunny says my friend wants to know if we lose parallelism when we use a table variable inside the stored proc not exactly so you can get parallelism with a table variable depending on what you're pulling out of it but inserts updates and deletes when you're modifying the contents of a table variable that goes single threaded and because sql server tends to have no idea what's going on inside the table variable even in 2019 it only knows the number of rows not their contents we still end up being likely to get single threaded plans because we get such low ball estimates when a table variable is involved so here's the stored procedure that we're back on originally so let's copy that out and one of the things that that sql server that i used to not be a fan of i used to not be a fan of query hints i used to not be a fan of query hints because i'm like you don't know more than the sql server query optimizer stop trying to boss it around hints are not flexible they don't give you a lot of wiggle room later but right now i kind of want to use a query hint because sql server is so insane about this query cost this query cost is going to cost less than one query buck oops my bad it's costing eight query cents when in reality this thing's taken like 30 seconds and i can run it over and over again there's no cost threshold for recompile sql server doesn't automatically go back and recompile whenever a query plan is absolutely terrible and it also doesn't know how many times this query is going to run sql server puts the same amount of work into a query whether it's going to run one time or a million times so maybe i need to start using a query hint to kind of guide sql server along i need to guide sql server along by saying hey look use more cpu cores and this macs up which is funny max drop doesn't work either this doesn't do it all this tells sql server is look if you want to use more cores here's how many cores you can use so let's go in and hit this i did that and then go in and get our estimated execution plan again and if i look at our execution plan we still don't have parallelism inside here there's nothing inside here in terms of parallelism that option max dot hint that doesn't tell you to do parallelism to do parallelism you need a different query hint so i'm going over in googling because that's how i roll sql server parallelism query hint they added a new execution plan hint that you can use to go suggest a parallel plan and i'm going to do just like you would i'm going to copy paste it directly from the internet and go paste it into production because that's how i roll so this hint tells sql server hey i would rather you think about going parallel with this query plan now ian over on youtube says what about cost threshold for parallelism does that affect it it does but only when your query is expensive enough here sql servers cost threshold for parallelism at less than one or your sql server's query cost at less than one ain't nobody set in their cost threshold for parallelism down to zero so cost threshold for parallelism makes no sense here when sql server's estimates are this bad so if i put in this hint if i say hey look try and use a parallel plan if you can let's go see if sql server will go through and build a parallel plan and if so is it faster so we have two questions when i hit execute one is a sql server going to use a parallel plan two is it going to go faster three two one execute then i'll go pop over in sp blitz who and go see if we can get the live query plan and go see what's going on and we have parallelism now we have parallelism so the query is going to go faster right this particular server has eight cpu cores in it let's go into task manager look at performance oops this sql server has eight cpu cores in it my mac stop is probably like four but still at least the query should go four times faster right four times faster than 30 seconds that should be pretty quick enough my user should be happy about that except you know what's funny it's slower it's it's not actually going faster it's going slower a lot slower terribly slow so what's happening so what's happening if i go over to sp blitz hoop and i look at the live execution plan again so look to see where that parallelism operator is where that parallelism operator is i want you to think about everything downstream everything from this side back all went parallel across multiple cores but that doesn't mean that this work is each evenly split up what it means is that this plan is three dimensional from that point back this plan is three dimensional and there is one plan per core so in the beginning god made in the beginning sql server went to look to see how many rows it would find per location and it divided those rows across threads but not necessarily evenly and then whoever poor sucker got john skeet they are screwed they are doing all of the work for john ski when the rest of the query is sitting around or the rest of the cores are sitting around idle not doing anything we are now at two minutes the same query that ran in 28 seconds single threaded is now running over two minutes in parallel so when i'm thinking about building query building and tuning query plans this is a great point to stop and think about our 30-minute tackle we made pretty good improvements we made the query basically twice as fast when we looked at the indexing approach we made it slower with the tempdb approach we made it slower with the parallelism approach when i'm thinking about query tunings let's go over the resources slide for this particular session if i go to brentozar.com go slash tune queries this is where you can see the examples that i'm working with you can see past videos of me tuning other things and if you go all the way down to the bottom i've got my b creepy process and this is the process that i use whenever i'm doing query tuning so i called it the be creepy process because of these are the things that i do in order when i'm tuning queries and when you watch the rest of my query tuning videos you'll notice that i tend to steer pretty close towards those i go in order but this session i didn't i went in kind of the wrong order i went from indexing first remember clippy said hey buddy squarey would make your query this index would make your query 98 faster let's do this and it didn't make my query 98 faster in fact it made no difference on the query so i did add a couple of indexes that helped cut it in like 50 but users never want 50 users 100 faster and then we played around with parallelism and we didn't get across the finish line here we had a pretty frustrating hour where we didn't make a lot of progress instead go through this as i explain inside these other sessions work through them in order and you'll have a much better experience if you go through and start by running sp blitz and sp blitz index to check to go see your server's health overall end user requirements gathering ask them what their finish line looks like does the query need to be tuning in or do you get say one hour to tune or two hours to tune capture your query metrics read the query and experiment with query costs work through them in that order don't come from the bottom up that's what the b creepy process is all about everything that i talked through today across this session is all available at brentozar.com queries that is everything that i wanted to talk with y'all about now we are going to switch into the uh prize session for the sql saturday oslo and at this point i will drop out and i'm going to go downstairs and have some coffee from my local coffee shop because it's now 8 15 a.m here in san diego so thank you all for hanging out with me and i will see you again later i'll actually see you probably next weekend to sequel saturday gothenburg too so adios everybody thank you rent and that's the stream yes we are uh getting close to uh our final leave that webinar here we go oh let me switch over and uh move this over here so that i get vaguely decent white balancing uh so there we go folks so you got to sit in on sql saturday oslo and i'll put that up on the site too as well so there we go that's the first time i've ever tried to to simulcast in front of go to webinar and to the streaming with y'all and that i think it worked out pretty well it wasn't great because i had this uh camera sitting literally in the middle of my monitor the whole time i was going it didn't wasn't perfect but i could live with that it's better than better than nothing the more people that i can reach the better because of course y'all wouldn't have gone to uh sql saturday oslo because you're addicted to things like twitch and facebook and uh youtube streaming well that's it so i am going downstairs go get myself my uh locks and bagel and coffee i will see y'all later adios everybody foreign

Channel: Brent Ozar Unlimited

Views: 2,934

Rating: 4.9466667 out of 5

Keywords:

Id: hkRHncESV6k

Length: 51min 46sec (3106 seconds)

Published: Sat Aug 29 2020