SQL Server Performance Office Hours Episode 26

Some of my reporting queries finish a little faster with NOLCOK hints even when nothing is going on in the database. Any ide why?

I get the argument that new features sell better than fixed up old features, so how come highly upvoted User Voice items for new features have sat around for years with no action?

There are a lot of applications out there that do horrible things to SQL Server. Why don’t software vendors hire people like you to fix things?

What is batch mode not a good fit for?

Is it always possible to tune queries to save money in the cloud with smaller hardware?

Video Summary

In this video, I delve into some intriguing questions submitted by viewers during my Office Hours session. We explored topics ranging from why certain queries perform better with no lock hints to the challenges of getting new features implemented in SQL Server. I also touched on the dynamics between software vendors and database performance experts, offering insights on how to optimize cloud costs through query tuning and hardware reduction strategies. Whether you’re a seasoned DBA or just starting out, there’s plenty here to help you navigate the complexities of SQL Server optimization.

Full Transcript

Erik Darling here with Darling Data, and we have another exciting rendezvous with Office Hours. My favorite. That’s where I get to be a real man of the people and answer some people questions that you submit. If you want to submit your own question, you can go to that link, which is down in the video description. If you feel so inclined and you like the material that I produce on this YouTube channel, you can go to that link, which is down in the video description. If you feel so inclined and you like the material that I produce on this YouTube channel, you can also sign up for a membership, you can also sign up for a membership, and you can support me with as few as $4 every month. If you need SQL Server Consulting help, I am out there in the world, working with clients, helping people, tuning queries, doing all sorts of stuff. And as always, my rates are reasonable. If you want to get in on my performance tuning training, you can get all 24 hours of it for about $150 buckaroos that is at that link with that discount code. That is for the everything bundle that covers my performance tuning training. If you would like to buy my new T-SQL course, that is also available there. All 23 hours of beginner content is done and recorded. This course will double in value from $250 up to $500 after the advanced material comes out after the summer months.

So save yourself some money now and buy that while you can. Redgate is being a bunch of real sweethearts and dragging me out of my house to varying degrees this summer. New York City, August 18th and 19th. Dallas, Texas, September 15th to 16th. And Utrecht in the Netherlands, October 1st and 2nd. This is all part of the Pass on Tour events that they are putting on, little mini Pass events. And this is all leading up to Pass Data Community Summit in Seattle, September 17th to 21st. But it is time to go answer some questions now. It is time to have a bit of question asking and answering fun. All right. So first up, here is an interesting one. Some of my reporting queries finish a little faster with no, let’s just call that a no lock hint.

Even when nothing is going on in the database. Any eyed why? Ah, sane eyed. So when you use no lock hints, aside from the fact that you are requesting the read uncommitted isolation level, which will allow your queries to ignore locks taken by modification queries that are executing concurrently, you also allow the SQL Server storage engine to access data a little bit differently. It can use what’s called an allocation order scan using IAM pages rather than using index pages to read through stuff. And that can sometimes be faster.

So, um, like that, that is most likely why I have an old video about it where there was a really dramatic difference between a query with no lock hints and a query without it with nothing else running on the server. Uh, part of it was reading, uh, like from a cold cache that was like reading from disk into memory. Um, you might look at the difference between those and you might see the, uh, the ordered property of whatever index scan operators being false.

Now keep in mind, this is for index scans. Index seeks don’t really get that behavior because you’re seeking along a B tree, but scanning a big index will, will definitely see that. All right. Next up here we have, oh boy, getting, getting personal here. I get the argument that new features sell better than fixed up old features.

So how come highly upvoted user voice items for new features have sat around for years with no action? Well, apart from the fact that, um, Microsoft seems to have the attention span of a squirrel that got into a bag of meth. Um, you know, uh, a lot of what, like, like you have a, like every, like every release, they have like a certain amount of budget that they can spend like people’s time and like salary on to develop stuff.

