How Do I Know If My Query is Good Enough for Production?

do so bye hello party people good morning here it is a nice beautiful sunrise here in san diego oh it's just the perfect time uh oh that's the first time in a while so we're on the flight line for san diego airport oh nice ups um it's uh i get excited when we have big planes again we ever since the coronavirus hit we haven't had a whole lot of large planes come through it's been kind of you know tourism has gone downhill of course uh there's been just less traffic and shipping kind of stuff all it's been a very different kind of time out here kurt says my brain still hurts from the last three days of parameter sniffing what was i sniffing yeah so we just finished tuesday i did the fundamentals of parameter sniffing and then mastering parameter sniffing was wednesday thursday friday we had kurt we had christopher cgs good to see you again i was in in those classes as well i still have a lot of work to do on the labs on those i feel like i just always want to take it up a notch and then we got feedback so i was i tried to run surveys when i think i might need to tweak something about the lab i run surveys in the classes to see where the lab's too hard too easy or just right and the mastering parameter sniffing about one third of the folks said that it was too hard and then two-thirds said it was just right so i've got some work to do there on those labs i might do those in the next stream or two we'll see how that's going and then this morning's topic uh well i got a great email yesterday from a client the client asked how do i know if my query is good enough or not to go into production oh you got your power back oh that's good how do i know if my query is good enough to go into production and i was like oh that's a great i love that question because it's something that i kind of have a gut feel as a person but i don't know that i've ever really put it down into a blog post about what my thought process looks like so i've started writing the blog post yes exactly alex says it compiles uh and then step two it actually executes successfully three i am still alive when my query finishes so i was like so how do i put that in for new developers if you're a new developer because so many people out in the world their production server is completely different it has a where clause and it's not where one equals one the production server and the development server are so different in terms of horsepower like maybe the production server is so beefy and yet their dev servers are really small or their production database size is so totally from their development database size these days in the day and age of gdpr and hipaa and pci and sox compliance and all that kind of thing oh good very good to hear super they have all these compliance things often people are developing with a completely synthetic database that has nothing to do with the database that's out there in production so this this led me into going okay i can't give this client an answer quickly over email and it was such a good uh question so i'm like all right i need to go write a blog post for that so i've started writing the blog post but in last week's stream y'all were very emphatic when i gave you the choice between would you like me to write the wordpress part of the blog post or just work on doing more demos you were very emphatic that you wanted to see more demos instead so i've started writing the blog post but i'm not going to go into the wordpress side of it and i'm just purely going to do uh the oh so sql wwe the demo side of it so i'll work on the demos and put in my like start capturing the screenshots for it and then i'll actually write the blog post separately and it'll run in like two weeks or so my blog post queue for this coming week is already baked just finished doing the uh the release work for the first responder kit so the first responder kit new sp blitz blitz cash blitz index and all that will go live this morning uh or this afternoon the release will go live on github this afternoon hi folks and then the blog post for the new first responder kit and the new consultant tool kit will go live on monday and on monday tuesday wednesday i'm talking about things you can do with the first responder kit those blog posts are baked so this one's really for like two weeks from now so let's go in and i'll show you the outline and we'll start writing demo queries so these are the things that i thought about when i when i'm writing the blog post for this person i said okay so how do i know if a query is good enough there is a there are a list of things that i think about before i ask or answer the question and those up here i'm not going to dig into in the blog post all i'm going to do good morning big mike all i'm gonna do is just kind of give them a list of here things that i think about and i'm not gonna give you the answers because if you're smart enough to ask the question you know the impacts that these have for you you know there's only so far i'm gonna go when writing a blog post in terms of spoon feeding folks so i'll let you all read lines three through seven before we start building uh demo queries and then now when i think about measuring queries i think about these three things i think about duration reads and memory grants there are other ways that you can measure queries as well locks is a an interesting one are you holding entire table level lock so you're just holding row level locks i'm not going to go into that level of detail in the blog post though because i usually try to aim for around a thousand words thousand to two thousand words max and between these three that's probably going to be bad idea thong oh that's interesting it's probably going to be 1 to 2 000 words just with these three things here so duration i don't usually like measuring clocks or measuring queries in terms of clock run time but i want to show the reader how to do it and to do it i want to show them a query that's going to take a long time in order to execute and that it's going to burn multiple cpu cores while it works i think that there's an art to i think there's an art to writing demo queries to make them as simple as possible then i can use as few tables as i possibly can and make the query seem real world make it seem like something that somebody would normally write this is why i hate adventure works and north wind style queries because they're just disconnected from reality somebody will write some demo query and they're trying to explain it to you and they're leaping five you know words or five sentences in trying to go look i didn't you know no one would ever write a query like this i want my queries to look real so i need a query that's to go parallel across single threads and it's going to run for a while so knowing the stack overflow database i want to use as few tables as i can in these queries so i'm going to say there are a bunch of big tables i'm going to take the posts table as an example the post table over at stack overflow hosts holds everyone's questions and answers so i'm going to say for example i want to find the most popular posts for a date range i'll go use the comments on the date table post which is kind of funny we're talk we've talked about uh several of the interesting things you can find in the stack overflow database so i'm gonna use a post table just to come up with something let's say i'm going to say find the most popular posts for a given date range so let's go see so to do that i'm going to say oh you know what i could also do i also like doing as few demo queries as possible that show problems across all of these the users table is so small though um no yeah let's stick with the post all right so select top 100 uh let's actually go top 1000 uh star from dbo post p where p creation date between uh oh we'll do we'll do functions we'll do functions in the where clause so let's say uh we'll go where p creation date is greater than or equal to 20180101 and p creation date less than or eat he's already had a uh he's already had actually if you go back through the blog there's been uh clippies posts in the past 201802.01 and then group or order by p score descending uh i'm also going to say where it's questions only and p post type id equals actually let's do this let's do p inner join dbo post types pt on p post type id equals pt id and pt type equals question okay so let's see what happens and i'll i'll be fair and i'm going to do 100 rather than a thousand so let's do this now in order to before i run the query if my goal is to measure duration i'm going to say set statistics io and time on and then i'm going to go run the query and i'm going to include actual execution plan the reason why i'm going to include actual execution plan is i want to teach the readers where they can get times inside the execution plan even if they don't turn on statistics i o and time on in newer versions of sql server they include all kinds of additional metrics over in the actual plans because from what i've seen microsoft support was getting actual plans from people but the actual plans didn't have enough troubleshooting data for them to solve the answer to solve for the problems with the query so they've been gradually pumping more and more stuff into actual plans including logical reads times trace flags weight statistics so really they can just get the plan and figure everything out from there also i think has to do with probably them tuning azure as well because if they're going to have to tune based on the actual plan they need lots of stuff built straight into that actual plan cob had asked are you getting any blowback from microsoft from belittling clippy or all his fans gone almost no one at microsoft likes me at all a little inside thing there i burned my bridges there a really long time ago uh microsoft especially back when i was in the mvp program microsoft let it be known to me more than one time that uh mvps do not write negative things about microsoft you know that that isn't mvp-like behavior and i'm like well you're the one who gave me the mvp status i didn't ask for it so if i'm exhibiting behaviors that isn't like an mvp i guess there's only one group that can fix that if i'm not an mvp maybe you should take it away from me and they never did i ended up giving it up for lots of interesting reasons that maybe someday i'll go into but it still kind of feels a little raw uh let's see here so uh not the dba you're looking for says we are dbas nobody likes us so i like this this query is going to do absolutely beautifully in terms of uh illustrating how slow the the query response time is and i can also say this is why i can't necessarily judge uh queries by how slow they are in development because it may be that in production i have enough ram to cache this in table table in its entirety so i'm just going to make a note in here things to discuss in the blog post in dev i may not have all the ram to cache the table in production i might so the runtime may be way lower index behavior will be the same between well it'll be the same we'll be roughly roughly the same between production and dev though so if i'm getting a table scan in pro and dev because there are no workable indexes then i'm probably gonna get one improv so the inaccuracies here and accuracies lead me to use this measure instead so and i'm not going to wait for that thing to go finish although i'm going to show y'all just in terms of the execution plan if i go into activity monitor if you want to see like a query that's running a really long time what kind of status it's in you can go into activity monitor and in recent versions of sql server there's this active expensive queries thing here you can right click on this and go show live execution plan and it'll show you the plan live the status where we're at this is kind of useful if i want to go in and see how far back we are from finishing the thing but i'll tell you what see how it's not displaying anything this is totally my regular experience with live execution plans as well i have like a one in three shot of getting them to actually work a good half the time they don't hi meyer we're not doing open q a in this question though so if you want to ask ask that feel free to search for no lock on my blog so if you if you uh if live query stats isn't turning up results what you can do is oh and it just finished at the exact same time i was going to say what you can do is sp blitz who and if you run sp blitz who it'll show you the live query plan for any running query just makes it a little more usable so let's go over here to the query finally finished over here richie and i are dealing with well richie's mostly dealing with we had a problem in constant care overnight where richie deployed a new query to production to help y'all get better missing index hints from constant care and it promptly drove our database server cpu to 100 with this beautiful graph we should actually tweet that of how cpu just goes straight to 100 on our database server overnight and richie woke up to all kinds of failure emails so here if i go and look at the messages for this oh so that's beautiful um so i'm gonna grab a screenshot of this just so that i can use it during the blog post so here we'll grab this out so that i can show the number of reads there that goes then let's see here does it also have yes oh that's beautiful so i'm going to go down to this and grab a screenshot of this instead that's what i really want so this will show me the time and that's good that's perfect so there we go we are we are more than human we are inhumane to our database server most likely okay so that's good that gives me that now if i go over and look at the execution plan it's a yellow bang yellow bang i gave you way too much memory oh that's so funny it's such a terrible answer so i'm going to grab a screenshot of this for use in writing the blog post and of course there's our hey buddy i see that you're querying my post i body and creation date sure would help if you had an index on post type id and creation date so if i if i'm uh so i said that microsoft's trying to get more and more stuff inside the execution that's actually not a bad idea chris that's uh not a bad idea at all so if i want to see how many logical reads that a query did i don't i can't see it out here like i would i should be able to see it for the query overall i do get to see it for individual tables though like if i click on the post table and i go over here to i o statistics i can see uh across my ios so what i'm going to do is i'm going to grab a screenshot shoot no that's not the screenshot key you i'm going to grab a screenshot of that just so that i got that to do and then number of rows red i should also wow it's interesting that all threads that didn't go parallel that's wait what hold on now why would you not go parallel you didn't go parallel even though you read what it ran for 205 seconds and it didn't even go parallel that's bananas why would you do that uh so wow oh the stats are terrible on it too i'm amazed that it didn't go parallel and there's no uh non-parallel plan reason that's really funny okay so this is kind of amusing this is just as a side note so this is i'm in sql server 2019 compat level i decided that for today's webcast i wanted to go to 2019 compat level and i almost never do that um it's it's true he's correct um the cost threshold for parallelism is either five or fifty uh so the estimated subtree cost on this is eight thousand three hundred fifty one but the estimated subtree car or the uh cost threshold for parallelism on the server is 50. so you can see up there i love how you all think that i don't have pants on i think that's very amusing thanks appreciate you wondering what my cosplay no seriously i actually like when y'all because sometimes you actually catch me and lots of times you catch me and dumb stuff but this time so what i i wanted to uh today start using sql server 2019 compat mode inside my demos because i just keep seeing behavior with no pants you know just where i look at 2019 and going what the hell is going on in here like why would you run the query that way so just for laughs because it's us this won't be inside the blog post but just for last because it's us let's go ahead and switch into stack overflows database compat level 2017 so i'm just going to drop back over to 2017 and hit ok i'm going to rerun the exact same query again first i'm just going to get the estimated execution plan i'm going to hit control l to get the estimated execution plan so 2017 goes parallel you see the little racing stripes there on the posts table i love how eric darling calls those racing stripes then let's go through and run it again and let's see how she goes now before as a reminder she took 200 and some seconds uh and i don't expect it to finish instantaneously because this this is actually a big query scanning the entire post table and i don't have uh indexes on either creation date or post type id so this will this should be taking some time in order to scan one of the nice things though because this query went parallel if i go in and look at task manager so now you see that's actually kind of good that's what i want to see is when sql server has a lot of 8k pages to read i want to see that it goes parallel but of course this will be in the pudding that the query actually runs faster so we'll give it a second to go through and finish there there we go check that out 53 seconds that's fantastic that's way better than it was before and if i go down and look at it's also less execution time too as well less cpu time less elapsed time that's just fantastic again not going to make it into the blog post just because there's only so much that i want to confuse the readers with so let's go back over here and pop it back stack overflow back into 2019. howdy folks uh good to see you say okay and then so that's that query that showed me duration taking a really long time now uh and the the other thing that uh that i kind of wanted inside there was i wanted a query that went parallel though because i really want to teach the readers uh what happens when a query goes parallel and it maxes out all of your cores for an extended period of time well it's amusing that on 2019 we don't get parallelism inside there so i got to write a different query so how can i write a different query and get sql server to go parallel on that i don't wanna it's so stupid to have to frustrate freaking do that okay instead of how could i get uh i can't believe that query didn't go parallel that's utterly ridiculous and then it ran for three minutes and didn't go parallel i was going to say i could use a query hint like disable batch mode that's probably going to do it but real life users would never do that either let's try adding a join let's see whose question it is we'll say enter join dbo users you on p owner user owner user id equals you you really like sub queries don't you because i think you did that the last time i was writing a demo id so we'll say you display name p id as question id well we'll do we'll put that later in the query uh p title p score well we'll do p score first because it's sorted that way uh p score p title uh there you oops yeah yeah you display name and let's see if that one's going to go parallel i'm just going to hit ctrl l to get the estimated plan just to see if it uh estimated look at y'all i'm not no come on mike you know me better than that i am not doing max stop equals one someone else asks two as well y'all seriously i'm not stupid plus besides it went parallel a second ago when i did the 2017 query man give a fellow a break okay so now that i added a join to another query it's actually going to go parallel so and i know that because it showed in the execution plan and just to confirm we are still on 2019 compat level so now let's see if i go through and run it how long this little fella takes i'm also going to delete my screenshots from the previous one because i don't want to deal with i only want to have the screenshots that are actually relevant to the blog post i am about to do mike nice recovery there very nice recovery before when we were doing the other demos and i was showing you to look for a live running query i said if you couldn't get it from activity monitor you could also get it from sp blitz who well here's an example of that what i did was i ran sp blitz who and it gives me the query that's running its query plan but also its live query plan i can click on the live query plan and this isn't animated it doesn't show you the moving arrows between there but what i can do is i can hover my mouse over here and i can look at say for example number of rows red so that i can go see how many rows we've read out of the entire table this is really helpful when i'm trying to see if i know that this table has 40 million rows i can get a rough idea what my progress looks like so let's come back over here and she finished in 49 seconds okay that's good now let's see in terms of time did this thing go parallel this is a fun interesting thing to show you in terms of just because you see something on an execution plan that goes parallel doesn't mean it actually went parallel so what happened here is that we've spent 50 seconds on the clock and we used about 50 seconds worth of cpu time that's not real parallelism real parallelism actually spends more cpu time than there is on the clock if i have one minute worth of query runtime and i'm spreading it across four cores i expect to see four minutes worth of cpu time one minute elapsed on four cores should equal four seconds or four minutes worth of cpu time and i don't see that here it's almost one to one which means that really only one core did any work this is also not the object of the blog post but it's fun to see it's always interesting that i can i always come up with uh execution plans that illustrate a problem when i'm trying to show you some other problem life of doing a presenter so i get to file this query away and use it later when i'm trying to illustrate cx packet weights so what's going on here so sql server did a clustered index scan across the post table but how was that work actually balanced notice that it has eric darling's racing stripes there that doesn't mean that the work actually went parallel if i right click on that and i go into properties then i can come up here to see by thread how the work was allocated in this all of the threads were given pages so there are four threads here that all did work there was work spread across all four now how many of them found rose now this wasn't what i meant to talk to tell you about but this is so much fun to see and it's one of my favorite lessons so we're going to talk about it here for a minute if i told you and so in a in order to tell the story i will go back to how to think like the engine so the post table is a stack of pieces of paper over there in the closet it's a whole stack of 8k pieces of paper and if i told you hey you get three of your friends and all of four of you go into the office supply closet and go start scanning the post table and what i'm looking for is i want each of you to find moshiko we're not we're not doing open q a in this webcast so in here i want you to go into that table and i want you to go find posts that match that date range you're like okay all of us are going to go in there right we're all going to go in and we're going to start looking at the posts select top 1000 star from dbo posts and so here is how the posts are organized there there's an id column starts at one and goes up to a bajillion so they're organized by id and you and your three friends are each going to grab a stack of posts you're going to grab them just by the way the sql server chose them sql server decided to give one of you the first quarter of the table one of you the second quarter of the table one of you the third quarter and one of you the fourth quarter in order of ids now which one of you do you suppose is going to find posts that were created in one month in the year 2018 think about that for a second if i hand you out just to say let's keep the numbers simple let's say that person number one gets ids one through one million whoops million person number two gets ids one million one through two million million and then i'm copy-pasting the rest because i'm way too smart for that uh person number three and then person number four this person gets two three three four if all these ids are in order and i tell you find all the rows where and i'll copy paste the creation date out so that you can see it again and if these started back in the year 2008 to 2010 and these were like say 2011 through 2013 and these were maybe 2013 to 2016 and then these were 2017 through 2019 guess who's gonna find all the rows the work isn't going to be evenly balanced because sql server doesn't know that there's a correlation between the ids and the creation dates so sql dev dba nails it only one of these workers is actually going to find anything to work on the rest of them will go shuffling through like john travolta and pulp fiction they won't find any rows so as a result all of the rest of the work in there is down to just one thread this is one of the problems with parallelism and cx packet so in sql server you see cx packet weights whenever the weight isn't evenly balanced across all four threads so i had person five that's been pretty good actually so all of those it's unevenly balanced as this data flows up through the execution plan all of the work is still divided by core so let's come back up here and look at who found the the work so when i look up at the number of rows that were found only thread 4 found anything now all of those rows all 186 000 rows are still confined to thread 4 as we move up so if i look at this hash match and then i look at the number of rows there's only one one thread doing any of this work then if i look at the sort you would think that the sort work would be evenly divided across the workers but it ain't if i click in on here and i go look at that all of the work was done by thread four and then also the sorts memory was evenly divided across all four threads so a bunch of memory in this query was simply wasted three of those threads didn't need any ram but thread 4 did so if i hover my mouse over here and go look in we end up spilling to disk thread 4 ended up having to write a whole bunch of pages to disk because we just didn't have enough ram now same thing up here as we continue to get up this nested loops join this index seek all this work is only being done by one thread so at the end of the day this other thread is just sitting there the other three threads are sitting around idle if i go look in i'm gonna click on the select way over there on the left over on the right i'm gonna go into weight stats and i'm gonna go look to see what were my top weights for this query and in this query we had 50 seconds worth of cx packet weights because those other threads are just sitting around not doing anything waiting for thread four to get up off his i was going to say his lazy rear but he's really the only hard working thread inside this whole query so this is an interesting fun side note change the light balance on this just so that y'all can actually whoops nope that's not it so there we go so um contact administrator unfortunately no it's just basically luck of the draw whichever thread gets the pages that all have the uh sorting to be done ge says if all four threads scan their rows why is only one thread generating all the cpu time the the work of reading pages is super simple you can read millions of pages very quickly just do a count star and you'll see how many pages you can read very quickly but sorting them sucks rocks sorting is very cpu intensive and that's why only one thread is really generating all the cpu work so that's why when i talk in my mastering server tuning class why i say that whenever you see cx packet weights your first step is just to set same default parameters for cost threshold and for macstop but that really at the end of the day doesn't fix anything you have to go in and look at your queries to see which ones are reading the most data and then tune them so that they read less the whole reason that we're in this situation is just because right now we have to scan this whole entire table because we don't know where those rows are going to be found at if we knew or like if we had an indexed copy of it then sql server would make different execution plan decisions so when you see cx packet don't worry as much about tuning parallelism itself worry much more about just finding queries with a lot of reads and go tune them i also i find this uh really amusing because you and i know when i i wrote out the steps of where are we going to find all the posts that were created in that month you and i can kind of figure that out by looking at the the rows and their creation dates like the ids and their creation dates but sql server has no way of knowing that id and creation date are kind of tied in lockstep after all there's nothing to stop you from going in and updating a post creation date to something else and if you did we still have to find it in its earlier or later id just because you know that two columns are tied together kind of in lock step doesn't mean that sql server can guarantee that so now after all that knowing what we know so far if i'm going to do a parallel query i can't do it based on creation date because if it's on creation date all the work is going to get nailed down to one thread i could fix that with an index i could create an index that has creation date but then the query probably won't need to go parallel either because there isn't going to be that much work to do so what i need to find instead in my demo query is i need to find something other than creation date that will roughly evenly distribute rows across time so what i'm going to say instead is tags at stack overflow questions can have tags so for example you can tag something c sharp sql server html aws aurora postgres so what i'm going to do and of course those tags would be roughly evenly distributed across time they wouldn't be if we picked a modern tag like aws aurora something that has only popped up in the last few years but as long as i pick a tag that should be roughly distributed across time like sql server it's gonna work so i'm gonna have to change my demo query again i'm going to change my demo query to have where tags like sql server or tags equal sql server so that i can see the most popular sql server tagged questions over time now i know when i do that that i'm going to get questions from people about you're not catching all the questions tagged sql server and at which point i'm going to refer them to this twitch screen so that they can go watch this whole entire thing and they can see how hard work it is in order to write blog posts um a quick shout out to this week's sponsor so this week's sponsor is brought uh quest software where they put together an ebook with query tuning tips from me panel janus you can go download that totally free over at brentozar.com go slash optimization uh narrow pack says off topic question i saw your schema were you looking up my pants uh do you refer so that the problem oh hey panel good to see you um the the problem with uh normalizing that like joining out to other tables to get things like score is when you're at stack overflow scale when you're dealing with 10 20 100 000 queries per second you can't really join out to other tables and do aggregates for uh scores comment count etc it would just take too long and they don't need to be transactionally accurate these aren't stock trades so you don't have to worry about having the precise decimal accuracy on the number of likes that is that a post has had for example okay so now let's go back in let's change the query so that it uses uh tags instead so let's see oh cgss where did the twitch query bucks go well i dropped out of the twitch affiliate program because in order to be in the twitch affiliate program you're not allowed to live stream on both youtube and facebook and twitch all at the same time twitch wants exclusivity they want to be the only ones hosting your stream and i'm like i would rather reach more people because about a third of my audience is on youtube and about two-thirds are on twitch i don't want to lose the youtube people facebook there's like two or three people who watch at any given time it's not like a huge number especially given my times so so yeah so i had to drop out of that in the twitch partner program i was in line to be a twitch partner as well and they're like they're kept going they're like are you sure wouldn't you like to just stream on twitch and i'm like not if i'm not allowed to stream anywhere else i'm not you know unless you start paying me like doctor disrespect did i say that out loud i think i did say that out loud um kurt says my uh copy of stack overflow doesn't have a post type of question it has a whole lot more question types okay so i'm not going to go off topic here that's kind of going a little off topic there all right so let's go change this and instead of doing an inner join on post types i'm going to take that out and not do the type on question actually i still could i still can do that that's okay and instead of creation date i'm going to say where p tags equals i'm going to say like uh sql server all right and let's see here let's i'm going to take a view at the estimated execution plan on it first just to make sure that it goes parallel and it does okay good um just as a side note i'm gonna go into properties and you saw that it kind of took sql server a few seconds there just to compile the plan to compile a plan it took 20 milliseconds worth of cpu and it took 3.9 seconds worth of time isn't that odd it took 3.9 seconds out of for time but only 20 milliseconds worth for cpu well it has to deal with the fact that we just queried on a new column that we haven't played around with okay very cool uh crazy tech um queried on a column that we hadn't queried on before in this database so sql server did an auto update stats sql server did an auto create stats just did it by sampling the entire table and then used those stats in order to generate the execution plan so let's try it again let's go through and execute the query again now remember last time it took 50 some seconds and essentially the query went single threaded because only one thread found to work things may be a little different this time so the work's going to be spread across multiple threads but also there's going to be a hell of a lot more cpu time because this operation right here is extremely cpu intensive whenever you want to crack open a string and examine its contents looking for pieces of something cpu just absolutely goes through the roof so if i to show you what's going on there is if i say select top 100 star from dbo posts if i go through and say select top 100 star howdy ernesto and then go scroll across to the tags over here these are the tags any question a stack overflow can have up to five tags so you can tag a question with sql server indexing performance tuning triggers you know you can tag with up to five different tags and those of you who are database professionals are probably like itching and screaming with hives because that that's so frustrating to look at and have to query and you're probably thinking would stack overflow really store their data this way yes that's how it started got started when sql so when stack overflow just got started late now these days they use a different way of doing it in production but another common question i get all the time is couldn't i use full text search for this well things like this will absolutely make stack or full text search blow chunks same thing with c sharp same thing with dot net 3.5 full text is just going to blow chunks whenever it tries to hit something like that so instead what we have to do right now is we have to do where tags like sql server we have to use that leading percent sign so good news so now at least we have our query results so our query result says how do i do an update from a select oh i like it that's pretty cool how do i add a column with a default value very cool these are good questions how can i remove duplicate rows oh wow these are very nice so now if i go look at messages because after all my whole thing here the whole reason we've been working for the last 40 minutes is that i wanted to build a query on 2019 compat level that would go parallel across multiple threads and actually use more cpu time than clock time let's see what we've got and well it's not great but it's better than nothing i was really hoping to see something that would just hammer cpu across multiple threads and here i don't have that here the query took 53 seconds and it took 72 seconds worth of cpu but you know what in terms of writing a blog post this is good enough for me i wanted to at least just show the audience when you're measuring time i want you to measure more than just time on the clock i also want you to measure how much cpu time you used because the high just because you have a query running for 50 seconds doesn't mean that your cpus are idle the whole time and you could actually hammer multiples of them the whole time okay good let me grab some screenshots here real quick so let's put this in move you down just a little so that i can get a good screenshot here copy you out and then look at the execution plan see if there's anything that i want to tell the now i'll just show that grab the execution plan there i also want to show them the time that's in the execution plan so if i right click in here and go over into query stats that doesn't even match that's 134 135 seconds this over here said seconds now maybe let me scroll down and see if there's something else is there maybe is like a hidden gem somewhere is it do i have to like hit up up down down left right left right baba start that's quite good that's true if you don't like what one of them says you can use the other one and say that your query is ready for production all right so let's get the picture of that so that we can put it in the blog post and then my friends and i use that term loosely over at microsoft can look at that and they can seal the kind of crap that i have to deal with as a presenter because this is ridiculous about this quality of this piece of junk all right let's come over here there we go now let's switch over here it's not sms the sql server itself it is so frustrating to me the quality that we're dealing with in sql server these days the quality has just utterly gone downhill it's just been this complete dive bomb of patch quality engine quality bugs out the wazoo so this week i just like i'm just like okay let's do it microsoft i'm tired of you just dumping this crap out on production and calling it good so i'm going to start getting ugly i'm just going to start as every time i find these things i'm just going to go blog about them and i'm going to go i'm not even going to file a bug request i'm not even calling microsoft support you deal with it just like you shove this code out the door and you expect us to deal with it microsoft it's on you good luck here you go i'm pulling your pants down in public knock yourself out this is the crap that we have to deal with on a day-to-day basis the sql server professionals we have to recover from this stuff when people are trying to figure out why is this query not measuring the metrics that are coming up with the metrics that we would expect it to and the metrics aren't even close daniel says kind of scary as i usually use the stats parser you know what's messed up i don't know which one's right i don't know whether statistics time or the actual execution plan the xml is actually correct i have no idea for me to figure it out what i would have to do is i would have to run the query and watch the number of cores and how busy they are and i don't have time for that because i would rather be live streaming and hanging out with y'all anyway yes sad but true all right so i think that that's amusing enough uh and that i am going to keep it inside there because this is educational for the users to go here's the metrics that i want you to look at but unfortunately i have no idea which metrics are right or wrong and of course microsoft is going to see that they're then going to go fix it cumulative several cumulative updates from now they're not going to post anything about how those numbers are wrong it's not going to be in prior cumulative update updates um alex says i saw an opera a predicate in an actual plan i done a prospect type uh yeah i can't uh that's kind of off topic there all right so i'm happy with that first one that gives me enough i may take that actual execution plan i'm going to take that actual execution plan and i'm going to put it on paste the plan just because i know somebody's going to want to see that or they're going to want to use a database microsoft is going to blame operator error when it's clearly on here on a webcast anyway that's how my life rolls so i will right click in here and say show me the xml i'll copy paste this out i will go over to my friend paste the plan paste the plan dot com and then paste and it's off by 2x it's not like it's even close so it's like my answers were in college uh so i'm going to just dump that link in here so actual plan for first demo paste that in that was hilarious to me microsoft buying tick tock the number of years that they have tried to put something together in the social space i'm just gonna just vaguely microsoft passport for centralized logins consumers just gonna use this one login everything's going to be cool hotmail oh it's going to be dominant everybody's going to use it god stuff is just absolute junk i get more problems with spam complaints from hotmail users who say they can't get our newsletters or whatever because of spam from you could tick talk with my sql server rants and i'll try to make them danceable zune mixer they just gave up on they put tens or hundreds of millions at all exactly hundreds of millions of dollars in the mixer paying out people like ninja and all that unreal uh toby no no short answer no because that is the point with the statistics time and the execution plans they both measure multi-core usage they're just measuring them differently which is against the rules okay so i'm happy with that now that's a good first demo query now and uh let's come back up to our skype yes another one absolutely uh so then the next one that i end up measuring instead and i'm actually going to talk about that in the blog post that time is so unreliable when i'm dealing with queries time is so unreliable and so changing between dev and production versions that i tend to not measure by that and what i usually tend to do instead is reads so uh in here i want to also have proof that reads alone are not that big of a deal the sql server is crazy fast at reading large numbers of rows so what i'm going to do is i'm going to go back over to my uh that's not where i wanted to go go back over to my stats results so here we did like and i really wish that they would put commas in there but evidently that's too hard for microsoft a multi-billion dollar company to put commas inside there um it's doing 11 million logical reads so what i'm going to do is instead of putting any i'm going to take the exact same query and i'm going to do 11 million logical reads but i'm not going to do anything with the data so right now it's taking depending on which number you look at 72 to 130 seconds worth of cpu time the cpu time is all crunched on this order and shredding of the strings in where it will become yeah it'll become a chameleon right so let's go take this exact same query and i'm going to do it with reads just doing no select top 100 instead i'm just going to do count star and i'm not going to do any filtering or ordering i'm going to say just go get everything and go now remember before this is taking like 50 60 seconds of clock time in order to run watch what happens when i rip the filters out so i'm not shredding strings and i rip the order by out three two one go eight seven six five four three two one i was hoping oh oh no no no you know the reason why it's oh it's taking so long because it's got to do the joins because join in or do the joins it's going to have to sort because i don't have these sorted by post type id and by users let's try this one more time ten nine eight seven six five four three two one you're right you're probably right that's probably what it is where did my laughter button go i had a laughter button oh there it is okay what is going on here okay so i have a pretty good idea i have a pretty good idea that it's a 2019 regression so after this finishes we're going to switch over from 2019 compat level and switch over to 2017 compat level and see how long it takes okay so 54 seconds 54 seconds and then that's absolutely true so 54 seconds oh use sql server there are no words um okay so what's happening here so if i go in and no it's not doing that it's not doing that so what it's doing here is it's going parallel says it's going parallel across multiple cores if i hover my mouse over here it's doing batch mode execution so if i get in on here batch mode execution up there so it says actual execution mode is batch mode and i'm going to go across here and look at the other operators in the plan this is also in batch mode as well if i come all the way back over here to parallelism finally in here it goes uh parallel or goes back to row mode at some point looks like it goes into row mode here when it does the parallelism thing it didn't actually really go parallel the amount of cpu time here is lower than clock time which means that really one thread did all of the work but of course given the comical thing that we saw just a second ago in query time stats i want to double check that and look at that look at that ridiculous thing so cpu time here being 88 seconds whereas it showed like 50 seconds in on uh stats time on so those two numbers don't even come close to agreeing now just as a reminder over here is 56 50 50 seconds now let's pop it back over into 2017 compat mode let's go into 2017 compat mode and say okay then let's try the exact same query again now as a reminder in 2019 compat mode it took 50 seconds let's try 2017 compat mode and give it a shot you would think you would think oh i should look and see too uh another way that you can kind of tell the parallelism is happening is if cpu is cranking out at 100 across here it's not uh we're not doing 100 cpu across all the cores uh toby plus plus if you saw if you saw the query running for 1.6 seconds you have a different concept of time than i did now look at how cpu times chugging so now it's because different phases of the query are doing different things the reading the pages the aggregating the data this though is bananas uh i am stunned that this is taken as long as it is and i don't know if this is a regression in uh 2019 i think i'm still on cu5 53 um so this thing jeez it should not be taken anywhere near this long secret says the routine grumpy brent today it seems like it's well you saw me yesterday too on the mastering parameter sniffing thing i lost my mind a couple of times over there so i'm really surprised that this would take as long as it does to count 40 million rows that's kind of a bummer and it is doing about that number of logical reads so let's do this instead of doing a count i'm going to do yeah neil you're right i got to stop running sql server on 8-track cassette tapes yeah that's a good point there really smart so drop table employees i'll let you do that instead what i'm going to do is i'm going to go say i'm going to go pull a an attribute that shouldn't be true i'm going to say where view count is a negative number constricting my thoughts so let's say select star from posts where view count is negative 1 million and try that so this should also read all of those pages and i just picked the view count number as a pure random number so this is starting to become a better gauge of uh how fast sql server can read data without aggregating it because with the count star i'm still aggregating that data so now i should look to a cpu to see how that cpu is doing cpu is still not at 100 percent oh is it going single threaded sql server 2019 you suck no it's going parallel my butt so it says it's going parallel uh let's go through and read well let's do a live execution plan this time number oh won't show number rows red while it's executing hey see at least that's what i want to see so here i've got cpus churn right around like to 100 gallon bunga i went through that earlier so i'm not going to go back over to that um jimbo yes i'm still on 2017 compound mode so this is what i want to see is i want to see cpu running at 100 while i'm running that query and the thing finished in 30 seconds which is much closer to realistic so here uh mon manny that's what i was that's what the blog posts about that we're watching so if you uh maybe pay attention instead of asking questions you'll have the answer you're looking for maybe not so now it was able to do that 11 million logical reads it did it in pull the thing off in 30 that's just a lie that's just flat out not true so it's saying that the query ran for 30 seconds and it did 45 seconds worth of cpu that is simply not true and you can see it i'll show you if that was true then we would only be maxing out one core plus a half really for the span of the 30 seconds that this runs and that's not the case if i click query include live execution live query stats turn that back off let's go throw task manager on and i want you to be able to see that task manager is essentially sitting idle here at the start of this then when i run it three two one go watch the cpu now in the beginning it is only using one core basically if you watch that top left he's really only doing stuff across one and then at some point in here it hits 100 across all four that's really weird because it should immediately divide these threads off across all of the cores uh howdy rodman um you're not my best friend from boca though i got uh an even better friend in boca st steve farina now look at how the cpu all of a sudden goes up across the board all of a sudden the cpu goes to 100 what the hell was it doing earlier and now cpu is just banging now cpu is just banging across all four cores now look at that and the query contact you're welcome to you can go ahead and do that that's why i use open source stuff so that you can go and reproduce these same tricks so you see how that's going it went parallel for a brief time span across all four cores now if i go back over to messages that might actually be right because it went single threaded for so long it's just amazing to me that the same query again and again is having such dramatic different behavior i'm going to go execute it again and watch my task manager so one time it took 30 seconds this time we'll watch the cpu i like how you all just yell out random stuff i like how you just yell maybe there's a memory leak you should change your blinker fluid have you had enough bananas for breakfast maybe you should try a banana now you look low on potassium are you wearing galoshes y'all are like off by one error so yes yeah you put the thing in reverse and then start look how good so it goes to 100 all of a sudden out of nowhere so it takes forever takes its leisurely time and then goes to 100 this is one of those cases where if i was paul white and i knew how to use a debugger i would probably lose my entire afternoon trying to understand why nope x shivaleth i'm getting physical reads every time because the the table is larger than in memory so um so yeah and look and so now i'm back up to 54 seconds again that's just utterly bananas uh things i am not going to investigate today we are going to close that one and move on so it has been a little while so i am going to stop there for that i've had about enough that freaking demo um so oh that's the wrong thing hold on let me switch to something else that's i have one chat window that i use for slack um tigress you're welcome to do that i'm not gonna do that uh to try donuts yeah um so it was jambo says it was 24 seconds at 100 cpu shouldn't it be way higher than 54 seconds yes those stats times numbers are clearly wrong and you know this is always a tough thing with doing live demos and i can show you that clearly the stuff inside the execution plan is wrong stats io is wrong but what am i going to do with that am i going to open up a bug report no i don't this is my own free time where i'm trying to just like write a blog post and show y'all stuff i don't mean to find errors like that inside the product there's only so far that i will go in terms of putting in bug reports on stuff like that now i am going to put in a bug report on the stats time io and the time in the execution plans not matching to do that the first step in submitting a bug report to microsoft is that i need to go check to see whether or not a bug report has already been filed so to do that i am going to put in a wait for jesus um so to do that i am going to uh do use google search because the search on feedback.azure.com kind of sucks so the first step in doing any kind of blog or any kind of bug report is that i need to be able to to describe it in a way that i think other people are going to describe it so the way that i would describe this is that statistics time and then the time in the query don't seem to match so if i go over to query time stats so and i'm only going to do elapsed time or i'm only going to do cpu time i'm not going to do elapsed time although does the lapse time elapse time doesn't even match um that's messed up so let's go see so the terms that i'm going to look for are elapsed time so over here under query time stats i am going to pull that elapsed time i'm going to look for the words elapsed time in the bug reports so in the bug reports we're going to go to feedback.azure.com oops that's not it feedback.azure.com then i'm going to do a search for cpu time and elapsed time because those are the two words sql server 2005 causes a hundred percent cpu by lazy writer well that's really relevant so that's the only bug okay that's the only book that pops up inside microsoft search i always want to be careful though because sometimes their search kind of blows so i'm going to say cpu time elapsed time site feedback.azure.com and so you notice that we get something different in here but it's just because mike or google's a little bit smarter about breaking up those words um last run say zero fixed reads um so i'm not going to go into that i appreciate that you all want to ask other questions and that's cool but i'm not going to go there you're welcome to go in and look at other things so let's see here if i go in just to see consider the db pricing tiers nope doesn't look like any of those are relevant all right so i need to suggest an id or suggest an answer brentobranozar.com it's going to ask me for my password let me go get my little hi bala thank you and welcome to the club i'm going to switch screens for a second here just while i go grab my password for live live and grab that password copy paste that in because i use a password manager because i'm a wonderful human being and now let's come back over here to this so now i am signed in thanks to the magic of that uh actual plans cpu time and elapsed thank you greg elapsed time do not match statistics time on set there we go now we'll scroll down just to see if sometimes the the autocomplete or the guesses actually match the something that was already filed and i'm scrolling down and nothing in here usually seem or nothing in here seems like it matches no it's really close incorrect number of rows returned that's kind of funny that's there's a bug that i was um bug that went away when i was talking about live query stats uh to do they don't put stuff in yellow that's uh google that's google's uh search there uh or my control f one of the two i would hope maybe not maybe they do maybe you're right no you're you're right they do that's really messed up all right so let's post a new idea category is bugs uh when i do a long running query these uh time from set statistics time on does not match the actual execution plan and i might put in the zoom in a little here so you all can see it the cpu and elapsed time um for example in this live stream and let's go get it from uh the live stream twitter.com they'll love that microsoft loves it when you live stream bugs uh copy that link let's see here copy go over here nope don't put it in copy and close and close and then come back over here for example in this live stream um i ran this query and we'll go do one just to go grab it so let's see here select count star from dbo posts and then turn the actual plan on and i also need set stats on set statistics time i o on i'll do a sh i was going to say i'll do a short one but yeah no screw it i actually should do a long one um so i ran this query from the stack overflow database from one of the large stack overflow databases select star from and that one's in that one's in feedback.azure.com or select count star from dbo posts from dbo posts the actual plan the statistics io or time time output so we'll go get the statistics time output out of there when that finishes so and this is how you post uh bug reports over at microsoft if it's an urgent bugger board if it's something that's stopping you from solving a production problem then you can pay 500 bucks i think it's 500 bucks it's been years since i've actually done it but you can call microsoft's customer support and open a case for like 500 bucks and if it turns out to be a bug then they will refund your money i didn't say that they would fix the bug i just said that they would refund your money let's go get the execution plan i'm going to show the execution plans xml copy that out and then paste that over at paste the plan paste a new plan paste submit and then there's my new link and then so the actual plan is here and there we go oh i can attach the file too can't i oh that's even better so let's attach the actual plan just so that they can't um so desktop this will surely not be marked as closed by design and then i'll go attach that back up attach a file i don't know that i put that on the desktop i'm really bad with putting files okay there we go perfect sql plan post idea please work please work please work this is how they make sure their bug count goes down they just don't let you file box i have a guess i just don't know that but i have a guess that it's probably that file so let's remove the file and then say post time post idea your idea is being processed and will appear shortly so here uh the new bug report i'll put that out in chat so that you all can then go see that and there's that uh file name was too long it could be a different post under a different name that's actually really good i could live with a lot less of that all right so that is probably a good time for us to stop and do a bio break so this is really not where i wanted to go with this my query wasn't you know what my topic was remember my turn my topic was how do i know if my query is good enough for production is like ready for production i think what we've learned is that we've learned some things about sql server and its quality of being ready for production but i digress alex says as a data person for microsoft business i don't always have to pay for my sport cases i try to insist that they at least take some action like updating support docs i agree i would love for them to um to at least say in ceus and knowledgebase articles things like that that yes this is a known bug and the thing is broken or whatever so we'll stop here and take a bio break we'll take a five minute bio break and when we come back we'll shift gears into open questions so over on twitch you can ask any questions that you want i won't do questions from youtube only because it's easier in twitch i can just accept questions as and put them into my queue so in twitch you can ask questions ask whatever you want and then i'll go put them into a queue and then i'll go through and just answer them in order so we'll be back in five minutes go take a bio break and off we go where the hell is the pause button there it is um uh yeah uh blue uh let's go uh this all right so short out shout out to uh aid dba and to oleg for being the last couple of uh folks to join in in the club if you like what you're seeing you're gonna lie on earth you would like what you're seeing um it's funny when i when i do this stuff i don't do this to get rich or be famous or anything like that i literally just do it because i would be here working regardless and it's more fun working with y'all than it is working by myself usually i on weekends between like 6 a.m and 8 a.m i will go and write blog posts or set scripts up write new training material whatever uh just before erica wakes up erica usually wakes up around 9 9 30 somewhere inside there so the weekend mornings are like my time i get to do whatever i want to do because you're already rich and famous yeah exactly um and yeah it's before my my coffee shop wakes up and the coffee shop wakes up at eight i've actually had a half of a lox and bagel this morning we had we bought two of them yesterday and we had um one half left over so i had that this morning for breakfast i'm still trying to make a game time decision about whether or not i go downstairs uh helmet is not back up yet i got a phone call uh this week and i finally finished the pizza thank god chris uh i got a phone call this week from musicar the people who have helmet i'll show you a helmet so helmet is uh over and in musa car in portland so let's see here music car portland flickr uh and they have let's see here so you remember flickr the old photo site you know kind of like myspace for photos um but this is where they post photos of the builds that are ongoing and so when when helmet showed up so there he is they pulled helmet into the shop and they're working on him they like put all kinds of protective stuff all over the car so that when they're uh putting things in and disassembling it that they don't break things and screw things up um and then the let's see come down past the volkswagen so that was the truck that he came in on uh and then this was the stereo gear and the radar and laser gear that they were supposed to put into him and then they decided they sent me a phone call or phone called me they were like hey email me first hey can you uh get on a phone call real quick and they're like we're getting ready to do a brand new series of audio gear for the porsche 911 would you be interested in being like the prototype person where it's our best and newest stereo equipment that's going into there and i was like yes i would yes please sign me up um so that's where he is i said it was totally okay if they spent more time there hi uh nunu bunny or nunu booney nunu booney um so i'm like yeah take your time with him take all the time you want uh because also my wheels are uh are still on the way from rotiform so i bought brand new wheels for him and this almost kind of kills me he's going to be in storage in a few months five to six months five to six months our lease is up the end of this year but we're gonna do another month to month here in san diego until i think we fly out to iceland at the beginning of march i don't remember the exact dates but it is coming really fast uh really really fast and erica and i were just talking last night about like where should we move after this as we were sitting out having my ties in august sitting out in t-shirts and shorts and we're like so where do we live after this where do we want to live next because we've rented for like 15 years and we enjoy renting because it lets us jump around and see different parts of the us and uh she's decided she's she wants to stay here too she wants to stay in san diego we had kicked around the idea of moving to either la or malibu or colorado to like a ski town in colorado bobby table so it's interesting the question isn't whether you can fly the question is can you get in because there are flights to iceland for example but the bigger problem is are they going to let u.s citizens in right now they do if you pass a quarantine test you have to to pass a test when you get to the airport they they give you a corona virus test right then and there the results take three to five hours did you hear that richie three to five hours their tests are done in three to five hours so then you take that and then you get another test and i forget if it's five days six days or seven days what it is you get another test there there afterwards just to double check that you're not uh not contagious or whatever so but what i worry is because of course america is going to have all kinds of outbreaks between now and when we get over there i worry that they're going to lock the country down before it's time for us to get back over there that's the part that i'm panicked about uh but oh so helmets wheels i'm so excited uh the wheels i ordered i don't know why i'm showing you all this i don't know if y'all even really care but of course because i'm a car person i care a lot uh rotiform vces so these are the wheels that helmet is getting uh come over here to rotiforms camaro says of course camaro says yes um so he's getting a set of black rotiform vces those have wheels on it or i have a writing on it from anti-social social club mine don't i didn't have i don't want any writing any anything inside there not even the name rotiform i just wanted pure flat black because everything on him is flat black so i was all excited because it's like an oz racing wheel kind of thing and i ordered these two months ago because you have to do them all custom they're made just for your car i ordered these two months ago and then ken block comes out with a partnership this week ken block wrote a form he is doing his own wheels for rotiform and they look amazing they look really good and at first i was like oh man i really i would have loved some of his new wheels that he's doing with rotiform and then i look over in the corner at the white ones and i'm like wait a minute what's that what's that wheel right there and then i zoom in on it and i'm gonna mute this just so that i don't get in trouble for the copyright police and i'm gonna go into one of his wheels and i'm like wait a minute that's my wheel that's the oh that's the rotiform vce but they let him do his own version so this is by rotiform rotiform motorsports that's the same wheel i was getting except mine is just flat otherwise it's exactly the same and mine's black but i was like i could have had ken blocks wheels so now i got to go call them and see hey if mine aren't manufactured yet but these aren't actually out until fall so i'm kind of like but at least i i have something that looks kind of similar so i'm kind of happy with that we'll see how it goes uh matt valiant says i'm still jelly he says i'm still rolling around in a 14 year old scion xa so the the thing is never was my whole feeling on cars you should only pay cash for cars cars are things that depreciate cars the value goes down you only want to buy things you only want to to ever pay credit for things that might appreciate that might go up in value houses for example i'm okay with you know doing a mortgage for a house that makes perfect sense your house will eventually go up in time of course you're also building ownership in it it costs an insane amount of money but you shouldn't pay credit for a car if you can avoid it you're doing exactly the right thing rolling around in a 14 year old scion i rolled around in old cars for the longest time too and the the only reason that we bought the porsche was we hit it at a point in our lives where we could pay cash for it and i was like all right let's do it kurt says what about zero percent financing if you don't have the cash though you shouldn't be buying it you should you buy have a car that isn't going to have you in debt in the future especially in these trying times like you have no idea what's going on with the economy who's going to get laid off for things uh whose business is going to get a get imploded um richie and i are extremely lucky touch wood that are we're still doing really well but like we were paranoid about buying or spending anything always pink says i'm still rolling around in a 99 toyota good that is exactly what you should do that is fantastic um and pudota says if you have enough money to pay for a lease you can save them a bit to buy a car and the problem is people who least generally want something that is uh zero maintenance that they know like i wanna have my wife in a car that i know is gonna be under warranty that it's going to be 24 7 working and that if something does blow it up in it she has a factory loaner that kind of thing so i'm not as worried about that you say that camaro 322 the new ones don't like the new ones unless you get something limited edition like a speedster or a gt3 uh then then the price actually goes up but i know mine from the moment that i drove it off the lot like if i had to go sell it today i'd lose 20 30 40 grand but you got to be okay with that if you're going to go buy a car like that because it's a a a treating yourself lifestyle kind of thing like i waited my whole life to be able to buy a 911 i've wanted one since i was little i'm still trying to find my childhood pictures i used to make model cars and i'm hoping that i have a picture of one of the models that i built of used to build porsche 911 convertibles corvettes all that kind of thing curious drive says what do you think of leasing cars if i am mass valley uh if i was in a family if i had a family and kids and all that i would 100 percent lease uh if i wanted a bulletproof car that would not break that wouldn't uh did if erica was on a rainy road in the middle of the night that she could drive safely and not have to worry about it so i would be okay with that with leasing on that the problem that i see is so many people lease luxury cars that they wouldn't ordinarily be able to afford and if you're watching this and you're one of those people that's okay it's just that you're kind of flushing your money down the drain and it though a friend of mine used to do that and i the best advice i could ever give to him as i said look with the money that we make you can only do one thing two things you can either look rich or you can be rich but you can't do both with the money that we make you can't both be rich and look rich sure jeff bezos can both be rich and look rich you know the guy can have yachts uh larry ellison you know those people can both look rich and be rich but for the rest of us it's you gotta make a choice between one of the two so uh it's made by tonka that's nice um says isn't leasing almost always more expensive than paying cash depends on the car that you're buying so like with my porsche 911 you're talking like 150 000 bucks out the door to go buy it in cash you could lease it for like 1500 a month so if your goal was to just have it for two years and then walk away from it and go get something else you'd be way cheaper off leashing um marathon i didn't actually i dropped out of the affiliate program because if you want to live stream in both youtube and facebook and twitch you're not allowed to be an affiliate or a partner so i dropped out of that uh and there was another one that was uh interesting in there let me scroll back oh darren davis said if you wanted a porsche for that long it must have been tough to pick which one to buy no i've always wanted a 911 targa that was exact i wanted a 911 targa that was the only model that i've ever wanted just all about it um and then there was another uh coming back up there was another one that was interesting in it now i've lost it it's gone up past that ah well okay uh so no g jesus you're welcome to ask questions but i'm not going to do any more coding today i'm done coding for the day today i get to go goof off i have a client on monday i have a client on monday and then i teach mastering query tuning i don't know how many of y'all are in my mastering query tuning class tuesday wednesday thursday that's actually my favorite class out of all the ones that i teach one of the students this week in mastering parameter sniffing said it's also one of the most hi mr stefan welcome to the club um uh said it is uh also the hardest class and i think that's probably true but i i kind of like it it's a lot of fun uh marathon yes i do i absolutely do they were in sweden i think uh dro or flew out there i remember that absolutely um and then sid says 911 targa you must be a porsche fanatic i'm really just a car fanatic i love all cars just all kinds of cars ah that's funny oh very cool it's a small world it's neat to see the number of people who i've been able to meet all around the world just being from the traveling things not that we can do it anymore but you know it was fun now while it lasted and i met those of y'all who read my personal blog over at ozar dot me you'll you may have read the latest post this month i'm going to see and try if i can go the rest of my career without traveling i'm going to see if i can go the rest of the tour or rest of my career and just go ahead and retire without traveling again for either client work or for a conference so make it rain again yeah no more rain because i dropped out of the affiliate the twitch affiliate and partner programs just because i wanted to be able to live stream across both youtube and facebook if you're going to be an affiliate or partner at twitch you make money from things like subscriptions and ads and all that to give you a rough idea i was making about 250 bucks a month on twitch but the catch is you can't live stream across multiple platforms and because i'm really doing this to reach as many more of y'all as i can then it made more sense for me to give up the 250 bucks a month and then focus on reaching more of you on facebook youtube all that kind of thing ana says do you have any recently starting class yes tuesday tuesday i start mastering query tuning so if you go to brentozar.com and click training up at the top the next one's mastering query tuning starts this tuesday ge says he likes the dbas with the cars that go boom that's where he's in that's why helmet is in portland too is getting a new stereo put in it that i'm very excited about um marathon has a good question why would i go to the training when i got the stream because the streams are so short and so we go into way more detail about sql server internals inside the classes that's kind of my my thing with the streaming is that just i'd rather give people the short easy stuff but it's not necessarily in a an organized table of contents kind of way it just kind of scatters around uh whatever i am too as well i will take five minute and ten minute videos all day long like the when i'm learning you know it's really our i can't watch recorded videos it just does nothing for me it's bananas yeah and the twitch streamer thing in terms of money a lot of it is outside sponsorship too you know you can make decent money at twitch but you can really make good money when you do ads and streaming speaking of which let's give a quick shout out to this week's sponsor this week's sponsor is quest software quest has given out a totally free ebook on query optimization so you can go get that over at brentozar.com go slash optimization where you get tips from me penal and janus i haven't actually read it still i hope that it's good and i hope they didn't screw things up but i really should read that today and i'm kind of uh going to i should probably do that today marathon that's funny and i haven't been out there i remember talking to a couple of folks from over there a year or two ago about coming out we couldn't make the the time work jesus says i'm never going to pay for learning i don't a lot of you can get really far in your career without paying for training but then you get to a certain point where if you want to be a serious like master level on something then you're going to have to invest by the time that you get to that point and i say that too because i had to pay when by the time that i got to that point in my career went out to microsoft for three weeks the whole nine yards and you know you you can learn a lot without ever uh paying somebody else or trying to learn on your own some of it is also time it depends on how much time you have uh in order to learn um savash tavishnab says let's switch over here to this one dba that's awesome that's great um uh savashnav says any suggestions on how to learn how to use what we learned in fundamentals classes when my day job doesn't include that deep kind of work i just write select statements and make reports so um a long time ago in a galaxy far far away i got my start as a developer and gradually segued into database administration as well the thing that i'll tell you is in your office stand next to the most expensive thing stand next to the most expensive thing and wait for that to break so if you do that and you're helpful and volunteer and you offer to take responsibility and you offer to be on the hook when the thing breaks like i'm gonna try and go fix it if any if anyone else wants but i'm gonna take all the blame if i do something wrong i'll totally take the blame but let me go see i'm gonna take a risk personally is that cool with you all bosses will love you for that because then often they don't have to pay to hire someone else from the outside or someone good they can settle for someone incompetent like yourself but that's how you learn and grow your career go stand next to the thing that's the most expensive wait for it to break and then that's how you gain experience that's how your boss starts to trust you more and then give you more permanent responsibilities like then the next time that it breaks even if you're not standing around it your boss will come to you and say oh uh so and so it feels first latex so-and-so was really responsible and took ownership of this let's bring them back in and also tell you that through my career that's also made executives come to me and say when i was a database administrator they would say brent i know you're you're the database administrator and you don't really do vmware or you don't really do fiber optics but we have a problem that's it's worth a lot of money to us internally you know we're getting ready to spend a whole lot we have this there's smoke coming out of it whatever you seem to be really good at googling and fixing things the in a way that we can trust you with high value stuff can you go tackle this problem and so then it just automatically snowballs in in your career if you if you focus on writing queries and writing crystal reports there's never going to be a lot of money in that unless you go to teach others so if you passionately love writing queries and crystal reports start teaching it to other people and then you too can drive a portion 911. uh all right so uh cr dodia says when is penal starting the live stream uh he uses uh he does he has a bunch of 60 second videos he's been doing recently he's done really short videos and i love that because like all of us are trying different experiments to see how to make things uh work darren davis says because of microsoft's database costs my company is pushing people to open source databases do you see that as well i see not only that but also cloud services that people will go why would i pay money for uh a full-time service somewhere when instead i can just pay amazon or azure for every time i want to use it i just go and attack that thing like serverless dynamodb for example i am seeing a lot of that and where i'm seeing it is people are deciding to use the cheapest thing that accomplishes their goal rather than saying we have this expensive data fridge over in the corner we got to use this all the time and nothing else so i love it because it lets sql server just focus on the things that are relational data which it's really good at uh and lets other kinds of data go to other places like key value storage and cache and geo stuff should never be in sql server to begin with malik says what open database should we learn other than sql the one your company has you know whatever one your company has so that you can put it on your resume as you're doing it that you have production experience if your company isn't using any open source databases i would start with postgres i would start with postgres because it's easy to find all over the place azure has it amazon has it google has it you can download apps that have it and there's a decent amount of training stuff out there because it's super mature if you're but either that or my sequel you're probably pretty well off your skills in sql server will translate well over to those things nuna bunny said my friend told me i like that that's pretty good i like that my friend told me that adding an identity column to a table variable which is joined with many other tables can help his query go fast any truth to that not really it's possible that your friend had two different query plans one with the the identity and one without and for some other reason that helped their joins but what i would just start is i would try a temp table first instead do a temp table and usually you'll see that that blows the table variable things out of the out of the uh water i don't learn nosql so i have no idea there ge says are there ways to improve performance for large xml fields bloating the database yes get them out of the database sql server is not a good place to store xml now yes microsoft will be like you should put everything in sql server put your files in here your pdfs your mp3 collection that you acquired legally wink wink um of course they're going to want to sell you to put everything in sql server because it costs seven thousand dollars a core as you run into performance problems they start making more and more money off of you so obviously they want to put it in back here the rest of us who make money when it's fast we go why don't you put that in a place that's more uh more relatable like a file server some kind of key value storage a database that really specializes in nosql edward the tech guy says uh hi everyone i already have one year experience in software development yay um i want to know what i'll do in the future what do i look like aunt aunt miss cleo miss cleo that's what her name was that's live miss cleo hilarious but i want to know what i'll do in the future since i used to learn programming language without knowing what to develop i don't know if i should become an embedded developer a security developer or learn data science can you give me any advice on the future of these fields so if you're a developer you're all you should always be okay as long as you listen to what the business wants it's much less about the language that you use it's much less about the framework that you use and it's much more about can you hear what the business wants and then build something that solves that pain so go to the business people that you work with like managers at your company sales people people who seem to have a lot of money budgetary money type things and just go hey can i talk with you for five minutes what is it that you want to be able to do that you can't today like what's the thing that your department needs but you don't have or you don't know how to solve that problem and maybe it's an application that you want that you can't afford to buy maybe it's a business problem that you have that you can't figure out how to solve but talk to the business people and figure out how to translate what they want into something that you can deliver and if you can do that it doesn't matter what programming language you're using doesn't matter whether you're a data science person or a front-end development person whatever you're a person who solves problems and the business always has money for that for more about that look at jonathan stark so jonathan stark jonathan stark has a series of stuff about double your freelancing rate and he really talks about how you can solve problems for businesses rather than trying to bill hourly as a developer always pink says yesterday i had to copy a database from 2016 to 2008. you had to what you had to what what what 2008 is not even supported anymore it's 20 20. feels like it's been 2020 for a really really long time but yeah no you know so that's like saying um how can i shoot myself in the foot with a double-barreled shotgun stop doing that that is a bad bad bad idea i said people reviewing data don't want to upgrade i don't care if they don't want to upgrade that's their problem go get a sql server express edition instance express edition is totally free they can then go connect to that and do whatever they want but that is the end of that now express edition does cap out at 10 gigs of data but now if we're talking about something more than 10 gigs of data per database moving the data around is going to suck really bad anyway so just be like yo go connect to you know the appropriate version quarantine the database yeah it would suck pretty bad um savashnav says what's a better option from a performance standpoint creating a view to suggest a report versus running the native sql from a report layer the uh there's really no difference from a view as opposed to the native t-sql views get a really bad rap from database administrators as if views themselves natively a problem they are not so here's how to kind of prove it if you search for viewsitebrandozar.com i have a blog post about this called your views aren't the problem your code is uh and in there i basically explain how i take the view code and i run it natively and i show you back and forth a and b that the views really aren't a problem at all but the code that you put inside the view is so i'm totally okay with people using views inside reporting tools it kind of limits the surface area of what i have to break so that's kind of cool scott says is it me or everything did everything go fuzzy that is you you need to put the mimosa down we're not uh it's a little early in the morning for you to be drinking also it's either that or your wife near his uh got her hands around her throat and she's like get away from twitch stop watching tv it's time for you to do some yard work caroline oh good to see you caroline uh caroline said i created a computed column to avoid making a data type conversion my query but the query plan still gives me an implicit conversion warning should i be concerned if uh so when you say query plan warning if you do a conversion on the way out with a select you'll get an implicit conversion warning that's fake that that doesn't actually hurt anything and i'll show it to you so if i go over here i'm going to say select star from dbo users where display name equals brent ozar i'm also going to create an index uh display almost did that on dbo users display name hi alexi how's it going are you the alexa alexian island man i i should probably say that slower isle of man just so that people don't get freaked out about what we're talking about there uh the if i go down to the users table and if i look at the contents of the display name data type so the display name data type is an nvercare unicode and varicare if i pass in a vercare so here's what unicode looks like unicode looks like putting an n in the front if i put the n in the front and i execute this you'll notice it runs nearly instantaneously sql server is able to do an index seek it die bombs directly into brent ozar and it reads the rows out hardly reads any rows we're out of here and you can tell that it hardly reads any rows because if i hover my mouse over this look at number of rows red up here we only read one row and we outputted one row that's as efficient as it gets oh wow you actually moved to london i saw your instagram stuff that you kept showing london pictures and i didn't know if you were just visiting i was like damn he visits a lot uh you still work for the same company or did you move work for somebody else that's awesome that you moved i'm gonna guess you worked for somebody else and now i want now i'm gonna have to go check in on you and go see what's going on um so now so that's got the n in it for unicode but if i take the n out and i execute it again query still goes fast i still get an index seek and if i hover my mouse over here what the what the what it says scalar operator hold on a second here i'm going to say option recompile just to make sure that i don't have a problem that's unrelated execute and then zoom in yi no okay that's fine so it says index seek and i still only read one row okay so far so good but if i turn around and say select cast last access date as no get out of here congratulations oh that's awesome dude as date as this is a date if i turn around and run that same theory but i put a cast inside there and i go look at the execution plan oh that one's actually as a cast oh that one's not so bad i thought i was going to get a warning inside there let's do this declare uh last access date date select last access date equals last access date and then try that what i'm looking for is to fire the false alarm about uh implicit conversions it's still not doing it that's what happens when i try to write a demo on the fly um let's try if ah that didn't work oh i can do it as an invaricare and varicare 100 that might do it yes yes um so in here i get an implicit conversion warning it says down here if i hover my mouse over it it says warning convert implicit may affect the cardinality estimate and i'm like what are you smoking crack that doesn't have anything to do with the cardinality estimate i'm not searching my last access date last access date has nothing to do with how many rows i'm going to find this is a known bug in sql server this in management studio and in sql server itself because sql server itself is throwing this error on the plan so if your plan is doing that is doing an implicit conversion and it's because of what's coming out you're fine now it may also happen if i say declare display name varicare 100 and then i say select star from users where display name equals at display name and i'll set this to brent ozar so here i'm passing in a different data type than what the execution plan has if you hover your mouse over the index seek you see an implicit conversion down here yeah see that's tough for me to see without seeing the actual execution plan but the first thing that i would just say is are you getting an index seek and if you're getting an index seek and it's only reading the number of rows necessary to output then you're totally okay okay so coming back over here on twitch swimwear asked when you retire will you also retire from streaming the thing that i love switch over here the thing that i love with streaming is that i can do it when i want to uh as opposed to like that right now i'm doing it on a scheduled basis but i'm only doing it on a scheduled basis until mid-september starting in mid-september i've got some weekend classes for folks who pay for the live class season passes so i'll stop doing streaming then and then i have the black friday rush we do our company does a big annual sale in november for black friday um and uh so after that i'm probably not gonna stream again because not ever but like not on a scheduled basis and i'll just pop in and do it whenever i want to i'm different than a lot of streamers like professional gaming twitch type streamers because i don't need to make a living from the streaming i just kind of do it for fun when i'm working so i'll still do it but it'll be randomly and when i'm connected i like to also travel a lot and so as soon as we hit the road like we're going to iceland for three months in 2021 uh touch wood as long as i can get into the country again um that's nothing about me personally that's the coronavirus situation it's not like i've been banned from iceland but i wouldn't stream during that time either kenny says how do you decide when to use select into versus insert into for temp tables that is beyond what i can explain quickly we do talk about that a little bit in mastering query tuning but it is kind of beyond what i can demo quickly uh shibboleth says are there any downsides or gotchas in changing a data type on a table yes it sucks it's going to be absolutely terrible it's going to require a lock on the table and rewrite the whole uh table itself so the way to do it is search for michael j swart j batching uh uh michael swart has a really good series on a hundred percent online deployments i'm gonna move this around just so that i can zoom into it so if you search for michael j swart online deployments he has a great series on how you go about doing that it's a lot of hard work requires a lot of prep and training but it's that's how you go about doing it jesus says do you have any tips to slow down queries without doing sp configure settings changes or dropping indexes i'm trying to build some performance tuning test cases yes write terrible queries so there you go next up let's see here coming back over here oh andy good to see you too as well i saw you earlier and i forgot to mention because i was the middle of something uh have fun out in the greenhouse and stay safe andy grows marijuana he's on great marijuana really good stuff he takes pride i probably shouldn't say that on a webcast because i think it's pretty illegal where he lives but i'm sure he won't get in trouble i'm kidding he does not grow weed i'm not saying he doesn't smoke weed but he definitely does not grow weed he pays for it just like you and i do it's so terrible it's going in such a bad direction uh let's see here what there was another one up there maroon asked like a three paragraph long question how would you go about adding foreign keys so for me foreign keys aren't really wonderful uh in terms of query performance they're good for limiting bad data going into the database but if you just search for query or for foreign keys on brainozar.com eric darling wrote a great series about why they don't really help well in terms of performance and why they suck so bad to implement so search for foreign keys on the blog and you'll get a big huge long series of posts on there bandar says i just replaced a udf with string split but my output is an int do you recommend casting as an int so if string split causes all kinds of other interesting problems i would want to see what the execution plan was and see what your objective was and how you make it go fast we actually talk about that kind of switch in the mastering query tuning class but i really would have to see the execution plan to do that your next thought is going to be brent how do i send you my execution plan you go to brentozar.com and click consulting up at the top of the screen it's one of those things that's kind of beyond the scope of what i can answer quickly all right well along with andy i am also going to go out although i am not going to go out and work on my weed my weed is doing just fine doesn't need any attention today instead i am just going to go down and go get some coffee down from my local coffee shop and then i'm going to go sit out on the balcony because i have a nice day off here here i don't have any weekend emergency clients this is the first weekend in three or four weekends straight that i haven't had an emergency client i'm just ecstatic and i'm also ecstatic because when i do emergency clients my wife actually gets all of the money our agreement is whenever we do whenever i work weekends that means i have to cancel the things that i'm doing with her on the weekend and i have to work with clients instead if my wife passed away heaven forbid i would work 24 7 because i absolutely love what i do um but so i don't wan otherwise since to make sure that i don't work on the weekends i uh give her all the money and so then she's okay if i actually work weekends no breakfast nachos today the nachos place doesn't open until like 9 00 a.m i think i think it's 9 a.m that the nachos place opens but i'll probably we may push that for later we were really bad yesterday we had a bunch of mai tais and fish and a chocolate lava cake at this hawaiian place down near us on the on the marina so curly bracket ai welcome to the club i'm so if you want to get alerted whenever i stream because i probably will be streaming monday and friday just i'm not going to know the times in advance it's going to depend on how my client gig works if you want to know when i'm streaming if you subscribe to me on twitch or youtube hit the subscribe button the follow button whatever and then you'll get an email whenever i go live streaming you and i always go live like five minutes in advance so that that way you can get the email and then stumble in if you want to so thanks everybody for hanging out and i will see y'all later this week adios folks you

Channel: Brent Ozar Unlimited

Views: 7,305

Rating: 4.9751554 out of 5

Keywords:

Id: yJgwQP84PdE

Length: 124min 4sec (7444 seconds)

Published: Sat Aug 08 2020