SQL Server Performance Office Hours Episode 16

I’ve heard you talk about your career path a few times, and it’s pretty weird. Do you have any regrets? Are you still happy with what you do?

Do you have differing approaches for performance tuning an OLAP system vs an OLTP system?

Do you know of any disadvantages of using a filtered index to filter NULL values? We have a very heavy transactional table, like 10k trans/sec, with a clustered index and one non-clustered index. We don’t have any queries that select rows with NULL values from this table. The DBA team said we should avoid using a filtered index without any proof. What do you think?

In all your demos you compress (page) your indexes. Do you default to that with your all your client workloads? Do you see more benefit than a negative impact in your experience? Thanks!

I’ve seen you suggest columnstore for paging and dynamic searches. How do you make your non-clustered columnstore indexes perform acceptably on tables where all of the data is hot?

Video Summary

In this video, I dive into a series of questions from viewers, covering topics ranging from my career path and the challenges it has brought to performance tuning strategies for OLAP versus OLTP systems. I share insights on filtered indexes, page compression, and columnstore indexes, addressing common concerns and providing practical advice based on real-world experiences. Whether you’re curious about the ups and downs of a SQL Server consultant’s life or looking for tips on optimizing your database performance, there’s something here for everyone. Additionally, I discuss my upcoming courses and events, including the new “Learn SQL with Eric” course and Pass Data Community Summit in Seattle, offering early access deals that you won’t want to miss. So, if you have any burning questions or need some expert guidance on SQL Server performance, this video is a must-watch!

Full Transcript

Erik Darling here with Darling Data. And today me and my pal Bats here are going to kick off an Office Hours episode where I’m going to answer five of your most burning pertinent questions about life, love, high finance, extreme fitness, and of course SQL Server performance. So that’s what we’re doing today. If you like this channel and you want to support it with money, you can sign up for a membership. There is a link in the video description. If you like this channel, but not in a way that is monetarily beneficial to me, you are free to, for this is absolutely free, like, comment, subscribe, and of course, ask me questions for these Office Hours episodes that I enjoy so very thoroughly. If you need help with SQL Server, perhaps in a way that goes beyond what a YouTube Q&A can do, can do you for. I am available as a consultant to consult, to do these things live and in, well, I mean, sort of in person on your SQL servers. The usual stuff, health checks, performance analysis, hands-on tuning, responding to performance emergencies, and of course, training your developers so that you have fewer emergencies. No one likes heartburn, I guess. If you would like some performance tuning training from me, I have 24 hours of it available.

For 75% off, that brings the price down to about 150 USD, and that is a very good deal. I am also dropping a new course on T-SQL that is not part of that. It is a brand new thing. It is called Learn SQL with Eric. That’s me. It is in the works. It is up for tech review. I am recording things, and I’m going to be releasing it over the next summer period months, and it should all be complete by the time we get to pass. There is going to be the beginner and advanced material on that. If you are going to pass, if you are going to attend the pre-cons that I am doing at Pass, you will get free access to the material. But right now, the pre-sale price for the course is $250. Once all the material is fully booked out and live, the price will go up to exactly $500. The price is going to double when the material is complete, because by then, I am probably going to want to make more money off this thing.

Upcoming events. Lots of fun. Well, by the time this goes live, SQL Saturday in New York City will have come and gone. I should probably delete that, huh? Pass going on tour this summer. New York City, August 18th to 20th. Dallas, September 15th to 17th. And Amsterdam, October 1st to 3rd. And then, of course, all of those events presage, the big one, Pass Data Community Summit, taking place in Seattle from November 17th to 21st.

So with that out of the way, let’s answer some questions over here. Let me just tidy this up a little bit. Let me bring that over a little so everything fits on the screen once Zoomit decides the appropriate level of default Zoomit-ness. Oh, so it’s a non-SQL Server question first. I’ve heard you talk about your career path a few times, and it’s pretty weird. Yes, it is. I forget when the last time I talked about it. It was probably on that Simple Talk Redgate podcast.

Do you have any regrets? Are you still happy with what you do? Jeez, bare my soul, huh? So, yes, of course, there are regrets. I think most of them, though, are limited to me. I do a lot of consulting, and being on the phone a lot wears you down.

I always wish that I had more time to put into producing new training material for money, but it always seems like as soon as I’m like, wow, this week’s going to be nice and open for me to do all this stuff, there’s just like I start working on it. I’m like, yeah.