And, you know, some, some narratives get pushed a lot harder than others, you know, like, um, turning pink because of some weird light thing here. But like, like, you know, just like, if you look at like a lot of, a lot of recent releases, you know, like some narratives got pushed really hard that ended up just being like nowhere’sville. You know, like, um, you had big data clusters, which are a complete flop, right?

They’re, they’re completely deprecated now. Uh, ledger tables, no one cared, right? Like blockchain stuff. Um, you know, it’s like, there’s just a lot of things that, they get poked out there that, you know, are driven by these sort of like checkbox items to, you know, make sure that you have the new shiny thing in your database that people that like executives and C-levels currently care about.

And not a lot of that stuff aligns with what users care about. You know, it’s like, uh, like from what I know talks about, like, you know, improving partitioning come up, uh, like every release cycle and everyone’s just like, well, I mean, whatever. Uh, you know, the argument is that like, you know, it’s like, it’s like this stupid circular argument.

It’s like, like, well, the feature kind of sucks. So no one, like, yeah, like we could fix it. And like, yeah, but no one uses it. So it wouldn’t really have that much impact.

It’s like, well, no one uses it cause it sucks. Right. It’s like, you made it painful to use. Of course, no one uses it. Right. Like, of course it doesn’t have wide adoption. You made it miserable. Right. So, you know, uh, don’t expect that to change. Uh, I guess that’s the bottom line.

Unless you are like Walmart or something or SAP and you, uh, swing a big purse at Microsoft and you really want to push for stuff. You probably, you, you have the worst voice in the room, the quietest voice in the room. Um, and you’re like, like that, that user voice size is just a black hole for ideas anyway.

So like, unless you’re reporting a serious bug, uh, good luck. Good luck out there. There are a lot of applications out there that do horrible things to SQL Server.

Why don’t software vendors hire people like you to fix things? You know, that’s a great question. Um, I have actually worked with a number of software vendors. I am always happy to do so.

Uh, and it is always nice to improve products as a whole. Uh, I have noticed that this, this trend has actually peaked up a bit, uh, since like software as a service has become more and more ingrained in what a lot of companies are offering. You know, when it was like, you know, we sell you a piece of software and you install it on a SQL Server that you’re responsible for.

You couldn’t trust anyone to install anything and do anything right. Like you had no idea what version or a, like you could, you could put out a best practices guide, right? And you could say like, here’s a setup checklist.

Here’s the stuff you should do. Like here, like the bare minimum requirements here, like, you know, hardware requirements, depending on database size, stuff like that. There is no guarantee that anyone’s going to follow any of that. So, uh, the software as a service thing has, um, has, has gotten me hired by more software vendors because now they’re responsible in house for like infrastructure, the code, the indexes, everything.

And so I have been doing more work like that, but, uh, I would love to do additional work like that. So if you know of a software vendor who you think, uh, should, should hire perhaps an Erik Darling in the world who has, who has very reasonable rates, uh, to improve their software offering, you, you, you feel free to send them. Well, I don’t, I don’t have a resume.

You can send them to my website and that’s, that’s probably as good as it’s going to get as far as, uh, resumes go. Uh, let’s see. I seem to have lost my Zoomit cursor there. We’re going to try that again.

Uh, so here’s a, here’s an interesting question. What is batch mode not good for? Well, I think, you know, uh, I assume this question is because I’ve been, you know, uh, doing some videos lately about stuff that batch mode is good for writing batch mode friendly queries time. You know, when like, you know, like how to do stuff, how to think like batch mode, stuff like that.

So, uh, batch mode is typically like, you know, good for like your heavy lifters, right? It’s like your, your, your bigger queries that have to like go through a lot of stuff. If you just have to like find a few rows and like send a few rows along and, you know, stuff like that.

If it’s like, think like, like, you know, OLTP ish workloads, uh, batch mode isn’t generally a great fit for stuff that you do in there. Like typically where batch mode comes into, into play are, you know, when you have to do like big scans of tables and you have to aggregate a lot of rows down, uh, or, you know, you’re like, you know, you have like, you have to like hash join a lot of rows or hash aggregate a lot of rows, stuff like that. If a lot of your query plans are like single threaded, like little loopy join things anyway, that’s, that’s really not a good fit for batch mode.

