SQL Server Performance Office Hours Episode 28

How often have you dealt with corruption? When is the last time you saw it?

Have you ever met a query you couldn’t tune?

Which database will win the database wars?

What are the hardest kinds of queries to write?

When will Joe Obbish be back for a podcast?

Video Summary

In this video, I dive into answering five of your most pressing questions about SQL Server and beyond during another exciting Office Hours episode. I cover a range of topics from dealing with corruption to the future of database management systems like DuckDB and Postgres. Whether you’re curious about tuning queries or wondering which database might win in an imaginary “database wars,” this video has got you covered. Plus, I share some upcoming tour dates for my consulting services, including stops in New York City, Dallas, Utrecht, and the eagerly anticipated Past Data Community Summit in Seattle. If you have any questions of your own or want to support the channel, there are links in the description to get involved.

Full Transcript

Erik Darling here with Darling Data, and we are in the midst of another wonderful Office Hours episode coming from me to you, in which I answer five of your most pertinent, important, burning questions about SQL Server, life, love, the world around you, mysteries of the universe, anything that you’re interested in. If you would like to ask your own questions for Office Hours, there is a link right there to do that. If you would like to support the the channel, there is a link, well actually both of these links are down in the video description. If you’re feeling very kind, and you want to ask me a question, or you want to sign up for a membership, you can do either one. If you need help with your SQL Server, if performance is just not what you always dreamed it would be, I am available for consulting as a consultant to help you with those problems. Health Checks, Performance Analysis, Hands-On Tuning, Dealing with Performance Emergencies, and of course, Developer Training. And as always, my rates are reasonable. Performance Tuning Training, about 150 US bucks, about 75% off there. That link down in the video description, and you will get all of that content for life. Speaking of for life, my T-SQL course is, well, all the beginner content is done, which means it is somewhere near half done. The advanced material, of course, will be bigger than the beginner material, because there is much more, to say in the advanced material, but 23 hours of beginner content is out there now. It is 250 bucks at the pre-sale price, and that price will be going up, doubling the value of the course after the summer months. The summer months, of course, will be busy times for your friend, Erik Darling from Darling Data. Red Gate is taking me on tour across several points of interest across the globe. New York City, August 18th and 19th.

Dallas, September 15th and 16th. And Utrecht, October 1st and 2nd. And then, of course, the event that we’ve all been waiting for, Past Data Community Summit in Seattle, November 17th to 21st. But with that out of the way, let’s do these office-y, hour-y questions here. Let’s see. Let’s make sure that’s framed up nice for everyone. We don’t want anyone to have to struggle to read these things, do we? More importantly, we don’t want me to struggle to read these things.

How often have you dealt with corruption? When is the last time you saw it? Hmm, geez. So, I don’t deal with corruption a lot. It’s not really up my alley. You know, I am a performance-tuning person. I enjoy performance-tuning.

I do not enjoy things like corruption, high availability, disaster recovery, backup, stuff like that. I can, you know, I can work with it a bit, but it’s not, those are not things that I care deeply about. So, I have dealt with corruption a few times while consulting.

You know, most people are surprised when we come across it. You know, some of the initial analysis that I do when I work with people does, like, look at things where, you know, corruption might be popping up, cropping up, hiding, and where they just might not, like, be able to see it. So, you know, like, use SP Log Hunter and look at the error log.

Use SP Health Parser and go through the System Health Extended event. And then, also, I look at the Memory Dumps DMV. And sometimes, you look in the Memory Dumps, and the Memory Dumps will also indicate data corruption.

But it has been a while since I saw it. I don’t see people hitting data corruption issues so much. I think, you know, disks have become, at least to my mind, sufficiently resilient that corruption is not as common of an issue as it used to be.

I, you know, I don’t have any experience with that aside from the work that I do. But, you know, I do run across people who have not run DBCC CheckDB ever. And, you know, maybe they have gotten at least, I don’t know, maybe very lucky that they have no corruption.

But it’s just not something that I run into a lot. Let’s see here. Have you ever met a query you couldn’t tune?

Well, I mean, yes and no. There are certain, let’s just call them pathological issues with an environment that make some queries much more difficult to tune than others. You know, a lot of the times, if you are not allowed to add indexes, then tuning a query becomes a little bit more difficult because, you know, like a lot of the sort of logical rewrites that you do would depend on having some sort of reasonable index around that would allow you to access data in an efficient way.