And then it’s like avalanche of new people need to have problems. Like, well, you know, I’m not getting any younger. You can’t say no to money, so that happens.

You know, I’m still mostly happy with what I do. Of course, there are ups and downs. Some days are more frustrating than others. But, you know, it has been weird.

And, like, you know, a lot of the weirdness with my career path was, like, prior to SQL. But there’s been plenty of weirdness with my career path, like, since then, too. You know, like, before I started working for Brendo’s Unlimited back in 2015, I was, like, a relative nobody, right?

You know, I had presented a few times and I had, like, you know, done some stuff, but, like, I had a blog that I maintained lightly. But, you know, I, like, I didn’t know how weird parts of the SQL community were before I started working there. Like, I was not at the cool kids table at all.

Like, I had no idea that there was, like, so much, like, just crappy high school clicky, like, stuff. So, like, when I started working there, you know, like, you find out about, like, all, like, the stuff that goes on, like, beneath the covers a little bit. And, you know, like, there are people, like, especially in the MVP community who have just been pure nasty to me because I work there, right?

Because, like, I have some, like, friendships and relationships with, like, Brenton people who work there. There are people who have just been awful to me throughout my career. And, like, they can all go fix cars for all I care.

But, you know, it’s one of the, like, like, like the weirdness didn’t stop when, like, you know, I stopped bouncing and got into databases. Like, it’s just been weird all throughout, right? It’s just, like, like, it’s strange stuff, right?

But, yeah, you know, no, like, no, like, giant regrets. Of course, you know, you know, there are things that I wish I had done differently and a bit more smartly when I first started my own consultancy up. And there’s stuff that I still wish I was, like, doing a little bit better at.

Like, you know, I’m not good at, like, SEO and marketing and all the other stuff. Like, you know, I can produce content. But, like, when it comes down to it, you know, I’m not, I am not a marketing master. So, you know, there’s stuff that I wish I was better at.

But, you know, my regrets are all my own. They’re not anyone else’s. So, anyway, let’s go on to the next question here, which is, do you have differing approaches for performance tuning and OLAP system versus an OLTP system? Well, yeah, of course.

You know, it’s OLAP is all about throughput. Sorry, OLTP is all about throughput and OLAP is all about latency, right? You know, OLTP, you need to be able to pound, pound, pound, pound, pound, pound, pound, and get a whole bunch of stuff in and out very quickly. OLAP, you need to have, like, big things happen faster, right?

So, you know, it certainly changes, you know, the things that you look at as far as, you know, like, which queries you go after. You know, like in an OLAP system, it might make total sense to go after things that take the, like, longest or use the most CPU to run. In OLTP, you do have to sort of balance that with, like, you know, what runs the most?

Is there anything we can do about this? Things like that. But, you know, there are, of course, differences, you know. Like, even, like, indexing strategies, OLAP, I’m going to push columnstore. OLTP, I’m going to push, like, narrow rowstore indexes, stuff like that.

You know, there are, of course, like, differences in those things. But, like, a lot of the environments I see are kind of mixed, right? Like, there’s, you know, there’s OLTP plus reporting, right?

Like, plus the OLAP stuff. So, you know, you do have to sort of balance out both, you know. OLAP is sort of, like, I don’t know. OLAP is interesting in a way because you have queries where, like, the expectation is that, yes, they’re going to take longer.

But, like, you also have to balance resource usage a lot differently, right? Because, like, you know, you might only have, like, four or five queries running at the same time. But, like, that’s where you really start running into, like, resource semaphore stuff.

You know, OLTP is typically where you start running into, like, thread pool stuff. Then when you mix them, you get both. It’s a real joy. But, yeah, there are different approaches to it. But, you know, like I said, a lot of the stuff that I see is kind of mixed.

So you do have to attack both sides of that coin when working on things. So let’s see here. The next question we have.

Oh, boy. It’s a lot of writing. Do you know of any disadvantages of using a filtered index to filter null values? We have a very heavy transactional table, like 10K transactions a second, with a clustered index and one nonclustered index.

We don’t have any queries that select rows with null values from this table. The DBA team said we should avoid using a filtered index without any proof. What do you think?

Well, what proof did they want you to present to them? That is the question. What proof? What are they looking for? How does one gather proof without being able to do an experiment in which evidence is gathered and some hypothesis is tested? So I don’t know what proof they want.

