SQL Server Performance Office Hours Episode 3
| What’s the best SQL-realted book of the past ten years? I’m struggling to beat Database Reliability Engineering by Campbell & Majors. |
| How excited are you to attend the Microsoft Fabric conference this year? |
| Everyone I follow on YT and Twitter are SQL Server wizards. Please reassure a rookie that not every DBA has the level of knowledge that you and Brent have. |
| What kind of scenarios have you encountered in regards to Dynamics GP systems? Where there any common problems you resolved as well? |
| Hi Erik 👋 I saw on the engine documentation that it can be expanded. Is there any physical operator or rule that MS is sleeping on? |
To ask your questions, head over here.
Video Summary
In this video, I dive into answering five questions submitted by you, the viewer. We start off with a discussion on the best SQL Server books from the past decade, leaning towards “Database Reliability Engineering” by Campbell and Majors as the top pick for practical database management knowledge. Then, we delve into my thoughts on Microsoft’s Fabric Conference, expressing disappointment in its development and implementation. Following that, I share insights on common issues encountered with Dynamics GP systems, focusing on blocking problems and the challenges of optimizing queries within this Microsoft product. Lastly, I address a question about potential improvements to SQL Server’s query optimizer, suggesting unrolling joins with OR clauses into UNION or UNION ALL sets as an area for enhancement. Join me in these office hours for more candid discussions and valuable insights!
Full Transcript
Erik Darling here with Darling Data. And we’re going to have a thrilling episode of office hours, in which I answer 5 questions that you, the user, submit. How do you submit those questions? Well, that is a great question for you to have submitted right now. You can, there’s a link that appears here, that it also appears down in the video description. And if you click on that, that link, the one down there, you can’t click on that one. That’s not a clickable link on the screen. As devious as YouTube is, I haven’t quite figured out if that’s a possibility or anything yet, but that’s how you ask questions. If you want to sign up for a membership to support this channel, you can do that for as little, few, as $4 a month. There’s a link also for that in the video description. If you, you can, I don’t know, it’s like 50 something, 50 something, 60 people do that already. So you can, you would be in great company if you did. If you are, for some reason, don’t have four bucks or you think I suck and you just watch this out of spite and you want me to have some false sense of hope about the future of this channel, you can do other things to lie to me. You can, you can like, even though it’s a, it’s a hate like. You can comment, hate comment, and you can subscribe, which is the most hate thing you can do. The most hateful thing you can do is to get notified, build your rage every time I put a video up. If you would like to hate me live, you can hire me as a consultant. I do health checks, performance analysis, hands-on performance tuning, queries, indexes, settings, all sorts of other neat stuff. I deal with performance emergencies. If your server is burning down on fire and you can’t place an order or whatever, Oh, I can, I can, I can, I can usually fix this stuff. Then I can also train your developers so that you, you have fewer or less of those problems. Uh, if you would like to get some training, I mean, it’s gonna, it’s gonna shock you that, uh, a consultant has something that has something to sell. Well, I have about 24, 25 hours of performance tuning training. It’s all SQL Server, beginner, intermediate, advanced. Uh, if you use that coupon code brings the price to about 150 USD and, uh, you have that for the rest of your life. That is not a subscription package. It is for you forever. So you’re welcome. Uh, we have SQL Saturday, New York City coming up May 10th of 2025. Beautiful times square. Uh, from what I hear, it’s, it’s still, it’s still there. No, not, not, not in its former glory.
It’s sort of cleaned up. It’s like, I don’t know. It’s a weird place these days. You’re just going to go inside the Microsoft building and hang out for the day. You don’t have to worry about the outside stuff. You don’t have to worry about the, the New York centric view of times square. But anyway, let’s, uh, let’s get through these office hours questions. Let’s answer some of the burning things that my adoring fans have on their minds. Uh, the first question is, and, uh, we’re gonna, we’re gonna do a little spell check on this one for you, my friend.
Uh, what’s the best SQL realtor book? I know we all know that’s related, but that’s okay. Uh, of the past 10 years. So that would bring us back to the year 2015 at this point. I’m struggling to beat database reliability engineering by Campbell and Majors. Well, um, if we’re talking about the last 10 years, yeah, I generally think you’re right.
Um, and unless you are looking for academic books about building databases or like, you know, like, like database space problems that haven’t been solved, like, you know, like query optimizer stuff, cap theorem stuff, um, things like that. Right. Like, like, you know, like bigger database problems that aren’t just like you as like a data person, like working with the database, like making sure that that database stays up and healthy and fast and all that stuff. Yeah. You’re probably not going to do a lot better than that. Um, you know, all of the, the books that I really liked are older than 10 years at this point.
Uh, like the last, I think good SQL Server book came out in like 2012 or 2013. Really since then there hasn’t been a lot of like good, like, like fundamentals internals literature, uh, about SQL Server. Uh, you know, like I’ve said before, all the like Kaylin Delaney books where she had, you know, people from Microsoft, the co-hosting talking about the optimizer internals, all sorts of like isolation levels, locking all sorts of good stuff in there.
Um, you know, like, like good books about SQL Server had been non-existent really. Um, they’re just kind of all the same like rehash of things. Uh, you know, even, even the books written by Microsoft employees, uh, that have come out, um, since then, uh, were, uh, let’s just call them unproductive in many ways.
Um, you know, it’s, it’s, it’s a lot of like, uh, Microsoft company line material on things with, uh, very little admission of, um, you know, uh, where, where SQL Server as a product is somewhat lacking. Um, so, uh, yeah, database reliability, reliability engineering is probably the best, like working with a database book. If you have other interests with databases, um, there, there are probably better books out there that are more academic, theoretical, you know, relational algebra stuff.
But yeah, that’s a good one. Um, you know, not oddly enough, but like a lot of the stuff in that date, in that book, uh, is, I think a lot of the examples, if I’m remembering correctly, were like MySQL related. So it’s not even like specifically SQL Server stuff.
But anyway, uh, how excited are you to attend the Microsoft Fabric Conference this year? Shockingly, I was not invited to the Microsoft Fabric Conference this year. Can you believe that?
I mean, probably what I’m most excited about is to not attend whatever it’s named next year. That’s going to be fun. Uh, whatever the next squirrel Microsoft sees is, uh, you know, I don’t want to spend too much time on it, but man, uh, Microsoft has just lost a lot of goodwill with their inability to stay focused on, on a product and see it through.
Um, the, the, the way that they’ve switched up the, uh, everything that’s led up to Fabric so far has just been annoying to everyone except like a few groups of people. Like a Microsoft employees who will get fired if they don’t pretend they love it. Uh, B, uh, consultants who have to keep re-implementing the same thing over and over again with a different name.
And see like MVPs who are so terrified of getting kicked out of the MVP program that they won’t say boo about a crap product. So like, like, there’s a, like, those are like the weirdest people. Um, cause like, like that’s a closely overlapping Venn diagram or the, the, the consultants who deal with this stuff in the MVPs.
That’s almost a circle right there. But, uh, man, you, you want to talk about an unserious, unfinished product? What? Why? Fabric is like, fabric is just like the, it’s like, it’s like, it’s like, it’s like, and I’m not, I’m not ranking Databricks and Snowflake.
I’m saying like, you have Databricks and Snowflake. And then like, like, like, like, let’s just say like, you have Databricks and Snowflake here. And then you have Fabric like down here.
And there’s not even anything else in here. Like that, like that, that’s, that’s how bad it is. And, um, you know, like I, I have a bunch of clients who have been forced to use it by sea levels and like, because Microsoft either gave them huge price breaks on it or like actually fed them money to start using it. And they are just miserable.
So, um, you know, I don’t, I don’t, I don’t know how to help there. I don’t know. I don’t know what else to say. All right.
Next one. Everyone I follow on YouTube and Twitter are SQL Server wizards. Well, good. You should, you should follow people who you consider to be smart about a thing that you care about. Please reassure a rookie DBA that not every DBA has that level of knowledge.
Well, of course not. If everyone had that level of knowledge, there would be no point in me doing this. I would just be, I don’t know, stating the obvious to everyone.
Um, so of course not everyone has that level of knowledge. Uh, I, a lot of my clients have like full DBA teams full of people who maybe don’t have as much knowledge as I do. You know, uh, and there, there are all sorts of wonderful areas of knowledge out there for SQL Server.
You know, like I, I know nothing about SQL Server outside of like the query and index tuning stuff. I mean, like, you know, some stuff, but like, well, I guess what I’m talking about is like, like if you wanted me to like troubleshoot an AG outage or you wanted me to like, you know, like set up an age, a perfect availability group for you. Uh, like that would be way outside the realm of something that I’m comfortable with because that’s not stuff that I do and study and work with every day and care about.
Like I have friends who do stuff like that. My friend, Sean Gilardi, wonderful with availability, high availability stuff, knows it inside and out. Um, like I just know nothing about it.
And he actually like knows nothing about query optimizer stuff. So it’s like, you know, there are, there are all sorts of, there’s all sorts of space for people to know a lot and be ignorant of, of the rest. So, um, yeah, no.
And, uh, you know, in, in a funny, in a sort of funny way, like I miss a bit being like a rookie with this stuff because like, I don’t know, there was just so much to do. Like the world is this wide open place full of mystery and magic. Like there are so many things to test and try and there’s so much knowledge to sort of like, like eventually like soak in.
It was, it was, it was, it was great. Uh, these days, you know, it’s like you, you build up a callus kind of, and, uh, it becomes harder and harder to be impressed or surprised by something. It’s like you have to, you have to really do yourself now to get anything.
You know, it’s like just keeps taking more and more to, to, to build you up there. Um, you know, so, you know, uh, yeah, but I, I, first up, appreciate the kind words. I do, I do, um, appreciate you, you following me places and thinking that I’m wizard-like in some way.
Uh, but yeah, uh, it, it’s, it is, it is, it is a learning process and, um, we, we, we don’t, we don’t all start off like this. It’s the only person I think who started off, uh, knowing everything is Paul White. So there’s just, there’s just that.
All right. Let’s see. Our next question here. What kind of scenarios have you encountered in regards to Dynamics GP systems? Were the common, what were there, where, where there, any common problems you resolved as well?
Yeah. So Dynamics GP is a Microsoft product and it’s one of the, I mean, I know that they didn’t start. I think they bought it or something.
Uh, used to be something else. They bought it and it’s whatever. But, um, yeah. So it’s one of those unfortunate things where like, it’s another one of those Microsoft products where it’s built to use SQL Server, like built to have a SQL Server database backend.
But the people who did that work never spent one second talking to the people at Microsoft who built the database product. So it’s a real bad time.
It’s real unfortunate. Dynamics GP is like, like, I think for, for what it does, this is one of VWare’s products, the way it works with the database. Uh, unless you have like a pathetically small, like sample set for stuff, like you’re good. Like as soon as that gets to a meaningful database size, you’re in real trouble.
Um, as far as problems that I typically solve with Dynamics GP, um, there’s a lot of blocking. Uh, there’s a lot of selects with upd lock hints. Um, you can fix some of the blocking problems with indexes and with an optimistic isolation level, but not all of them because like the second one of those selects, selects come along with an upd lock hint.
You’re back to having a bad time. Uh, so, you know, you like RCSI is a good choice for it. Um, you know, like, like finding your crappy queries and like seeing if the indexes at all line up with what they’re, what they’re trying to do.
And then, uh, I think the, the final thing that you’re able to really do, cause like all the Dynamics queries come from inside the application. They’re not like store procedures or anything that you can like tinker with. So like your last resort for a lot of things is either plan guides or forcing plans in query store because there’s not much else you can do.
So like, even if you have, like, even if you have a great index in place and SQL Server is not using it because of cardinality estimation, it doesn’t want to do a key lookup or whatever. Like, like you don’t want to add a super wide cause some of those tables are massive, right? Like, like width wise and like, you don’t want to add just like, you know, 40 column includes, right?
That’s not a good time. So like sometimes you do have to resort to plan guides or forced, forced query plans in query store in order to like get your queries to actually use whatever indexes either you already have or whatever indexes you end up creating. Based on what your, what your worst Dynamics GP queries are.
But I have done a lot of consulting on Dynamics GP systems over the years. And every time I wish I had charged more. All right.
So the last question here, somewhat strangely worded, cause it almost sounds like they, they, they, they, they, they, they, they, they were asking a different question, but I understand it now. It says, hi, Eric. Hi, how are you?
I saw on the engine documentation that it can be expanded. It was a little unclear what it was at first, but I think I worked it out. That it is the engine. Is there any physical operator or rule that MS is sleeping on?
Well, I think if there was a specific space that I wish the optimizer did more work in, it would be unrolling like joins with or clauses to be like union or union all sets. That would be where I would go with it.
I think that, that also I hear, well, I mean, I guess they already have done work on that. I’ve heard rumors that Microsoft is working on how they handle sub queries to do that a little bit better because the current implementation is somewhat incomplete. But I haven’t actually seen the results of that yet.
I do know that in Azure SQL DB, I found a couple new optimizer rules that help you when you’re doing like the left join to find rows that don’t exist. So like select from table A, left join, table B on like the primary key columns where the table B primary key column is null. So in other words, there’s rows that are in table A that aren’t in table B.
They do have a couple of new optimizer rules to change that from that query form to like a not exist query form, either a left or right anti-semi join. So that, like those are two new optimizer rules that I have a lot of hope for. I do hope that they make it into SQL Server 2025.
They’re, they’re cool. They’re good things, right? They’re good optimizer progress. But I think, you know, ultimately you can spend a lot of time coming up with stuff like that. Like there are certain, like, like, honestly, like the, the join with the OR transformation would be a great one, right?
That would be fantastic to see. But I think ultimately the, the, the query, like you have the query optimizer here and the query execution engine over here, right? And these two things need to get a lot closer together because like, like you can spend all the time on rules and cardinality and, you know, heuristics and stuff over here and still come up with a God awful plan over here.
And with, unless these two things have a, like a really clear line of communication, like all this stuff over here is just like spinning wheels after a certain point. Because once that query starts running, it just eats, eats it anyway. Right?
Like, like, like the optimizer is like, it’s the perfect plan. I’ve got it. It’s right here. Nothing can go wrong. And then all of a sudden it runs and it’s like, it was wrong and like wrong in a lot of places. So like the query optimization and query execution need to become much closer friends.
Uh, I think in order for, um, Microsoft to maintain, uh, it’s, it’s, it’s, it’s, it’s query optimizer championship belt and for any major progress to get made. Um, because, uh, you know, like for, forever and ever the optimizer was just like, it came up with a plan. And as long as that plan was in the cache, we’d reuse that plan.
And like, there was no communication at all, but like, you know, with the intelligent, intelligent query processing set of features, there’s been more and more sort of like feedback and communication and stuff there. And like, those are great steps, but I think ultimately what you need is way more, um, you know, uh, way, way more like runtime flexibility, uh, for queries. So that like they’re, they don’t get stuck the way that they do now.
Like, like for a lot, aside from adaptive joins, which, you know, are not horrible, but they’re not, but like, if all you get is adaptive joins, there’s a lot of other stuff that you leave on the table. Uh, that could be like made, but they could be more adaptive at runtime that are not, um, you know, like, like, like one of the things that sticks out to me is memory grants. Where like, if you just get a way under memory grant, like you like at runtime, you’re hosed.
And I understand there’s a lot of cost and complexity and there’s a lot of stuff that can go wrong. If you were to just start like dragging memory in for a query, there are other things that have sort of dynamic memory, like, like index creation and some stuff with like, I think columnstore inserts. But, um, like query runtime memory, like you can only get that feedback after the queries run.
And like, if you had a query that ran for like an hour because it was spilling out to disk, you know, like, I don’t know. I don’t know precisely how valuable that feedback is after an hour, right. Or after however long that query runs for, like there’s, there’s stuff that I wish were way more runtime adaptive to prevent like, like the really bad query holdup stuff.
But I don’t know, I don’t know. I assume that smart people have looked at this and either disregarded it or decided to choose a different path for things completely. So, um, I don’t, I don’t think that I have anything, uh, particularly, uh, new or enlightening to, to, to provide to them.
But, um, there’s, there’s a lot of stuff that I look at when, uh, when I’m looking at query plans, when I’m looking at like performance problems. And I’m like, well, you know, it’d be nice if SQL Server had like a little bit like a fail safe on this stuff. You know, um, I think, I think one, one area where I think the optimizer, actually, sorry, two areas where I think the optimizer could use, um, a lot more work are, uh, costing for random IO and the value of early aggregations.
But before joins, cause you know, like, you know, lookups and loops and stuff are, nested loops are all still costed. Like they’re on crappy old spinning disc hardware. And, uh, a lot of the times you have to manually rewrite queries to use early aggregations, uh, because, uh, the optimizer just won’t.
Like, even if you have like a ton of duplicates and two tables of the columns you’re joining on, like you, you have to physically rewrite one or both, uh, to do like a group by or something before the join. Otherwise SQL Server just doesn’t choose to do that. And you end up doing way more work, dragging way more stuff along.
It’s just, it’s, it’s, it’s, it’s aggravating. So that’s some, some areas where I think the optimizer could use some work. Anyway, that brings us to the end of these five questions. Um, I will ask five other, I will answer five other questions in another video.
So you have, you have that to look forward to, but thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in that, in that next, in the next video, whatever, whatever that is, whatever magic mystery tour we go on.
All right. Thank you.
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.