If you are dealing with tables that are very big and don’t really have indexes on them, that becomes a little bit more difficult. But, you know, as far as just like, like, have I ever looked at a query and been like, I have no idea how to fix this? No, that’s, you know, that it’s been a very long time since I’ve had that happen to me.

But I’m trying to think. Yeah, I mean, really, it’s just, you know, dealing with, it’s usually not like, like, like local to just the query that’s in front of me. Usually there are like extenuating circumstances with either the hardware or the database or something that that that make tuning efforts a lot more difficult.

But as far as just like, you know, could I, if given unlimited resource or not even unlimited, just given like some more time and some more resources, I could I could I could really do this. But for now, we can we can take some shortcuts and like, you know, try to get batch mode happening or something to to speed that up or like crank maxed up up a little higher. So we have more CPU is working on on on on all those rows or something.

But there’s never been a query that I’ve looked at and been like, well, I just can’t do anything. We’re we’re we’re stuck. Let’s see here.

Which database will win the database wars? I mean, obviously, DuckDB. No, I don’t know. I mean, look, Postgres is having its moment.

I and I don’t think that’s a bad thing. I do hope that Postgres having this moment will force other database vendors to perhaps realign their priorities and perhaps think a little bit more about their pricing models for things and what they offer along with those pricing models. It is, you know, you know, I joke that like everyone who uses SQL Server and has never used Postgres thinks that using Postgres will solve all of their problems.

But in general, you’re you’re sort of just trading for a different set of problems there. There it’s it is amusing to me also that, you know, doing development work on Postgres is like working with SQL Server 2050. But doing like monitoring Postgres stuff is like working with SQL Server seven.

Right. It’s like there is just not not a lot built in that’s terribly useful or helpful. And I know that there are plugins and extensions and things that you can add to it that surface more.

But yeah, like out of the out of the box, if you don’t have like some some someone who like knows and cares and pays attention, getting getting meaningful information out is a lot more difficult. Postgres query plans specifically are not as friendly and graphically inclined as SQL Server ones are, nor do they surface. I think some of some of some of the more interesting details that the SQL Server plans give you.

So, you know, you know, do I think SQL Server is going to win? Probably not. I mean, SQL Server, to my mind, does have the best query optimizer.

Postgres’s query optimizer is not nearly as good as Microsoft’s. Not to say that it isn’t capable of doing some things better than what Microsoft SQL Server does. But I think like just generally speaking, I would I would much rather have Microsoft’s query optimizer than than Postgres’s.

But, you know, like I said, I think that Postgres having its moment now is a good opportunity for other big relational database vendors to perhaps have a moment of clarity about some of the things that they’ve been doing with the product over the years. Perhaps correct their courses in a way that will make the specter of a free database perhaps a little bit less appealing or something. Anyway, let’s see here.

Oh, I got a good question next, but you got to get through this one first. What are the hardest kinds of queries to write? I answered a similar question, I think, recently. Anything that involves like a lot of math, any sort like I don’t I don’t have any groundings or foundations in math.

So if there’s like, you know, some advanced data analysis formula that you need to apply to stuff, you know, I would have to, you know, go look at that and figure out how to, you know, write the T-SQL version of that, which I am not particularly good at. And I would not be able to necessarily like, like, like, validate easily. Like, well, I wrote all the math out.

It looks right. Can someone tell me if it’s right? So those are the hardest kinds of queries to write for me are the ones that involve like equations and algorithms and things like that, because like, I just don’t have any grounding in those areas. So here’s the question.

Here’s a good question. When will Joe Obish be back for a podcast? Well, I’m hoping that Joe will be feeling in the mood for recording in the, you know, I don’t know, reasonably near future. I don’t have an exact date, but I have at least gotten an email from him expressing interest in recording again.

So hopefully that is on the horizon. Anyway, that’s five questions. They’re short ones again this week.

All right. Well, I don’t know. I think, I guess it’s, it’s nice when they’re not overly long and involved because then I don’t have to think too, too much. But anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something and I will see you in the next video, which will be, I don’t know, something suitably SQL Server-y. 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.