But, you know, the big thing with filtered indexes is they are really only terribly sensible if the filter is going to exclude a large number of rows. Usually you want to, you know, like if you’re only going to exclude like 25 or 30% of the rows, you’re not going to see a dramatic difference in query performance. Once you start getting to like the 50, 60, 75% range, that’s when you start to see bigger differences with things.

So, you know, first, you know, find out what proof they want. Second, figure out what your queries are doing. Figure out how many like null rows your filtered index would actually exclude and kind of go from there.

You know, take some of your, you know, assuming you have some sort of development environment, you know, create the filtered index that you care about and start testing queries against it. You know, there’s not really a downside. The only thing that you must remember is with your filtered indexes, whatever column, like you said, you want to exclude no values.

I assume there is a column or maybe multiple columns with no values that you are looking to exclude. Make sure that those columns are in the filtered index definition somewhere, not just in the filter, but like as included columns too. So SQL Server doesn’t have to do as much guesswork.

It has those columns available to it so we can evaluate whatever you want, look up free. There are a lot of peculiarities with the optimizer around filtered indexes, specifically with nulls that do sort of force you to need to have those columns in the index definition beyond just the filter. All right.

Let’s see what we got here. In your demos, hey, someone’s paying attention. Good for you. You can press page. Your indexes. Do you default to that with all your client workloads? Do you see more benefit than negative impact in your experience?

Yes, I do default to that. If you take a look at my new store procedure, SP index cleanup, part of the results in there, well, like for the part of the store procedure where like merge index, like index merge statements are like generated for you to like bring two indexes together. So you can replace like multiple indexes with a single index.

Like any index create statement there gets created with page compression by default. There’s also a whole section of the results that scripts out adding page compression to your existing indexes. I use that.

I use the hell out of page compression. Most people who I work with have far more data than memory. And page compressing indexes, you know, aside from columnstore compression, but you can’t put columnstore on a lot of tables for a lot of reasons, which is actually now that I’m looking ahead a little bit. I see that’s sort of in the next question.

But yeah, like page compression makes your data smaller on disk and in the buffer pool. And you can make way better use of the hardware that you have. So if there is any IO boundness to your workload, right, like you see a lot of page IO latch underscore sh and ex weights, you know, queries are just constantly going to disk.

You know, it could be when you look at weight stats as a whole or when you hit SP who is active and you see all these queries bogged up waiting on reading pages from disk. Page compression can take some of the edge off that by having smaller objects to a read, right? Like having less data to bring from disk into memory is a faster process.

And then having that data be compressed in the buffer pool means that every object up there takes up less space in the buffer pool. So you have more space for more things, right? It’s sort of like those vacuum bags where the people pack their, like, you know, winter clothes in when summer rolls around and they suck them down.

And like you have these like giant puffer jackets and blankets and stuff and just bring them down to this tiny little nice compressed thing. And it just makes you gives you a lot more storage space. So it’s the same basic idea there.

Okay, so the final question. What do we have here? I’ve seen you suggest columnstore for paging and dynamic searches. How do you make your non-clustered columnstore indexes perform acceptably on tables where all of the data is hot?

Hmm. Hmm. So if you pay very careful attention, one thing that I say is that columnstore indexes should generally be reserved for large tables. Usually, you know, a lot of the pamphlet material from Microsoft is at least a million rows.

I’m not sure that that’s where I start on. I think, you know, 5, 10, 15, 20 million are more sensible numbers there. So we’re going to run a question by you.

And that is, do you really have 5, 10, 15, 20 million row tables where all of the data is hot? You have a table with that many rows where people are just constantly updating all 5, 10, 15, 20 million rows. Do you really?

Honestly, truly have a table like that. That would be a very strange thing. Very, very strange thing indeed.

I think perhaps you’re misinterpreting where I suggest using these columnstore indexes. If you truly have a table like that, then that would be an interesting consulting engagement. Good Lord.

Yeah, so, you know, you are right that, you know, updating columnstore indexes does not quite always go as well as updating the rowstore indexes. But, boy, I think there is some attention that needs to get paid to the types of tables that make good candidates for columnstore indexes here. All right.

I’m out of breath. I’m winded. My allergies are terrible. My lungs are not at full capacity. So I’m going to go breathe for a little bit and then I don’t know what. But anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something and I will see you in whatever video we do next. All right. Cool. Thank you. 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.