ICYMI
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.