Obviously modification query is not a great fit for batch mode, things like that. But, you know, um, you know, it, it’s, I, I am happy that Microsoft has stopped locking some of the intelligent query processing stuff behind batch mode because that was, that was kind of a drag. Uh, but you know, I, I, I do wish that like batch mode on rowstore was a little bit more lenient with where it would kick in.

But anyway, uh, let’s go on to the next one here. Cause this is, this is, this is a kind of a fun thought bubble question, isn’t it? Uh, is it always possible to tune queries to save money in the cloud, but like with smaller hearts?

So I mean, I assume that means like by reducing hardware. So I would say generally, yes, but it really does depend on where you’re starting. If you’re already not on, uh, if like, if the hardware that you’re on is like sufficient for what you’re doing and, you know, like you, you’re thinking to yourself, I need to add hardware to keep up with this.

Then yeah, absolutely. Um, but if you’re, if you’re at a point where like, you know, you have like terabytes and terabytes of data and, you know, and like the, like maybe like, you know, high double digits, low single digits memory. And, you know, depending on kind of like some, you know, user concurrency parallelism stuff, like a small number of CPUs, it’s, it gets harder to, uh, you know, tune things so that, uh, so that you can reduce hardware for that.

You can certainly tune things to make better use of the hardware you currently have, which can give you a better idea of what you might need to increase stuff to. So like, like one way to think about it is, you know, like if, if you’re on like a smaller server and you know, you need to go to bigger hardware, it could certainly pay off to do a lot of query and index tuning. So that, uh, you can make a more, like you can make a more like good conservative choice about the next hardware step up.

So like, instead of like quadrupling hardware, maybe only like double or like, you know, 1.5 X your hardware. So you don’t have to go like, you know, like nuclear and get like, you know, like four terabytes of memory or something. Right.

Like not that, you know, a lot of, a lot of, a lot of cloud instances don’t even go up that high, like managed instance piece of crap. But, uh, anyway, um, like, you know, like it is, it is always possible, but really it depends. Like if you, if you have like, you know, like if you just went like to the absolute biggest server you, you could possibly find, uh, and like, you know, no one’s really doing the, no one, no one’s doing, or no one has historically done like meaningful query and index tuning.

Then yes, absolutely. You know, you can, you can bring a lot of stuff down that way. But, uh, you know, a lot of the stuff that I see is sort of painfully like under, under provisioned, uh, from a hardware perspective as a starting place.

And that just makes it a lot, that this makes it a lot harder to do stuff that can make enough of an impact to say, oh, now you can reduce hardware further. It’s like, cause you’re already spending like, you know, some tiny little instance size already, you know, and you’re, you’re expecting, you know, this miraculous 24 seven mission critical 30,000 batch request plus reporting workload to, to all of a sudden fly on that thing. It’s, it’s a little bit tougher of a proposition.

Um, so, you know, like, but there are things you can do, obviously, you know, like, you know, like aside from like the generic index and query tuning, um, you know, I like one of the, one of the biggest things that I, I push is, uh, compressing indexes, which makes them smaller on disc and in the buffer pool. So that you make better use of, you know, those expensive, like, you know, uh, IOPSy discs that you, uh, pay, pay too much for. And so that, and you also make better use of the memory on the server.

Um, so, you know, there’s of course, you know, times when absolutely you can, you can like hit home runs with this, but there are also times when, uh, you know, you like, sometimes you, sometimes you put in all the effort and like, you just allow the server at that size to tread water a little bit longer. So, you know, always no, but generally, you know, you can, you can, you can do meaningful things to, uh, to improve the situation almost regardless of what, what the starting point is. But, uh, there, there are some, certainly some outlier starting points that I’ve seen that, uh, you know, like you could, you, you could tune, you could tune to your heart’s content and, uh, you just wouldn’t move in.

You wouldn’t move the needle enough to, um, like to, to allow, like allow for a downgrade of hardware. Anyway, that’s about good for this one. We have answered five questions, me and the voices.

Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. 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.