SQL Server Performance Office Hours Episode 11
| We have an application that uses two RDS nodes [Write-Only + Read Replica]. The read replica does not autoscale in any way. Do you see any advantage in this architecture? To me it just looks like wasted resources, since the UPDATE/INSERT/DELETE will have to be executed twice now. |
| Can you give me a quick summary of the changes made to Query Store in SQL Server 2022? |
| Hi Erik, Could you record a video where you explain when ‘LEFT JOIN – WHERE IS NULL’ is preferrable from ‘NOT EXISTS’, if there are such cases ? |
| Our index templates for devs to use default indexes as Desc order. Are there any performance issues you’ve run into with this for single column indexes? I know an index can be read both ways, so curious what else it might impact. |
| We have 4 SSDs in a RAID10 setup for our database server. We recently replaced the SSDs and the data centre are asking if we want the cache to be enabled or if it was turned off for a reason. Is caching recommended for SSDs in a RAID10 setup? A read only cache? |
To ask your questions, head over here.
Video Summary
In this video, I’m Erik Darling from Darling Data, and we’re diving into another exciting Office Hours episode where the community gets to ask me their burning SQL Server performance tuning questions. Whether you have queries about optimizing your database setup or just want some general advice on life and everything in between, these episodes are packed with valuable insights. I cover a range of topics from the practical aspects of SQL Server performance tuning—like when auto-scaling isn’t necessary for RDS nodes—to more nuanced areas such as the changes made to Query Store in SQL Server 2022. I also delve into some technical queries, explaining scenarios where `LEFT JOIN WHERE IS NULL` might be preferable over `NOT EXISTS`, and discussing the implications of indexed templates designed with default sorting columns. Whether you’re a seasoned DBA or just starting out, there’s something for everyone in this episode.
Full Transcript
Erik Darling here with Darling Data, and I am just pleased as punch because we are about to do another episode of Office Hours! Yay! These are the episodes where I answer five user submitted questions that you, the user, submits. Typically about SQL Server performance tuning, but you are free to ask me personal questions about life, love, weightlifting, I don’t know, eating, drinking, sleep hygiene, general hygiene, etiquette, manners, whatever, whatever you need to know about because I’m, I’m a man of the world, baby! I know all sorts of stuff. If you would like to answer, ask questions that I answer on Office Hours, if you start answering questions, we’re in trouble. You have this wonderful, beautiful link right here in the most kindest of blues. That link is provided to you in the description. below the topic of video description. Below the topic of video description, there is also a link where you can sign up for a membership to say thanks for doing all this stuff that you do here on YouTube for SQL Server people. Join 60 or so other people who have said yes to the dress. If you like this content, but you don’t particularly like dresses, you can also like, comment, and subscribe. Other ways to help my channel become the top SQL Server channel on YouTube. All wonderful things that you can do. If you need help with SQL Server, if you just, if you’re staring at this thing and it’s just not getting any faster by you staring at it, you can hire me. I am a consultant and I do this stuff for a lot of people all the time. Health checks, performance analysis, hands-on tuning of your servers, queries, and indexes, dealing with SQL Server performance emergencies, and of course, training your developers so that you have fewer SQL Server performance emergencies in general, which is usually a great way to improve sleep hygiene. We’re talking about that. If you would like to get some training from me, you can do that as well. Go to that link, plug in that discount code, and you can get 75% off whatever you buy. If you buy the everything bundle, you can get that for about $150, and that lasts for the rest of your life.
So, live a long time. Happily, healthily, all that stuff. Upcoming events. This slide, all of a sudden, got very, very busy. There is a lot going on in the SQL Server data community at the moment. We have SQL Saturday, New York City, May 10th of 2025. We have Redgate on tour in New York City, August 18th through 20th, in Dallas from September 15th to 17th, and in Amsterdam from October 1st to 3rd. And, of course, we have the venerable Pass Data. Well, I suppose I should say Pass Data Community Summit, but I don’t want to spell community wrong and mess anything up.
Taking place in Seattle, November 17th to 21st. I should probably double check that, make sure. Let’s see. Our internet’s a little slow here. Let’s see. Pass Data Summit, 2025. Pass Data Community Summit. Oh, that’s a wrap for Community Summit. Hey, there we go. Yep, 17th to 21st. I was right. Cool. All right. Let’s go answer some questions now.
What’s that? I’m dead. No. Yeah, it’s so cold. All right. Let’s answer these questions here. We have an application that uses two RDS nodes, one write-only and one read replica. Well, that makes sense. Well, that depends on what you call a waste of resources.
Well, that depends on what you call a waste of resources. I don’t think you’re offloading reads if you’re keen on that. So I don’t think it’s a waste depending on how you’re using it. I don’t really see what auto scaling has to do with it unless you have a primary on like one set of hardware and a read replica on some diminutive set of hardware.
But that’s between you and whatever buttons you clicked in Amazon. But no, I don’t think it’s a waste. There are all sorts of good uses for replicas that don’t involve auto scaling. Bit of an odd bird there. All right. Can you give me a quick summary of the changes made to Query Store and SQL Server 2022?
Gosh, we are going into reading the documentation mode. All right. So let’s see here. Query Store is on by default. That’s a nice one. Query Store is on by default. There are all sorts of additional configuration options that you can set to decide which queries end up in Query Store.
It got like, what is it, optimized plan forcing or whatever that feature is called. That one’s kind of neat. You get Query Store hints, right? You can add hints. You can manually add hints to queries in Query Store. It got tied into, I think, a couple other features.
I forget if the parameter sensitive plan one needs queries to… It feels like it doesn’t. Maybe it does. I don’t know. But things like cardinality estimation feedback, memory grant feedback, those rely on Query Store. We’re supposed to get like the thing where it’s available on second, like AG second, availability group secondaries.
But that’s still not in… That’s like not safe for production, so says Microsoft, three years later. So, I don’t know. Got me on that one.
But yeah, that’s sort of the important stuff that I can think of off the top of my head. Let’s see. Hi, Eric. Could you record a video where you explain when left join where is null is preferable from not exist, if there are such cases? Well, I would if I ran across such a case.
For the most part, the cases I run into, not exist just works a whole heck of a lot better. So, if I happen to come across a case where the left join, where something is null pattern works out better, I will surely record a video for it because that will be a magical outlier to what I generally witness out in the wild. All right.
Let’s see here. This is probably a weird one. Okay. Are indexed templates for devs to use defaults? Okay.
So, you have indexed templates for your developers that presumably someone created where you default to sorting columns in descending order. Are there any performance issues you’ve run into with this for single column indexes? I know an index can be read both ways, so curious what it might impact.
Well, yeah, sure. I mean, lots of stuff. But it really depends on what your queries are doing.
Queries can be read both ways. However, backward scans are not eligible for parallelism. So, that’s one thing.
If you are using a lot of windowing functions where you need to sort things in descending order, this might be very helpful. If you’re not, it might not be. If a lot of your queries are asking for columns in descending order, this could be useful.
Like in the order by, that could be useful. But, geez, for someone who has made a template for developers, you sure seem unaware of the repercussions of your choices in designing these templates. Or whomever designed these templates could maybe answer the question as to why it was designed that way.
And shed some light on exactly their decision-making process for that choice. So, yeah, there’s all sorts of stuff that it can affect. But does it affect?
I don’t know. You and I would have to look at your queries together. It sounds like a fun consulting engagement. All right. We have four SSDs in a RAID 10 setup for our database server. We recently replaced the SSDs in the data center.
We are asking if we want the cache to be enabled or if it was turned off for a reason. Is caching recommended for SSDs in a RAID 10 setup, a read-only cache? So, here’s my take on it.
Is I don’t want… I generally don’t want any caching on my disks. Generally, I want my caching done with memory.
I know this isn’t going to directly answer your question. But really, your reads should be as non-reliant on disk in any disk settings as possible. You should strive to keep the set of data that you most often care about in the buffer pool because going to disk is always going to be slow and painful, regardless of any caching settings you have on there.
But the only thing that cache settings do is inflate crystal disk mark benchmarks. So, you know, sure, it might be nice to have some moderate cache of, like, hot data for reads on there, but your goal should not be to read anything from disk anyway.
You didn’t ask about right caches, but that’s a whole prickly world of strange things anyway. So, yeah, I don’t have a big strong opinion on this because my opinion gears more towards, hey, let’s not go to disk.
Let’s have it in memory so we don’t have to deal with it as much. You might be in a special situation where maybe you’re on standard edition and your buffer pool is limited to 128 gigs.
Or maybe you’re on enterprise edition, but you have, you know, 70 terabytes of data and you can’t possibly get enough memory to deal with that. That’s when you should be looking into stuff like compression, page compression, maybe even columnstore, maybe even using a handy script to clean up your indexes, like sp index cleanup, which you can get at my GitHub repo.
So there’s all sorts of things that I would want to get through before I start tinkering with disk cache settings because once you get, like, I feel like if you get to the point where you’re tinkering with disk stuff, you have sort of failed your workload in a different way.
Like there are many things within SQL Server that you, and, you know, with memory that you could be doing that would provide you much, much more benefit than worrying about this. This is not a finish line setting at all by any stretch of the imagination.
So work on some of the other more fundamental stuff first. All right. So that’s five questions, right? One, two, three, four, five.
So says the row numbers. The row numbers never lie. So we’re going to put this one in the bucket. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in whatever the next video is. Whatever just explodes out of my brain and onto the screen.
All right. Anyway. All right. Thank you for watching. Goodbye.
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.