SQL Server Performance Office Hours Episode 24
To ask your questions, head over here.
| Hi Erik! Can you demonstrate or share topic about HA Setup with Two Database Node and one AG Listener? |
| You made a comment recently on LI about switching from int to bigint not being as easy as it sounds with compression tricks. What problems have you hit? |
| If you worked at Microsoft, what would you do with SQL Server to make it competitive with Postgres? |
| Do you ever offer discounts on consulting or just training? |
| Do you plan on offering Postgres training or content? |
Video Summary
In this video, I dive into some interesting questions from our community and share my insights on various topics. First, I address the challenge of setting up a High Availability (HA) environment with two database nodes and one AG listener, explaining why it’s not as straightforward as it might seem. Then, we delve into the complexities of switching data types from `int` to `bigint`, particularly when dealing with compression techniques. I highlight how most real-world scenarios are more complicated than simple table setups, emphasizing the need for careful planning and potential dependency issues. Lastly, I discuss my hypothetical role at Microsoft if I were to help shape SQL Server’s future, focusing on modernizing T-SQL and enhancing query store features to better support monitoring and performance tuning. It’s a lively session packed with practical advice and candid thoughts!
Full Transcript
Erik Darling here with Darling Data. And of course, we have five questions to answer, so we are going to office hour. Or as the ancient Egyptians said, office horus. Alright, still working on that one. Anyway, if you like this channel and this content, you can sign up for a membership. Link in the video description. Link in bio, as they say. If you want to ask questions, privately, that I will answer publicly on these office hours episodes, you can go to this link, which is also in the video description, and you can ask me questions. If you need some help beyond what asking a question on this whole YouTube thing can possibly get you out of, maybe you’re just in a lot of trouble. I am still a SQL Server consultant. I do all these things at a very reasonable rate. And I am available for hiring. So, get it well done. It’s hot. If you would like to get my performance tuning training, all 24 hours of it are available to you for about 150 USD at that link with that discount code. If you go down to the link down in the video description, you will be very happy to know that it is all wound up in there for you. And I also have my new T-SQL course available, Learn T-SQL with Erik. All 23 hours of the beginner content is fully available.
to you. It is down at the pre-sale price of 250 bucks, which will go up to 500 bucks after the summer when the advanced content is recorded. Speaking of summer, boy, oh boy, we’re wearing a lot of shorts out there. I am going on tour with Redgate on all the Pass On Tour events, as well as Pass Data Community Summit. Pass On Tour is, of course, hitting New York City, Dallas, and Utrecht. And Pass Data Summit is, of course, hitting Seattle, November 17th to 21st. So, get in on those if you are in the nearby area. But let’s go answer some questions now and let’s get through all this stuff. All right. First question. Oh boy. Hi, Eric. Can you demonstrate or share topic about HA setup with two database node and one AG listener? Hell no.
I don’t ever talk about that stuff. I don’t care for AGs. I don’t care for boring setup tasks like this. I don’t care to do walkthroughs. There are other people who do. God bless them. They like this stuff. I am all query performance. That is not something I want to get into. All right. Next up. You made a comment recently on L.I., I believe that’s LinkedIn for the folks at home, about switching from int to big int not being as easy as it sounds with compression tricks.
What problems have you hit? Well, if you saw me make that comment, then you may have seen the video link that I put in there about my thoughts on it. The biggest problem is that, you know, every blog post that tells you about this one weird trick, it’s like a table with like just a clustered and not a clustered primary key, but a clustered index on it. That’s the only index. And it’s just like, oh, magic with row compression or page compression. I can just switch from int to big int without doing anything.
The problem is most of your tables aren’t going to have just one index on them. And even if you rebuild all your indexes with row or page compression, if like you like, you’re going to have all these weird object dependency issues that you have to sort out. So it’s like not only do you have to like, like either drop off a bunch of indexes that that that column is a part of, but like, you know, anyone’s remaining, you have to add, add the compression to, and then maybe you can do it all very easily online. But most people don’t have that simple of a setup. So it’s like, it’s near useless.
I really wish Microsoft would just make this a metadata only change where new pages assigned to the table would be able to use would be designated with the big int. And you could either like rebuild the whole thing later to align everything to a big int or just let new pages come in with with that thing as a big int. But, you know, instead, we got fabric, stupid ass fabric.
Here’s a neat question. If you worked at Microsoft, what would you do with SQL Server to make it competitive with Postgres? Well, you know, like the reason why Postgres is competitive is because it’s free.
Not a lot of people are like feature shopping Postgres and being like, wow, like this, this spec sheet just beats the pants off SQL Server. Or let’s, let’s go with this, like, you know, like, let’s go with this champion work, like racehorse. Most people aren’t go, aren’t, aren’t, don’t, don’t, don’t do that.
Most people are like, does it tick enough boxes that I can use it as a data as a relational database? Yeah. Oh, okay. Great. You know, cool.
Let’s, let’s, yeah. Great. It’s got tables. It can select data from them. It can insert, update and delete. Yeah. All right. Great. Like, what else do I need? Like, you know, SQL Server is expensive, right?
Standard edition, $2,000 a core and Microsoft still hamstrings you with crappy hardware limitations. Enterprise edition is seven grand a core. And, you know, God almighty.
Look, if you look at Microsoft’s pathetic offerings for like platform as a service SQL Server, what are these VMs? Like even with that new knob for like adjusting memory on its own, like you’re, like you still have, like you can still only make it in like one of the available SKUs. Right?
Like you can’t like, like you can’t handcraft it. You can’t get a server with like eight cores and like 560 gigs of memory. Speaking of which, 560 gigs of memory is still the top one. You can’t get a terabyte of memory for SQL Server.
It’s like Microsoft doesn’t even know its own product. It’s like, what are you doing? But like if I work there, you know, obviously like the big things with SQL Server that, you know, people complain about are like the sort of like half-assedness of a lot of the things. You know, like a lot of features that have been around for a long time that, you know, got pushed to like 75, 80% done and then never touched again.
Aside from like maybe bug fixes. T-SQL like hardly sees any improvement. You know, we got all this vector crap, but like T-SQL itself like barely got any like meaningful improvements for SQL Server 2025.
Like there’s just so much stuff that, you know, that other databases are eating Microsoft’s breakfast, lunch and dinner on like, like from a development point of view that that’s really, that’s really where they ought to focus. Or that’s really, rather that’s where I would focus is, you know, just kind of like the modernization of T-SQL. And I think, you know, if like, if I, if I really wanted to get in there, I would, I would make query store much more of a, of a monitor, like a legit monitoring repository than it is.
You know, right now it collects query performance data, but it doesn’t collect stuff like blocking or deadlocks. It doesn’t collect any like, you know, like, like it doesn’t collect weight stats. Well, I think the nice way of putting it, you know, there’s, there’s a lot of stuff that, you know, like as a consultant or, you know, like, you know, you as a DBA might monitor and look at in SQL Server that is not centralized in any way that makes it easy.
Uh, and I don’t understand why Microsoft doesn’t try to make it easier for like, like, let’s just say the average person who’s like, I don’t know what I’m doing. Please just give me an answer. Like, just give me something like, like, like has to now become an expert in like five different things and like learn like XML querying to like, you know, get any good data out of, out of SQL Server.
So like, you know, there’s, there’s all that stuff there, but as far as like what, you know, competing with Postgres, because it’s hard to compete with free, especially hard to compete with free when like free is out featuring you on a whole bunch of stuff. So, uh, you know, but Hey, we got fabric.
Uh, God almighty. These people never learn. All right. Uh, do you ever offer discounts or on consulting or just training? Uh, I am a wheeling and dealing man.
And if, uh, if, if, if you, if you email me directly and you want to, you want to wheel and deal on, on pricing for things, I will wheel and deal all day. I’m happy. I’m happy to do that.
Uh, I, I, I do. There are limits to how much wheeling and dealing I will do, but I will always, uh, wheel and deal. All right. We have another, another Postgres question here.
Jeez Louise. Uh, do you plan on offering Postgres training or content? So here’s the thing. Uh, I could certainly play with Postgres enough to learn stuff about it and produce training content on it. However, you know, part of the comfort there is part of the comfort that I have with producing the training content on SQL Server is, uh, experience.
Right. Right. So like, you know, I’m, I’m not run, like, I’m not obviously not running Postgres and production anywhere.
Uh, I’ve never worked with Postgres live in production and, you know, there are certainly, you know, like. Things that you can still be technically correct about and you can still like read the documentation and maybe put together in a friendlier way. Or even offers like, well, if you use SQL Server and you want to learn Postgres, here’s A and B, like stuff like that.
But, you know, um, you know, especially from a, like a performance tuning perspective, um, you know, the, the type of stuff that you, like the type of stuff that makes for good content is often the type of stuff that you figure out. Uh, working with like real production workloads and seeing real meaningful differences there. Um, you know, that’s also, you know, where you learn a lot of the sort of, uh, limitations, restrictions, uh, you know, just stuff that, um, stuff that is not easy to find just running a query and like, you know, on a server, uh, you know, type, type things.
So, you know, I’ll never say never, but, um, you know, like it’s, it’s, it’s something where, uh, I would, I would need to be exposed to it in like a, a, a realistic way. I think before I felt confident in thinking that I could train others on like how to, how to, how to do things best with it from a, from a performance point of view. All right.
Well, that’s five questions, I think. One, two, three, four, five. Yep. We did it. Hey, good job us. All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video where I don’t know, whatever it is, figure it out when we get there, I suppose. 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.