SQL Server Performance Office Hours Episode 80 – No Sleep Till Portugal

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 80 – No Sleep Till Portugal


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and it is horrible once again here, heat-wise. Yesterday it was so bad that my thing actually disconnected from my phone when I was trying to record. So if the end of that video yesterday was a little wonky, now you at least have a reasonable explanation why. I’m managing, but just barely. I’m gonna take a cold white wine bath, probably Sancerre, Chablis, something like that, and then maybe just drown in it. Like a fruit fly, just drown. Like so many of the fruit flies I have envied drowning in a glass of wine in my life, that’s how I’m gonna go out. Anyway, we are going to finish, or we’re not finished, we still have a long way to go on this. But, the next thing we have up is, uh, answering, draining more of the office hours queue here. So, we’ll start off with, sometimes blocking chains disappear if we raise max stop. That feels backwards. Explain, please. Uh, well, it, most likely it’s just that some of your queries are finishing faster. I mean, uh, like I don’t, you didn’t really describe what, what sort of blocking problems you’re having. If you’re under the default read committed garbage, isolation level, uh, where read queries and write queries still fight with each other, then you, you, it very well could be cases like that where things are, uh, are improving and, uh, you know, you see your, your, your queries finish faster, so blocking goes away. For modification queries, of course, the, the, the, the right cursor portion of the query plan is always single threaded, except for in some limited insert cases. Uh, but, I suppose it’s also possible that the read cursor portion of those finish quicker, uh, with a more wide parallel distribution of rows, and that would explain it, part of it as well. So, that’s, that’s what I would go with there, at least, um, if, if I’m thinking about your question correctly, which hopefully I am. All right. We forced a plan in query store. Ah, this computer’s getting dim. Uh, and performance got worse.

Why would that happen if it was the fastest plan historically? Well, two things could be happening to you. Uh, one, uh, your, your, your plan could, uh, fail to force. That’s, that’s one. And two, uh, it might have only been the fastest plan historically under a certain set of circumstances that, uh, that are not shared by all the other, uh, query patterns, uh, that, that are now forced to use that plan. Uh, that’s something that has happened to me in the past that has bitten me, and it wasn’t fun. Uh, so, uh, so, uh, that’s most likely, uh, a parameter sensitivity issue and not just, uh, this query got a bad plan issue, right? So, uh, that’s what I would, that’s what I would hedge that one on.

All right. Uh, what plan operators usually signal something very wrong is happening here? Well, in a select query, uh, an eager index pool would be the first one that comes to mind. Uh, perhaps, uh, a lazy table spool, uh, would be another one that I would look at. Um, any sort of pattern where you have the constant scan, uh, merge interval thing happen that goes into a nested loops join, especially a serial nested loops join that usually indicates that you have a join with an or clause or perhaps some mismatched data types and, uh, correcting those, which I have many, many videos on would alleviate those plans. Uh, parallel merge joins, uh, order preserving operators, uh, order preserving exchanges and parallel plans.

Uh, that, that would include, well, I guess, sorts, uh, and stream aggregates. Never like to see those. Never like to see those. Uh, that’s, that’s, uh, top above a scan would be another one that I would, I would say rounds out that bunch. Those are, those are my least favorite things to see in query plans right off the bat there. Uh, let’s see. Uh, let’s see. Next up. Why do sorts suddenly explode in memory usage when data grows a little bit? Uh, could just be estimates. Um, could just be some, it could be the, uh, stats updated, uh, along with stuff. Um, cause you know, like data growing a little bit could still be influent, but could still require a lot of modifications to your tables where, uh, you know, you would see, uh, you know, stats get updated automatically. Uh, perhaps a stats update job. Um, yeah, that’s, that’s usually how it goes.

Uh, but you know, the thing that, that, that usually drives, uh, sort, uh, memory usage, you know, aside from the number of rows that you’re, uh, that you’re dropping, that you’re putting through the sort operator, estimating to put through the sort operator is of course the width of those rows. And I’m not saying that anyone has gone and changed the width of your string columns, but sometimes adding, uh, just a few, like, uh, like a more rows to the cardinality estimate will also serve to inflate, uh, the size of the data. The SQL Server estimates, uh, will be, uh, passing through the sort and could make memory usage, um, will explode to some degree.

All right. Last question here before I go melt into oblivion in my bathtub. Uh, is it possible? I hate the doors. I don’t want to die like Jim Morrison. Gah. All right. Forget that. It’s not going to happen. I’m going to figure out a different way.

Uh, is it possible to tune around terrible cardinality estimates or do you usually rewrite queries instead? What does tuning around terrible cardinality estimates infer if not rewriting the queries? Uh, you, you, you have asked a tricky question. Um, so like, I mean, I guess tuning around, I don’t know.

Are you updating statistics? Are you, are you, uh, are you, uh, creating indexes or creating statistics of some variety? Uh, are you forcing different cardinality estimation models? If not, if you’re not doing any of those things, I don’t know what tuning around this means if you’re not rewriting the query.

Uh, but you know, for me, terrible cardinality estimates, um, if you can, if you can root cause the terrible cardinality estimate, um, perhaps it is in outdated statistics issue. Perhaps it is, uh, the cardinality estimation model you’re using an issue there. Uh, perhaps you are doing something non-sargable or, uh, using some query form like a local variable or table variable or even table value parameters, which, uh, mess cardinality estimation up in slightly different ways. Uh, that was an all B possibilities as well.

Uh, and you know, uh, maybe you’re using a common table. A CTE, a popular blog, uh, table, uh, topic this week, uh, CTE not being so great. Uh, and perhaps, uh, you have some very complicated query in there and perhaps, you know, dumping stuff to a temp table is many SQL Server luminaries have suggested over this past week.

Uh, could be a viable exit from your terrible cardinality estimation problems. But, uh, your question, uh, as it stands, it’s quite an odd bird, right? Because I don’t, I still don’t quite get, how do you tune around terrible cardinality estimates?

Or do you usually rewrite the queries instead? That is going to twist my melon for the remainder of my days, which as soon as I figure out an alternative to drowning in a bathtub of wine will, uh, be shortly numbered.

Anyway, uh, the heat is getting to me. There’s a, there’s a moped gang going by and, uh, I, I, I have to go do things. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And I’ll see you in tomorrow’s video, which will sadly be my last one from Paris. And, uh, we’ll be, uh, uh, I’ll be picking up next week in, in sunny Portugal, which we’ll see, we’ll see how hot that is and how much my, my will to live is diminished by that heat.

All right. Thank you for watching.

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.

Related Posts