Watch Brent Abuse TempDB

good morning folks good to see folks uh coming in this morning and the regular crew as well looks like we have a surly dev it's funny sirley dev says uh i was literally here watching a video on demand to brent and now i get fresh brent with a fresh coffee oh sequel emmanuel welcome first time you got one from the start huh you know it's really funny so i have asthma and uh first thing whenever i start talking in the morning the first thing when i go get up and start talking whenever that is i immediately cough several times this has nothing to do with covet 19 i've been tested for that too as recently it's like like two weeks ago and i i always forget that i should go talk before i get on camera because i get up first thing in the morning and then i don't actually go and uh you know talk until i come on screen with you fine polite people so it's oh dark 30 out here in san diego 5 31 in the morning and i am working on tempty b uh good morning ah good to see you matt good to see you as well dimitri uh good a bunch of the uh training folks in here so a couple of y'all who have like matt and dimitri who have the live class season pass so what you're going to see me working on this morning is actually a trick that i want to put together for mastering server tuning my mastering server tuning class andrea says any plans to start selling your recorded videos here in the eu the problem is that the eu's only was only about five percent of my revenue when i used to sell to the eu and so i have a tough time should i focus on 95 of my customers or should i focus on five percent it's really tough and it's just so much person hours in order to be gdpr compliant and like feel comfortable if i was gonna go face an auditor uh so it's it's on my list it's just not very high up on my list uh surly says did you get the swab down the throat and nose yeah i like had a the swab go straight up the nose and i'd heard legendarily about how bad that was you know people like oh it's terrible you know it feels like they're sticking your brain so i'm laying down in the chair when they do it to me and they you know shoot the swab all the way back there and uh i'm like ah it doesn't didn't really bother me that much but what was amazing afterwards was i could breathe easier out of that nostril i have really thick allergies and all of a sudden i was like i could breathe clearer through that nostril and i could smell clear through that nostril i'm like i was like ma'am i i don't know what you just you know went all the way back there but could you go do the other ear too and she's like hey i've never heard anyone ask that before and b no because we're kind of like low on swabs and i was like ah okay that's kind of fair i guess vassey says good afternoon from russia paul says hi boss howdy amen good to see you again from united arab united arab emirates over there um and for peta good morning brent so what it is that i want to do so i was telling dimitri and matt there so what it is that i'm working on in this morning is i've wanted for the longest time certainly since i swatted the nurse's hand accidentally and grabbed the swag i pulled back four or five times i you know i don't know why i i got lucky with that one i don't know why it seemed like it wasn't that big of a deal to me but uh i guess i'm just kind of lucky that way and i certainly don't want to make it seem like i have like a saudi arabia that's right i always i always screw that up um but uh i i don't know why i don't have like a high pain tolerance by any means i'm a total baby it's just like absolutely so the thing that i've i've wanted to do for a while for mastering server tuning was i wanted to write one query that hammers tempty b in a whole bunch of different ways now something that's important to me inside my classes though is i always want the queries to seem realistic i don't want to be like cross joining from tables that produce you know gajillion results i want it to look and feel like something that an end user would actually write or a back-end process that would actually run something that people mean to do good but then it just backfires in all kinds of hilarious ways so a vampire brent lives hi jorge sequel chicken oh good to see you and this is actually like uh normal brent i'm usually up at the 2 a.m 3 a.m i just have to wait before i start broadcasting to y'all so if i think about the number of ways that we can hammer temp db let's kind of write them out and i'm going to just start doing the demo live what i'm going to do is i'm going to first start out by taking notes of what are the different ways that we could go and hammer temp db so i'll say what hammers tempty be want to become famous yeah no i'm actually pretty good uh let me block a couple of those users so that i can get rid of those oh salam good afternoon from paris so what hammer sql servers tempdb and i'm going to put these out in no particular order just brain dumping things that come across to me so let's say when people create temp tables when queries spill to disk so i'm going to say spill to disk inadequate memory grants what else spills to disk the version store version store surly dev that's right i need a mod to take care of those for me surly dev true story i ha i run on inbox zero meaning that i try to have nothing in my inbox other than actions that i immediately take you are one of three emails in my inbox right now setting up that featured.chat and making you a moderator in here those are the three things inside my uh email box right now so the version store triggers so for triggers not it uh the virtual insert update oh no kidding typing mod takes care of that right away okay watch this uh let's go see twitch.tv brentozar and then let's go over into the chat and we'll say mod surly dev there we go granted moderator privileges to surly dev all right that was easy ta-dah thank you sirleydev i appreciate your uh input here on the channel and for showing up it's always uh cool to see you you always have good input now they're i can immediately theorize a bunch of the rest of people going type mud my name um so these are four ways that that queries constantly hammer temp db maybe sometimes without knowing it so what i want to do is i want to write one query that triggers as many of these no pun intended triggers as many of these as possible kind of accidentally well in order to get the version store i'm going to have to enable the version store i'm on sql server 2019 here and i'm just going to go over here into the stack overflow database as flames here mod me exactly so let's right click on here and go into properties and then if i go over into the options for the stack overflow database either enabling rcsi or snapshot isolation will be doing it everybody be liking the swords so if i turn on rcsi what that does is turn on optimistic concurrency aka mvcc multiversion concurrency control this is how oracle behaves by default and he's right actually how oracle behaves by default and how uh postgres behaves by default how azure sql db behaves by default and you can't turn it off what this does is every time you're doing inserts updates and deletes sql server is automatically tracking a copy of your changes over in tempdb so as soon as i turn this on i'm going to immediately have a higher overhead in tempdb for modification queries so it just kind of gives me a thing inside here look at him [Music] or or i was going to say suck it trolls but second trolls also works as well there so i've got that turned on now to change the database properties actually i'm going to say no i'm going to go script it instead this is what see i give them moderation capabilities and the guy can't even spell this is this is how my life works i'm i don't i'm not made i'm not known for especially good decisions here uh so let's go grab that and we'll put this in our little breadcrumb trail list of things that we're going to go build in order is that where your other hand was yeah so here we have we're going to go turn on our csi so that takes effect instantly and we're off to the races surly dev's never going to live that one down that one's going to be a little bit awkward um and yes rich yeah and also uh richie i really i hired you to keep me out of the security business i think that's really security and systems administration type database administration business i'm not someone who should be uh [Music] so that's the first step now this also means that as part of this i'm going to need to the query will need to do modifications i'm going to need to do inserts updates and deletes so i need to build a query let's think about the pattern that i need to build to write as few lines as code as possible to make it seem like a user kind of stumbled into this into all these bad behaviors i need to write a query that modifies a lot of data ideally holds a transaction open as soon as as long as possible alex cotti what do you want me to do about it well you want me to rebring you up to speed this is what restreams happen oh libra yes absolutely all the streams everything that i do hangs around on twitch youtube facebook mixer like everywhere that i broadcast you can watch all the replays for forever i say forever some of the the providers don't leave the videos on for as long as i'd like some of them only leave it on for say uh 14 days or 60 days but whatever the max length is i just leave them all on so i need to i need to do a query that modifies data holds a transaction open ideally as long as possible uses temp tables and hits triggers i'll write the triggers in a while they're ideally one trigger i want to do as few triggers as possible but i need a query that does a transaction temp tables and a and modifies things so what's what's a single query that we might do and i'm not really asking you i'm doing storytelling i don't really give a damn what you think um you know me i don't love my hair actually but so erica and i were talking we're like is it time for us to do another haircut and uh we're like what would happen if we just let it grow long again for a while so she's been cleaning up the bottoms and the sides but we've been letting it grow long true story so my hair when erica met me my hair was all the way down to my chin so i had i was like i would tell you that i was aiming for the kurt cobain look in reality i look like a mushroom from mario brothers it was not good i need to go through and find one of those no yes really no i'm with you i would never do anything like that i do like like the long chin length hair it just doesn't look good on me it doesn't look good on me at all my favorite haircut ever is there's one stylist in chicago who does a great job but uh unfortunately i'm not getting to chicago anytime soon and i'll go short again whenever the quarantines end lx boy alex cottie that's an interesting question if only there was some kind of engine that could help you with your search maybe there was some kind of way that you could ask the internet questions and it would give you answers rather than showing up on a random person's twitch channel and ask them that's very intriguing someone should invent that someday nah probably not because who would ever be smart enough to use it i'll stop there uh so now every now and then i will actually take open questions and answers but i'm not going to do that here during this session but bless your heart it is hard to find that kind of thing it's not really hard to find that kind of thing uh so in see inside here i need to write a query that's going to do batch updates or like they do updates across a lot of stuff now the one that i've been thinking about because i've been thinking about building this for a little while is there's this badges table but it's not random brent is indeed very very random so i'm going to say select top 100 star from dbo badges and then i'm going to go over this to the uh i'm gonna go into the right database first [Music] idiot um alex katie yeah i i can see why you would think that i'm just hanging out waiting to answer random questions for you for free i i can see how you would think that that's i can't really see how you would think that was the case um yes surly dev is exactly correct that is exactly change my lighting just a little bit here so there's this thing called badges where you can go and earn stuff like uh who's a who's asked a good question who hasn't asked a good question uh who's posted a good answer who's a guru who's uh resuscitated posts from the dead so these are granted normal some of them can be granted live and in real time like if you hit a certain threshold for answering a whole lot of questions or asking a whole lot of questions others can be updated in the background periodically it's not like we need transactional consistency it's not like badges have to be exactly accurate so let's go look just because we're in here we'll go look over at stackoverflow.com so you can see how badges show up on the public site when you click in oh goodness and start the hair grumpy gamedev says is there an i don't have any badges badge that could be one that i could write that's an interesting thought grumpy game dev i might actually use that as the badge that i go and write oh well so deadpool turns out deadpool has a stack overflow account that's actually kind of interesting he said richie is correct uh so here code in the dark okay all right that's cool so the badges that this person has earned here are his badges so deadpool has already gotten the organizer badge citizen patrol peer pressure requisite your inquisitive all these things so we can go hit view all badges and you can see everything that this person has owned well i kind of like the i don't have any badges idea by the grumpy game dev and i think we're gonna do that let's come up with our own badge what should the name be what should because we need a short name for somebody who doesn't have any badges i don't want it to be sexually subjective or anything like that i don't want it to be like too too fiery or racy but just something that suggests in a humorous way that someone has like no experience or hasn't earned badges yet the um hmm let's see what we should do for that and i'm gonna even start the pseudo code down inside here uh hi natik uh so let's say create i gotta put a go to go up here first so we're gonna say create or alter proc noob okay yep i like it that works new badge dbo usp grant noob badge or we'll because i like that noob being at the beginning i'm going to say noob badge grant red shirt oh that's even better oh yes yes the red shirt bag that's it right there so oizokin i'm gonna grab that before it scrolls off the screen ozokin so i'm gonna start the change log in here not the debut dba you're looking for bought my mug thank you i appreciate that enjoy the coffee so red shirt so we're going to put in here for the change log we're going to say 2020606 we're going to save the grumpy whoops grumpy game dev what's the red shirt badge so the red shirt badge is going to be usually in star hold on a second here let me switch over in star trek in star trek when you see someone go beam down to the planet and they're wearing a red shirt i cannot believe i have to explain this to you i gave you a moderator badge i thought you were smart exactly yes the guy who gets killed on the away missions or the person who gets killed on the away missions so whenever you see someone beaming down to the planet in a red shirt they're doomed so i kind of like that because you think of them as like throwaway people well to some extent uh people will often create um will create accounts on stack overflow just to post a throwaway question and they're i don't want to think of them as disposable but you know it's not like they're going to be around for a really long time most likely so what we'll do is we'll make it a combo they have no badges earned and they've been inactive for at least like three months or something like that so we'll assume that they beamed off the planet and they met their demise so let's say the grumpy game dev started this he say uh grant badges to uh users with no badges earned and then exactly yep and then we'll say 0607 coding in the future uh oizokin zokin renamed badge to red shirt all right so now that y'all are in there memorialized for fame inside the webcast they're both excellent ideas so now i've just started this and i'm going to say for a parameter so what we're gonna do is we're gonna i want something that's gonna be able to be able to rerun repeatedly um and ideally change users um or uh or do multiple updates at the same time so exactly yep so what i'm going to do is i'm going to put in some kind of date range parameter let's say start date date time equals null and end date date time equals null as begin and then end so we have the the shell of what we want to get started with and you've seen the howdy gym so we've you've seen the table now so the table looks like this daytime offset no i'm going to make an executive decision there no i like where you're going but no and the only reason i'm saying no is because nothing else in the stack overflow database uses datetime offsets so i don't want to introduce that concept when i'm teaching just because there are just only so many things i want to teach at one time but it's it is kind of interesting though and if i was a good coder that's what i would do but of course we're doing things as a as a kind of sketchy coder would do because time is hard you are absolutely right hi dave welcome to the club so now i have this badges table now what do i need to do i need to go find remember our goal is to abuse tempdb up top in as many ways as possible so i'm going to want to start a transaction i'm going to want to have temp tables and i'm going to want to modify data so let's start by taking out a transaction for no apparent reason let's just say begin tran pascual you may want to watch back at the beginning of the session we talk about what our goal is inside here we're not necessarily writing good code we're writing kind of shabby code i agree with you if we were writing good code but we're not so we're going to begin tran and commit and i might even purposely throw an error inside here at some point just to make sure that it rolls back because i think that would be kind of comical as well so begin tran now i'm going to say just like a now look i am i will say that i am writing code that's kind of purposely bad and i guess that's going to reflect badly on these folks and that's their problem exactly yes exactly so begintran we're going to say get the users we want to modify into a temp table and zeus hero you're absolutely right but we want to make it realistic though this is part of the fun of my my mastering classes is that they need to look realistic and be relatively easy to fix so let's say create table and we're going to put inside there so my goal is to absolutely abuse tempdb so a good developer all they would do is they would put the list of user ids inside here that may not be enough but i'll just do a regular temp table with just ids in it for now i may come back grumpy gamedev says attempt table and not an ad table yeah because what i'm going to end up doing is or i mean i could do that we'll end up seeing i also kind of want to make it very explicit right from the get-go when they look at this stored procedure that oh it's hitting tempdb a chant new you know you don't need try-catch for a rollback you can just do an error inside there that causes the whole transaction to roll backwards so inside here i'm going to say users who earned the badge and we'll say user id end oh ah no we'll just stop there uh and we'll throw we'll throw in a couple of other we'll throw in last activity date last activity day date time so now let's go find the people who match i'm going to find i want to find all of the users whose last activity date is within the range that we're looking for up here in our stored procedure so in order to do that and move it up a little so you all can see the code little easier insert into users who earned the badge user id last activity date select id last activity date from dbo users you inner join oh no no not interjoin oh we'll do a sub query users love sub queries uh where you last activity date uh between or we'll do we'll do greater than lesson greater than or equal to start date and you last activity date that why the hell are you underlined why are you underlined invalid object name uh intellisense why don't you go get a nice tall glass of in refreshing refreshment because that is totally a table in the use stack overflow database what in the hell because oh no that's right insert user id what in the hell why are you invalid object name dbo users i am in the stack overflow database i have a creator alter let's just try this execute oh last activity what the i'm going to hit refresh on intellisense again here because this is absolutely bananas oh is there a used master up at the top oh nice oystokin whoo damn wow oystokin damn wow that's impressive use stack overflow if that's what it is oizokin you are oh oy zogan you are uh you're like my new hero oizokin you are awesome oizo okay send me an email uh send me an email at help brentozar.com and i want to give you something for that because that was those were two amazing ideas in a row those were utterly fantastic number one was the redshirt thing and number two was that catch of the used master it wasn't even on the screen you're just on you brought your a game today not like the rest of these jokers inside here asking how to enable cdc what on earth okay so back over here so it isn't last is it not last activity date no of course not it's last access date so let's solve that one and dennis and the other folks there was somebody else who caught last activity day i was maybe just dennis you're good as well yes just not quite as good as zoizocon ozoriken is wow holy smokes uh you accidentally less than or equal to end date okay so now we got that now i want to do the sub query bruno says uh users love subquery it's true and it sucks yes uh and i'll type i'll i'll make this a little bit more nicely indented just to clean it up a little bit not quite as good as most end users would do but whatever so now and not exists select star from dbo badges where and i think it's a user id i think is how that works yeah user id where user and let's minimize this so you can see the code a little better where user id equals u id and if we're really good let's let's prefix this just so that people can follow the code a little bit easier all right good so what we've got here is we're dumping into a temp table whoops oops let's make last access date consistent while we're at it so i'm dumping a bunch of stuff into a table and this is as a bonus this will also be sensitive to parameter sniffing depending on what kinds of dates you pass in here because now we've loaded up a bunch of stuff into a temp table next up i want to go give them badges the truly evil way to do it grumpy game dev says getting abdominal pain from the select star it's actually not that bad so check this out so i'll go off topic here and do a quick demo we'll say if exists select star from dbo users where display display name equals brent oh we'll say the grumpy game dev uh the grumpy game dev uh print found him and then let's look at the execution exactly yeah because of the if exists well there's no index on that of course there's not because that's the way the world works create index just so i'm not going to create the index right now but what you end up doing is sql server will use an index seek rather than a table scan just because it's if as long as there's an index on display name he doesn't have to go back to the clustered index in order to get the select star which is kind of awesome um so and taja says i thought if exists was a good kind of sub query yeah i could make it worse but i'm just starting with users love sub queries as opposed to left outer joins i prefer left outer joins because for me they're more intuitive and easier to read but to each their own okay so we got that list now i want to go through and give them the badges give them the badges so i want to go give them the red shirt badge what the what the what mattias okay matthias we're not we're not gonna just randomly paste things into chat here today all right that's cool okay so i appreciate you wanting to share that with me but it uh as you can kind of guess up there i'm not gonna play around with that uh so if you so now we need to go grant them the badges i part of me wants to do it in the least efficient way possible part of me says part of me says that what i should do is i should start a cursor i should start a cursor and then loop through those rows individually it's the game grumpy game to have you and i are on the same page there i don't know that i want to abuse the students quite that badly because one of the things in my mastering classes is i try to give people queries that they can diagnose find the root problem and solve it within 30 minutes i use this 30 minute hourglass a lot inside the classes to emphasize that you want to flip this over and make as much progress as you can in half an hour i don't know that yes not the dba you're looking for me and you as well i have that fetish too as well oh my tire is cool so i don't know that i'm going to push them quite that hard but if i was going to go do this as like an exercise for the blog post readers who have a relatively unlimited amount of time a cursor is exactly what i would do so back over here i'm just going to do a flat out insert i'm going to say insert into dbo badges name user id date select select what do we have here from the temp table users who are in the badge so we're getting the name of the badge is going to be red shirt uh i think stick stack overflow badges have spaces in them so we'll put that in uh red shirt uh user id get date this is the date that they earned the badge so this would be fairly simple and straightforward like this is a totally realistic query and it would work but let's kick it up just one level and make it just a little bit uglier let's also make sure they don't already have the badge now granted we've done it up here already but i'm going to kind of cobble things together the way that users might have accidentally cobbled queries together over through the years so i'm going to say left outer join dbo badges b on you user id equals b user id and uh we'll move it over a little just so it's easier to see on one line and badge name equals red shirt so then we'll say where b name is null user hasn't earned the red shirt badge yet all right so now we have something that's decently realistic and it's going to produce just bad enough of an execution oh oy zokin yes i love it yes so let's say um let's say inner join dbo users u on uh u user id equals u id and then we'll get the i'm gonna add this in as you uh creation oh it's not you it's oh damn i use the u alias twice i'll call this b uh to do to do b user id and then we'll say you creation date um and i'm gonna i'm going to have uh some ville i love this social bill i'm going to write your name down too before this goes away because i'm going to uh we'll put this up in the change log because i got another idea here that i'm going to like a lot 2020 0608 uh shamville kazmi um remove badges uh if they become active oh that's such a good because you know what i'm gonna do with that is i'm gonna make that in the trigger yes oh that's gonna be sweet i love it that's just fantastic oh that's so cool oh yes the grumpy game dev u1 and u2 i love that too as well um so added to grant them the badge retroactively just so that this kind of confuses things up just enough we'll call this u and then u1 or you date well we'll call you one in honor of that terribly bad idea u and then u1 and that works beautifully and then we need to get you user id and then this is u1 creation date i love it that's fantastic oh it's lovely okay so and uh mac genie uh one good catch on the badge on the reuse of the b table i didn't catch that either i just happened to catch it without seeing yours okay i love this um so what this is going to do is it's going to lock the badges table and it's going to it's not going to generate changes in the version store though you know what i may also want to do while we're in here in this transaction let's make it as if the user's last activity date was just now updated so let's also update their ueb would be clearer yeah except we're trying to kind of be just a little bit unclear in here you may want to read back watch the beginning of the string we talked stream we talked about it so i'm going to also set their last act is there another no let's give them reputation points yes that's what we'll do so what we'll do is uh no short answer is no i mean short answer is yes it can but it's beyond what i'm going to go into here just for the sake of this so what can i set that's going to cause a change in here inside the users table thereby triggering more stuff inside the version store i like the thought of giving them a reputation point for this the reason why i want to give them a reputation point is i don't want to screw with their last activity date because there are other things up here involving their last activity date so we'll just give them a reputation point so we'll say give them 10 points for bravery during inactivity i suppose whatever uh update dbo users oh no no no no no no no no no let's go back to our temp table update you from users who earned the badge now i'll use matt's ueb just because i think it's funny to use different aliases at different uh places so from users who are in the badge enter join dbo users u on ueb user id equals uid then we'll update you set reputation equals reputation plus 10. okay so now we've given them 10 reputation points that's going to log stuff over into the version store i'm happy with that so now we have a transaction we've got our insert into badges this stuff should take some time i love it i may even also if i want to prolong this transaction a little what i may want to do yep zeus hero yes absolutely but i'm just writing one i'm going to leave that aside and keep going uh so uh great question i'm not going to address it here though i want to take you out behind the shed no okay hold on so i'll answer it i'll do justice to it okay so zeus hero said and and apologies for the short reaction because i was like i think you're trying to trick me to go down a specific way and i don't want to go down that way but it's actually i bet you're asking questions different the more sessions that i have does that make the version store larger yes if they're doing inserts updates and deletes so with this stored procedure though it's just one stored procedure and i want it to mimic something that would be end activity things that are happening over time so it wouldn't be something that i would go and amplify and run across a whole lot of sessions i like where you're coming from but i'm going to try to build a longer drawn out like batch process thing where i'm going to pass in really large date ranges to this grumpy game dev says so if a red shirt should happen to earn another badge should he lose the red shirt yes and this comes into remember how i said it at the like top of the text file i said there are a bunch of things that can hammer tempdb and one of them as triggers this is why i got so excited at i want to go back and get his name right because it was really cool shamville's suggestion shambill said remove badges if they become active and i utterly adore that so what i'm going to do is i'm also going to add triggers on the badges table i'm going to add a trigger on the badges table that says every time a badge goes in check to see if that user has the red shirt badge and if so revoke it and that's going to hammer tempdb even more so i absolutely love that shantnu said you had users without a badge then the next story correct shan't new yes go back and watch the replay and you'll understand how we got to where we are okay so now let's go back and see so i have this stored procedure and i think it's going to work now in order to make sure that this thing is actually using temp db i want to be able to measure how much it's using tempdb and one of the things that i love for that is atom mechanics sp who is active atom mechanic has this brilliant stored procedure called sp who is active it's totally free it beats things like activity monitor and what it does is it gives you a list of sessions that are open right now one of the columns that it has inside here is temp db current and to demonstrate it to you i'm going to go create a temp table over in another session i'm going to say select star into temp users from dbo users i'll go create this temp table welcome to the club uh johnny buchan i'll go create this temp table then go run sp who is active while this query's running and so now i can see richie says we micro services richie we should do it in amazon lambda we should have a queue service broker it's like whack-a-mole for uh managers and technology terms so it's really cool it'll show you now that line disappeared just now because my session is asleep so i can say show sleeping spids i want to say is the uh show sleeping spins equals one uh yeah oh what do what what's show sleeping sessions maybe uh that's that blows because i really wanna see uh well i'll do another query inside here select count star from dbo badges with index equals 1. so while this query is running if i execute over here and then pop back over and run sp who is active here you can see that's actually not correct i think i might have just found a bug in sb who is active i hate it when that happens so what i think is happening and i don't know this yet for sure i'm just going to make a note of that actually hold on a second here so i i always keep i guess i could do it on a text file i guess um no i'm just going to write it down on a piece of paper so who is active tempty be what i think is happening temp db allocations what i think is happening is that adam's sp who is active is probably only showing the should we log the bug in dfj here's a name i haven't heard in a long time um what i think is happening is that there are two columns in here tempdb allocations intend to be current and i think one of them is a bug i think that tempdb current is correct i think the tempdb allocations is incorrect because that session does still have stuff in tempdb like if i go do select star for select top 1000 star from temp users this session still has stuff allocated inside tempdb and eamonn it's really sad but you're right like i i have an alternate reality where i'm a quality assurance tester and i go and work on other people's stuff because it's just horrifying the bugs that i find that people are like how did you even to what i something inside here is still not registering that i'm actually using tempdb so what i'm going to do i'm going to actually try hitting it again i'm going to say select star yeah let's just try select star from temp users and then while that runs let's go run sp who is active yeah it still shows no uh no oh man damn okay so we have a bug inside there richie's right so richie joris is my developer on my team and so i uh whenever i check bugs in for stuff he's the one who gets them now to be fair they're usually my bugs they're usually bugs in my code they're not co bugs in richie's code i'm not smart enough to catch the bugs inside of richie's code all right so the thing that was the reason why we went off on this tangent was i said i wanted to be able to show you how to prove that this thing was actually using tempdb i may not be able to do it with sp who is active the way i wanted to quickly demo it so instead what i may just end up doing is i may end up just running with this code as is for now and not showing you how to prove that it's got space in tempdb because there are only so many things that i want to go teach you during the course of one live stream so i got a stored procedure and it's hammering tempdb in a couple of the interesting ways it is doing work inside the version store but it's i mean sequel emmanuel says time to whip out sp blitz who it you know that's a that's a fair point p blitz because it would be funny if we had the same bug because it could also be a bug in sql server itself i don't think we return it we don't return it by default but if i say expert mode equals one it might be one of the columns inside there so to test it let's go see select star into users from dbo users and then let's go run it and see what we get let's go look over at do we yes okay ooh ours is even in megabytes uh so i have temp db allocations in megabytes now i want to go see uh if i have the same bug over here i'm going to go see so now my session is sleeping it's done the bug that we were hitting with who is active was that when i ran it a second time the query didn't show up i don't think it's going to show up in here yet either because we have our own show sleeping kind of parameter then oh cranky old bastard good point i'll i'll do that here in a second uh i want to say it's show i can't remember show sleeping spids uh equals one it might be something else oh it is that oh beautiful oh i love it when a plan comes together sp ask brent um and now look at that so it may well be a bug in sql server itself my i would lean toward being a bug in both c in sql server just if both sp who is active and sp blitz who report the same thing is wrong no no no emmanuel no it's it's wrong that this both of us are scoring zero so now i'm at an interesting point so should i continue showing you the stored procedure angle let me go change that for a cranky old uh cranky old uh brilliant fella um so since it's no longer called a noob badge we're going to say uh grant red shirt badge grant red shirt badge good catch for that uh cob let me save this on my desktop and then i'm going to present y'all with a question and i'll see what y'all want to do so let's switch over to here so the thing is the sun is starting to rise here in san diego you have me for about another hour and a half i will give you a choice because i'm i'm gonna do both of these things it's just i may not do them this morning because i also have a client starting at 9am sidenote if you ever need me on an emergency you can call me and hire me over the weekend which is what i'm doing for a client uh this weekend it's not cheap you can get me over a weekend so what you can johnny b cat said did you update the change log in to store procedure no i'm not quite that good of a developer i'll thank you for the question though so um i'm going to give you choice between two things because i don't care which one i do though i'm going to do whichever one you don't choose i'm going to do after hours just it's going to be later probably won't be on a stream one option is to continue showing you the trigger type stuff in in building up more hammering into tempdb so choice one is tempdb choice two is to investigate that bug in sp who is active first responder kit etc or if it's a sql server bug that's rendering the wrong results into how much uh temp db space a session has so in the chat or in the comments put in one tempdb or two the sql server bug and i'll let you choose your own advantage adventure there and i don't have we don't have like a scientific number kind of poll so we'll just see what things are coming with here wow so most of you are on uh one still it's coming in kind of mixed here a lot of folks coming in on one we'll see here ten nine eight seven six five four three two one it seemed like most of the ones that went by were one so i'm going to focus on that and then maybe in a separate stream i'll go investigate the sql server uh bug type thing arpit the answer to your question is no okay so let's go pop back over here then and we'll discard that little bug and we'll go probably a very big bug uh jorge segarra may still be on the stream jorge works for microsoft i'm sure he'll pick this up to open up the source code and chase that down i'm sure so now what i'm going to do instead so i have this uh grant the red shirt badge piece inside here i'm this is probably zeus hero look man you're not my real dad i tend to not schedule my streams in advance my free stuff i just do them whenever my personal schedule permits if you want to see my live schedule for paid training go click on training on the top of my site those are scheduled almost a year in advance these are just winging it based on free time when clients don't hire me for stuff so i got this stored procedure i'm happy with the basics of the stored procedure i probably need to test it to make sure that it works but the second part of this is i said up at the top there are all these things that hammer tempdb so i nailed down in this one stored procedure i have temp tables i do have the version store i'm going to put that up ahead so these two are done now what i don't have yet to do is spilling it to disk getting sql server to make a bad estimate in terms of the number of rows that are going to come back purposely estimating on the low side and then getting an avalanche of rows to come back so what do i need to do in order to cause that things that get a memory grant are just among the headline news type stuff things that get a memory grant are order by if i do a sort joins hash matches group by um and adaptive joins those are the ones that i usually run into order by is one of the biggest ones so if i do an order buy what if instead of granting everyone activity or granting everyone the badge inside here what if i think of doing this inside a um a loop like it's a batch process it's going to run from time to time and i want to i want to get sql server to low ball the amount of rows that it thinks is coming in let's make this red shirt badge stored procedure let's make it a back-end process and say users to process int equals one now what we're gonna do is that we're gonna call it no one is unrealistic let me say a hundred let me say a hundred inside here so mattias listen for a second i am not doing ctes with passwords here i get that you are randomly massively in love with ctes with passwords stop i'm not interested in this demo thank you moving on i can be joking like that with matthias because i know him and he's a genius very smart and i like him and his work but very very smart person i like matthias a lot so in here so i need to get it to order by so when i'm going in here and doing this i am going to order by and i need to come up with an order by that sql server won't have an index on now i do have indexes on last access date this is just teenagers love youtuber hugs so i do have an index on last access date normally if i think about fast processing it would be it would use the index on last access date so as i'm designing this load test i just are designing this lab i want to make sure that i don't have any indexes on last access date the version of stack overflow that you're seeing on screen here is exactly the version that i use in my mastering classes and oh pin all welcome to the club too pinal you're right oh i love this um because so how can i do that by end date so end date is a parameter coming in but i could order by um because if i order my last access date let's go order by creation date let's order by their users creation date you creation date uh so that works pretty well welcome to bin 7 and colin stevens to the club um so and oh and you know because we've been going for almost an hour i should stop here and do two things before i go further hold on a second here so seeing pinal reminded me penal and i are doing a webcast for quest if you want to ask us any performance related database administration related questions whenever panel and i get on screen together we always have a hilarious time lots of shenanigans ensue pinal is one of the funniest fastest presenters you will ever have the pleasure of seeing i know a lot of you recognize pinal from his blog sqlauthority.com you have constantly done google searches in order to find an answer to something and he's given you clear easy to copy syntax where you go here's exactly how this function works and you go paste it in pinal doesn't often do videos online that are totally public and often he shows up at conferences if you're not getting the chance to go to a conference anytime soon this is your chance to go see pinal out in action and he is hilarious so between the two of us it is we're really just uh it's amazing that quest is allowing us to be on line together and it's i'm sure going to be a ton of fun so you can register for that totally for free over at brentozar.com go slash experts it'll be i want to say it's an hour-long webcast and i know what's going to end up happening is that a ton of people are going to end up registering this is the problem that we had with our last webcast request they had a thousand person limit in the webcast we actually hit it and people couldn't get in so if you're planning on going register and then you have to show up a little early as well it's registering won't be enough to get you in there the red the um uh the industry thank you for saying we're both funny um so just make sure that you go in early that morning as well so that's brentozar.com go slash expert i agree with a and sure pinellas blog has been a lifesaver they say all right so let's stop here for a second and go to a bio break we'll take a five minute bio break i will go refuel my coffee and take a quick uh bio break as they say five minute break and we'll come back in here and then i'm going to talk about the memory grant process how we're going to trigger this thing to get a really high memory or really low memory grant and then we're going to turn right back around and make it spill to disk like crazy further hammering out temp b then we'll finish up by writing the trigger that will hit over on the badges table as people earn a badge that disqualifies them from the uh their noob badge so i will see y'all back in here in five minutes [Music] [Applause] [Music] [Applause] [Music] [Music] baby [Music] baby [Music] [Applause] [Music] [Music] so [Music] [Music] so [Music] so [Music] [Music] so [Music] release the crack [Music] do [Music] [Music] so [Music] [Applause] [Music] so [Music] do [Music] [Applause] [Music] do [Music] released [Music] the crime all right welcome back howdy folks uh so yes uh bruno says where are the channel people chats you're on it yeah it's i have a little thing called restream that shows uh chats coming in from youtube facebook twitch all that stuff um i agree with you that i think mac genie i agree that twitch is the best twitch's least is my favorite anyway um taurus that live streaming event will be held only for the business owner not from students i'm not even sure what you're asking there um you're on the live streaming event we're here welcome hi i love you that's not entirely true um watch that's that's actually pretty good there i like that all right welcome to the club dr mullis i appreciate it my uh animations aren't right right now that reminds me i got to go talk to the guy who uh built these now it's not showing the text in the right place so we've i've pinged him about it but the guy's really super busy i like the way the new uh outlays were the new outlines work with where everything's about oh johnny says the the not the um the quest live stream the quest live stream is let me go back over to that the quest live stream is june 24th uh june 24th at 12 noon eastern time if you register for it they'll also send you the instant replays when that becomes available it asks for a lot of business owner info yeah totally because of course what they do is they have to pay people like me and penal you think we're cheap oh hell i mean like i'm five dollars that's all i charge five dollars for a webcast but penal it's ten thousand dollars ten thousand dollars it's that's how rare it is that he speaks you should totally go a 10 that's not true we both charge money um but so yeah so that's why they want a lot of business info is that they're going to turn around and try to sell you quest products which is fair i actually used to work for quest i worked for quest as an evangelist way back i had the 2008-2009 and i loved it like their their products are solid as hell they have just teams of developers around the world qa testers uh huge support team i am amazed by how many uh people it takes in order to run a business like that it's just staggering it's huge okay so when last we met uh super dba says our super dbases will the quest session be recorded and posted yes but it's only record or it's only posted to people who signed up for the webcast it's not like put out for free on youtube or whatever you still have to register in that same page that same exact page will actually work the same way after the recorded webcast it's just that you'll immediately get access to the recordings instead of seeing us uh do it live what is money i believe it that's when when erica and i first got married i always really wanted kids and she was dead set against kids and now i there's no way i could do it i have too many uh unbelievable number of toys that i like to play with and that's uh that's not compatible with children okay so when last we met we were writing a stored procedure and i said that we're trying to hammer tempdb in as many ways as possible inside one realistic stored procedure a stored procedure that it felt like end users might actually write and i said that in order a few of the different ways you can hammer temp db is you can build temp tables so we've got that under control uh we've got stuff in the version store we've done that and i said another thing that you can do is get queries to have an artificially low memory grant have sql server think that they hardly need any ram in order to execute then turn around and have that thing flood with data so that it ends up spilling to disk let's try to inject that into our stored procedure now our stored procedure already has a start and end date parameter so what i could do is i could send in a really tiny date range and make sql server think that there's hardly going to be any data then i can turn around right behind it and run it again with a much wider date range thereby flooding it with data another thing that i can do is i can make sure now taj says drop all indexes and statistics and that's true i do that in the mastering index tuning class i'm not going to do it in the mastering server tuning class because it isn't realistic because people have indexes inside their tables but what i'm not going to have i teach i'm right real close to you is i'm going to make sure by looking at that queries plan that it can't have the indexes that it wants yet i'm going to start with just making sure that it doesn't have the indexes that it wants then i'm going to add in a sort to make sure that even if it does have the indexes it'll still get a sort so i'll show you how i do that so here's our stored procedure and the the parameters specifically i've got this orders to users to process hold that thought i may not even need that i was thinking about that over the break and i try to keep my stuff as simple as possible and i i'm gonna try not to use that so i'm gonna knock that little fella out because i think i can get by without it but back over here so penalty suggested hey why don't you put a sort in here by date and just get say the top 100 users to process at a time now for those of you who haven't gone through the mastering query tuning classes just fyi sql server calculates memory grants differently when you do a top 100 or below than top 101 or higher so i'm not going to use 100 because that'll introduce an artificially low memory grant but then sql server won't need that much i'm going to go with a higher number than 100 something that's more similar to batch processing i'm gonna say a thousand select top one thousand now if i have an index on last access date then what sql server is gonna do is it's going to seek directly in to the ones on that last access date but if i throw in taurus says i don't have any business have you ever lied before you know how when someone asks you if you have any bad habits and you say no you know how when someone that you want to date when you see someone really attractive across the hall do you go over and try to talk to them and they say oh you know how are you doing and you say fine that's called lying you do it all the time and you don't even know about it just because it's on the internet doesn't mean you can't lie i'm gonna give you some news it's probably going to break your there are a lot of lies on the internet i know you think that just because it's written on the screen it must be true but just because you see it on the screen written in those fine handling finely crafted pixels doesn't mean it's the truth teja i am saying that you are allowed to lie on a registration form it's giving you official permissions so i'll leave now i like that right yes okay so back over here on this so i'm going to say top 1000 even if i have an index on last access date i'm going to sort by something else i'm going to sort by creation date hi crazy tech so i'm going to sort by creation date so what that's going to do is sql server will be able to seek into the rows in a range for this creation date but i'm going to give you this for this last access date but i'm going to pass in a staggeringly large number and so sql server is going to have to sort them by that creation date let's see what we get in terms of memory grants daniel welcome to the club thanks i appreciate the subscription so let's go see what happens what kind of memory grant this thing gets so let's put our stored procedure into production let's go to our create or alter and then i'm going to go execute it and i for those of you who haven't seen there's this really cool slider thing up here there's this little right up at the top over up at the top there's this slider and you can grab hold of this and drag it down and now you have two windows on a stored procedure or whatever file you're working on i really love that so that i can have the creator alter up there and then i can see down at the bottom later when i'm trying to like do the exact i can see what the params are so i can say exec usp grant red shirt badge oops grant red shirt badge start date equals now for beginning because i'm going to get him to artificially estimate a really low memory grant in the beginning i'm going to say 2020 we'll say an old date 2009 and date equals 2009.0102 now let's see when we go to execute this thing and all i'm going to do is get the estimated plan surly dev yes i i i approve your friend's uh request so if all i do is get the estimated plan what i'm going to do is go look over here at this select so here is where i'm going to find oh even better it's got a key look up so what it's going to go find is the users whose last access date was inside that range let's go see how big of a memory grant this thing needs in order to do its job so the memory grant desired memory is just 1.6 megabytes that's perfect it's a tiny little memory grant hardly any memory at all what happens if i change those parameters to be a staggeringly large date range what happens if i turn this into say 2020 so if i say 2020102 so like 10 years worth of history now let's see what kind of memory grant this thing wants and it wants two gigs perfect that's fantastic mohsen you suck show up on time no just kidding we didn't have a start date official motion you're welcome too but just don't ask questions because if you miss the first hour and uh 10 minutes of the webcast you're not allowed to ask questions so in here if i go for a much wider date range sql server wants like a two gig memory grant perfect chef's kiss so what i've got is my perfect little scenario here where i'm going to start by triggering a parameter sniffing issue i'm going to start by doing it for a very tiny date range then i'm going to turn around and run it for a much larger date range and i'm going to see if it spills to disk so let's start by just running it for a small narrow date range i i don't actually know if this thing's going to work because remember this is the first time we've ever actually executed this thing so let's go execute and all right cool so here's our execution plan here's the thing with the memory grant this is where i'm worried about or where i'm hoping that sql server asks for a really tiny amount of ram and sure enough it does now this that's those parameters have been set in stone oh it's gonna oh no this might actually work okay um so let's go through and run it now for a much larger date range i expect this to take a long time because we are inserting a lot of rows into the badges table that's okay that's a separate problem while it happens though let's go see how badly we're hammering temp db and i'm going to use sp blitz who only because it has that temp db metrics those tempdb metrics in megabytes in terms of cost so i can see it hasn't gotten to the point where it's putting data into tempdb yet so i may execute this a couple few times just to see if it ends up showing up but let's see here how far over was it it was two no p still at nothing um so just as a as a fun side demo if i uh tesla rx do you just like to spit out random facts are you like just do you like one of those you like the little daughter in that tom cruise movie the average human head weighs five pounds and you're completely wrong regardless anyway tesla rx like you're not even close you're not even remotely close the correct answer is over there but you're going to have to go get a pair of binoculars to see it that's how wrong you are you're not even in the same country as the answer you you haven't you don't even know how to phrase the question so back over here my query finished so it took a minute 12 seconds that's perfect that's good it's long enough that it'll it'll cause problems for people who are running over in the lab now let's go look at the execution plan and my goal is to find out yes sweet so this thing spilled to disc so that's fantastic it's spilled to disk wrote likes hey thank you johnny welcome to the club i appreciate it so those of you two who want to support me by doing the kinds of things that johnny just did um what you can do is if you have an amazon prime subscription you can join twitch prime totally for free and then you can subscribe to the channels that you want with twitch prime and then the creators get money every month even though you don't have to pay anything which is kind of cool so so back over here on this so crazy texas i love this stream so over here on this this is exactly what i wanted sql server low lowballed to the memory and now ended up spilling to tempdb that doesn't mean that it actually wrote to disk though it may not have written to disk because sql server for the last like since sql server 2012 there's a service pack and cumulative update where they tried to be more conservative with tempdb and it sql server will only flush the rights down to tempdb if really necessary so the next thing that i have to go do is i have to prove that this thing is actually writing to tempdb now i know that we talked about the bug earlier on possibly a bug in sql server or a dmv where sql server isn't surfacing the right amount of data about how much we're changing over in temp db i know how to fool that i know another way that i can get to it through another stored procedure called sp blitz first where i track reads and writes on files as things are running so we'll do that over in a second see just on the other side if there's anything else that we had from looks like there's a sort down there on the second part there's a sort down here on the second part oh this is kind of excellent okay remember early on remember early on when we were coding this and one of you suggested i don't remember who it was but one of you suggested that we should use a table variable instead of a temp table if i use a table variable it's going to play into our favor here because sql server will incorrectly estimate the number of rows that are going to come back out of that table variable right now it's using the stats on the temp table to estimate that a thousand rows are going to come back i can screw with it here even further by using a table variable that'll cause another spill to disk here i'm going to do that only because it's easy and because often people know that table variables have that problem of incorrect estimates so let's go switch this into a table variable let's say declare users who earned this badge table insert into users not the dba you're looking for said i thought the sql 2019 fixed that issue it does only if you go into 2019 compat level which triggers so many other problems that it's kind of quite a laugh all right so now we've got our table variable version of it let's go put that into production and now when i alter it i get a brand new execution plan so i have to trigger parameter sniffing again by running the very tiny date range and then when i go back to run the very large date range i'm going to make a change this time i'm going to go run in another window i'm going to go run sp blitz first seconds equals 90 and i'll explain what that does as it runs so left hand side we're going to run our let's actually we'll switch positions here between these two so i can do this a little bit more effectively save you over here so i'm getting ready to trigger my my uh pro my problem query over here query's going to take like last time it took 90 seconds to run who knows it's going to take that time you know what i should do is i should delete the the rows with um let's go delete the red shirt badges delete dbo badges where name equals red shirt just to get those out of the way so that we can reset our execution again well it's right it only did a thousand okay that's kind of cool so what i'm going to do is i'm going to run this query that i know takes like 90 seconds in order to run and i want to measure what it does while it runs so how do i do that over on the left hand side i'm going to run sp blitz first for like a 90 second sample so start that and then over on the right hand side start this so what does sp let's first do sp blitz first takes a snapshot of a whole bunch of dynamic management views things like weight stats the plan cache file stats takes a snapshot of a whole bunch of stuff waits for 90 seconds takes another snapshot and compares what was different across that 90-second time span to tell you what happened it doesn't trace individual query activity so i'm not seeing how many writes to temp db were done by this stored procedure i'm just looking at things like tempdb activity overall on the on the server one of the sections that i'm going to get back 90 seconds into this thing i'm going to get all kinds of diagnostic stuff but one of the things that i'm going to get back over there is a section of file reads and writes so for every data file in every log file that had a lot of activity during that time span sp blitz first sorts them by stalls descending shows me the top five files with the most stahls descending in the last 90 seconds what i'm looking to see is does tempdb show up on that list now over here on the right so some of the problem that i had was a was a data caching issue so here uh sandeep can you be less specific so over there over on uh this thing finished in like a minute seven this time which is nice if i want to push even more activity in and out of here i can do um i can do a higher number of rows so now over on the left hand side here i have woohoo so when this thing ran it pushed 60 megabytes worth of data to tempdb so we have a partial success we've gotten somewhere now 60 megabytes is it a lot no but now we can start ramping up the way that this query is written in ways that will trigger it to have different problems so how do i amp that workload up even higher one way that i could do it is that i could use a higher number here for the top instead of just doing a thousand rows at a time maybe i could think big data and batch process all the rows at a time now if i go back over here and look at the plan we have our spill to disk so this is activity against temp db this is activity against tempdb when we're building stuff into this table variable the updates are up our activity against temp db when we're doing inserts and the updates are activity against tempdb for the version store the insert isn't that bad though there aren't that many let's amp this thing way the hell up let's go instead of 1000 users let's go through richie says this would be so much better yeah yeah and also unmanageable let's say let's say 10 let's say a hundred thousand let's start with a hundred thousand and see what happens and see if this thing hammers it any worse uh so we've already set our snapshot on i'm going to go ahead and just comment some of these out just so that i can hit execute on this a little easier so now we got our exec in there and we got this in emmanuel thank you i appreciate it thanks for subscribing i thank you thank you very much so let's go put our stored procedure into production and then let's go run our small version of it to trigger parameter sniffing issues then we're going to run the large version of it so the manual says i forgot i had prime yeah i know that feeling too and then i'm going to execute sp blitz first and i'm going to start the big grant over here so i expect this to take at least a minute the one on this side i expect to take at least a minute because now he's doing a hundred thousand rows instead of just a thousand damn it's a party now thank y'all i appreciate it very cool cheers thanks for uh jim van allen as well there so i expect this the thing to take like a minute in order to run and then when it finishes we'll go find out dj webb says if you run it a second time it'll reduce the um the oh do you do the subscription on amazon no once you link your account go to my twitch page and then you can subscribe from there so then uh so tajweb says if you run it a second time it'll reduce the spill right so that was a yes and thank you for starting it actually so the that is true if you're on 2019 compatibility level and it backfires so badly that most people don't turn it on because basically what you get is you get a memory grant that was optimized for the last set of parameters that were run what you're saying is true if you run the same parameters twice in a row if you ever if you always run every query twice in a row on sql server 2019 the second execution is always faster i don't know that that's a good thing thank you pankaj i appreciate that welcome to the club thanks for subscribing so this thing took a minute 26 oh look at how beautiful that timing was that was just fantastic so it took a minute 26 in order to execute let's go see how temp db did during that minute 26 and we have oh man we didn't track any rights to tempty b during that time i like subs that can be read a couple of different ways just so that you're aware one is subscriptions the other is something else and that will i don't care which one you're referring to i like them either way you are both fine for me so i'm down um so we've got that out of the way i'm not happy with the performance inside that jim that's true i'm like that i'm like oh you know thumbs up people's videos and stuff when i like them because i'm like i'm always like trying to encourage creators to go do more of whatever it is that i like consuming so i i'm not really wildly thrilled with that let me go back over and look at the execution plan now we did spill sql service still reporting spelling like 60 000 pages to disk here we've got the table variable activity happening that we also now have the spill here this is good this is what i was really aiming for it was making sure that we spilled instead of estimating one row that we or it estimates one row and we brought back much more so unfortunately oh or wrestlers you have a point there's actually three methods then three things that sub mean and i like all three well four of course they're submarine sandwich or not just sandwiches but actual submarines and i i guess i like those too wow that's really cool so you probably shouldn't google or look for things like this on the urban dictionary or whatever that's probably a bad idea right now especially on a saturday your relatives are probably home and they'll see you don't want to do that so over here so i've got this this subs on a team so oh yeah that's true too wow there are how many different meanings for the word sub are there so let's go see sub dictionary that's kind of amazing wow filter by all naval railways chemistry wow that's kind of amazing sub submarine sandwich a substitute a subscription yep okay that was us uh yeah lower in rank oh sub as in lower okay that's kind of cool wow that's really let's go look at merriam-webster let's go see what it means so let's see here suburb okay i'm not sure about that a subordinate there are way more things than that but we're not going to dig deeper into there because then we're going to immediately go off and we're going to find the one that i'm referring to and things are going to go south very quickly all right so we've got we've gotten better but it's not still fantastic we might have a good time with this with concurrency if we try to multiply this across lots of sessions at the same time um but this is kind of i'm going to stop for a second the where this first came up was that dimitri who may still be on the webcast i hate yelling people's names out because you know i don't want them to all of a sudden oh i gotta pay attention but dimitri who was on here earlier when we started the webcast in our mastering query tuning class dimitri was like hey how much do i need to worry about things like table value parameters duh welcome to the club mac genie very cool thank you i appreciate it yes uh um uh yeah what's the other term for that uh depth charge depth charge is the other term i think we hear in america all the time when you drop a sub and drop a a shot of liquor inside a bowl or bottle of bowl of beer where the hell is i go on that okay so so anyway dimitri brings up this thing in mastering query tuning and he's like how much do i really need to worry about table valued parameters being passed between stored procedures or temp tables being passed between stored procedures is that something that i really need to worry about from a performance perspective danger i've asked a great question when do i decide to create a clustered index on a temp table that's another good example people go well how much should i be concerned about temp tables and i'm like you know what it's pretty rare that i see tempdb as a big bottleneck on servers in the year 2020 as long as two things have happened number one they've created at least four tempdb data files anytime you read anybody's setup checklist they'll all say create at least four tempdb data files sometimes they'll say eight depending on whose stuff you read i'm fine with either of them they're both totally acceptable as a starting point just one is usually not enough so duh mark thanks welcome to the club so as long as you do that and then the second thing is you put it on reasonably fast storage and what do i mean by reasonably fast storage i jokingly refer to people all the time as oh dimitri says this is why i'm here oh perfect i'm glad you're here so i always refer to people as my ipad i'm like look i have a terabyte of flash storage inside this thing so if your c your sql server should really have at least a terabyte worth of flash storage for stuff like tempdb now if you have a really tiny uh tempdb it may not be that big of a deal but if you're worried at all about performance put four to eight tempdb data files in there call it a day make sure they're on decently fast flash storage and and that's kind of the end of that gala bunga says is memory optimized table worth it that was one of the things we're going to explore if i can push workload hard enough at it if i can make the workload be really ugly while still being realistic because i see people do fake demos out there like microsoft did this when they brought out in-memory oltp with tempdb they're like watch this and then they do atrocious code that out of all the people in the world there may be like three people who write code like this like we shouldn't be focusing on that mainstream sql server it shouldn't be that big of a deal so i i am now in the process in this session um i love that solitaire kid you're an hour and a half into a live stream do you do that in your day job do you walk in an hour and a half after work started and be like hey what's up i uh i see y'all's doing some work here what what do we need to do if i was your boss and you tried that stuff i would fire you so fast that's not really true it's hard fired find good help these days right richie i'm just just kidding just kidding seriously just kidding aj says love your sessions probably because i'm a little bit of a jerk um my manny asks a good follow-up question manny says um so how many uh tempdb files should i do based on cpu cores so depending on who you listen to they're going to tell you either start with four or eight i'm fine with either of those if your sql server only has four cores just stop at four files if you have up to eight cores stop at eight files if you have more than that just stop at eight cores and call it a day uh shift clan says how's your day going thank you i appreciate it those are nice excellent words i thank you very nice arthur says you're funny and detobby says should temdb be on a different partition so for me it comes down to do you actually have different spindles do you have different storage for tempdb as opposed to your data and log files if you don't then it's kind of like what's the point the thing that i worry about is somebody blowing out tempdb because effectively anybody can do a denial of service attack on your sql server by in dumping stuff into tempdb and causing tempdb to fill up well if tempdb is sharing a volume that other databases are on or that your c drive is on that's where i worry about you running out of space and effectively having a denial of service attack on the sql server uh johnny mcad says how many files if i were to put it on a raspberry pi well given that the licensing on sql server on raspberry pi is kind of messed up like right now the only way you're allowed to use it is if you uh are shipping a production application with it it's not like you're allowed to just go buy it for home use so it's like developer edition only would be all you could really license it with and you're not going to go into production with developer edition anyway so screw it who cares um dtubie says our company policies is to put everything on the c drive well so much for that so now coming back to the whole thing with dimitri so we built this stored procedure and it's not really hammering temp to be that hard even though we're trying to we're trying to do things that are can somewhat antithetical um the next thing that i'm going to do is i'm going to go build a trigger on top of the badges table so that for every row inside the badges that goes into the badges table we're going to go do checking and of course i should do that with tempdb as well if i'm really going to try to hammer this thing lim says should we be considered about are concerned about tempdb if we use azure sure absolutely why not okay so when i do that so what i'm gonna do is i'm gonna go take our last bio break i'm gonna take a five minute bio break go refuel the last of my coffee i'm gonna order my bagel from downstairs because the dawn is rising here and eight a.m my coffee shop downstairs open and i'll be downstairs for that um aj says would it be possible to share the code no what i'm going to do is that's part of my mastering server tuning classes so if you're in my mastering server tuning classes you'll get access to that but otherwise no uh manny you're insane yeah i know that that's not what it does okay so we'll take a five minute bio break uh refuel my coffee before i and go order my uh coffee from downstairs and i will see y'all back in here in five minutes here in my place it's five minutes after the hour so i'll see y'all back in here at 10 minutes after the hour be right back [Music] [Applause] so [Music] so [Music] [Applause] so [Music] uh [Music] [Music] [Music] [Music] ah [Applause] [Music] [Music] me foreign [Music] do [Music] [Music] again [Music] all right so that's our bio break there now uh when i when last we met i said oh you know what i should do is i should add a trigger on the badges table and i should use temp db to do it no you know what because dimitri's in here because dimitri and i were having a conversation about really hammering temp tv you know what we should do is we should i'm already gonna do something really terrible you know how earlier we said we weren't going to do cursors well if i'm going to really hammer tempdb if i'm going to really cause contention you know what i could do is i could force it so that granting a badge thank you remus i appreciate it very cool you know subscriptions help make sure that i get interested in staying interested in doing this although let's be honest i would probably do this even if none of y'all were around um so what i could do to really hammer tempdb is i could say what if we forced all badge insertions to be done by one stored procedure what if we have a stored procedure that takes per a table valued parameter in order to go add a badge or what if i have a stored procedure that takes a string and it'll take a comma delimited list in a comma delimited list of users and badges that we want to add and then it immediately dumps that into a temp table and then runs off of that so i like the uh the size of that uh stretch that's not really related to what we're going to talk about inside here but it's interesting question but you can check that out at dba.stackexchange.com not doing an open q a one here i may do an open q a one in the future actually when you want to ask questions because several of y'all asked questions go ahead and go hit this so this is an open webcast where penal and i will take your questions and do all kinds of performance tuning advice live if you want to sign up to ask us questions go over to brentozar.com go slash experts and then you can go register for that free webcast and submit your questions over there now i'm going to go back to live coding so for this let's say that all of my inserts have to go through one stored procedure yes tajab i love where you're going with that and the thing is with the user defined function i may not be hitting tempdb so in the interest of speed just because i've got about 45 minutes left before my coffee shop opens downstairs i might use the built-in function i might use oh no i can't oh you're going to love oh i could do this so oh there's some interesting ways that i could do this okay so in here if i go back and i look at my uh query here right now with this stored procedure right now i'm taking a i'm doing the insert directly here a udf forces it to single core yes but i'm trying to hammer temp db though i don't necessarily want to slow down the cpus i kind of want uh sql server to be able to throw a lot of cores at it and but i want to press temp db really hard so what i'm going to go back to doing is i'm going to go back to saying that this i need instead of doing this inside the same stored procedure i'm going to go pass over to another stored procedure i'm going to have another stored procedure that act that gives people badges so i'm going to say in order to do this i'm going to have a child stored procedure that gives people badges that child stored procedure needs to take something that's going to be passed back and forth in temp db in a perfect world now some of you might be saying this is what tvps are for table valued parameters and that's true but another thing that works that same way is temp tables i can go create a temp table with the stuff that i want pass that into the stored procedure and have that stored procedure just use whatever was in the temp table you know i don't really like that solution the more that i talk about i think i'm going to be kind of hammering that needlessly yeah let's go back to the trigger let's go for the triggers surly dev says a global temp table yeah but the thing that i don't like about and i love that suggestion though it i love that suggestion because for some strange reason i have a sales call this week with a client that does all their work in global temp tables like the whole application is built around global temp tables i have a lot of questions so instead and it the the the so the reason why you might want to do that a friend i i totally have i love that that's actually really the reason why you might want to do it is that up in azure for a while you weren't charged for work that happened inside tempdb like there wasn't a scale limit a performance right limit or anything like that they weren't doing any throttling on it and vacure yes you're absolutely right i agree with you there too so i've seen people architect things inside a tempdb like do their data warehouse staging inside of tempdb and azure because that way they didn't have to worry about any cost penalties for it like dtu limits i don't know that i would rely on that for a long time like if microsoft hasn't already closed that loophole i'm sure they will so what i am going to do though is let's go back and do the trigger so we're going to say thanks to let me go back and get the person who who had this idea shamville shanville said let's remove badges if people had life finds away let's remove badges if someone earns a badge that isn't the uh red shirt badge we're gonna go yank their red shirt badge so let's think through the trigger that we're going to need to write what this trigger needs to do it needs to when a badge is inserted check to see if that user has the red shirt badge if so delete the red shirt badge and let's really screw them in terms of like versioning and stuff like that let's go subtract 10 points from their user reputation because we gr we gave them 10 points uh in order to we gave them 10 points when they earned the red shirt badge we're going to revoke that now that they've earned some other badge all right so how do we go create a trigger on the badges table i don't know about you but when i go to have to go do something like that that i certainly don't very often i'm going to go say sql server create trigger example and let's see if we happen to find let's see here dang it panel i was actually hoping that yours would have an example let's go see sql authority oh xcammer blacksburg were you the the uh public uh education thinking brandt they're all good dogs brandt nobody probably got that joke uh so we have inside oh yeah cool i think i was supposed to be there in june i don't know if y'all's offices are open back up yet or not i have to uh uh go email the head honcho over there who i love dearly she is awesome she's fantastic and i'm not just saying that because i'm on a public webcast i actually like her a lot she's great um so our offices are closed that's what i was afraid of do you have an opening date yet do you know by chance when you're opening back up um because i think i'm supposed to be out there like the third week of june and it's still like that that date is still blacked out on my calendar i just figured i'd ask y'all just what you want if you want to do remote training or what you want to do or try again for later in the year we can always go see not that we have to hammer everything out over twitch but you know there you go and then i'm gonna go talk we have nothing planned oh dang geez which is kind of a blessing and a curse right like this is the whole the coronavirus thing is it a lot of us now i feel like i should if i'm doing this i should switch over to the other uh the other window this is a blessing in the curse of the coronavirus thing right like it's good as a geek that we get to work from home because we all have these jobs that are really doable from home it's not like we need to be standing right next to the server in order to get our job done but on the flip side you miss the human interaction with people i know that's something that i struggled with when i first started telecommuting like 15 20 years ago when i first started telecommuting i had a really hard time i i don't like people i i don't like strangers like if you sit me down in a room full of people i'm never going to talk to anybody i'm going to be on my phone the whole entire time yeah so like ex-camera says i could be a hermit honestly i have the same i would love to just be holed away somewhere in a place with high-speed internet like this and this is usually all that i need like talking to people online is totally good recognizing regular names inside of twitch like i have no idea what any of you all actually look like it doesn't matter i don't care you know we're just this community that gets together and hangs out cowabunga that's uh sad but true um but back here yeah human interaction is important to connect with the team and the business users richie and i are in slack all day long just chatting with each other i'm a terrible manager though i have not seen richie even in live please place the github path if you can damn dude if you can't google my name and github give it up hang it up you see the window over there run and jump if you can't use google and find my github dude that ain't my problem that's your problem because you can google my name and you find all kinds of stuff things you don't even want to see you'll find it you go share the github path damn um yes i think richie and i in person have seen us seen each other maybe two three years ago i mean i don't know how long it's been we stopped doing the company retreat but uh but we at some point you know i keep going oh we should start redoing that even if it's just my family and his family but then at the same time now the virus thing hits and you're like ah travel i'm not quite so sure about that um it's a there's a possibility that i'm gonna go this entire like a 12-month span not leaving my house for work stuff uh i last left my house for work stuff in early february went to puerto rico for a client love that uh can we get you to come to puerto rico yeah sure and then they were nice people too which is even cooler but i might go an entire year without an on-site client visit this is going to be uh kind of bizarre i don't know exactly how that's going to go and jim's is too many stupid people out there these days i'm amazed by was it last year that i came down to coconut grove i don't i don't know if it was last year i don't think it was i think it was further back than that i think it's been a while um aylin good to see you howdy it's been a while but yeah jim when i go out to i'm gonna go downstairs to my coffee shop in like 40 minutes and i guarantee you there will be people walking down the street with no masks on and they're faking it as i'm exercising and i'm like you you haven't ran yet you're not heavy breathing or anything like that but they're just using it as an excuse at least with my coffee shop and most of the restaurants down here you're you're required to have a badge or have a badge that's has a have a have a uh a mask on in order to go in um but now restaurants are starting to open and erica and i were thinking last night we have a favorite restaurant that overlooks the water in uh in san diego costera there's this coaster and island prime and sea level restaurant that's right on the water there's great margaritas it's like 15 bucks for fish and it's not expensive but geez it's i don't know that i want to go back inside a place just yet mac genie says is there a breno's art mask there's that stig helmet and i i did that but that's about it wow richie was it 2019 wow i'm surprised it is man it seems like it has been so long since then wow that seems like forever ago that we actually got together down in coconut grove did the rental office thing geez yeah surely that's eating in a restaurant with a mask will be messy so the way that they're doing it is the tables have to be situated at least six feet apart and everyone who's at the table has to be in like they're calling a familial unit like has to be part of the same family people can't just sit down next to each other so i'm like does that happen very often people usually sit down um but i can see how if you were you were richie yes like but it's weird like richie and i have this job where you know it's a blessing and a curse that we can just continuously live on without uh interacting with any human beings it's kind of weird uh and you your reaction was priceless on my comment oh great thanks glad you liked it it's about so all right so let's write a trigger so i uh when i was doing the trigger thing i said uh when i write a trigger i'll use or when i write any piece of code that i'm not usually familiar with how often do i really write triggers i'll just go and google and get an example i was trying to tie into trying to tie into pinal's blog and of course sure enough we have one example here it took me a minute in order to go trigger one which cut trigger one so let's see here so let's go see what we've got for a trigger so it's create trigger instead of insert as and then go do some checking against it oh i love that because that's absolutely terrible sub strings and stuff that's fantastic so let's think about create trigger and let's see what was the example out of there was the name of the trigger on the table instead of insert instead instead of insert as begin again and end uh so the name of our trigger will be dbo revoke and i probably got that name wrong nope okay revoke noob uh oh after insert um i was gonna do instead of inserts so that i intercepted their call but you're right actually an after insert would make more sense because all i'm not modifying whatever they're inserting all i'm doing is revoking one that was already done so i like that not the dba you're looking for i like that so after insert it's a revoke red shirt on dbo badges after insert as begin and go so if the oh we'll we'll do okay so who should we do not the dba we're looking for we'll put him in as the changelog or her in as the changelog change log change log it's log it's log it's better than bad it's good um get that right there so this is 2020606 uh not the dba your and i'm going to leave that terrible misspelling unforgivable probably you're probably rate like length limited right not the dba you're looking for um what so if they earn a earn a badge revoke their red shirt badge so the first thing that we're going to go do is check to see now you know what the most common error that i see inside triggers is and i'm going to go pull this so that you can see it uh brent ozar trigger bug because i see this constantly and i'll go put it under what that's my website that my website is not undergoing maintenance uh hold on a second here let's go look at my site and see what's going on uh because that shouldn't be happening it would be really funny if it was in maintenance mode for some reason come on in buddy whoops exit refresh site is offline wow oh there's there's bigger problems over at uh at uh where do you turn on uh maintenance mode uh so maintenance oh you know what i think it might be in my theme let's go look here watch brent troubleshoot wordpress impreza impreza theme options my host is wp engine and they're actually really good but let's make sure that maintenance is not on okay maintenance mode is not on all right that's good let's blow the cash purge all caches okay and then let's he says it's back okay cool all right something went wrong in terms of caching there oh that's fun okay so now that i have that let's copy paste that and johnny maybe you were the problem johnny i would like to blame you um so what i have here is a bug that i run into all the time inside triggers people will write their trigger expecting that only one row is in the virtual inserted table they just assume that there's one yes i don't do any auto updates though but i think you're uh irkano i think you're the one that uh nailed it there i think that it must have done an odd uh uh uh status html wrongly set you don't even wordpress what the hell are you talking about um so dan over here so it's where uh people often assume that there's only going to be one row in the inserted table and there are probably going to be multiple rows inside the inserted table for those of you haven't played around with triggers yet god bless you because it's absolutely terrible captain ashtar it's called that's what temptibi is is it's a terrible place down in your parents basement they never told you about this but this is where they keep all the dead bodies you should probably go ask your parents about it you've hit that age where it's acceptable for you to go ask tell them brent sent you all right so not i'm not saying specifically your mom i'm just saying your parents it could be either one of them just also tell your mom i said hi so also so what we're going to do here is after badges get inserted it's a miracle that they allow me to do this so when badges get inserted um so when badges get inserted in here we're going to this after insert we'll say first thing we have to do is check to see if the user has any badges has has the badge named red shirt if they have a red shirt badge oh this is tricky because it happens after the insert because it happens after the insert will the inserted table the inserted table will have a red badge as well because i what i don't want to do is i don't want to have the trigger say after we insert a red badge row then we immediately go oh they've got a reg bat red badge row so i might actually need an ins a instead of trigger let's just roll with it and go see what happens because i've only got like half an hour left if they have a redshirt badge revoke 10 points of reputation update dbo users we'll say update you from inserted and i never remember there we go inserted i inner join dbo users u on i user id equals u id so set reputation equals reputation minus room i always i always screw that up minus 10. all right so what i think that's gonna go do is if we added a oh no no no no no it's got to be only for the red shirt badge where i name equals red shirt so what this should do is if they have a red shirt badge already damn it no that's not right okay so where exists because this would be the the row we just inserted that's not right what it is is where exists select star from dbo badges where and we'll format this just a little bit nicer not elegant but just a little bit nicer from badges and i always like alias stuff where hi night sniping where b u where i user id equals b user id and b name equals red shirt so what this is i couldn't look at the inserted table because the inserted table has the the rows that they're inserting now that may not be the red shirt badge hopefully it's not the red shirt badge then down here i'm saying where they already have a red shirt badge so i think this is going to revoke 10 points if i wanted to be really careful let's also say where i badge where the i name is anything other than red shirt and exists that all right so i think that does what i want to be clear in in the course of this stream i'm not going to check for accuracy yet i'll check for results sometime else later so i think that gets the red shirt badge revoked now i also got to go delete badges from their account because first i had to go make sure that they had a red shirt badge before i removed the row certainly says yes exactly don't forget to remove the red shirt badge otherwise each time they get a new badge we'd have a problem so now let's delete the i'm going to call it rs for badge red shirt from dbo badges brs inner join dbo we'll just do the inserted inserted i inserted inserted i on brs actually i prefer going from the the virtual table from the trigger it's not like that's going to affect performance but i just think it makes readability a little easier from inserted i inner join dbo badges on i user id equals brs user id where the inserted badge name is anything other than red shirt and okay and uh brs uh oh thanks captain ashtar i appreciate it and the answer to your question is no uh so brs name equals red shirt all right so now i'm saying wherever they got inserted a badge that's anything other than red shirt and the badge that already exists it wouldn't be the first time remember i'm going to be referring them to your parents so down under here so i'm looking for specifically the badges with on the red shirt is night sniping that's a really good question actually is it really that hard no you would be stunned at the number of idiots who can do it yourself included for sure so now i've got that delete set up it is an experience thing though like the more experience that you have and the more confidence in yourself the the easier of a time you can do to have it the tough part about getting into programming honestly is the job part the really hard part is there are generally a large pool of people who want to get paid to do it and there are only so many job openings available so that's the part where it gets a little tough if you want to build something for yourself like go build an app and go sell it online or build a site a plug-in whatever and sell it online that's a much easier route to success if you have business knowledge about here's a problem that industry is facing then you can go build something to solve that and you won't be competing for anyone else with programming knowledge you're really just starting your own empire there okay so we've deleted their badges i think that's in good shape now i'm not really hammering tempdb at all inside this the inserted table does deal or does uh get versioned out in tempdb or is not versioned but it's handled out as a temp table basically so depending on how big this is that might be a problem all right um oh the other thing that i could throw into here is a memory grant we don't need no stinking badgers uh we could i could try to figure out how i would throw in a memory grant in a trigger oh i'm i'm gonna i'm gonna leave this in the change log though i'm gonna say change log uh to be done later add a memory grant and spill because i won't do that right away here all right let's see if that works so after insert go update their reputation delete the badge for red shirt i think that's pretty good so let's go put that into place and we'll test it so the way that we'll test it i'll go put this little thing in place so now i can go look over at the badges table and look at the list of triggers in order to test it what i got to go do is i got to give a red shirt badge to somebody and i'll make that myself just to say so we'll say insert into dbo badges name user id date values red shirt my user id and the current date go now i'm going to go look at my reputation select star from dbo users so my rep not star you so where uh id equals 26 837 i i was proud of that no one else caught it i was very proud of that but yeah so here my current reputation so my current reputation is reputation equals 11 825. now let's go see what happens if i insert into badges a different badge name let's go give myself a very polite streamer badge we all know how true that is go execute that one now yes ah at first that might not jump out at you as a success but here's the deal whenever you have a trigger if you don't set no count on if you don't set no count on inside the trigger your messages will tell you everything not doesn't tell you everything the trigger does but it goes through and tells you that if rows were affected in other tables so what are these one of these is my own reputation that just got dropped by 10 points and the other one is the badge that just got removed so to test it let's go look at my own users row and my reputation has now been dropped by 10 points yes and if i go look at welcome to the club speaker i need to figure out how to say that speaker freak i'm thinking what that is speaker freak which if you're really a speaker freak you and i share a bond because i really love dying audio speakers there are a lot of speakers that i love but my wife won't allow me to have speakers that you can see inside our apartment i'm only allowed to have speakers that you kind of can't see so i have apple home pods in a couple of places but then i really want to like build a house someday and have a big wall of dynaudio speakers and whatever so now i've added that badge let me check and see select star from dbo badges nailed it speaker freak nice uh where user id equals 26 8 37 um order by one descent to me fireball says don't let her kill your dreams god bless her she's super supportive i absolutely love her she's wonderful so the uh the red shirt badge is gone that's kind of awesome and i bought two of them it's kind of crazy uh johnny b cat says i just thought of something we're not capturing the history of removing and adding badges and i love that if but the problem is it's not going to hit tempdb i love that in terms of an accidental surprise workload that's kind of awesome but yeah okay so we have now we have this we have it in our trigger and we've tested it this is fantastic let's set no count on inside the trigger just so that it doesn't pop up right away and then set no count off down here set no count off all right love it so my trigger works now there's okay all right you're right i didn't do creator alter creator alter man i love creator altar so i forget whether this came out in 2016 service pack when or or what or or what bird exact specific cu it was but that one line alone to me that was like i need to stop and thank microsoft for that because that was phenomenal i love that finally we get a really good usability improvement there so put that into production and now let's try our uh stored procedure again so i'm going to try my stored procedure one two one more time i haven't changed anything about the stored procedure although you know what i am gonna do is i'm gonna set no count on inside here set no count on uh just so that he's in too as well and then 100 just so that we got this done and then execute all right so i'm happy with that let's go try to run that one more time surly devtrance trance as in uh drop if exist see i don't like drop-off exist because the problem with drop-off exists is that you lose permissions when you go to recreate the object if you do drop if exists and then you create the thing again it lost whatever permissions assignments that you had however i will say what i love about drop if exists is tables i love dropping if tables dropping tables at the start of demos and that makes it just way easier love that it's fantastic okay so we have surly dev certainly dev you're my moderator i need to be able to trust you you've got to keep it together it's not true it's a pretty casual shop around here um table users names so now we'll go ahead and try our a little trick again here what we'll do is we'll put in the very tiny plan into memory we'll go back and run the big plan and we'll measure what the effect looks like on tempdb so over on the left hand side let me separate this out into different windows here um new vertical tab group walden says i've never turned it off in triggers i don't want you to get the impression that i know that off the top of my head literally the only reason why i put that on there is i haven't written a trigger in probably a year or two i have no idea where the scope begins and ends i just immediately thought oh if i'm turning it on i'll go turn it back off at the bottom what you just saw me do there don't take it as a best practice like i just have no idea if you need to do that or not i always want to be clear with what i know and what i don't know which is most things so on the left hand side i'm going to start my sp blitz first sample again i'm actually going to pop it up to two minutes i'm even tempted to pop it to three minutes just because i don't know how long this query is going to take so we'll go start the first version of this the first version should be fairly quick and straightforward now now that i ran it even just for the first one you'll also see as i scroll down through the execution plan there will also be the execution plan for the triggers so we have the triggers inside here as well um uh balakrishna it's for the whatever your queries scope is so like if you're doing a select and it built a work table the work table will die when the select finishes all right so now we did that one now and i just love that it's doing much more work behind the scenes oh it's got a sort hot dog it's got a sword inside there i might be able to to get that to screw up the memory grant again later i got another sort up here too as well today's music is childish gambino so now let's try it for a big monster date range let's go start our sp blitz first sample and then start the stored procedure i expect this to take at least a minute because it was taking a minute whenever we were doing this uh without the trigger and now we've added the trigger so i expect it to take a longer period of time what i'm looking for i don't really care as much about the execution plan right here although i'll be interested to see if we get sorts inside the trigger that spill to disk because that would be freaking amazing parameter sniffing inside a trigger are you kidding me that's gold i have a twisted definition of what gold is then on the far left hand side what i am going to be interested in is how much did we write to temp db during the course of this so if dimitri is still on the call my goal for this call webex stream whatever now my goal for this will be if i can get this thing to hit parameter sniffing spill to disk do the version store stuff in the next thing that i'll do is introduce this into a sql query stress load and dimitri you know this from the mastering classes but over the mastering classes we run sql query stress with a wide variety of queries that are all running simultaneously that will be the key that'll keep the version store around longer because i'll be pushing lots of activity and causing this update to take longer causing the tempdb activity to grow i don't want to run this query across a whole lot of threads because i don't think that that's realistic from an end user perspective because you're in the mastering classes i'll just give you the code in there for that so if you want to try sql query stress across lots of threads with this for synthetic purposes you could the query actually finished after a minute 22 which kind of surprises me in a nice delightful way so the query finished after a minute 22 which is good so now let's go look at the execution plan just to see if we got lucky and got spills on the trigger so we have spills here we always had spills here and if i went to a higher number for my top then i'm pretty sure or if i took out the top all together if i stop pretending it was a batch operation then i could make that spill even worse if i come down here further that one did not have anything that would spill to disk sql server only estimated that one row was going to come out of the table variable but that estimate had a really small blast radius because nothing spilled to disk i could throw something into that insert into badges to force an order which would do a spill if i wanted to take this again to the next level coming down a little further on here we have a yellow bang on our update what's the story there yes well kind of sort of if i look at it'll always my sequel weather man here and this is warning danger i gave you way too much ram well i got excited about that but that's the opposite of the problem that i'm trying to fix here i'm trying to encourage spills which means sql server wouldn't have granted enough memory i did the opposite here so that's not that big of a deal now when i looked i was like it's not that bad the the grant size is fairly small so who really cares but if i go down a little further let's see if we got anything on that sort so we have the delete how's our yellow bang on the delete again excessive grant so that's not as nice as i would prefer then if i come down here now i got a sort on that that's kind of nice so this is okay oh the reason why those other ones so the reason why the triggers are estimating low memory grants is because uh they don't have red shirt badges these people don't have red shirt badges yet okay so it did spill to disk a couple of times now is the moment of truth so now [Music] so this thing still only wrote 60 megabytes of stuff to temp db and this is kind of a blessing and a curse so when we started out with this the whole reason that we're in here today is that in one of the mastering classes dimitri asked a wonderful question when would i be worried about stored procedures or other things passing themselves data back and forth in tempdb and i'm like you know it's been a really long time since i've seen anything involving tempdb as an issue as long as you did one of two things as long as you created enough tempdb data files and you put your tempdb on reasonably fast storage and by reasonably fast storage i mean typically as fast as at least an ipad you know just general flash storage so i'm very happy with this i think it we did pretty well in terms of showing that even when you're trying to abuse tempdb in a little bit of an accidental way it's not really that bad i'm going to do one final thing which is i'm going to take off the top instead of just processing 100 thousand rows at a time i'm going to process the whole crew i'm also going to do something unrealistic in the where clause i'm going to say that everybody gets a red shirt badge so let's see this one is going to be the last like mythbusters style attempt to blow this stored procedure up so in here before i had a top 100 000. i'm going to comment that out so that we're actually getting everyone and then i don't know that sql server won't optimize out that order by whenever i don't have a top so what i'm going to do is i'm going to leave the top in but i'm going to put a ridiculously high number i'm going to put up a higher number than we even have in terms of users at stack overflow so this is a hundred thousand obviously i can't use commas when i put the number in but just to show you i'm going to put a hundred million in there aren't a hundred million users at stack overflow but this way i just will still get the memory grant um and i just even if sql i won't have to worry about sql server optimizing out the order by sql server will actually need to do the ordering so that's kind of cool so let's go get everyone we're going to say we're last access date okay good that's all perfect we're going to insert anything that left outer join is fine that update is fine i think we're good there let's also for the ultimate stress test we're going to go make sure that we have the very first date the very first date of activity and the very last date of activity inside here so when i go run this stored procedure so right now we're running it for hi richard good to see you again as well so right now we're running it from 2009 to 2020 i'm going to say 1970 to 2037 just to make sure that i catch every single user part one is i put the tiny plan in memory so let's go to him execute now i'm going to turn around and run the big plan now the big plan took 90 seconds before um i'm i have no idea how long this is going to take now now that i'm running the whole entire table uh anshu man of very few words even no words and just an at symbol i bet you're popular people love you around the ancient you god bless them and she's like that's not picking on me um you know when that might the answer the if you want small trivia the at sign the the people in amsterdam call that the strudel i think that's the strudel uh so it might it might not even be amsterdam might be somewhere else that they do that i can't quite remember so let's start our sp blitz first sample and then we'll go start our red shirt grant now before this was taking about 90 seconds and i have no idea how long it's going to take now now that i'm not batching it and it may not even finish by the time that i need to go downstairs and go get coffee so while it runs i'm going to do a quick shout out to our sponsor so my sponsor this week is quest software quest is doing a totally free webcast with me and panel dave so for those of you who wanted to be able to ask us questions or you're firing off random stuff as who is my daddy and what does he do uh questions inside of the stream you're welcome to go ask those questions during this stream so this one is what we call an ask the experts mike shaw mike i actually have written down i don't have a lot of stuff written down outside my desk but i actually wrote down so that i would remember it's probably not even gonna focus worth a damn that's okay you get the idea but i actually wrote your name down so that i would remember this time and stop calling you mark so he's like hold on i want to take a picture so in this webcast you can ask us whatever questions that you want around performance just one thing to know there's always a limit of like last time it was a thousand people were allowed into the webcast and we knocked that completely to the top and people couldn't get in if you want to get in it's not enough to register you also have to show up a little early like even if you're five minutes early you're going to be fine because so many wackos just try sliding in size ways like five minutes after the hour where am i um so branozar.com go slash experts those are always a lot of fun uh just because i love talking to panel we have a great time doing that uh drop table employee says that in russia they call the at and i'm not even going to try to pronounce that which literally translates as dog oh that's kind of cool i could i don't know why that either i mean it makes me think of the so you know about the dog's magic button all about a dog's magic button you know how that works you may not know i don't know if you all have heard about the magic button before or not we'll see if anybody puts their notes in on slack and or uh twitch or whatever saying that they know what the magic button is um richard says over on youtube at is a strudel in israel netherlands we call it the monkeytail sabacca is another no so i don't think you all know what a dog's magic button is so when we were kids my uncle gail so anshu i'm not going to talk about that one inside here just because we're at the end of the webcast it's a good question but feel free to ask that during the webcast request um so i'll leave you all with a story so when i was a kid uh we had this uncle gail uncle gail and he would tell the kids that dogs have a magic button and if you can touch the magic button you get a piece of candy and so what the magic button was was the dog's butthole so you would see all these little kids in the family of family gatherings running around trying to touch the dog's magic button and our poor dog would run around constantly trying to look who's who's coming up on my rear end and of course you know there's no such thing as as a magic button but it was hilarious because you know with dogs you can kind of see it so now for those of you who don't have children that's a great thing to go a great gift to gag to go play whenever you're at family events isn't that terrible god it's just so awful so my 90-second sample ended uh my 90-second sample ended and over here on the left-hand side i can see that temp db did have right activity during that time span and the way that this works the way that s p blitz first monitoring works is it shows you the top five files ordered by activity descending well it just so happens that stack overflows data files had more activity than tempdb did during that time span you can see that tempdb had reads of a fairly decent amount but not necessarily rights yet or at the rights that it had were fairly small so what i'm going to have to do in order to instrument this for the mastering server tuning classes is i'm probably going to just change sp blitz first to return more data or else we'll go look at the output table from sp blitz first instead of looking directly at this because the output tables for s people's first actually include all of the rows not just the top five by reads and writes so there we go so hopefully y'all learned something or had some fun throughout the course of this morning thanks for hanging out with me i will now go vanish off to my downstairs coffee shop it is a beautiful morning out here in san diego the rain has cleared thank goodness we had drizzle early on so it looks like i'm going to be able to walk down there and go get myself a nice fresh cup of coffee the replays for this for those of y'all who got in uh late the replays will be up on twitch facebook youtube mixer all those sites and i don't think i'll do a blog post with this uh because it's not really relevant to most of the blog post readers it's just fun exploring in terms of t-sql life legacy did high elevators freak anyone else out it depends on their speed so like i have i've lived in buildings before like 50-story buildings that would get me to 50 floors within 15 seconds that freaked me out that was insanely fast so thanks everybody for hanging out and i will see y'all next time adios have a good weekend [Music] you

Channel: Brent Ozar Unlimited

Views: 5,497

Rating: 4.858407 out of 5

Keywords:

Id: SkOc_ISBpqc

Length: 147min 1sec (8821 seconds)

Published: Sat Jun 06 2020