SQL Server Performance Office Hours Episode 19
To ask your questions, head over here.
| Tell me about the perils of using SNAPSHOT for writes. Is learning how to deal with conflict detection really worse than the pains of pessimism? |
| Why SqlServer doesn’t have parallel rollback ? |
| How can you be for SORT_IN_TEMPDB and for Accelerated Database Recovery? If tempdb is good for performance, then isn’t ADR bad for performance? |
| Your experience says to prefer the legacy cardinality estimator. What is fundamentally wrong with the new estimator? |
| You previously discussed why you use SORT_IN_TEMPDB. Would you default to that in Azure SQL Database given the lack of control over tempdb (including sizing) in that product? |
Video Summary
In this video, I dive into answering five questions submitted by my YouTube community during an Office Hours episode. We cover a range of topics, from the practical aspects of using Snapshot isolation in transactions to the more technical question about why SQL Server doesn’t support parallel rollbacks. Additionally, we explore the differences between the legacy and new cardinality estimators, discussing their performance and personal preferences based on testing. The session also delves into the use of Sort operations and TempDB in Azure SQL Database, weighing the pros and cons despite potential limitations. Whether you’re a seasoned SQL Server professional or just starting out, there’s plenty to learn from these real-world questions and my insights.
Full Transcript
Erik Darling here with Darling Data and, well, we are background folk again, aren’t we? We’ve got a dumbbell, barbell behind us. It’s not a dumbbell. Dumbbells don’t bend like that. Barbells bend like that. And we’ve got a Darling Data logo, so that must mean we are doing another Office Hours episode where a helicopter of some sort. Hopefully it’s not a drone strike. Microsoft’s finally after me. Anyway, we’re going to do an Office Hours episode where I answer five entire questions submitted by you, my adorable users. If you want to submit your own question, you can go to this link, which is down in the video description. If you would like to support my channel, if you’re like, wow, this man deserves to get paid for all the work he does, well, you can do that also down in the video description. If you don’t feel like I deserve to get paid, maybe I am well deserving of a like or a comment or a subscription to the channel. I don’t know. I think we’re up to still around 60 paid subscribers and a little over 7,000 paid members and a little over 7,000 unpaid subscribers.
So I think a few of you might like the channel. All right. If you need help with SQL Server, health checks, performance analysis, hands-on tuning, dealing with performance emergencies and whipping your developers into shape so you have fewer emergencies. Well, I happen to be pretty good with a whip. Just saying. Doesn’t have to end there. If you would like to buy my performance tuning content, you can get all 24 hours of it for 75% off. That is about 150 USD and that is for life or 8 life. You can do that with the link down in the video description. And of course, I have a new T-SQL course with me, Eric. Ignore that. I need to fix that at some point. I’ll remember to do that someday. Funny story with Podia. But anyway, it’s on pre-sale price now. 250 bucks. It’s going to go up to 500 bucks when the advanced material drops after the summer. If you’re attending Kendra and I’s past pre-cons, you get access to all of the content here with the price of admission. So that’s a nice deal for you.
If you want to catch me live and in person, I will be at all three of the Pass On Tour events. That’s New York City, Dallas, and Amsterdam taking place August, September, and October of this year. And then, of course, at Past Data Community Summit taking place in Seattle in November of this year. So you could see me four times this year if you were really ambitious.
I don’t know. That might be too much for both of us. I know how social you people are. Let’s not push it. Anyway, let’s go answer some of these questions. Has anything ever been less useful than the Dropbox badge that shows up here? Like, there’s nothing useful about that. Nothing useful about that has ever happened.
All right. Anyway, let’s start here. That’s right at the very top. Let’s see. One, two, three, four, five. All right. Five questions. Tell me about the perils of using Snapshot for rights. Is learning how to deal with conflict detection really worse than the pains of pessimism?
Well, you know, it does depend a little bit on your, I guess, skill and comfort as a developer. Like, the main peril for, like, using Snapshot for rights is you get errors if you try to update, if two queries try to update the same thing in a Snapshot transaction. If you are cool enough with dealing with those errors, then it’s not a big deal.
You know, of course, the pains of, you know, non-snapshot rights can be, you know, under most isolation levels be like lost updates, right? Like, you know, like, like one query could do something and like another query could immediately overwrite it and that’s not a good time. So, really, it just, you know, it’s just kind of like picking your poison.
Like, like, what’s a bigger problem? If you’re cool with dealing with the errors that come along with the, like, like right conflicts, then cool, go with it. I’m not going to try to talk you out of it.
Me, personally, you know, really depends on, like, for me, you know, it’s more of like an application, like, like expectation issue. Like, like, like, like, like what, what, what would make an, what would make most sense to the end user? Like, what, what is the most sensible end result of two queries trying to update the same thing?
Is it one query failing or is it one query overwriting what the other query just did? Like, really just comes down to that for me. Let’s see here.
Why SQL Server doesn’t have parallel rollback? I don’t know. That’s Microsoft. Do I look like Microsoft? I can’t tell you these things. They didn’t, they didn’t implement it.
It’s doable. I don’t know why. Maybe it’s hard or something. I don’t know. Why don’t you go work for Microsoft and put it, write it into the product if it means that much to you. All right.
How can you be for a certain 10 dB and for accelerated database recovery? If 10 dB is good for performance, then isn’t ADR bad? Are you drunk?
These are… What? Huh? This doesn’t even make sense. I can’t answer this.
This is… It’s mind-blowing. Anyway. Ah. I’m just going to forget that. I’m going to start drinking after that one. Maybe I am too sober to answer that question.
I should get drunk and try to reread that one. Your experience says to prefer the legacy cardinality estimator. What is fundamentally wrong with the new estimator? Well, fundamentally, it doesn’t estimate things as well most of the time.
You know, like when I’m writing demos for, you know, my classes and, you know, for my videos and all that other stuff, you know, I always give both estimators a chance to see which one does a job that, you know, I am happier with. And just probably like 75, 80% of the time, it is the legacy cardinality estimator that does the better job. The default cardinality estimator, or as Microsoft calls it, I don’t call it that.
I just call it the new one because that’s all it is. It’s new. Most of the time, the new one, meh, just doesn’t do it for me.
It’s either like a guess that’s close enough to legacy or it’s a guess that’s way wronger, way more wronger-ish than legacy. So, you know, I don’t have any specific things to, like, show you these differences. It’s just, you know, just a general testing that I’ve found.
It’s just not quite as good. You previously discussed why you use Sort and TempDB. Would you default to that in Azure SQL database given the lack of control over TempDB, including sizing in that product?
Yeah, I think I still would, or rather I still do. I don’t really see a need not to. Honestly, I can’t think of a good reason why either of the things you mentioned would prevent me from doing that.
You know, like, the stuff, the limit, the TempDB limitations that I really care about have nothing to do with that. Like, I think both Managed Instance and Azure SQL DB, like, neither one of those still allow for the in-memory TempDB metadata, and sorting in TempDB would have no effect on that.
If TempDB performance, like, if you test it and you find TempDB performance is worse for creating indexes or whatever you’re doing with indexes when you sort in TempDB, then certainly stop. But, like, for me, from just, like, a general, like, I’m going to create this index perspective, I would still prefer to sort in TempDB, regardless of the locality of my database, unless testing proved otherwise.
There may even be times on-prem when a sort in TempDB would be like, hey, why is this slow? I don’t know.
TempDB is created on, like, an old pile of boar’s head Swiss cheese. It’s on some rye bread and salami in there. It’s like, I don’t know.
TempDB sucks, don’t go there. If it’s okay, go there. You know? It’s like, again, it comes back to, like, the public restroom metaphor for TempDB. If you open the door and you don’t like what you see, close the door.
All right? Walk away. Go pee behind a tree or something. Anyway, thank you for watching. Thank you for sending in questions, by the way.
I hope you enjoyed yourselves. I hope you learned something. For the person who asked the question about accelerated database recovery, I hope you have sobered up by now. Perhaps you could restate that question in a way that a sober person could understand.
Not that I’m sober. I’ll, like, permanently. Just when I do these, I tend to be. So perhaps you’re just on a different wavelength there. Anyway, thanks for watching.
I will see you in another video. Doing another thing, I suppose. Makes sense then. All right. Cool. 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.