In this video, I delve into a fascinating debate: when is union better or worse than union all? Often maligned due to its requirement for distinct rows, the union operator can indeed be cumbersome, especially without proper indexing. However, I demonstrate that there are scenarios where using union can significantly improve query performance by avoiding unnecessary sorting and spooling operations. Through a practical example from my recent SQLBits session, I show how tuning with union instead of union all can drastically reduce execution time—from 16 seconds to just three seconds in one case. This video is not about promoting vintage seltzer but rather encouraging viewers to consider the context before choosing between these operators, emphasizing that responsible query optimization requires understanding the nuances and specific needs of your data.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data, which only makes sense. I’m drinking this lovely vintage seltzer. This YouTube video is not sponsored by vintage seltzer. I’m here to ask an interesting question, and that is, is union always better, or always better, always worse than union all. See, union gets kind of a bad reputation on account of it has to come up with a distinct set of rows and columns, or fields and records, if you’re into that sort of thing. But, and that can be painful. So when we think about union, we have a ton of columns, we have a ton of records, rows, or whatever you want to call them, who’s he, what’s it’s gadgets and ding-dongs. It can be pretty painful to come up with a unique set of those, especially when you’re there’s no supporting indexing, get some string columns in there, stuff can get pretty nasty. And that’s where union all can kind of be better because the SQL Server is not wasting time trying to uniqueify that result set. It’s just saying, you’re all welcome, come on, hang out, come into the light, some duplicates, repetitives, nulls, anything you want, throw it in there.
But, sometimes, getting a unique set of values is a good idea. And I’m going to show you one example of that. Now I’ve got two queries here. Now this is kind of a small deviation of a query that I presented in my indexing session at SQLBits. And the query doesn’t do anything terribly interesting, but I rewrote it a little bit. And this one here uses a union operator. And this one here uses a union all operator. And I’m going to show you something kind of interesting. I’m going to run, which one do I want to do first? You know what, I’m going to run the union all query first. And I’m going to kick that off. I’ve got query plans turned on, I hope. Otherwise, I’m going to waste about 15 seconds of your life. And while this runs, and runs, and runs, and runs, and kind of just keeps going. Oh, 16 seconds. Yeah, all right. Splendid. Now I’m going to run the union query.
And, ooh, that felt better to me. Three seconds. Not bad. Go us. We tuned that query with the magnificent use of union over union all. So what was the difference between these two queries? Well, let’s look at the union all plan first. I’m going to go over into the execution plan. And we’re going to concentrate on this bottom part, because believe me, this bottom part is where SQL Server did the majority of the work.
And let’s first look at this sort operator. And what I want to do is hit F4 there to bring up the properties window. Before we get to that, if we look at the sort, we can see that we spilled two threads. Spill level two. Oh, wrong button. There we go. And we spilled about 1,287 pages to disk.
Not a lot. Not terrible. If we look at the actual time statistics, we can see that we spent a little under a second on the sort. Where we spent the rest of our time was in this index. Sorry, this table spool.
So if we look at the actual time statistics here, a full 10. Oh, come on. Zoom it. There we go. A full 10 of our 16 seconds was spent spooling data about.
Spooled all over the place. Spooled once forth and beyond to the grave. Spooled.
It’s a long time to spend spooling data. If we look at kind of what we did for work in here. Well, we did about 243,000 executions. Right down there.
We did, if you add up the rebinds and the rewinds, that’ll add up to that number down there. And we spooled through above. That’s a lot.
Throw some commas in there, about 10 million rows. All right. Cool. So that’s our union all plan. If we look over at the union plan, right? That was three seconds, remember? Three wonderful seconds.
This is about the best you get out of me. It’s about, I mean, by that I mean that’s about as quickly as I can tune a query down to. Don’t think that there is any innuendo there. Sick people.
But if we look at the actual time stats here on the sort, we do about the same. But we have a slightly different sort over here. In this plan, we have a distinct sort. So this is where SQL Server decided to go and make our data set unique.
This is the distinct sort. If we look back over at the other plan, we just have, oh, where’d you go? Why’d you run away from me?
We have a regular sort. There is no distinctification going on in this sort operator. So let’s go back over here and let’s look at what happened in this query plan. So now let’s look at this table spool.
And it’s kind of interesting that if we look at what the spool did, it’s the same as the other one. We have 243, 205. And if we add up these, 40938, 202, 266, it adds up to the same thing.
But we spooled a lot less rows through this one. Yeah. Far fewer rows ended up going through that because we had a distinct result set from the union of those two tables rather than everything.
So sometimes, sometimes, we can save a lot of time in the query by getting a distinct result set. Now, in the session that I presented, I gave you a different way of tuning the query to get it down pretty quickly. Again, about three seconds.
So that’s why they call me three seconds, Eric. That’s why I tune queries down to. Anyway, sometimes, union can be better than union all.
Not always. Sometimes. It’s up to you as a, what do you call it? What’s a good word? Responsible.
Yeah. As a responsible query tuner to do this kind of investigation into your queries and why they’re slow. Anyway, I’m almost at the seven minute mark and that’s two minutes longer than I wanted to go. So thank you for watching and I’ll see you next time.
Video Summary
In this video, I delve into a fascinating debate: when is union better or worse than union all? Often maligned due to its requirement for distinct rows, the union operator can indeed be cumbersome, especially without proper indexing. However, I demonstrate that there are scenarios where using union can significantly improve query performance by avoiding unnecessary sorting and spooling operations. Through a practical example from my recent SQLBits session, I show how tuning with union instead of union all can drastically reduce execution time—from 16 seconds to just three seconds in one case. This video is not about promoting vintage seltzer but rather encouraging viewers to consider the context before choosing between these operators, emphasizing that responsible query optimization requires understanding the nuances and specific needs of your data.
Full Transcript
Howdy folks, Erik Darling here with Erik Darling Data, which only makes sense. I’m drinking this lovely vintage seltzer. This YouTube video is not sponsored by vintage seltzer. I’m here to ask an interesting question, and that is, is union always better, or always better, always worse than union all. See, union gets kind of a bad reputation on account of it has to come up with a distinct set of rows and columns, or fields and records, if you’re into that sort of thing. But, and that can be painful. So when we think about union, we have a ton of columns, we have a ton of records, rows, or whatever you want to call them, who’s he, what’s it’s gadgets and ding-dongs. It can be pretty painful to come up with a unique set of those, especially when you’re there’s no supporting indexing, get some string columns in there, stuff can get pretty nasty. And that’s where union all can kind of be better because the SQL Server is not wasting time trying to uniqueify that result set. It’s just saying, you’re all welcome, come on, hang out, come into the light, some duplicates, repetitives, nulls, anything you want, throw it in there.
But, sometimes, getting a unique set of values is a good idea. And I’m going to show you one example of that. Now I’ve got two queries here. Now this is kind of a small deviation of a query that I presented in my indexing session at SQLBits. And the query doesn’t do anything terribly interesting, but I rewrote it a little bit. And this one here uses a union operator. And this one here uses a union all operator. And I’m going to show you something kind of interesting. I’m going to run, which one do I want to do first? You know what, I’m going to run the union all query first. And I’m going to kick that off. I’ve got query plans turned on, I hope. Otherwise, I’m going to waste about 15 seconds of your life. And while this runs, and runs, and runs, and runs, and kind of just keeps going. Oh, 16 seconds. Yeah, all right. Splendid. Now I’m going to run the union query.
And, ooh, that felt better to me. Three seconds. Not bad. Go us. We tuned that query with the magnificent use of union over union all. So what was the difference between these two queries? Well, let’s look at the union all plan first. I’m going to go over into the execution plan. And we’re going to concentrate on this bottom part, because believe me, this bottom part is where SQL Server did the majority of the work.
And let’s first look at this sort operator. And what I want to do is hit F4 there to bring up the properties window. Before we get to that, if we look at the sort, we can see that we spilled two threads. Spill level two. Oh, wrong button. There we go. And we spilled about 1,287 pages to disk.
Not a lot. Not terrible. If we look at the actual time statistics, we can see that we spent a little under a second on the sort. Where we spent the rest of our time was in this index. Sorry, this table spool.
So if we look at the actual time statistics here, a full 10. Oh, come on. Zoom it. There we go. A full 10 of our 16 seconds was spent spooling data about.
Spooled all over the place. Spooled once forth and beyond to the grave. Spooled.
It’s a long time to spend spooling data. If we look at kind of what we did for work in here. Well, we did about 243,000 executions. Right down there.
We did, if you add up the rebinds and the rewinds, that’ll add up to that number down there. And we spooled through above. That’s a lot.
Throw some commas in there, about 10 million rows. All right. Cool. So that’s our union all plan. If we look over at the union plan, right? That was three seconds, remember? Three wonderful seconds.
This is about the best you get out of me. It’s about, I mean, by that I mean that’s about as quickly as I can tune a query down to. Don’t think that there is any innuendo there. Sick people.
But if we look at the actual time stats here on the sort, we do about the same. But we have a slightly different sort over here. In this plan, we have a distinct sort. So this is where SQL Server decided to go and make our data set unique.
This is the distinct sort. If we look back over at the other plan, we just have, oh, where’d you go? Why’d you run away from me?
We have a regular sort. There is no distinctification going on in this sort operator. So let’s go back over here and let’s look at what happened in this query plan. So now let’s look at this table spool.
And it’s kind of interesting that if we look at what the spool did, it’s the same as the other one. We have 243, 205. And if we add up these, 40938, 202, 266, it adds up to the same thing.
But we spooled a lot less rows through this one. Yeah. Far fewer rows ended up going through that because we had a distinct result set from the union of those two tables rather than everything.
So sometimes, sometimes, we can save a lot of time in the query by getting a distinct result set. Now, in the session that I presented, I gave you a different way of tuning the query to get it down pretty quickly. Again, about three seconds.
So that’s why they call me three seconds, Eric. That’s why I tune queries down to. Anyway, sometimes, union can be better than union all.
Not always. Sometimes. It’s up to you as a, what do you call it? What’s a good word? Responsible.
Yeah. As a responsible query tuner to do this kind of investigation into your queries and why they’re slow. Anyway, I’m almost at the seven minute mark and that’s two minutes longer than I wanted to go. So thank you for watching and I’ll see you next time.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I found myself live-streaming SQL Server tips and tricks, only to be greeted by the emptiness of an empty chat. It’s moments like these that make you question your entire existence as a content creator. Despite the initial disappointment, we managed to gather a small but enthusiastic audience, including some familiar faces and a few curious newcomers. The discussion ranged from enterprise edition licensing headaches to optimizing SQL Server performance with core-based configurations and max degree of parallelism settings. I also shared insights on free monitoring tools like OpsServer, which might just be the lifesaver for those stretched thin in terms of budget but not in need. The stream turned into a lively debate about workload replay tools and memory allocation strategies, making it an engaging session despite the initial lull.
Full Transcript
I’m live. Uh, no one yet. I don’t even have a thumbs up. I didn’t even give myself a thumbs up. What a sad day. Oh, boy.
So iffy. Will anyone actually be here? How long will I have to wait? How long? One person. One person. There we go. You better have a lot of SQL Server questions, one person, or else I’m screwed. My big plan after this is to go wine shopping for the weekend.
Oh, well, if it’s Josh, I’m not too excited. That’s never a good sign. These developers show up and get nothing out of them. Three of you.
Wonderful. Wonderful. We have enough people to successfully implement quorum until a fourth person shows up. No, there you are. There you are. You ruined the quorum, fourth person. Thanks for nothing. Thanks for nothing.
Do, do, do, do, do, do, do, do, do, do. Do, do, do, do, do, do. Back down to three.
Thank God. now we’re back to quorum now we can successfully run our failover cluster we can successfully fail that was funny it was fun successfully fail fail upward onward and upward that’s my that’s my that’s my plan yeah what do you what do you what do you know about failure kenneth what do you know about it by the way this week’s whatever you want to call it is brought to you by a thing that helps babies fart that’s our sponsor this week so everyone round of applause for our generous sponsor plastic thing that helps babies fart this has never been used by the way just something that came with a new baby a while ago that i found and i thought it looked funny you kind of like a little flute but you know don’t worry that’s never been anywhere weird i still have no idea what that means don’t i don’t want to know i have nothing wow an actual sql server question hello steve how are you uh lars says i use sql enterprise recently came across a 20 cpu limit due to the wrong install media attempts at addition upgrade failed what’s the difference between variants of enterprise edition uh so there is the old version which is like the cal based what you want to do is uh in download the install media that has the core based licensing in the title and do a skew upgrade to that you don’t need to do an edition upgrade you need to do a skew upgrade so um yeah that’s that’s that’s where you want to go with that you need to upgrade additions your edition is fine enterprise is enterprise but the uh the skew of enterprise is what’s skewed up zane says what kind of deluxe baby store did you pick yours up at that it came with accessories uh the hospital where um it it plopped out uh dev dba asks can i send you my copy of great post eric for an autograph uh hmm no no um if you no i don’t even have any copies of that left uh no nothing personal i just don’t want anyone to have my my home address who doesn’t who i don’t know don’t take it personally but everything i do everything from home and it would be weird if people just knew my address but if i if you ever had a conference and i’m at or a SQL saturday or anything that i’m at then i would happily sign it for you for free p.o box yeah no why would i do that yeah like madison yeah madison wisconsin i’m gonna be there what is it friday the 5th for a full day of training and then saturday the 6th where i have a session about the optimizer and how the optimizer works it’ll be fun devdb i don’t know i don’t know what which conferences you get to so be prepared i just might show up anywhere who knows who knows i don’t think i’m going to pass this year though they didn’t pick me for a pre-con and that’s an expensive trip when you’re not getting paid for it expensive if i lived in seattle that’d be one thing yeah bring it everywhere i mean what’s the what’s the worst thing that can happen you’ll run into lots of interesting new people who are like man that’s a cool book what’s that about and then more people will buy buy the book and then i’ll i’ll make another six dollars and that’ll be that’ll be nice yeah mcdonald’s the mall uh rest stop bathrooms you know various basements uh you know bars bars with no one in them that open at 9 00 a.m good places to go good places to be let’s see if there’s any questions uh on another thing yeah no all right good there was a question that came in via email that uh i answered via email and it was it was a it was that someone um someone uses uh sentry one at work but they have so many sql servers that it was getting expensive to keep licensing them all for sentry one because sentry one you know well i mean not just sentry one any any paid monitoring tool is going to be about the same price so it doesn’t matter if it’s like sentry one or uh like quest spot fog or whatever they’re calling it these days but uh yeah like anything’s gonna be about the same price but because there were so many people there are so many servers right there not so many people so many servers who um they needed to monitor uh they were looking for something free or open source and i suggested uh uh so stack overflow and the the very very smart people at stack overflow um have developed their own monitoring tool over the years the price you pay is that it is open source and it’s a little difficult to install but it’s it’s called ops server and it gets a lot of cool stuff it collects a lot of awesome metrics i’m actually gonna pull the link up for it but yeah it’s pretty sweet uh if you can get into if you can if you can get past the the install and config and get it up and running it’s it’s pretty awesome so if you need a free monitoring tool i would do that uh what uh that uh what is that it doesn’t make sense nothing makes sense anymore yes you’re welcome that is a great link it is a wonderful link uh okay i’ll show that uh sql watch dot io wow i need to get a dot io website those things are very popular right what would i do could make it i could get like taxes dot io that would be funny or i get like slow io what else could i do yeah i don’t know lots of stuff dot io that would be a funny url uh have you used the newest azure data studio it has some pretty cool monitoring built in that you can report off of uh no i i have never used azure data studio um because the majority of the stuff that i care about is in execution plans and azure d azure data studio is more like azure duty studio when it comes to execution plans it does not have uh anything anything good going on about it so no and i i i despite a lot of people being very excited about notebooks uh you know i i have i have my own little little pad of paper that i much prefer to keep my notes on my wife’s dad was it was a cop for a long time and when he retired he gave me all his all his like uh his police notebooks so i keep all my notes kind of kind of funny i keep all my notes in like a police notebook and the the first the first the first page has all sorts of funny stuff an ambulance rescue squad fire so like all this like a justice of the peace i don’t know what a justice of the peace does what do they do it’s like it’s like all it’s missing is like a constable in a priest i don’t know what goes on there let’s see here someone has a very important sql server question but they haven’t asked it yet so i’m just going to wait on that i’ll wait 20 minutes if i have to kapil says there was one i recall from spaghetti dba a sql workload yeah if so that’s not i don’t as far as i know that’s not a monitoring tool that allows you to replay a workload on one server from another server in a little bit better way than uh the the built-in tooling uh does that so um that’s that’s all i know about it though that’s what i know that is that is most i know no collusion wants to know how can i get over a million application locks per second the most i can get is 325 000 per second uh try harder that’s what i would do try harder doesn’t sound like you’re trying very hard so eat right get eight hours of sleep and literally just try harder is all you need lar says what do you like to use to replay workloads uh i don’t actually do a lot of that i just don’t um as far as stuff that i know that does it uh what was it benchmark factory is good but expensive um so this used to come up on on other office hours quite a bit where someone want to know how to do that and tara would always talk about her experience where she’s worked at like very large companies uh that had like entire teams dedicated to uh setting up workload tests like they would like they had their own in-house homegrown software uh and they would set stuff up to run to do like have like a like a like a production quality workload inserts updates the leads stuff like that uh yeah distributed replay is a piece of crap it is not fun or funny or useful or amusing it’s like it’s like wow what a great idea imagine being able to do that then you go to use and you’re like hell no i don’t want it i wouldn’t want anything to do with this this is awful dan says if you have a 56 core cpu on a 2012 standard instance of sql server can that cause issues with performance no uh i would so i would say no it’s not going to cause issues with performance standard edition certainly isn’t going to see all those cores just because of the of the limits imposed on it um you know 2017 you can see 24 cores so i the the next question i have is that uh is that um 56 physical cores or is that 56 logical cores with hyperthreading that’s another question who knows let’s see do i have questions coming in from anywhere else no not yet good good good everything’s quiet it’s just you i think we’re alone now what about memory allocation to the cores uh sure so that’s why i was asking uh kind of about the setup over there is um if it’s like if it’s a four-way if it’s a well no maybe yes uh so run sp blitz and see if you have memory offline uh it’ll it’ll warn you about that so uh sp blitz will warn you if you have schedulers offline if you have sequels if you have schedulers on your box that’s equal server isn’t using or if memory is allocated to a numino that sql server can’t currently see so yeah i guess like in like a weird situation if you had like a four-way proc but in like every numino had memory attached to it then yeah you could run into some weirdness with that but um run run blitz and it will tell you about it no collusion insists that having 56 cores on standards and might throw off worker thread counts yeah it very well may very well may uh trying to think of some other funny stuff that might happen let’s see i don’t know like i get a parallelism i don’t know like like i’m just trying to think about like like parallelism and stuff like that but i don’t know it’s it’s one of those things that i’d love to test out but i’m just not good i’m not good at like theoretically figuring out if like one like wait if one numino has like three schedulers hooked up like what will happen yeah look at that look at no collusion with the with the smart answers kavil says if i have a four socket 20 core hyper threading enabled total of 80 logical processors what is my best option to set mac stop eight eight eight just leave it at eight eight’s good uh i wouldn’t i wouldn’t go to one around much past that if you if you set it higher than that it doesn’t really like like so like eight really is kind of like a weird sweet spot for mac stop i have never uh seen an entire workload that benefited from a super high max stop i’ve seen like some queries that benefit benefited from higher max stop but overall setting max stop isn’t just about like you know single query performance either setting max stop is about um you know uh making sure that parallelism doesn’t harm concurrency because if you set max stop to like a very high number then you can like the number of like cores and threads that get spun up for a parallel query can increase exponentially you end up with like a crazy amount of threads allocated to parallel queries and that can that is generally considered a bad thing because the more of these you know super parallel queries you have with lots of threads assigned to them the fewer queries you can run overall you’ll run right into thread pool weights so setting max stop isn’t just about oh gee what’s the best setting for you know like general query performance it really is like a it’s a concurrency setting as well and if you’re not setting cost threshold appropriately aside from that you know setting max stop is you know only only half the battle so careful careful with that uh i would i would start with eight though eight is eight is a nice number for max dot eight is eight is a pretty decent uh sweet spot for query performance and it sounds like the number of cores you have uh hopefully hopefully you have adequate concurrency with max stop set to eight but you might have to you might have to chop max stop down to like six or four you might have to raise cost threshold up to like from 50 to like 75 or 100. who knows i don’t know if you if you’d like more specific advice uh i would i would love to love to help you out in a consulting capacity but all i can give you is vague recommendations set max stop to eight set cost rest so for parallelism to 50 and and tune accordingly let’s see here check db can benefit yes check db can benefit from using the number of cores in a single numenode but who does that god uh let’s see zane says some stuff about some things uh no collusion says if you ever have your work spread over too many schedulers and some schedulers are significantly more busy than the overall query time will degrade because some threads will finish work early that’s also true if you have like two cores though because there’s all those uh there’s all those old craig friedman demos where he make you know he like sets up like a two core vm and he makes one core very very busy with like a max stop one query and then runs a parallel query and you can like see the imbalance i think as long as microsoft hasn’t torn his blog down uh you can still find one of the posts where he does that uh so if you if you’re really interested look for like uh craig friedman post about like parallel scans and i think there’s a demo in there of that happening yeah well i hope you’re not look kabil says uh he’s a consultant too well gee i hope i hope you’re not billing anyone for this time careful on that you’re here you are seen sir you are seen cost threshold for parallelism i start at 50. yes that’s a good starting spot um but it’s it’s funny how that like it’s it’s it’s also it’s also amusing to me how it’s like you have cost threshold set to 50 and that certainly prevents like low cost queries from going parallel but then it’s like like you have like some low cost queries it will never break like like 10 and then you have like like the really bad queries that i’ll have a cost of like 2000. and so and so you’re sending me a look at this like well well cost threshold of 50 is good like it’s keeping like the really small ones from doing anything weird but the man those those those bad queries are really bad so you gotta be a little careful with those uh let’s see no collusion has oh boy no collusions on a roll today holy cow there are some latches and spin locks use some types of queries that don’t scale well with max dot yes it would be nice if someone blogged about those who’s smart and who knows these things and has 96 core servers or or if they’re not haven’t been downgraded to 48 core servers who can who could write about these things with some authority that would be wonderful nesting transaction full is used for parallel inserts into heaps and column stores a good example going above maxed up eight may not help with runtime at all might not you’re right you’re right about that again it would be it would be nice if if someone smart wrote about these things in some detail because the the the only the only other the only person i know who might do that wears a funny hat why should i ever enable auto growth i i enable auto growth all the time can you imagine a job where you you got alerts that you had to go grow a data file i would lose my damn mind and quit auto growth is a wonderful thing auto growth auto growth saves your life just don’t set it to something stupid like a percentage 10 of one gig is not the same as 10 of 100 gigs so i love auto growth i would keep auto growth why not use it for log files i don’t i don’t i don’t know who you’re asking that question to if only conversation threading worked better in here yeah one meg auto growth is a funny one uh that’s one of those one of those cute things that’s like you just well you know the funny thing about the one meg auto growth is at least you’ll never wait a long time for it if you like if you if you were just constantly growing by a meg cool you’re never going to wait a long time to grow by one meg let’s see no collusion says i like auto growth for log files and not data files well aren’t you just a pre-sizing madman oh so okay so then then how how big do you make your data files then if you don’t like them to auto grow that’s what i want to know this is where this is where we see some absurd number like in the in the terabytes i guarantee it you guarantee it how many terabytes as big as required you size queen you dirty dirty size queen how do you how do you know how big is required it’s not that big oh thanks for letting me know that’s the uh lars says he read recently that you might want to allow for enough space to hold two copies of your largest index during rebuilds yeah but who rebuilds indexes that’s for the birds don’t rebuild indexes is a waste of time go do something important like check db or backups no inclusion says i created a database with 72 data files huh how many on purpose like are you sure you didn’t like it wasn’t like a loop that got out of control how big were those 72 data files that’s what i want it must have been huge huge what do you put on 70 what do you put across 72 data files 1.5 so you had i don’t know whatever 70 100 and something gigs i did a little math on the top of my head yeah i want file group that would be funny that would be funny problems with pfs contention yeah that’s a good reason to create that pfs contention is not just for tempdb folks if you are creating enough objects in your data files you can run into page contention in the same way that you can run into it in tempdb and it will show the same exact weight types it’ll show show you those same exact where is it uh the page latch up page latch up weights yeah show you those in the exact same way and there’s no real differentiation like sp who is active can show you like uh which database are happening in but uh if you’re just looking at weight stats and you’re like holy crap i have these page latch up weights what’s going on i already have tempdb set up right could be happening in a regular data file could be anywhere just don’t you could you could just take a a cautionary tale from no collusion and not create well like millions of tables in your user databases or in a short span of time you might run into some scalability issues rcsi is a magic bullet not for pfs contention it’s a magic it’s a magic bullet for uh readers and writers not arguing with each other but not unfortunately for pfs contention that’s not going to help too much uh microsoft is doing some cool stuff with uh with tempdb’s system tables being in memory coming up and it’s in sql server 2019 so that’ll be fun it’s a magic bullet for developer sanity yes that is true otherwise otherwise you make developers spend a lot of time writing no lock hints and they could be doing much much more productive things with their time than writing no lock another quick like the question always comes up when i’m talking to people is uh about if if no lock is different from read uncommitted they think like like one like one is somehow magically better than the other like no it’s the same thing just with less typing well i guess a little bit more typing up front a little bit less typing overall yeah no one wants to solve deadlocks deadlocks are the most boring thing in the world to solve except parallel deadlocks parallel deadlocks make the sexiest deadlock graphs they they they look so cool though it’s like amazing that that kind of line work can be done in a tool designed to write queries and manage a sql server instance with but they’re pretty nifty uh and i like those regular deadlocks this is like i took a key lock i took a key lock i took a key lock i took a uh dull dull dull get up kapil says uh any link that can help me in setting up a four node geo cluster or multi subnet cluster you know what’s funny um my friend sean who works at microsoft had a blog post about that but his blog got taken down recently so i had a link but the link doesn’t work anymore so sorry about that uh you’ll you’ll just have to i don’t know read the documentation let’s see uh someone who who got who got very drunk and didn’t have a good time the next morning at SQL bits is saying they came into a job with deadlock priority low everywhere yeah they that would be a that would be a deadlock priority that would be a deadlock issue huh yeah you look great the next day did you ever figure out where that blood came from i take that as a no yeah that was a good time if anyone if anyone needs a a good place to drink in manchester go to the britain’s protection they have 300 kinds of whiskey and uh and and we have drank them all so that was a good time just stay away from the irish whiskey in a green with a green label because that’ll that’ll do you in yeah that’s where we were yeah good question yeah that was like there’s like a bar within walking distance from the hotel and you didn’t know that’s where we were you wouldn’t you would did what did you like you and martin like bump like bump heads real hard and knock each other out maybe let’s see uh yes where else would we have been that could that could explain it yes that could explain the blood blood can come from many mysterious places mostly inside things though inside living things you could just keep a jar of blood with you sprinkle it on stuff where did it come from i don’t know all right all right any other questions anything else fun going on what’s everyone doing it’s friday some of you should be doing things that are better than this it’s at least it’s 5 30 where you are see darren says last week or week before you said you were doing some century one mining scripts are those still forthcoming yes i actually just scheduled that blog post last night uh that’ll get published you know let me see here uh i’ll go look i have magical powers where i can see when blog posts are gonna happen unfortunately i think i’ll my website is slow who do i call if only there were some kind of website performance tuning expert i could talk to about why my website is slow by the way that that’s just that’s just one query that i have lined up it’s nothing i don’t have like a whole series of them it was just one that i was particularly proud of that’s just one of the things that i’m not going to be able to do it i blame wordpress kenneth uh stephen cutter says any good tips for moving file stream data yes put it in the garbage stop using file stream an awful awful awful feature that is hi devdba says uh trying to get toad to recognize my new tns name dot aura file bad time yeah a lot of things with oracle are a bad time sorry about that uh use sql developer instead i hear jeff smith says that that’s much much better uh let’s see wordpress unshared hosting uh do i have that i don’t know what i have managed maybe i don’t know how do i get a how do i get a faster website who do i pay for like enterprise edition of wordpress zane says oracle sql developer is my nightmare well you should open a ticket about that you know what you should tell jeff smith on twitter that you don’t like don’t like it let’s see here i gotta answer that question though where are my posts where are my posts is uh they’re still loading so i blogged so much i made wordpress slow zane just doesn’t like oracle zane has oracle problems let’s see oh yes so the query that uh i wrote for century one will get published april 10th sorry about that darren uh you know what tell you know what i how about this just because darren because you asked i will have that go out uh when can i get rid of this thing what’s tomorrow the 23rd i have blog posts scheduled until april 12th that’s how crazy my life is that’s how little i have going on with myself so let’s see today’s the 22nd uh and that was uh uh yeah you know what i’ll uh i’ll do i’ll do a quick thing i’ll do a quick schedule change and uh when when i get off here and i’ll have the the century one helper query go out on uh i guess monday of next week so watch watch for it monday the 25th that’s what i’ll do for you because we’re best we’re best friends now and i don’t mind changing my blog post schedule for you let’s see uh lara says not small table with old data but typically only last two weeks of data queried i’ve heard that partitioning for performance is not a good idea but need to keep stats updated to retain good query plans uh so that’s where something like filtered statistics or filtered indexes would probably be helpful uh where so like even so so here’s the thing is even if like you partition the table you’re right it wouldn’t be helpful for performance and it also wouldn’t help stats because while sql server will sample statistics at the partition level the final histogram that it puts together is the same 200 steps for the entire table so it’s not particularly helpful uh for that so it won’t help you any that won’t help you any better like you could update stats for a single partition but it’s not good it’s still going to be the same it’s still going to do the whole 200 table things they’re not gonna i’m not gonna help you with that you really need to do is create like filtered stats or uh filtered indexes that will have filter statistics on your hot data and that way you can figure that way you can keep that chunk of data um you know better stats up to date it did so there’s always that dean says uh dude has a bug that caused it to get stuck when opening okay i don’t want to read that uh darren’s yep no problem darren tana says beats me i only have them uh i don’t know what that means uh yes spiffles let’s see uh did a 3000 word blog post the last day i’m taking a week off what would ever possess you to put 3000 words in one blog post that’s a book that’s like five blog you could have had you could have had like a week of blogs with that why would you put that all in one blog post there’s nothing nothing good about a 3000 no one’s going to get to the end of that no one’s going to get to the end people are going to die reading that thing it’s like a farewell to arms or remembrance of things past it’s far too long to exist sounds like half of a paul white blog post paul thanks to my expert tutelage has gotten much better about breaking his blog posts up into consumable chunks you have me to thank for that that rogo’s blog post was one blog post said four and give it recaps tldr you need that stuff no one’s as smart as you yes that that likely is where the blood came from banging banging your head against a laptop for 3000 words i would do the same thing yeah i think i think rogo’s rogo’s was originally one post it’s like you are out of your damn mind what software do you use to compose blog posts uh google chrome on wordpress uh i don’t use the block editor uh and i don’t use what’s that god i don’t eat gutenberg that thing is such a piece of crap writing blog posts in gutenberg maybe you want to stop blogging i eventually like there was a plug-in to use the classic editor and i’ve never looked back god gutenberg such garbage uh hang on a second my wife is texting me okay all right apparently my wife is ready to go wine shopping so i’m going to get out of here it was lovely having you all this week uh i will see you next week and we will talk about more stuff and things for sql server or whatever you bring up i will maybe maybe i’ll start reviewing wine on here because that would be the only reasonable use of a friday is to drink wine in front of all you people anyway uh thank you all for coming i’ll see you next time goodbye uh remember this week’s sponsor is a thing that helps babies fart so thank you thing that helps babies fart is is is is is is is is
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into a quirky yet important aspect of SQL Server execution plans: the warning for implicit conversions. I start by sharing a personal anecdote about my silent air horn and how it led to a bit of downtime, but now that I’ve wrapped up those preparations, I’m excited to share insights on query optimization. The video highlights a common scenario where an implicit conversion in a query can trigger a warning, making you think the execution plan might be suboptimal. However, I demonstrate through examples how this warning isn’t always as dire as it seems; sometimes, even without the conversion, you won’t get a seek plan due to missing indexes. By creating these necessary indexes, we see the difference in performance, with SQL Server finally able to use a seek operation instead of a scan. This video aims to clarify when such warnings are actually significant and when they might be misleading, helping you make more informed decisions about your query optimization strategies.
Full Transcript
Erik Darling here with Erik Darling Data. Still do not have a proper air horn. Still have a silent air horn, which is very, very sad and depressing for me. I’m going to get on that this weekend. I’ve been a little busy. Now I’m less busy. Now that I don’t have bits to prepare for. Now that I just have a couple other things to prepare for. But now that I’ve delivered the material, I’m really happy with it and I think I’ll be able to move on from there. But anyway, I wanted to talk to you about something kind of funny that shows up in execution plans that can be a little bit misleading. And it’s a warning about implicit conversions. And I’m just going to jump in and show you the warning so that we’re all on the same page here. Now when I run this query over here, when I hit execute, I get query plans turned on. I’m going to run this and I’m going to get results back and that’s not really the point. The point is that when I go into the execution plan, I have this little bingo bango over the select operator. Sign that SQL Server is angry with me. We have summoned the wrath of SQL Server. All of a sudden, we have to worry about these things.
That little bang is coming up because we have an implicit conversion in this execution plan and it may affect the seek plan in the query choice. Yeah, it might do that. Yeah, crazy, right? Something went terribly wrong. And what went terribly wrong is that we have flubbed this query from the get-go. What we’ve done is tremendously idiotic. We are converting a column that’s an integer to be a varkar 10 and then comparing it to a string over here. I know. Who would ever do that in real life?
Anyway, what’s misleading about this is that if we run this query in a way that does not summon the implicit conversion gods like this, we still won’t get a seek plan. I know. Crazy, right? Over here. We are still just scanning the clustered index. Bananas. Bonkers. Outrageous. Slings and arrows, my friends. Slings and arrows. There is something different in the plan now, though. We do have a missing index request. Now, SQL Server is saying, hey, pal, between you and me, you create this index on this badges table on that user ID column.
We could do good things together. We could have a good time. And that’s exactly what you need in order to get a seek. See, without an index on that column, the way this query is set up, specifically without an index that leads on user ID. If we had user ID second in the index and we had another equality predicate first, then we could seek for both. But with this one with just one predicate, without an index that leads on user ID, this 22656 is just going to have to scan something else.
The primary key clustered index on the badges table is a column called ID, not user ID. So we are missing out on something fundamental there. But if I go and I create an index on user ID, well, I’m sorry, this takes a second for some reason. There we go. Now we’re cooking with indexes. Get rid of you. Goodbye. Now if we go back and run this one, we will in fact get a real snappy seek plan. Get that. Good. Yeah. Seek. Look at that. We did it. Thanks, ma. Look, ma. No hands.
But now it makes this warning reasonable. So now if I run this and we look at the execution plan, we are going to go back to scanning. Well, not back to. Now we’re going to be scanning our nonclustered index rather than our clustered index. But we still had to scan it. Now when we get this warning over here about affecting the seek plan choice, that’s not terrible at all to say. Thanks, Microsoft wording people. But now this warning is at least accurate because it did affect the optimizer’s ability to seek. So just a heads up, when you get that warning, that doesn’t mean that you have a good index for SQL Server to use to seek into.
That means that even if you did have the index, we wouldn’t be able to seek into it. So don’t think that just because you get rid of the implicit conversion, all of a sudden you’re going to see a seek plan, you still need an index to back it up. Anyway, that’s it for me. I’m going to keep this to five minutes. Thanks for watching. Again, I’m Erik Darling with Erik Darling Data. And thanks for watching and I’ll see you next time. What’s that stop button? Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
I have no idea if this is a bug or not, but I thought it was interesting. Looking at information added to spills in SQL Server 2016…
If you open the linked-to picture, you’ll see (hopefully) that the full memory grant for the query was 108,000KB.
But the spill on the Sort operator lists a far larger grant: 529,234,432KB.
This is in the XML, and not an artifact of Plan Explorer.
Whaddya think, Good Lookings? Should I file a bug report?
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
While working with a client, I came up with a query against the SentryOne repository.
The point of it is to find queries that waited more than a second to get a memory grant. I wrote it because this information is logged but not exposed in the GUI yet.
It will show you basic information about the collected query, plus:
How long it ran in seconds
How long it waited for memory in seconds
How long it ran for after it got memory
SELECT HostName,
CPU,
Reads,
Writes,
Duration,
StartTime,
EndTime,
TextData,
TempdbUserKB,
GrantedQueryMemoryKB,
DegreeOfParallelism,
GrantTime,
RequestedMemoryKB,
GrantedMemoryKB,
RequiredMemoryKB,
IdealMemoryKB,
Duration / 1000. AS DurationSeconds,
DATEDIFF(SECOND, StartTime, GrantTime) AS SecondsBetweenQueryStartingAndMemoryGranted,
(Duration - DATEDIFF(MILLISECOND, StartTime, GrantTime)) / 1000. AS HowFastTheQueryRanAfterItGotMemory
FROM PerformanceAnalysisTraceData
WHERE DATEDIFF(SECOND, StartTime, GrantTime) > 1
ORDER BY SecondsBetweenQueryStartingAndMemoryGranted DESC
The results I saw were surprising! Queries that waited 10+ seconds for memory, but finished instantly when they finally got memory.
If you’re a Sentry One user, you may find this helpful. If you find queries waiting a long time for memory, you may want to look at if you’re hitting RESOURCE_SEMAPHORE waits too.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
To test FROID, which is the codename for Microsoft’s initiative to inline those awful scalar valued function things that people have been griping about for like 20 years, I like to take functions I’ve seen used in real life and adapt them a bit to work in the Stack Overflow database.
The funny thing is that no matter how many times I see the same function doing the same thing in a different way, someone tells me it’s unrealistic.
Doesn’t matter what it does: Touch data. Not touch data. Do simple formatting. Create a CSV list. Parse a CSV list. Pad data. Remove characters. Proper case names.
“I would never use a function for that.”
Okay, Spanky ?
Too Two!
In CTP 2.2, I had a function that ended up with this query plan:
Tell Moses to get the baseball bat.
The important detail about it is that it runs for 11 seconds in nested loops hell.
Watch Out Now
For reader reference: The non-inlined version runs for about 6 seconds and gets an adaptive join plan.
The plan is forced serial with inlining turned off, naturally.
You’re cool.
I sent the details over to my BESS FRENS at Microsoft, and it looks like it’s been fixed.
To Three!
In CTP 2.3, when we turn on functioning inlining and do the same thing:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
Fad Gadget
No more nested loops hell. Now the function gets an adaptive join plan with parallelism, and finishes immediately.
Thanks, frens.
And thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Thanks for watching!
Video Summary
In this video, I decided to take a break from the usual technical content and share some of my more… colorful experiences. As you might have noticed, things got a bit chaotic as we went along—lost rubber bands, dropped microphones, and an unexpected vocal change or two! But amidst all the chaos, there were some interesting discussions on topics like SQL Server 2019, NoSQL databases, and the differences between consulting work and working for a single company. I also tackled some more technical questions, such as troubleshooting DB mail notifications and discussing page splits in indexes. It’s been quite an adventure, and while it wasn’t entirely smooth sailing, it certainly kept things interesting! If you’re curious about how SQL Server 2019 might impact your work or just want to hear a bit of off-the-cuff tech talk, this video is for you. Enjoy the ride!
Full Transcript
Oh, Jesus, live. Live, live, live. Live, live, live, live. Live. I was to do the thing where I put the browser. No, in the middle. I just shrunk my whole thing down. That was terrible. There we go. There are people. Hello, people. Why did you keep shrinking? Stop shrinking. Stop shrinking. I don’t want you to shrink. I just want you to move. Keep doing it. Oh, you dumb dummy. Yes. Yes. The joys of moving a window and windows. Slowly, slowly. If you do it too fast, it’ll shrink. If you do it too fast, it just shrinks. Oh, I did it too fast. Sucks. Whatever. This is good enough. This’ll work for me. Three of you, huh? Three. I shaved just for you and there are only three of you. Monsters. Monsters. Two of you now.
Hmm. Hmm. Hmm. Hmm. Hmm. Oh, now we’re back up to three. Yes, Julie, you are the best of the best. I don’t care what the other two people here say about you. You’re the best. I hear, uh, Paul White’s supposed to show up today to try to make me apologize to spools, but I’m not going to do that. He’s going to be very disappointed. Now there’s four people here.
When, when we hit, Oh, five people. All right. Five people. So now I have to announce this week’s sponsors. Um, no, I’m not apologizing. This pools. You’re curmudgeon. So this week, uh, this webcast is sponsored by two red rubber bands and a roll of athletic tape that has some stuff on it.
Cause it fell on the floor. So I want everyone to thank our generous and gracious sponsors. Uh, I don’t get paid to apologize to spools. I’ll never apologize to those things. How about, how about this? How about you get someone to apologize to me for entity framework? And then I’ll apologize to spools. Because that’s the only way that’s going down. Cause every time I see a spool in an, any, any framework query, it is the pits.
Don’t I get paid for fixed? I wish I got paid by the spool by this, by the corrected spool. I would, I would, uh, I would be having a much, much nicer office. Okay. I could stop being in a gang. It would be awesome. It’s all sorts of things in my life that would, that would, that would improve. Yeah. Well, you know, the check would be in the mail.
Yeah. So Josh, you apologize for entity framework. All of it, the entire history of entity framework. I want you to apologize for. By the way, don’t let, don’t let these characters overwhelm you. If you have an actual SQL Server question, you’re not just here because you’re on drunk New Zealand time and you want to harass me.
Please ask them. Paula is down to 1% of a wine bottle left. Isn’t today sponsored by blue badges? These blue badges? Cause these are the only blue badges I have.
Today is sponsored by lazy New Zealand couriers who apparently can’t deliver anything within five days of getting a package. It’s a good time. Always fun. It’s just too much traffic, too much traffic.
No one can show up. Boy. Oh boy. All right. Well, someone, if someone doesn’t ask me, if it’s just me arguing with Paul about spools is everyone’s going to be really bored. So Paul asks, have you ever globally enabled trace flag 8690 at a customer?
And I’m this close to doing it this close. And when I do, I’ll have the last laugh and you’ll be apologizing to me for defending spools because they’re indefensible. Indefensible. Indefensible. Except in that one query. Indefensible.
Darren says, why are front squats so much harder than back squats? Cause you don’t do as many front squats. If you did more front squats, they would get easier.
Then the mechanics of a front squat are kind of weird. Like doing the hold or the hold like this and different back angle, too. You have to stay much more upright with a front squat.
Tough to stay that upright. Keep the head in good position. Back squats. Natural. Kind of a natural position to be in. Front squats just feel weird.
I’m going to… Excuse me. There’s apparently a truck race going on outside right now. But Paul just apologized to me.
So I’m going to pause to take a screen cap of that. There we go. First time for everything. I also have a screen cap of the one time Paul said he thought one of my jokes was funny.
I have that. I have that hanging up on my wall. I got a big head of it. Paul’s big head with a bird bubble.
It’s an incomplete sentence. Incomplete sentence. Oh.
You know, actually, I just got a very smart advertising email from a cab company, a cab app called Arrow, wishing me a happy St. Patrick’s Day. And you know what that means?
As they’re getting ahead of the drunk crowd saying, don’t drive to the bar. Call Arrow and be drunk and abusive to one of our drivers on the way home. That’s a good idea.
I’m sorry Paul doesn’t understand why spools are bad. That’s what makes me sorry. A guy as smart as Paul, a guy as good looking as Paul, sees a spool and falls in love with it.
Sad. Very sad. Makes me upset. Paul is in love with this thing so far below his league.
Bums me out. Paul came here to win. No, he didn’t.
Paul came here because he’s drunk. And it’s New Zealand Friday. I don’t even know what time. So you can only imagine how many empty bottles there are. Storn about his bare feet.
Do, do, do, do, do, do, do, do, do, do, do, do, do. Flipping around 5.08 a.m. You madman.
It’s a good thing you don’t have a job either. Otherwise, when would we speak? I’m so glad that we’re both unemployed and we can just hang out at computers whenever we want. It’s the life.
I’m telling you. It’s the life. It doesn’t get any better than that. It’s like every day I wake up in the morning, there’s Paul.
I go to bed at night, there’s Paul. I accidentally drink too much at 2 in the afternoon, there’s Paul. It’s wonderful.
It’s absolutely fantastic. Just two bums hanging out. Doing bummy stuff. Talking about goth music. Paul pretending he listens to the songs I send him.
It’s fun. Josh asked me what I think about Power BI. Nothing. I don’t use it. I’m more of a video guy.
If Power BI starts doing stuff with video, I’d be happy to give it another shot. I’m a video artist. I don’t deal with dashboards and charts and graphs. You know what my problem is?
I’m not in the crowd with the charts and graphs jokes is the real problem. Everyone has these funny jokes that they make that everyone else who deals with charts and graphs immediately gets. It’s about pie charts and bar charts and stacked 3D bar charts.
Everyone else who deals with charts and graphs is like, ha, ha, ha, good one. Yeah, that’s funny. It’s a cute joke.
Me, I’m like, I don’t know. I can’t read that thing anyway. I can barely read an analog clock. Why do you want me to read a chart or graph? You out of your mind? Just tell me what you want to say. Why do I have to sit there and figure out your chart and graph and your X and Y axis and your Z axis and your color coding?
It sucks, too, because this is always like seven graphs in one. Make it simple for a dummy to read. I can’t deal with it.
Darren says, why do NoSQL DB companies always think they have to promote it as a replacement for relational DBs? Because over time, they are adding more and more relational database stuff like transactions and schemas and keys. And NoSQL is becoming SQL so slowly.
I hardly even noticed. Just trickles in over time. Like, oh, yeah, we need to do that. You can’t use NoSQL for that because we need this thing.
And they’re like, oh, we can add that. No problem. And all of a sudden, you’ve just got another relational database. That being said, I’ve heard a couple people who are pretty smart say that Snowflake is really cool as a database. So I don’t know.
I had no angle on that. Snowflake is not a sponsor. Again, this week’s sponsor is some athletic tape. And I think I dropped the rubber bands on the ground.
I’m so sorry. No longer a sponsor. Farah says, what are typical differences in skill sets between those who do consulting work and those who work for a single company? I think it’s less of a technical skill and more of a personal skill.
And that people who do consulting have to be a bit more extroverted and have to get out there a little bit more and make a name for themselves and be a presence and sell themselves and what they do and what they know. Whereas people who get into a company, you know, you have an interview and you just go from there. Like you have that you have one interview and you just keep showing up after that consulting.
You have to keep interviewing every single damn day of your life. I don’t think there’s a whole lot of technical. There are full time people who are, I mean, obviously far more technically proficient than I am who know a lot more about stuff, whether it’s, you know, AGs or whatever.
But, you know, got to sell the sizzle. Sometimes the sizzle is better than the steak. Like if you’ve ever had steakums, you know, sometimes the sizzle is better than the steak.
But selling that sizzle is part of what I think people who do consulting have to do that. People who are full time employees don’t. Like every once in a while you have to like, you know, convince someone in a company that you’re right.
But every time, isn’t this funny? Because every time I see someone technical talking about, talking publicly about trying to discuss something at work with someone who is not technical, they seem very frustrated and they always lose the argument. So that’s a difference too.
Julie says, I have job notifications set up for a SQL agent job. The job fails. No email is sent.
Note, failed to notify DBA support via email is in the log. How can this be fixed? Hard to say from just that. There’s a pretty good blog post from a while back by Kenneth Fisher who had a bunch of queries to help troubleshoot DB mail.
And I think you have more of a database mail problem than a SQL agent problem. At least in my experience when I haven’t gotten emails. It hasn’t been because SQL agent did something wrong.
It’s been because something went wrong with DB mail. And I don’t know. That’s where I started doing my troubleshooting. Paul says, will 2019 make DBAs and consultants redundant?
Gee, I hope so. Because I really want to do something else with my life. I just need an excuse to do it. My wife won’t let me out of this office until we stop getting paid for SQL Server. So, you know, I hope so.
Microsoft doing cool. The thing is that I think that as long as we still have parameter sniffing, there will still be consultants and DBAs around. They got to get on that ball.
So, I don’t know. No, but seriously, like, so what I’ve been doing recently is because CTP 2.3 seems like a pretty good step forward in, like, stuff that’s going to be in RTM. I’ve been taking all my demos that I do right now on 2017.
And I’ve been running through them on 2019. And, like, there’s some stuff that changes and there’s, like, some promising things that happen. Like, some demos.
I’ll put it this way. Like, if you have a lot of, like, through the Ringer demos that you’ve been leaning on for a long time with, like, functions and table variables and multi-statement table valued functions and sort of, like, boring, like, you know, like the real, like, DBA 101 stuff like that, you’re going to have to start thinking a little bit harder when it comes to newer, like, SQL Server 2019. You’re going to have to start thinking about some more interesting stuff that happens in execution plans.
If you don’t, you’re just going to not have material pretty soon. I mean, it’ll be a while before people start adopting 2019 in, like, a meaningful way. But there’s a whole lot of good reasons to adopt 2019 for people who have tough times with workloads, either because they can’t afford to put development time into them or they have a third-party vendor app that is just not going to see any tuning.
Who knows? Like, I see people who… So, like, it’s the craziest thing.
Like, I know people who use third-party vendor apps. The third-party vendor has been out of business for, like, years. And I’m not talking about AdventureWorks.
I promise I’m not talking about AdventureWorks. But, like, people who have, like, third-party vendor apps have been out for years and they won’t change anything. You know, like, well, we can’t change the code and we don’t want to, like, do anything with the indexes. I’m like, well, why?
It’s not like you’re going to lose support. It’s not like there’s a support contract to be worried about. But for those people and people who are in sort of similar situations, you know, 2019 will be nice because you can just plunk stuff in there and certain things that have been awful for years could stop being awful. So it’s certainly something to consider.
Jazz voice at some point. I didn’t mean to get breathy on you there. I just ran out of breath. Let’s see.
Dan says, do page splits really matter? How can I prove, disprove the performance impact of page splits? So the page splits, it’s been a long time since, like, page splits mattered a bit when you were on, again, like, page splits. Page splits to me go right along with, like, index fragmentation where it’s like, you know, if you had, if you have really, really bad, crappy IO, yeah, a lot of page splits could gang up on you.
If you have decent IO, page splits aren’t going to be all that big a deal. What really sucks is the way SQL Server tracks page splits is it counts new pages towards that. So if you just put data into a table, even if a page doesn’t split, if you add a page, SQL Server counts a page split.
It’s kind of weird. And it counts page splits everywhere. People made a real big deal out of it for a long time.
But, you know, anytime, anytime. Anytime someone’s like, I have a lot of page splits, I’m like, good. Good.
You have data. You should have page splits. As far as proving or disproving, I don’t know. Like, if you have an absence of page splits, like, how do you prove an absence of page splits?
If you set fill factor real low, if you set fill factor real low. Say, like, if you set fill factor into, like, 50%, right? So every time you rebuild your indexes, you have pages that are only half filled with data.
Not only your index will be 50% bigger, and all your read queries will have to do twice as much work. But those pages will eventually fill up and split again. Fill factor doesn’t get honored on modification.
Fill factor only gets honored when you rebuild or reorg your indexes. So you’re constantly just having to make more empty space for more stuff coming in. It’s a losing battle.
I would just, I would avoid that battle. I have people making a big deal about why? Why?
What did the page split ever do to them? Let’s see. Peter says, how come you move the time slot back an hour? Did I miss that earlier on? Do you live in a country that doesn’t honor daylight savings time?
Because when I look at my clock, I am still doing this at noon, like every other day. Like every other Friday. Except the Friday I was at SQL Bits.
Every other Friday, though. There’s a Friday I might miss coming up, and I’ll be in Wisconsin. Well, actually, I’m definitely going to miss it, because I’m going to be in Wisconsin. Madison, Wisconsin.
While Joe Obish shakes his head at me. It’ll be fun. Paul says, automatic tuning seems like a promising idea. SQL Server saving multiple plans for different parameter values. Speculatively adding useful looking indexes.
Dropping them if they don’t help, et cetera. Yeah, that does seem interesting. I think my main problem is that it assumes that any one of those plans is actually good. Plus, you know, I’ve seen some of the missing index requests.
But I’ve seen some of the speculative indexes that come out of automatic tuning. And I’m going to be very honest with you in that they don’t seem too much better than what comes out of DTA or the missing index requests that exist now. Even though I’ve been assured it’s a different set of code, they look startlingly similar.
So, it seems promising, yeah. It’s not going that extra step, though. It’s not going to rewrite your code.
It’s not going to test the union versus the union all. As far as I know, it still can’t fix a spool. So, that’s an interesting thing, too.
Is I’ve seen automatic tuning code that does not take really, really big, bad, awful index spools into account when it starts testing things. So, take that. Take that.
Take that, punk. Or what comes out of most of you. Yeah, you’re right. No, that’s totally true. You know, there’s a, what do you call it? It’s a dearth.
Yeah, let’s go with dearth. Because dearth is a good word. There’s a dearth of good advice that comes out of most people. Paul’s advice is always good.
Paul starts with, have you tried restarting it? And Paul starts, and then Paul asks, if you’ve tried getting drunk and ignoring it. And, you know, at some point I would accuse Paul of being American with that attitude.
It’s funny because it’s true. It’s funny and true. It’s the best, best possible outcome. Best possible outcome.
Oh, my God. Oh, my God. We’ve been OMG’d by Mr. White himself. What’s coming next?
By the way, I want to remind everyone that this week’s sponsor is a roll of athletic tape that fell on the floor. Rubber bands are still down there. Rubber bands fell on the floor and didn’t get picked up again.
If I bend over on camera, I might not get back up. It’d be a rough day. Rough, rough day. Let’s see.
Nothing good going on there. Nothing good going on there. Ah. Yeah, I know. Sponsored. Drinking already. Always. If I stop, I’ll just be so shaky on camera.
You don’t want that. You don’t want to see me getting all twitchy and shaky and scratch. You don’t want to see me not drunk. You know, out of your mind, it’s a terrible thing. Drinking already.
I started drinking in 1993, Peter. I think it’s funny. I think you’re funny. Yeah.
I don’t know. A lot of people accused my grandmother of being an alcoholic, but she wasn’t. She only drank in the morning. Good for her. She was able to stop at some point. Mostly by going to bed.
You’re lucky you’re not having to wait for a courier to deliver your next bottle of wine. That’s not, by the way, that’s not a bottle, my friend.
I would never send one bottle of anything. Unlike some other people, I would never send one bottle of anything. I’ll get you set up for a little bit.
You will have like 6,000% wine for a minute. But I have faith in you. I have faith.
Yes. Unlike some people we could mention who send one bottle at a time. I would never do that.
You know why? Because there’s no such thing as one drink. There’s no such… One drink never treated me well. And I’ve never gone out and been like, I’m going to have a drink.
And been like, yeah, that was it. That was it for me. Peter says, can you give some examples of non-physical objects you can lock with SPAPA? So, yeah, so it’s more like a…
You can only… Like, you can lock anything. You can name it whatever you want. It’s an imaginary resource. It doesn’t physically exist anywhere.
It’s just a reference to a thing. The SQL service says, nah, only one of you can use it. There are different ways to use SP get app lock. Like, you can… It’s weird because you can take shared locks and you can take intent locks with it.
And that’s like, okay, but… Then other things can use it. So…
I’m not 100% sure on the usage of the shared and intent locks there. But you can take update or exclusive locks that will block other things from doing it. So, really, it’s to help… So, like, you know, thinking about the example that I gave, I didn’t flesh it out enough, but I wanted to keep the video short.
But think about a situation if you have a store procedure that you only… Like, you don’t… Like, the typical scenario, you have a big, long store procedure where you do begin tran and you do a whole bunch of work in between here and there.
You update a bunch of tables, you get data from a bunch of tables, you modify stuff, you go through, and you hold locks on all those objects the entire time that you’re doing begin tran. With SP get app lock, you can… Like, you can…
Like, say… You can serialize that process. So, you can say, only one of these store procedures can run at a time. And that has the same effect of you… But without all the crappy, like, table locks that hold on from begin tran to commit or rollback, you can just say, you can’t use any of this code until this code is finished.
So, it’s great for serializing a process rather than serializing data access. So, you can serialize the entire process. You can say, no other process can come in and use this process until this one completes because if another process comes in and uses something in here, then they could mess each other up.
It could be like a weird race condition. But you can say, nope, you can go in here. Like, this proc can run and can modify a thousand tables without having to hold begin tran and commit locks on all those thousand tables.
And another process will just be like sitting there waiting, like, well, I’ve got to wait for this one to finish. But that sucks because then, like, every other query in the database, you might be walking, like, a bunch of tables in that procedure. And you would hold that from, like, everyone else.
When really all you want to do is isolate, when really all you want to do is serialize code rather than serialize everyone’s access to the underlying objects. Let’s see. Paul says, so, seriously on the spool thing, would you say spools were a sign that SQL Server is trying to help a fundamentally poor query?
Are they useful at least as a light red flag? Yeah. I mean, I get why they’re there. And in certain conditions, they can help sort of batch work in the same way that cross-supply can help batch work.
Where you were like, so, like, in an example that I’ve seen where union versus union all going into a spool was much, much different. Or rather, where the spool broke down, like, the size of a sort. So, without the spool, you would have to sort, like, a kabillion rows at once.
But with the sort, much like with cross-supply, you can break that sort into smaller chunks. So, spools can certainly be helpful. I’ve never – well, actually, yes, I have.
I have condemned all spools for all eternity. But here’s the thing. I think eager index spools specifically are a huge red flag and should always be looked at. Whether you end up creating an index or just decide to live with them based on the size of the spool and its effect on the plan, because remember that those spools are built serially, or if you’re constantly building gigantic index spools, like, 8, 9, 10 million rows every single time, or if they’re, like, a bunch of them in the plan and they all have the same sort of, like – and they all have the same index definition in the spool, then it’s really, really worth going after them.
But with table spools, it’s a little bit different. With table spools, it’s a sign that you have something to – you have something to test. So table spools, well, you know, they may very well be better than the alternative plan that you get without the spool.
Fixing the condition that caused the spool is really what you should be going after. So there’s, like, three levels of bad. There’s the spool plan without the spool that would suck.
There’s a spool plan with the spool that does better than this one. But then the third one is the plan that never needed a spool to begin with. And that, that’s where you make your money.
I listened to a DMX interview before I was on here. So if I bark at you, I’m sorry. Peter asks if SP AppLock is just queuing by another term.
Yeah, it’s a lot like queuing. It’s a lot – yeah, it’s a lot like being able to queue a process without having to, like, use a queue table or, you know, set, like, weird isolation lever or something like that. Paul says, AppLocks are underrated and underused.
I agree. When I first saw them, I was terrified by them because I didn’t understand them at all. But this – I mean, it was a long time ago. Since then, I’ve gotten a bit more – I’ve gotten much, much cozier with them. And I feel like they’re cool.
Like, when I first saw SP GetAppLock, I totally misunderstood the purpose of it. I thought that GetAppLock was a way to preemptively take a lock on an object. I didn’t understand that it was, like, this just imaginary resource.
And I was like, oh, my God, you’re locking a table before you need to lock it. What are you doing? But then, like, I read about it. And I was like, oh, that makes sense. That’s pretty cool.
Let’s do that. Yes, I like it. Let’s see. Paul says, eager index pools are often a sign of a missing permanent index but not always. Yeah. And, you know, there are times when you can’t add that index. You already have – I’ve seen cases where, like, you have these gigantic index pools on tables with, like, 30 indexes on them already.
And I’m like, well, I mean, clearly you chose poorly. But before we go and add that 31st index, let’s roll some of these back. Let’s get rid of some of these.
Let’s do some work first. Let’s see. In a similar vein, one could ask if sorts were always usually bad. Well, you know, like most things in life, size is everything.
A small sort is pretty cool. A big sort, less so. An undersized sort, even more so.
So, like, so I think, like, what gets me down about sorts, like most things, is what comes down to parameter sniffing. Right?
So, like, you get a little plan that, like, really underestimates the amount of work a sort is going to take. And then you get a big plan where that sort is going to get put to work. That’s where sorts really suck.
A lot of people don’t know to look for sorts in very specific circumstances, too. You know? Like, like the whole thing with windowing functions or window functions, whatever you want to call them.
If people were, like, like, like really on top of when sorts can backfire terribly, I wouldn’t worry so much about them. But then I see people who are, you know, generating a row number over, like, millions and millions of rows without a supporting index.
And they’re like, slow. Window functions are slow. SQL service sucks. Like, man, you didn’t even try. Forrest asks, oh, wait, let’s see here.
You have horror stories about abandoned app blocks. Sean likes to make stuff up so he can sound like he does something at work. Don’t.
Don’t take, don’t think too much of that. He said VMware, too. I don’t trust that. Abandoned transactions in VMware. The hell does that have to do with the other thing?
Like, like someone started creating a VM and then quit? No. Let’s see here. Farah says, so what operator is the strongest indicator of inadequate design and indexing? Left join.
No. That’s a good question. It’s hard to pick a favorite.
I think, you know, the eager index pool is probably the chief amongst them because SQL server is literally creating an index for you. It’s like every time that query runs, it’s like, no, no, here’s an index dummy.
Look, I got one for you. You missed it. I got it. You screw that up. So I think, I think the eager index pool is going to have to win that just based on the fact that there’s like an actual factual index creation in the plan every time it runs.
Sean lives in a state that legalized weed. Yes, but he is allergic to weed. So it does him no good.
Paul says the knee jerk reaction would be scans and hashes. That’s a good point. That’s a good point.
Most people really don’t pay much attention to what their index is until they see an index scan and then they, then they lose their damn minds. Hashes I agree with to a certain extent, but you know, sounds like an OLTP problem to me.
Good. You’re worried about hash joins. You got OLTP problems. Other than that, let’s see, you know, sorts could be one of them.
Ooh, sort merge. So like if you have a, like if you have a plan where SQL Server is like consistently doing like a sort to support a merge, merge join, I think that’s another good example that you’ve done something weird to your indexes because, you know, that sucks too.
SQL Server is choosing to inject a sort operator, much like it, like it chooses to inject an eager index spool in order to support a specific join operation. So you could, you could go, you could, you might be able to go so far as to say that a sort before a stream aggregate might be another sign that a SQL Server is asking you for an index.
Paul says the most reliable sign is probably queries that take longer than is acceptable. Yeah. But that could, that could go beyond indexing. That could be a situation that I’ve seen a few times where, um, app designers didn’t know that you could have a where clause that would prevent all the rows in the table from being funneled to an application.
I’ve also seen where people just didn’t know how many rows were getting sent out. This is sort of like weird, like, I don’t, I don’t, I don’t, I don’t even know what to call it.
I don’t, I don’t think it’s a misunderstanding. Josh says the entity framework, entity framework is certainly guilty of it, but regular, like, so, you know, and I think entity framework is, at least makes things, makes things like the top operator accessible to developers who otherwise would have no idea to use a top, top when they might need one.
But aside from, like, you know, queries that I’ve seen that just were straight up missing a where clause, uh, some people just don’t know that now 500,000 rows are getting shoveled off to the application.
Some people just don’t know that their application, that it’s not SQL Server that’s being slow about it, that it’s their application consuming those 500,000 rows. It’s, that’s slow.
So there’s stuff to, there’s, there’s stuff in there that, that indicates a design problem that’s not necessarily a SQL Server design problem. Also, if I could do one thing to help the world, it would probably be to limit the textual size of queries.
Yeah, I’m, you know what? That, that’s a good idea. A lot of people, when they start writing a query, they, they fall in love with the idea of doing it all in one fail swoop.
And that, the longer and crazier those queries get. I saw, I saw someone the other day who chained, God, like eight or nine CTE together, was joining the, the CTE inside the other CTE.
And then finally had like this other query that joined the results of those CTE and other ones. And it was just like, the plan just never, like it, it took plan explorer. I want to say about eight minutes to render the plan.
It was like unreadable. I’m like, why? You don’t need to do that to yourself. Like, and, and, and, and the, and the other, the other terrible thing about that is, you know, people will, inside a CTE, come up with this completely, this, like the string of like non-sargable calculations.
And that’ll be like the basis of the where clause in the next CTE. And it’s like, man, you ain’t helping no one. Like, that’s just, that’s just messed up that you’re asking SQL Server to do all that. You know, one thing, one thing that I try to instill in people is that if a, if a query plan is big and confusing to you, the optimizer probably didn’t think that much more of it.
The only way to get smaller plans is to have smaller sets of queries. I’d rather, I’d much rather troubleshoot like 20 small query plans than one gigantic query plan.
The more we can break this stuff up, the better. At least I think so. I’ve had very good luck breaking things up into smaller chunks of logic. I’d much rather fight with SQL Server over like the plan choice of a plan that has like two or three joins in it than the plan choice of a query that has like 40 joins in it.
Because it seems, it seems to me like I could, I could, I could have more of a say in what SQL Server is going to do with a two or three join query than I could with a 30 or 40 join query.
That just, that, that just seems like common sense to me, but you know, I don’t know. I’m just a bouncer. What do I know about this stuff anyway? Josh says, I’m thinking of when people accidentally use an eager operator and their EF prior to adding the where part.
So the query runs and then they filter in memory. Ooh. Ooh. Why would you do that to yourself? It’s an in-memory filter. I’ve seen people do that with paging queries though.
And I’ve seen them specifically do that with paging queries where let’s say you have someone selecting column one and column two, but then you need to like filter or order by column three.
And their choice would be to read everything into memory and sort by column three or do like, or like sort and or like filter on column three or, or do that in the database. When they do it in the database, SQL Server becomes painfully slow and unusable.
But when they do it in any framework, SQL Server seems to be okay, but it’s slow, but only because of the app servers. And it’s a lot cheaper to give more CPUs to an app server than it is to give more CPUs to SQL Server.
Imagine a world where we could give 24 cores to SQL Server for the same, for the same amount of money that you can give 24 cores to an app server. Or you can, you can give 512 gigs of RAM to an app server for free without, without the, that magical sprinkling that enterprise licensing does to your CPUs where it makes the same damn CPU that’s worth $2,000 in one place or $7,000 in another place.
Licensing is magic, magic, magic. Let’s see. Josh says, I think Stack Overflow mostly uses a micro RM called Dapper.
I have no idea. Paul says, what’s your view of columnstore batch mode adapt, adoption rates? Too small.
I mean, 2019 is going to make the batch mode stuff quicker, but columnstore is still going to be, I think, pretty niche, unfortunately. That’s why Joe can never get another job. Only like three people in the world use columnstore.
Professionally. No, I wish, I wish there was more of it. I wish I saw more. I frequently work with people who I see would benefit from it, but they’re on like 2012. Well, good news and bad news.
Bad news is you’re going to, you have, you have an upgrade project to complete. But, you know, I wish there was more. I am vaguely hopeful that 2019 will see improvements to batch mode that prior versions haven’t seen.
Specifically to batch sorts. And, and, and more operator adoption. Of.
Batch mode processing. I swear to God, if, if I ever see batch mode nested loops, that’s not just a bug in the plan XML. Someone at Microsoft is getting a fatal hug. Like that would be, that would be crazy.
See, Paul says, you’re absolutely right about tuning small queries. Even if you eventually combine some of them into a larger query in an informed way, it’s a better approach. Yeah.
You could even stick them in a CTE with a top to make them optimizer proof. Right? Sorry. Peter says, Nick Craver was all over EF Core on Twitter a while back.
Guess I just assumed. I think he’s doing .NET Core. I don’t think EF Core is necessarily what he’s talking about. But I don’t know.
I don’t know specifically. The only time Nick has actually responded to me on Twitter was when I posted the thing about the Windows key in period and Management Studio. And that was just to call me a monster.
Paul says, as long as top uses parentheses, I’m happy. Yes. You love to give your top expressions hugs. You’re a very, you’re a sweet and tender man.
And I hope that if there’s one thing that comes out of this webcast sponsored by a roll of athletic tape is that the world knows that Paul White is a sweet and tender man who deserves many more cases of wine than I could ever send him. But I’ll try. I will die consulting.
I mean, I’ll die consulting, trying to. Yeah, you get it. That thing. Yes. Heart with.
And you know what? I think that’s the perfect message to leave this webcast off on. Paul’s select top heart message. So thank you for coming. Thank you for putting up with me. And thank you to this roll of athletic tape that fell on the floor for sponsoring this week’s webcast.
See you next time. Amen.
Video Summary
In this video, I decided to take a break from the usual technical content and share some of my more… colorful experiences. As you might have noticed, things got a bit chaotic as we went along—lost rubber bands, dropped microphones, and an unexpected vocal change or two! But amidst all the chaos, there were some interesting discussions on topics like SQL Server 2019, NoSQL databases, and the differences between consulting work and working for a single company. I also tackled some more technical questions, such as troubleshooting DB mail notifications and discussing page splits in indexes. It’s been quite an adventure, and while it wasn’t entirely smooth sailing, it certainly kept things interesting! If you’re curious about how SQL Server 2019 might impact your work or just want to hear a bit of off-the-cuff tech talk, this video is for you. Enjoy the ride!
Full Transcript
Oh, Jesus, live. Live, live, live. Live, live, live, live. Live. I was to do the thing where I put the browser. No, in the middle. I just shrunk my whole thing down. That was terrible. There we go. There are people. Hello, people. Why did you keep shrinking? Stop shrinking. Stop shrinking. I don’t want you to shrink. I just want you to move. Keep doing it. Oh, you dumb dummy. Yes. Yes. The joys of moving a window and windows. Slowly, slowly. If you do it too fast, it’ll shrink. If you do it too fast, it just shrinks. Oh, I did it too fast. Sucks. Whatever. This is good enough. This’ll work for me. Three of you, huh? Three. I shaved just for you and there are only three of you. Monsters. Monsters. Two of you now.
Hmm. Hmm. Hmm. Hmm. Hmm. Oh, now we’re back up to three. Yes, Julie, you are the best of the best. I don’t care what the other two people here say about you. You’re the best. I hear, uh, Paul White’s supposed to show up today to try to make me apologize to spools, but I’m not going to do that. He’s going to be very disappointed. Now there’s four people here.
When, when we hit, Oh, five people. All right. Five people. So now I have to announce this week’s sponsors. Um, no, I’m not apologizing. This pools. You’re curmudgeon. So this week, uh, this webcast is sponsored by two red rubber bands and a roll of athletic tape that has some stuff on it.
Cause it fell on the floor. So I want everyone to thank our generous and gracious sponsors. Uh, I don’t get paid to apologize to spools. I’ll never apologize to those things. How about, how about this? How about you get someone to apologize to me for entity framework? And then I’ll apologize to spools. Because that’s the only way that’s going down. Cause every time I see a spool in an, any, any framework query, it is the pits.
Don’t I get paid for fixed? I wish I got paid by the spool by this, by the corrected spool. I would, I would, uh, I would be having a much, much nicer office. Okay. I could stop being in a gang. It would be awesome. It’s all sorts of things in my life that would, that would, that would improve. Yeah. Well, you know, the check would be in the mail.
Yeah. So Josh, you apologize for entity framework. All of it, the entire history of entity framework. I want you to apologize for. By the way, don’t let, don’t let these characters overwhelm you. If you have an actual SQL Server question, you’re not just here because you’re on drunk New Zealand time and you want to harass me.
Please ask them. Paula is down to 1% of a wine bottle left. Isn’t today sponsored by blue badges? These blue badges? Cause these are the only blue badges I have.
Today is sponsored by lazy New Zealand couriers who apparently can’t deliver anything within five days of getting a package. It’s a good time. Always fun. It’s just too much traffic, too much traffic.
No one can show up. Boy. Oh boy. All right. Well, someone, if someone doesn’t ask me, if it’s just me arguing with Paul about spools is everyone’s going to be really bored. So Paul asks, have you ever globally enabled trace flag 8690 at a customer?
And I’m this close to doing it this close. And when I do, I’ll have the last laugh and you’ll be apologizing to me for defending spools because they’re indefensible. Indefensible. Indefensible. Except in that one query. Indefensible.
Darren says, why are front squats so much harder than back squats? Cause you don’t do as many front squats. If you did more front squats, they would get easier.
Then the mechanics of a front squat are kind of weird. Like doing the hold or the hold like this and different back angle, too. You have to stay much more upright with a front squat.
Tough to stay that upright. Keep the head in good position. Back squats. Natural. Kind of a natural position to be in. Front squats just feel weird.
I’m going to… Excuse me. There’s apparently a truck race going on outside right now. But Paul just apologized to me.
So I’m going to pause to take a screen cap of that. There we go. First time for everything. I also have a screen cap of the one time Paul said he thought one of my jokes was funny.
I have that. I have that hanging up on my wall. I got a big head of it. Paul’s big head with a bird bubble.
It’s an incomplete sentence. Incomplete sentence. Oh.
You know, actually, I just got a very smart advertising email from a cab company, a cab app called Arrow, wishing me a happy St. Patrick’s Day. And you know what that means?
As they’re getting ahead of the drunk crowd saying, don’t drive to the bar. Call Arrow and be drunk and abusive to one of our drivers on the way home. That’s a good idea.
I’m sorry Paul doesn’t understand why spools are bad. That’s what makes me sorry. A guy as smart as Paul, a guy as good looking as Paul, sees a spool and falls in love with it.
Sad. Very sad. Makes me upset. Paul is in love with this thing so far below his league.
Bums me out. Paul came here to win. No, he didn’t.
Paul came here because he’s drunk. And it’s New Zealand Friday. I don’t even know what time. So you can only imagine how many empty bottles there are. Storn about his bare feet.
Do, do, do, do, do, do, do, do, do, do, do, do, do. Flipping around 5.08 a.m. You madman.
It’s a good thing you don’t have a job either. Otherwise, when would we speak? I’m so glad that we’re both unemployed and we can just hang out at computers whenever we want. It’s the life.
I’m telling you. It’s the life. It doesn’t get any better than that. It’s like every day I wake up in the morning, there’s Paul.
I go to bed at night, there’s Paul. I accidentally drink too much at 2 in the afternoon, there’s Paul. It’s wonderful.
It’s absolutely fantastic. Just two bums hanging out. Doing bummy stuff. Talking about goth music. Paul pretending he listens to the songs I send him.
It’s fun. Josh asked me what I think about Power BI. Nothing. I don’t use it. I’m more of a video guy.
If Power BI starts doing stuff with video, I’d be happy to give it another shot. I’m a video artist. I don’t deal with dashboards and charts and graphs. You know what my problem is?
I’m not in the crowd with the charts and graphs jokes is the real problem. Everyone has these funny jokes that they make that everyone else who deals with charts and graphs immediately gets. It’s about pie charts and bar charts and stacked 3D bar charts.
Everyone else who deals with charts and graphs is like, ha, ha, ha, good one. Yeah, that’s funny. It’s a cute joke.
Me, I’m like, I don’t know. I can’t read that thing anyway. I can barely read an analog clock. Why do you want me to read a chart or graph? You out of your mind? Just tell me what you want to say. Why do I have to sit there and figure out your chart and graph and your X and Y axis and your Z axis and your color coding?
It sucks, too, because this is always like seven graphs in one. Make it simple for a dummy to read. I can’t deal with it.
Darren says, why do NoSQL DB companies always think they have to promote it as a replacement for relational DBs? Because over time, they are adding more and more relational database stuff like transactions and schemas and keys. And NoSQL is becoming SQL so slowly.
I hardly even noticed. Just trickles in over time. Like, oh, yeah, we need to do that. You can’t use NoSQL for that because we need this thing.
And they’re like, oh, we can add that. No problem. And all of a sudden, you’ve just got another relational database. That being said, I’ve heard a couple people who are pretty smart say that Snowflake is really cool as a database. So I don’t know.
I had no angle on that. Snowflake is not a sponsor. Again, this week’s sponsor is some athletic tape. And I think I dropped the rubber bands on the ground.
I’m so sorry. No longer a sponsor. Farah says, what are typical differences in skill sets between those who do consulting work and those who work for a single company? I think it’s less of a technical skill and more of a personal skill.
And that people who do consulting have to be a bit more extroverted and have to get out there a little bit more and make a name for themselves and be a presence and sell themselves and what they do and what they know. Whereas people who get into a company, you know, you have an interview and you just go from there. Like you have that you have one interview and you just keep showing up after that consulting.
You have to keep interviewing every single damn day of your life. I don’t think there’s a whole lot of technical. There are full time people who are, I mean, obviously far more technically proficient than I am who know a lot more about stuff, whether it’s, you know, AGs or whatever.
But, you know, got to sell the sizzle. Sometimes the sizzle is better than the steak. Like if you’ve ever had steakums, you know, sometimes the sizzle is better than the steak.
But selling that sizzle is part of what I think people who do consulting have to do that. People who are full time employees don’t. Like every once in a while you have to like, you know, convince someone in a company that you’re right.
But every time, isn’t this funny? Because every time I see someone technical talking about, talking publicly about trying to discuss something at work with someone who is not technical, they seem very frustrated and they always lose the argument. So that’s a difference too.
Julie says, I have job notifications set up for a SQL agent job. The job fails. No email is sent.
Note, failed to notify DBA support via email is in the log. How can this be fixed? Hard to say from just that. There’s a pretty good blog post from a while back by Kenneth Fisher who had a bunch of queries to help troubleshoot DB mail.
And I think you have more of a database mail problem than a SQL agent problem. At least in my experience when I haven’t gotten emails. It hasn’t been because SQL agent did something wrong.
It’s been because something went wrong with DB mail. And I don’t know. That’s where I started doing my troubleshooting. Paul says, will 2019 make DBAs and consultants redundant?
Gee, I hope so. Because I really want to do something else with my life. I just need an excuse to do it. My wife won’t let me out of this office until we stop getting paid for SQL Server. So, you know, I hope so.
Microsoft doing cool. The thing is that I think that as long as we still have parameter sniffing, there will still be consultants and DBAs around. They got to get on that ball.
So, I don’t know. No, but seriously, like, so what I’ve been doing recently is because CTP 2.3 seems like a pretty good step forward in, like, stuff that’s going to be in RTM. I’ve been taking all my demos that I do right now on 2017.
And I’ve been running through them on 2019. And, like, there’s some stuff that changes and there’s, like, some promising things that happen. Like, some demos.
I’ll put it this way. Like, if you have a lot of, like, through the Ringer demos that you’ve been leaning on for a long time with, like, functions and table variables and multi-statement table valued functions and sort of, like, boring, like, you know, like the real, like, DBA 101 stuff like that, you’re going to have to start thinking a little bit harder when it comes to newer, like, SQL Server 2019. You’re going to have to start thinking about some more interesting stuff that happens in execution plans.
If you don’t, you’re just going to not have material pretty soon. I mean, it’ll be a while before people start adopting 2019 in, like, a meaningful way. But there’s a whole lot of good reasons to adopt 2019 for people who have tough times with workloads, either because they can’t afford to put development time into them or they have a third-party vendor app that is just not going to see any tuning.
Who knows? Like, I see people who… So, like, it’s the craziest thing.
Like, I know people who use third-party vendor apps. The third-party vendor has been out of business for, like, years. And I’m not talking about AdventureWorks.
I promise I’m not talking about AdventureWorks. But, like, people who have, like, third-party vendor apps have been out for years and they won’t change anything. You know, like, well, we can’t change the code and we don’t want to, like, do anything with the indexes. I’m like, well, why?
It’s not like you’re going to lose support. It’s not like there’s a support contract to be worried about. But for those people and people who are in sort of similar situations, you know, 2019 will be nice because you can just plunk stuff in there and certain things that have been awful for years could stop being awful. So it’s certainly something to consider.
Jazz voice at some point. I didn’t mean to get breathy on you there. I just ran out of breath. Let’s see.
Dan says, do page splits really matter? How can I prove, disprove the performance impact of page splits? So the page splits, it’s been a long time since, like, page splits mattered a bit when you were on, again, like, page splits. Page splits to me go right along with, like, index fragmentation where it’s like, you know, if you had, if you have really, really bad, crappy IO, yeah, a lot of page splits could gang up on you.
If you have decent IO, page splits aren’t going to be all that big a deal. What really sucks is the way SQL Server tracks page splits is it counts new pages towards that. So if you just put data into a table, even if a page doesn’t split, if you add a page, SQL Server counts a page split.
It’s kind of weird. And it counts page splits everywhere. People made a real big deal out of it for a long time.
But, you know, anytime, anytime. Anytime someone’s like, I have a lot of page splits, I’m like, good. Good.
You have data. You should have page splits. As far as proving or disproving, I don’t know. Like, if you have an absence of page splits, like, how do you prove an absence of page splits?
If you set fill factor real low, if you set fill factor real low. Say, like, if you set fill factor into, like, 50%, right? So every time you rebuild your indexes, you have pages that are only half filled with data.
Not only your index will be 50% bigger, and all your read queries will have to do twice as much work. But those pages will eventually fill up and split again. Fill factor doesn’t get honored on modification.
Fill factor only gets honored when you rebuild or reorg your indexes. So you’re constantly just having to make more empty space for more stuff coming in. It’s a losing battle.
I would just, I would avoid that battle. I have people making a big deal about why? Why?
What did the page split ever do to them? Let’s see. Peter says, how come you move the time slot back an hour? Did I miss that earlier on? Do you live in a country that doesn’t honor daylight savings time?
Because when I look at my clock, I am still doing this at noon, like every other day. Like every other Friday. Except the Friday I was at SQL Bits.
Every other Friday, though. There’s a Friday I might miss coming up, and I’ll be in Wisconsin. Well, actually, I’m definitely going to miss it, because I’m going to be in Wisconsin. Madison, Wisconsin.
While Joe Obish shakes his head at me. It’ll be fun. Paul says, automatic tuning seems like a promising idea. SQL Server saving multiple plans for different parameter values. Speculatively adding useful looking indexes.
Dropping them if they don’t help, et cetera. Yeah, that does seem interesting. I think my main problem is that it assumes that any one of those plans is actually good. Plus, you know, I’ve seen some of the missing index requests.
But I’ve seen some of the speculative indexes that come out of automatic tuning. And I’m going to be very honest with you in that they don’t seem too much better than what comes out of DTA or the missing index requests that exist now. Even though I’ve been assured it’s a different set of code, they look startlingly similar.
So, it seems promising, yeah. It’s not going that extra step, though. It’s not going to rewrite your code.
It’s not going to test the union versus the union all. As far as I know, it still can’t fix a spool. So, that’s an interesting thing, too.
Is I’ve seen automatic tuning code that does not take really, really big, bad, awful index spools into account when it starts testing things. So, take that. Take that.
Take that, punk. Or what comes out of most of you. Yeah, you’re right. No, that’s totally true. You know, there’s a, what do you call it? It’s a dearth.
Yeah, let’s go with dearth. Because dearth is a good word. There’s a dearth of good advice that comes out of most people. Paul’s advice is always good.
Paul starts with, have you tried restarting it? And Paul starts, and then Paul asks, if you’ve tried getting drunk and ignoring it. And, you know, at some point I would accuse Paul of being American with that attitude.
It’s funny because it’s true. It’s funny and true. It’s the best, best possible outcome. Best possible outcome.
Oh, my God. Oh, my God. We’ve been OMG’d by Mr. White himself. What’s coming next?
By the way, I want to remind everyone that this week’s sponsor is a roll of athletic tape that fell on the floor. Rubber bands are still down there. Rubber bands fell on the floor and didn’t get picked up again.
If I bend over on camera, I might not get back up. It’d be a rough day. Rough, rough day. Let’s see.
Nothing good going on there. Nothing good going on there. Ah. Yeah, I know. Sponsored. Drinking already. Always. If I stop, I’ll just be so shaky on camera.
You don’t want that. You don’t want to see me getting all twitchy and shaky and scratch. You don’t want to see me not drunk. You know, out of your mind, it’s a terrible thing. Drinking already.
I started drinking in 1993, Peter. I think it’s funny. I think you’re funny. Yeah.
I don’t know. A lot of people accused my grandmother of being an alcoholic, but she wasn’t. She only drank in the morning. Good for her. She was able to stop at some point. Mostly by going to bed.
You’re lucky you’re not having to wait for a courier to deliver your next bottle of wine. That’s not, by the way, that’s not a bottle, my friend.
I would never send one bottle of anything. Unlike some other people, I would never send one bottle of anything. I’ll get you set up for a little bit.
You will have like 6,000% wine for a minute. But I have faith in you. I have faith.
Yes. Unlike some people we could mention who send one bottle at a time. I would never do that.
You know why? Because there’s no such thing as one drink. There’s no such… One drink never treated me well. And I’ve never gone out and been like, I’m going to have a drink.
And been like, yeah, that was it. That was it for me. Peter says, can you give some examples of non-physical objects you can lock with SPAPA? So, yeah, so it’s more like a…
You can only… Like, you can lock anything. You can name it whatever you want. It’s an imaginary resource. It doesn’t physically exist anywhere.
It’s just a reference to a thing. The SQL service says, nah, only one of you can use it. There are different ways to use SP get app lock. Like, you can… It’s weird because you can take shared locks and you can take intent locks with it.
And that’s like, okay, but… Then other things can use it. So…
I’m not 100% sure on the usage of the shared and intent locks there. But you can take update or exclusive locks that will block other things from doing it. So, really, it’s to help… So, like, you know, thinking about the example that I gave, I didn’t flesh it out enough, but I wanted to keep the video short.
But think about a situation if you have a store procedure that you only… Like, you don’t… Like, the typical scenario, you have a big, long store procedure where you do begin tran and you do a whole bunch of work in between here and there.
You update a bunch of tables, you get data from a bunch of tables, you modify stuff, you go through, and you hold locks on all those objects the entire time that you’re doing begin tran. With SP get app lock, you can… Like, you can…
Like, say… You can serialize that process. So, you can say, only one of these store procedures can run at a time. And that has the same effect of you… But without all the crappy, like, table locks that hold on from begin tran to commit or rollback, you can just say, you can’t use any of this code until this code is finished.
So, it’s great for serializing a process rather than serializing data access. So, you can serialize the entire process. You can say, no other process can come in and use this process until this one completes because if another process comes in and uses something in here, then they could mess each other up.
It could be like a weird race condition. But you can say, nope, you can go in here. Like, this proc can run and can modify a thousand tables without having to hold begin tran and commit locks on all those thousand tables.
And another process will just be like sitting there waiting, like, well, I’ve got to wait for this one to finish. But that sucks because then, like, every other query in the database, you might be walking, like, a bunch of tables in that procedure. And you would hold that from, like, everyone else.
When really all you want to do is isolate, when really all you want to do is serialize code rather than serialize everyone’s access to the underlying objects. Let’s see. Paul says, so, seriously on the spool thing, would you say spools were a sign that SQL Server is trying to help a fundamentally poor query?
Are they useful at least as a light red flag? Yeah. I mean, I get why they’re there. And in certain conditions, they can help sort of batch work in the same way that cross-supply can help batch work.
Where you were like, so, like, in an example that I’ve seen where union versus union all going into a spool was much, much different. Or rather, where the spool broke down, like, the size of a sort. So, without the spool, you would have to sort, like, a kabillion rows at once.
But with the sort, much like with cross-supply, you can break that sort into smaller chunks. So, spools can certainly be helpful. I’ve never – well, actually, yes, I have.
I have condemned all spools for all eternity. But here’s the thing. I think eager index spools specifically are a huge red flag and should always be looked at. Whether you end up creating an index or just decide to live with them based on the size of the spool and its effect on the plan, because remember that those spools are built serially, or if you’re constantly building gigantic index spools, like, 8, 9, 10 million rows every single time, or if they’re, like, a bunch of them in the plan and they all have the same sort of, like – and they all have the same index definition in the spool, then it’s really, really worth going after them.
But with table spools, it’s a little bit different. With table spools, it’s a sign that you have something to – you have something to test. So table spools, well, you know, they may very well be better than the alternative plan that you get without the spool.
Fixing the condition that caused the spool is really what you should be going after. So there’s, like, three levels of bad. There’s the spool plan without the spool that would suck.
There’s a spool plan with the spool that does better than this one. But then the third one is the plan that never needed a spool to begin with. And that, that’s where you make your money.
I listened to a DMX interview before I was on here. So if I bark at you, I’m sorry. Peter asks if SP AppLock is just queuing by another term.
Yeah, it’s a lot like queuing. It’s a lot – yeah, it’s a lot like being able to queue a process without having to, like, use a queue table or, you know, set, like, weird isolation lever or something like that. Paul says, AppLocks are underrated and underused.
I agree. When I first saw them, I was terrified by them because I didn’t understand them at all. But this – I mean, it was a long time ago. Since then, I’ve gotten a bit more – I’ve gotten much, much cozier with them. And I feel like they’re cool.
Like, when I first saw SP GetAppLock, I totally misunderstood the purpose of it. I thought that GetAppLock was a way to preemptively take a lock on an object. I didn’t understand that it was, like, this just imaginary resource.
And I was like, oh, my God, you’re locking a table before you need to lock it. What are you doing? But then, like, I read about it. And I was like, oh, that makes sense. That’s pretty cool.
Let’s do that. Yes, I like it. Let’s see. Paul says, eager index pools are often a sign of a missing permanent index but not always. Yeah. And, you know, there are times when you can’t add that index. You already have – I’ve seen cases where, like, you have these gigantic index pools on tables with, like, 30 indexes on them already.
And I’m like, well, I mean, clearly you chose poorly. But before we go and add that 31st index, let’s roll some of these back. Let’s get rid of some of these.
Let’s do some work first. Let’s see. In a similar vein, one could ask if sorts were always usually bad. Well, you know, like most things in life, size is everything.
A small sort is pretty cool. A big sort, less so. An undersized sort, even more so.
So, like, so I think, like, what gets me down about sorts, like most things, is what comes down to parameter sniffing. Right?
So, like, you get a little plan that, like, really underestimates the amount of work a sort is going to take. And then you get a big plan where that sort is going to get put to work. That’s where sorts really suck.
A lot of people don’t know to look for sorts in very specific circumstances, too. You know? Like, like the whole thing with windowing functions or window functions, whatever you want to call them.
If people were, like, like, like really on top of when sorts can backfire terribly, I wouldn’t worry so much about them. But then I see people who are, you know, generating a row number over, like, millions and millions of rows without a supporting index.
And they’re like, slow. Window functions are slow. SQL service sucks. Like, man, you didn’t even try. Forrest asks, oh, wait, let’s see here.
You have horror stories about abandoned app blocks. Sean likes to make stuff up so he can sound like he does something at work. Don’t.
Don’t take, don’t think too much of that. He said VMware, too. I don’t trust that. Abandoned transactions in VMware. The hell does that have to do with the other thing?
Like, like someone started creating a VM and then quit? No. Let’s see here. Farah says, so what operator is the strongest indicator of inadequate design and indexing? Left join.
No. That’s a good question. It’s hard to pick a favorite.
I think, you know, the eager index pool is probably the chief amongst them because SQL server is literally creating an index for you. It’s like every time that query runs, it’s like, no, no, here’s an index dummy.
Look, I got one for you. You missed it. I got it. You screw that up. So I think, I think the eager index pool is going to have to win that just based on the fact that there’s like an actual factual index creation in the plan every time it runs.
Sean lives in a state that legalized weed. Yes, but he is allergic to weed. So it does him no good.
Paul says the knee jerk reaction would be scans and hashes. That’s a good point. That’s a good point.
Most people really don’t pay much attention to what their index is until they see an index scan and then they, then they lose their damn minds. Hashes I agree with to a certain extent, but you know, sounds like an OLTP problem to me.
Good. You’re worried about hash joins. You got OLTP problems. Other than that, let’s see, you know, sorts could be one of them.
Ooh, sort merge. So like if you have a, like if you have a plan where SQL Server is like consistently doing like a sort to support a merge, merge join, I think that’s another good example that you’ve done something weird to your indexes because, you know, that sucks too.
SQL Server is choosing to inject a sort operator, much like it, like it chooses to inject an eager index spool in order to support a specific join operation. So you could, you could go, you could, you might be able to go so far as to say that a sort before a stream aggregate might be another sign that a SQL Server is asking you for an index.
Paul says the most reliable sign is probably queries that take longer than is acceptable. Yeah. But that could, that could go beyond indexing. That could be a situation that I’ve seen a few times where, um, app designers didn’t know that you could have a where clause that would prevent all the rows in the table from being funneled to an application.
I’ve also seen where people just didn’t know how many rows were getting sent out. This is sort of like weird, like, I don’t, I don’t, I don’t, I don’t even know what to call it.
I don’t, I don’t think it’s a misunderstanding. Josh says the entity framework, entity framework is certainly guilty of it, but regular, like, so, you know, and I think entity framework is, at least makes things, makes things like the top operator accessible to developers who otherwise would have no idea to use a top, top when they might need one.
But aside from, like, you know, queries that I’ve seen that just were straight up missing a where clause, uh, some people just don’t know that now 500,000 rows are getting shoveled off to the application.
Some people just don’t know that their application, that it’s not SQL Server that’s being slow about it, that it’s their application consuming those 500,000 rows. It’s, that’s slow.
So there’s stuff to, there’s, there’s stuff in there that, that indicates a design problem that’s not necessarily a SQL Server design problem. Also, if I could do one thing to help the world, it would probably be to limit the textual size of queries.
Yeah, I’m, you know what? That, that’s a good idea. A lot of people, when they start writing a query, they, they fall in love with the idea of doing it all in one fail swoop.
And that, the longer and crazier those queries get. I saw, I saw someone the other day who chained, God, like eight or nine CTE together, was joining the, the CTE inside the other CTE.
And then finally had like this other query that joined the results of those CTE and other ones. And it was just like, the plan just never, like it, it took plan explorer. I want to say about eight minutes to render the plan.
It was like unreadable. I’m like, why? You don’t need to do that to yourself. Like, and, and, and, and the, and the other, the other terrible thing about that is, you know, people will, inside a CTE, come up with this completely, this, like the string of like non-sargable calculations.
And that’ll be like the basis of the where clause in the next CTE. And it’s like, man, you ain’t helping no one. Like, that’s just, that’s just messed up that you’re asking SQL Server to do all that. You know, one thing, one thing that I try to instill in people is that if a, if a query plan is big and confusing to you, the optimizer probably didn’t think that much more of it.
The only way to get smaller plans is to have smaller sets of queries. I’d rather, I’d much rather troubleshoot like 20 small query plans than one gigantic query plan.
The more we can break this stuff up, the better. At least I think so. I’ve had very good luck breaking things up into smaller chunks of logic. I’d much rather fight with SQL Server over like the plan choice of a plan that has like two or three joins in it than the plan choice of a query that has like 40 joins in it.
Because it seems, it seems to me like I could, I could, I could have more of a say in what SQL Server is going to do with a two or three join query than I could with a 30 or 40 join query.
That just, that, that just seems like common sense to me, but you know, I don’t know. I’m just a bouncer. What do I know about this stuff anyway? Josh says, I’m thinking of when people accidentally use an eager operator and their EF prior to adding the where part.
So the query runs and then they filter in memory. Ooh. Ooh. Why would you do that to yourself? It’s an in-memory filter. I’ve seen people do that with paging queries though.
And I’ve seen them specifically do that with paging queries where let’s say you have someone selecting column one and column two, but then you need to like filter or order by column three.
And their choice would be to read everything into memory and sort by column three or do like, or like sort and or like filter on column three or, or do that in the database. When they do it in the database, SQL Server becomes painfully slow and unusable.
But when they do it in any framework, SQL Server seems to be okay, but it’s slow, but only because of the app servers. And it’s a lot cheaper to give more CPUs to an app server than it is to give more CPUs to SQL Server.
Imagine a world where we could give 24 cores to SQL Server for the same, for the same amount of money that you can give 24 cores to an app server. Or you can, you can give 512 gigs of RAM to an app server for free without, without the, that magical sprinkling that enterprise licensing does to your CPUs where it makes the same damn CPU that’s worth $2,000 in one place or $7,000 in another place.
Licensing is magic, magic, magic. Let’s see. Josh says, I think Stack Overflow mostly uses a micro RM called Dapper.
I have no idea. Paul says, what’s your view of columnstore batch mode adapt, adoption rates? Too small.
I mean, 2019 is going to make the batch mode stuff quicker, but columnstore is still going to be, I think, pretty niche, unfortunately. That’s why Joe can never get another job. Only like three people in the world use columnstore.
Professionally. No, I wish, I wish there was more of it. I wish I saw more. I frequently work with people who I see would benefit from it, but they’re on like 2012. Well, good news and bad news.
Bad news is you’re going to, you have, you have an upgrade project to complete. But, you know, I wish there was more. I am vaguely hopeful that 2019 will see improvements to batch mode that prior versions haven’t seen.
Specifically to batch sorts. And, and, and more operator adoption. Of.
Batch mode processing. I swear to God, if, if I ever see batch mode nested loops, that’s not just a bug in the plan XML. Someone at Microsoft is getting a fatal hug. Like that would be, that would be crazy.
See, Paul says, you’re absolutely right about tuning small queries. Even if you eventually combine some of them into a larger query in an informed way, it’s a better approach. Yeah.
You could even stick them in a CTE with a top to make them optimizer proof. Right? Sorry. Peter says, Nick Craver was all over EF Core on Twitter a while back.
Guess I just assumed. I think he’s doing .NET Core. I don’t think EF Core is necessarily what he’s talking about. But I don’t know.
I don’t know specifically. The only time Nick has actually responded to me on Twitter was when I posted the thing about the Windows key in period and Management Studio. And that was just to call me a monster.
Paul says, as long as top uses parentheses, I’m happy. Yes. You love to give your top expressions hugs. You’re a very, you’re a sweet and tender man.
And I hope that if there’s one thing that comes out of this webcast sponsored by a roll of athletic tape is that the world knows that Paul White is a sweet and tender man who deserves many more cases of wine than I could ever send him. But I’ll try. I will die consulting.
I mean, I’ll die consulting, trying to. Yeah, you get it. That thing. Yes. Heart with.
And you know what? I think that’s the perfect message to leave this webcast off on. Paul’s select top heart message. So thank you for coming. Thank you for putting up with me. And thank you to this roll of athletic tape that fell on the floor for sponsoring this week’s webcast.
See you next time. Amen.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
There’s a stored procedure called sp_persistent_version_cleanup we can use to clean out PVS data.
There’s some helpful information about it in sp_helptext:
create procedure sys.sp_persistent_version_cleanup
(
@dbname sysname = NULL, -- name of the database.
@scanallpages BIT = NULL -- whether to scan all pages in the database.
)
as
begin
set @dbname = ISNULL(@dbname, DB_NAME())
set @scanallpages = ISNULL(@scanallpages, 0)
declare @returncode int
EXEC @returncode = sys.sp_persistent_version_cleanup_internal @dbname, @scanallpages
return @returncode
end
We can pass in a database name, and if we want to scan all the pages during cleanup.
Unfortunately, those get passed to sp_persistent_version_cleanup_internal, which only throws an error with sp_helptext.
Locking?
While the procedure runs, it generates a wait called PVS_CLEANUP_LOCK.
This doesn’t seem to actually lock the PVS so that other transactions can’t put data in there, though.
While it runs (and boy does it run for a while), I can successfully run other modifications that use PVS, and roll them back instantly.
If we look at the locks it’s taking out using sp_WhoIsActive…
Watching the session with XE also doesn’t reveal locking, but it may be cleverly hidden away from us.
In all, it took about 21 minutes to cleanup the 37MB of data I had in there.
I don’t think this is my fault, either. It’s not like I’m using a clown shoes VM here.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
When Query Store rolled out, there were a lot of questions about controlling the size, and placement of it.
To date, there’s still not a way to change where Query Store data ends up, but you can manage the size pretty well.
Of course, there’s a lot more going on there — query text and plan XML heavily inflate the size of things — so there’s naturally more concern.
What About Version Stores?
When talking about traditional row versioning in SQL Server, via Read Committed Snapshot Isolation, or Snapshot Isolation, there’s always a warning to keep an eye on the size of your version store.
Rightfully so, too. They put their data in tempdb, not locally the way the Persistent Version Store does. That means tempdb size can quickly get out of hand with multiple databases storing their Version Store data in there.
Storing the data locally, there’s far less chance of system-wide impact. I wouldn’t say it’s ZERO, depending on where you put your data, but it’s close to it.
Let’s Make Some Indexes
Classics tho
I wanted to make this realistic — and after years of looking at your tables, I know you get rid of indexes at the same rate I get rid of SQL Server books.
So I went ahead and created 20 of them, and made sure they all had a column in common — in this case the Age column in the Users table.
CREATE INDEX [IX_Id_Age_AccountId] ON [dbo].[Users] ([Id], [Age], [AccountId]);
CREATE INDEX [IX_CreationDate_Age_AccountId] ON [dbo].[Users] ([CreationDate], [Age], [AccountId]);
CREATE INDEX [IX_DisplayName_Age_AccountId] ON [dbo].[Users] ([DisplayName], [Age], [AccountId]);
CREATE INDEX [IX_DownVotes_Age_AccountId] ON [dbo].[Users] ([DownVotes], [Age], [AccountId]);
CREATE INDEX [IX_EmailHash_Age_AccountId] ON [dbo].[Users] ([EmailHash], [Age], [AccountId]);
CREATE INDEX [IX_LastAccessDate_Age_AccountId] ON [dbo].[Users] ([LastAccessDate], [Age], [AccountId]);
CREATE INDEX [IX_Location_Age_AccountId] ON [dbo].[Users] ([Location], [Age], [AccountId]);
CREATE INDEX [IX_Reputation_Age_AccountId] ON [dbo].[Users] ([Reputation], [Age], [AccountId]);
CREATE INDEX [IX_UpVotes_Age_AccountId] ON [dbo].[Users] ([UpVotes], [Age], [AccountId]);
CREATE INDEX [IX_Views_Age_AccountId] ON [dbo].[Users] ([Views], [Age], [AccountId]);
CREATE INDEX [IX_WebsiteUrl_Age_AccountId] ON [dbo].[Users] ([WebsiteUrl], [Age], [AccountId]);
CREATE INDEX [IX_Age_CreationDate_AccountId] ON [dbo].[Users] ([Age], [CreationDate], [AccountId]);
CREATE INDEX [IX_Age_DisplayName_AccountId] ON [dbo].[Users] ([Age], [DisplayName], [AccountId]);
CREATE INDEX [IX_Age_DownVotes_AccountId] ON [dbo].[Users] ([Age], [DownVotes], [AccountId]);
CREATE INDEX [IX_Age_EmailHash_AccountId] ON [dbo].[Users] ([Age], [EmailHash], [AccountId]);
CREATE INDEX [IX_Age_Id_AccountId] ON [dbo].[Users] ([Age], [Id], [AccountId]);
CREATE INDEX [IX_Age_LastAccessDate_AccountId] ON [dbo].[Users] ([Age], [LastAccessDate], [AccountId]);
CREATE INDEX [IX_Age_Location_AccountId] ON [dbo].[Users] ([Age], [Location], [AccountId]);
CREATE INDEX [IX_Age_Reputation_AccountId] ON [dbo].[Users] ([Age], [Reputation], [AccountId]);
CREATE INDEX [IX_Age_UpVotes_AccountId] ON [dbo].[Users] ([Age], [UpVotes], [AccountId]);
Now We Need To Modify Them
BEGIN TRAN
UPDATE u
SET u.Age = 100
FROM dbo.Users AS u
WHERE u.Age IS NULL
ROLLBACK
This’ll run for a bit, obviously.
While it runs, we can use this query to look at how big the version store is.
SELECT DB_NAME(database_id) AS database_name,
(persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0;
Not bad.
The only thing is that it stays the same size after we roll that back.
I mean, the ROLLBACK is instant, but cleanup isn’t.
In the next post, we’ll look at forcing cleanup.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
There was always this big Wompson & Wompson around rollback, in that it was single threaded. If you had a process get a parallel plan to do some modifications, the rollback could take much longer.
ADR doesn’t solve concurrency issues around multiple modification queries. They both still need the same locks, and other transactions aren’t reading from the Persistent Version Store (PVS from here on out).
But they could. Which would allow for some interesting stuff down the line.
Flashsomething
Oracle has a feature called Flashback that lets you view data as it existed in various points in time. You sort of have this with Temporal Tables now, but not database wide. It’s feasible to think that not only would the PVS let us look at data at previous points in time, but also to restore objects to that point in time.
Yep. Single objects.
AlwaysOptimistic
With PVS up and running, we’ve got row versioning in place.
That means SQL Server could feasibly join the rest of the civil database world by using optimistic locking by default.
It could totally be used in the way that RCSI and SI are used today to let readers and writers (and maybe even writers and writers!) get along peaceably.
Avoidable
Happy Halloween
You know those spool things that I hate? This could be used to make some of them disappear.
The way PVS works now, we have a record of rows modified, which means we’ve effectively spooled those rows out somewhere already.
With those rows recorded, we could skip using spools all together and just read the rows we need to modify from here.
I’m Excited!
This is a very cool step forward for SQL Server.
I mean, aside from the fact that it took 15 minutes to cleanup a 75MB version store.
But still! This is gonna help a lot of people, and has potential to go in a few new directions to really improve the product.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
