SQL Server Performance Office Hours Episode 10
| Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join? |
| Do you ever see big wins just from changing compatibility level? What specific features cause these big wins? |
| When you hit performance issues that seem like actual bugs in SQL Server itself, have you ever had success engaging with Microsoft directly on getting improvements, or is it so much effort that if you can get by with your own approach, just don’t bother? |
| Hi Erik, our company is in the early-mid stages of migrating to Azure Managed Instance from on-prem SQL Server. Could you summarize some of the main issues/complaints you and/or your clients have with it? |
| Long time viewer. I know all of the theory behind the supremacy of RCSI, but I have 90 days to convince 25 people to enable it. Any advice just about winning those arguments? |
To ask your questions, head over here.
Video Summary
In this video, I delve into some of the common issues and questions that arise when working with SQL Server, particularly focusing on foreign keys and indexes. I also explore the nuances of changing compatibility levels and discuss whether it can lead to significant performance improvements. Additionally, I address the topic of engaging directly with Microsoft for bug fixes or improvements, emphasizing the importance of reporting legitimate bugs even if they might not be addressed immediately. The video then shifts to a discussion on migrating from on-prem SQL Server to Azure managed instances, highlighting potential challenges and performance considerations that users should be aware of during such migrations. If you have any questions related to these topics or other SQL Server issues, feel free to submit them through the magic Google form linked in the video description, and I’ll do my best to answer them in future episodes!
Full Transcript
Erik Darling here with DarlingNada. And there is something magical in the air, isn’t there? There always is. We do office hours in which I answer five entire questions submitted by you fine folks into my magic Google form. If you want to ask a question that gets answered here, you can go to this link. This link is down in the video description. about here. And you can click on it and submit your own question. While you’re there, you might want to consider clicking on the other link to sign up for a membership and join the 60 or so other folks who, out of the kindness of their hearts, support this channel monetarily so that I can keep bringing you all this delightful ad-free, commercial-free, absolutely no weird shilling for anything SQL Server content. If you would like to subscribe to our channel, please like to subscribe to our channel. If you’d like to support the channel in other ways, of course, you can like, you can comment, you can subscribe. We are treading perilously close to the, if I’m remembering correctly here, we can check the digits. We are perilously close to the 6,500 subscriber mark, of course. I remember it was not too, not so long ago, I was very excited about getting close to 5,000 and boy, now we’re getting close to 1,500 more than that. So, I guess, we’re on the way, we’re on the road to 10,000. We’re more than halfway there. It’s a good feeling. I don’t know. If you would like some consulting help with SQL Server, I am, of course, the best in the world outside of one house on New Zealand at doing these things. If you need health checks, performance analysis, hands-on tuning, responding to performance emergencies, or training your developers so that you have fewer performance emergencies, well, golly and gosh, you couldn’t spend your money better than on this fella here because my rates are reasonable. If you would like to get some SQL Server training, perhaps, you’re in dire desperate need of learning some performance tuning stuff, well, all of mine, again, down in the video description, you will find the link to do all of this stuff. You can get all of my training for about 150 USD.
And that lasts the rest of your life. So, it’s a good deal. It’s a good deal. Upcoming events, SQL Saturday, New York City, taking place May the 10th of 2025. Wonderful, crime-free, crime-free, Manhattan at the Microsoft offices, right across the street from Port Authority. So, you can take in a bus or a train and you can make some friends at the bus station. It’s a great time. I promise.
With that out of the way, though, let’s answer these gosh darn questions, shall we? All right. So, let’s start with this first one. Actually, let’s narrow this out a little bit so that when we zoom in, we get the whole thing on the screen. There we go. All right. That’s nice and fitting, isn’t it?
Many of our tables… Let’s make sure we read this whole thing. Don’t want to skip a word here. Many of our tables have 15 plus GUID S from other tables. Not really what I would want to inherit from anything, but hey, you know.
At least they’re GUIDs. At least they’re not like VARCAR 36 or NVARCAR 36, right? Because GUIDs, they’re nice. They take up 16 bytes of space. They sort kind of funny, but, you know, it’s really hard to run out of them.
They’re quite unique. But they’re not declared as foreign key. Oh, okay. So, these other tables… These columns are definitely foreign keys. The other tables are not declared as foreign keys. It’s an interesting choice.
Plus, you have foreign keys. Okay. It’s a little unclear if, like, they’re… Like, if you have a GUID column and then an integer column and they both foreign key to the other table, but… Details, details.
You know, there’s only so much you can type. When I add an index on for every foreign key plus indexes for declared foreign keys… So, like, the undeclared foreign keys and the declared foreign keys, all right. We end up with tables with 30 plus indexes for foreign keys only.
We don’t have right performance issues. Would you index every foreign key for every possible join? Well, yes and no. So, while I probably would do that, my indexing strategy, aside from…
So, like, think of foreign keys as sort of, like, database infrastructure. They’re, like, highways and bridges and underpasses and stuff. So that, like, you know, your people can find…
Like, your queries can find their way around your data and stuff like that. But, like, you have, like… And those are good things to support with indexes. But, like, my general indexing strategy is not just, like…
Because what it sounds like to me is you have 30 single key indexes on these tables, which is not a very good index design strategy. My index design strategy is to create indexes that not only support these foreign keys doing stuff, but that also, like, support, like, the grander goals of other queries.
Because it’s, like, in my experience, it’s fairly rare that you would be, like, you know, like, select… Like, let’s say, like, for a foreign key, right? Obviously, that’s a join between two tables.
So, like, you know, let’s say you’re selecting from table A, which has a foreign key to table B. You’re joining to table B. You’re probably selecting more than just the foreign key column or columns up here. And you’re probably joining on, like, the foreign key columns.
But then you might have, like, a where clause or group by or order by or other stuff that is good to index for involved in these queries. So, my indexing strategy is to, like, not only index to help this sort of database infrastructure like you have, but also to index for, like, the grander query design.
You might find that over time, you end up creating multi-key indexes, or as they say in fancy academic literature, compound indexes that have multiple columns. And when you have those, plus what you’re talking about, you have a lot of, like, sort of superset, subset, duplicative indexes that you probably don’t need.
So, as long as you have another index that leads with the foreign key column, then you’re fine. You don’t need, also, single key indexes to support the foreign keys. You already have an index that leads with the foreign key column, so you can figure that out.
But this is a good plug for my new store procedure, SP index cleanup. I realize you said you don’t have write through, write problems. Like, your write queries are still fine.
That’s great. But you may still have an opportunity to clean indexes up that lead, like, if you have these truly, like, 30 single key indexes plus other indexes that, like, also lead with the same columns, you can probably start pruning off those single key indexes because you just don’t need both.
All right. So, let’s move on to the next question here. Do you ever see big wins just from changing compatibility level?
What specific features cause these big wins? So, let’s preface this a little bit by saying that changing any compatibility level in SQL Server is perilous. It is somewhat less perilous if you are going from, like, 120, which is SQL Server 2014, which is when the new cardinality estimator got tied into a higher compat level, and a higher compat level.
If you are on a lower compat level, like 120 through, or, sorry, 110 through, like, whatever is officially supported at the low end these days, then it is more perilous. So, let’s put this into two buckets.
One, if you’re coming from compat level 110 or lower and you want to go to a higher compatibility level, make sure you use the database scoped configuration option, database scoped configuration option, force legacy cardinality estimation. That out of the way, like, cardinality estimation and query and database compatibility level are two separate things, right?
So, like, while going to a higher compat level will, like, also force you to a higher or different cardinality estimator, you can still force the older cardinality estimator and get the higher compat level. In some cases, I find, like, where I have to, like, people who have been using, like, compat level 130 switch to, like, 150, but do better with the legacy cardinality estimator.
But that’s not really what you’re asking. You’re asking if there’s any specific features in the higher compat levels that are big performance wins. And the two that come immediately to mind are batch mode on rowstore and UDF inlining. While neither one is, like, a panacea or a bromide or whatever, like, old-fashioned word people had for something like cure-all, like, they certainly do, like, add helpful things to workloads that keep you from having to do a lot of work.
So, for, like, batch mode on rowstore, it can save you a lot of, like, you know, like, creating temp tables with columnstore indexes on them and doing, like, the left join on one equals zero thing or creating empty filtered non-clustered columnstore indexes on tables to get batch mode happening in your rowstore queries.
And UDF inlining can potentially save you a lot of, like, scalar-valued function rewriting because, like, you don’t have to rewrite them as inline table-valued functions. Not, like, of course, like, neither of these features is perfect and probably never will be because they’re very difficult query optimization paths.
But, like, batch mode on rowstore, like, relies on a bunch of, like, heuristics about the tables and the type of query you’re running that may not pick up on every single query that would benefit from batch mode on rowstore. May also put batch mode on rowstore on queries that don’t really benefit from it and might hurt a little bit.
UDF inlining doesn’t apply to every single scalar UDF that has ever been written. There are a lot of, like, limitations on it, a lot of things that will just prevent it from happening where you would still have to manually rewrite functions.
But those are probably the two biggest ones as far as, like, compatibility-level specific feature unlocks that, like, I’ve seen be beneficial to workloads. All right.
Do-do-do. When you hit performance issues that seem like actual bugs in SQL Server itself, have you ever had success engaging with Microsoft directly on getting improvements? So, this is funny.
So, you’re talking about bugs, but then you mention improvements. Bugs get fixes. Bugs do not get improvements. You don’t improve bugs, you fix bugs. Or is it so much effort that you can get by with your own approach, just don’t bother?
So, no. You should always report bugs to Microsoft. Microsoft should be aware of its bugs. The one sort of flaw in the bug reporting process is that if you open an issue on user voice and you are not a, and you don’t have, like, a repro or, you know, you’re not, like, you can’t give them any, like, you can’t, like, you can’t give them an easy way to, like, show them what the bug is and, like, how to reproduce it and, like, so they can go and fix it, you’re going to have a hard time getting traction on it.
So, you would, like, probably require a Microsoft support case, and we’re going to talk a little bit about Microsoft support when we hit that next question, but that can be a real difficult process as well. The other thing with, like, bugs and stuff, reporting bugs and stuff for SQL Server is that, like, the user voice thing that they use, like, people have to vote on stuff.
Voting on stuff makes a lot of sense when you’re dealing with, like, features or when you’re dealing with, like, you know, improvements to the product, like you mentioned up there. It makes a lot of sense to file issues for features and improvements, but they’re a popularity contest.
Like, if you’re not fairly well-known or your issue doesn’t catch the eye of someone who is fairly well-known who can promote it, you’re not going to get a lot of votes. But, like, for the bugs, like, you know, like, that’s just a much tougher one.
But you should always file bug reports with Microsoft when you find legitimate bugs. Legitimate bugs are not just, I don’t like the way this works. That’s, you know, feature improvements and whatever.
But always report the bugs because, you know, Microsoft should know about them. Just don’t be surprised if they don’t get fixed immediately or at all, ever, because there are lots of bugs that have not been addressed.
So is it worth the effort? Well, I think that it’s generally worth the effort to log the bug because who knows if someone else might find it and say, oh, I’m not crazy.
So maybe if, you know, do it for yourself. Do it to maybe help Microsoft fix flaws in their product, but also do it for the general public who might come across your issue when they hit something similar and say, oh, this is a bug.
Oh, I hate this question. Hi, Eric. Hi.
How are you? Our company is in the early to mid stages of migrating to Azure managed instance from on-prem SQL Server. Could you summarize some of the main issues complaints you and or your clients have with it? Well, my clients’ complaints.
I’m going to leave my complaints out of this because if I tell you what my complaints are, then it just sounds like I’m ragging on managed instance. So I’m going to tell you what my clients complain about who use managed instance is, and I’ll let you decide whether these apply to you.
So let’s start a little ways back before managed instance was a thing. I was around, perhaps, you know, cutting myself open and showing you how many rings are inside. I was around for the great virtualization epic in computing, which means like when virtualization was the big craze before moving to the cloud was the big craze, you know, everyone was gung-ho on virtualization because they wanted things to be more manageable.
They wanted consolidation. They wanted to save money. All the stuff that people say about the cloud now. Guess what?
The early days of virtualization. Now, if you, like, let’s say that you had a physical SQL Server and you were very happy with performance. If you moved to a virtualized SQL Server that was, let’s just say, identical spec-wise, like I realize it’s not going to be absolute identical, but let’s just say it was identical spec-wise to the physical machine you had, you would typically see a 20% to 30% slowdown.
If you were very unhappy with, like, the performance of your physical SQL Server and you moved to a better VM, well, a better spec’d VM, you would probably see improvements.
A very, very similar thing happens with the cloud, specifically managed instance, Azure SQL DB, to some extent, and is on RDS, where if you are currently happy with the performance of your on-prem boxes, you’re going to need to spec up your virtual, like, your instance type in Azure by 20% to 30% to see commensurate performance.
If you are planning on moving to a general-purpose box in managed instance because it costs way less than business critical, you might need to even go higher. The reason for that is because the general-purpose instances store all of your SQL data in Azure blob storage.
You will notice that when you go to disk to read data in managed instance, you are making an HTTP request. The wait type actually has HTTP in it. If that ain’t a punch in the narbles, I don’t know what is.
So be prepared to oversize your managed instance general-purpose box or pay to the back teeth for a business critical box to compensate for all sorts of strange design choices. Another giant complaint that my clients have with platform-as-a-service in Azure is that the support for it is incredibly expensive.
When I say multiple tens of thousands, I mean 50,000 to 60,000 a year to hundreds of thousands a year and sometimes higher for support that gets nothing done. They cannot reliably support you there. You end up with third-party support engineers who say, hey, can I have logs in a repro?
You give them logs in a repro. You maybe go back and forth a little bit. They give you some humdrum advice about rebuilding indexes and partitioning your temp tables. Then you don’t hear anything for a while.
Then three months later, they shuffle you off to someone else and you start from scratch. It’s a terrible experience and you pay so much money for it. It’s not good.
It’s not good at all. So the big problems that you’re going to have, one, unless you way oversize your boxes, which costs a lot of money, or you go to business critical, which costs a ton of money, your performance is not going to be equitable between on-prem and managed instance.
And the support you get is going to be god-awful. So avoid that if you can. I would much more highly recommend Amazon RDS over managed instance.
Microsoft managed to botch a managed platform for its own database. Amazon did a much better job with it. So let’s move on here.
Longtime viewer. Oh, thank you. Won’t make any jokes there. I know all of the theory behind the supremacy of RCSI, but I have 90 days to convince 25 people to enable it. Any advice just about winning those arguments?
What arguments do you need to win? It’s hard for me to tell you how to win an argument when I don’t know what the argument is. You know, my basic outline of enabling RCSI is something like, do you have a lot of blocking problems with readers and writers?
Or deadlocking problems with readers and writers? If so, you can most likely solve the vast majority of them using RCSI. Do you already have no-lock hints sprinkled everywhere around your queries?
If you already have no-lock hints, there is absolutely no downside to switching over to RCSI. RCSI is actually better than no-lock when it comes to returning results. Right?
Because no-lock allows dirty reads. RCSI does not. Stuff like that. Sometimes you need to get a demo database where you enable RCSI and do stuff. If people are worried about TempDB, they’re living in like 2012.
You know, newer versions of SQL servers do a fairly good job of setting up TempDB for success for you. It creates extra data files. The trace flags 1117 and 1118 are enabled by default.
So you don’t have to worry about, like, black magic stuff when it comes to TempDB setup. There’s really not a lot to… Like, for most people, the switch to RCSI is not a very rocky one.
There are very few situations and there are very few code patterns where RCSI is a problem. If they are really, really worried about RCSI and you have reasonable control over the workload, you might have some luck winning people over by enabling snapshot isolation and going through and only allowing certain queries that everyone deems are, you know, safe to add the snapshot…
To add the set transaction isolation level snapshot to them so that you can start showing people just exactly how things work and behave. Granted, there are some, you know, there are some differences between snapshot isolation and read committed snapshot isolation. But generally, the snapshot isolation, like, having, like, a layover in snapshot isolation is a pretty good route to getting read committed snapshot isolation enabled.
So if you would like to supply me with some of the specific arguments you’re getting, then I’d be happy to address those more specifically. But in general, that’s sort of… Those are sort of my talking points on things.
Anyway, that brings us to the end of the fifth question. Again, I thank you all for submitting them. I thank all future submitters for submitting them.
And, of course, all past submitters for having submitted them. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in other videos and other office hours and… I don’t know.
Lots of stuff to… Lots of questions to answer. Lots of interested folks out there. So, anyway, that’s good for me. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.