SQL Server Performance Office Hours Episode 34
Questions:
* Why do parallel batch mode sorts only utilize 1 thread?
* With all of the recent content frequently mentioning no locks and read uncommitted isolation level, Is there ever a situation that you would recommend using nolock or is it always avoidable?
* What’s required for my query to use batch-mode on my rowstore indexes? Like what version or compat level is required? I remember a while ago, a fake join to a columnstore index was required. Are there any hints I can use?
* Hi Erik, i admire all the work you do for the community, thanks a lot! You are one of the great minds of SQL Server.
* what happened to the old sqlperformance.com website? they just suddenly stopped posting. but the authors are still around and authoring on other places on the interwebs
To ask your questions, head over here.
Video Summary
In this video, I dive into answering five user-submitted questions during my Monday office hours session. Whether you’re curious about parallel batch mode sorts or the nuances of using NOLOCK hints, there’s something for everyone. I also take a moment to thank those who support my work through likes, subscriptions, and sharing with friends—every bit helps! Additionally, I share some exciting upcoming events where you can catch me live in person, including the Redgate Pass-on-Tour in Dallas and Utrecht, as well as the Pass Data Community Summit in Seattle. If you have any questions or want to see me at one of these events, make sure to check out the links provided in the video description for more details on how to get involved.
Full Transcript
Hey, it’s me, Erik Darling here with Darling Data. And in today’s video, we are of course, it is Monday, so we do office hours and I answer five of your wonderful user submitted questions. And that’s fun. Before we do that, if you want to ask me a question for office hours, the link to do so is down in the video description. If you would like to hire me for consulting, buy my training, or become a paid channel member to support the effort that I put in the video, and then I put into all this SQL Server content for you. All of those links are available there as well. If you are uninterested in a monetary exchange with me, you can do free things that make me just glow with happiness. You can like, you can subscribe, and you can tell a friend or two or 20 or a thousand, assuming you know that many people. I’m not sure how full your Rolodex is, but… Hopefully it’s more… Hopefully it’s not just your mom. But your mom is important.
If you would like to see me out on the road, live and in person, I will be in Dallas, September 15th to 16th, and Utrecht, October 1st and 2nd. These are the pass-on-tour dates that Redgate is putting on this year. Aside from that, I also have Pass Data Community Summit in Seattle, from November 17th to 21st, where I will be doing not one, not three, but two days of T-SQL performance pre-conference. I will be doing the pass-on-t-cons with Kendra Little, and that’ll be great for everybody. Especially you. Because you’ll finally learn T-SQL, and that’ll be great. Anyway, let us office hours away here.
All right. First up, why do parallel batch mode sorts only utilize one thread? Well, I’m gonna… So there are two parts to this. One, your question is correct about one part, but parallel batch mode sorts, of course, do the actual sorting work on multiple threads. They do not do the sort on one threads. The sort only produces one thread.
The best explanation that I’ve ever seen in detail of why is in a Paul White blog post that I will put in the show notes about why batch mode sort spills are so slow in SQL Server. It has to do with the sorting algorithm that SQL Server uses internally and the complexity of producing parallel output from a batch mode sort. There is, of course, one operator in all of SQL Server that currently does support receiving parallel threads from a batch mode sort, and that is the window aggregate operator that gets used when window functions are processed in batch mode.
So there’s that. But I’ll put the link to Paul’s post in the video description and everything, because anything further that I said about why that is would be pure plagiarism, because the technical details there are best left up to the best SQL Server consultancy in all of New Zealand. Outside of New Zealand, it’s, of course, me, but within New Zealand. That’s Paul White.
All right. So next. With all of the recent content, frequently mentioning no locks and read uncommitted isolation level, is there ever a situation that you would recommend using no lock or is it always avoidable? Well, there are. So, you know, if it’s a query that you don’t particularly care about and it’s causing problems and no one seems to really care about what the results of that query are, you are free to put no lock hints on it.
You know, that would be that would be the first place. A second way of thinking about it would be a situation where. So one thing that’s interesting about the no lock hint.
is that it allows for SQL Server to use what’s called an allocation order scan, which basically just reads like data file contents in the order that the files were created. And in a nutshell, there’s there’s a little bit more to it, but, you know, I don’t have days to talk about these things, which can be faster in some situations, especially if you are reading pages from disk, I think. And so if you have a data warehouse type workload where you have a lot of data that gets loaded, say, at night or in the morning or something, and then like the data is fairly static during the day, you could use no lock hints on your big tables to sort of get encourage the optimizer to use an allocation orders order scan versus an index order scan, which can be faster.
So that would be one use case for it there. It would be very similar to if you changed your data warehouse to be read only once data loads were finished. That’s another way to achieve a similar result.
So that would be when I might use no lock. Aside from that, you know, I will judge you for using it. What’s required for my query to use batch mode on my rowstore indexes?
Like what version or compat level is required? I remember a while ago, a fake join to a columnstore index was required. Are there any hints I can use?
So there are no hints that you can use that I’m aware of. And that would extend into saying there are no supported hints that I am currently aware of that would allow that would force batch mode to occur. The fake join to a columnstore index will still get sometimes.
So the the the the the tricks you can play on SQL Server, one of them is, of course, the fake join to a columnstore index. The other would be creating a non clustered columnstore index on one of the involved tables with a filter that cannot possibly contain a row like say where ID equals one and ID equals negative one. No ID can be one and negative one at the same time.
So there would be no rows in the filtered index, but you would still have a columnstore index on the table that might encourage the optimizer to use batch mode in some places that will that will not. So like the tricks that you can use, I’ve found are not baked as deeply into the optimizer is when batch mode on rowstore happens. Like like something like some like sometimes you’ll see when you do one of the tricks, you’ll still read from tables in row mode.
But when batch mode on rowstore happens, you can read from rowstore indexes using batch mode. So there are some subtle differences that can affect how effective the batch mode is in the execution plan. But to get to your actual question, you need to be on Enterprise Edition.
It does need to be SQL Server 2019 or better. And you do need to be in compat level 150 or higher. If you have control of your queries and you meet most of those most of those requirements, you can use the option use hint.
And you can say the option use hint, the compatibility level 150 or 160 or if you’re in the cloud or something 170 in order to get a higher compat level for the query than what your database is currently set to, which can allow for additional batch mode stuff to kick in. Batch mode on rowstore itself has a bunch of heuristics. It looks at like the size of the tables, the complexity of the query, the joins and everything like that.
And it makes a runtime decision about whether to use batch mode on rowstore for your query when that happens. Like I said, you can use various tricks to encourage it, but those various tricks often do not get batch mode across the breadth of operators that batch mode on rowstore does. All right. Next up.
Hi, Eric. I admire all the work you do for the community. Thanks a lot. You are one of the great minds of SQL Server. That’s very kind of you. This is I’m going to.
This is not a question. I just want to point that out, but it is very kind of you to say that. And I appreciate I appreciate the sentiment there. So thank you. I think you are.
I think you’re also a great mind, but I’m not sure in what community you are a great mind. But I am sure someone benefits from you somewhere. So good job. All right.
Oh, here’s a fun one. What happened to the old SQL performance dot com website? They just suddenly stopped posting, but the authors are still around and authoring on other places on the interwebs. Well, this is another SolarWinds special.
You see, when SolarWinds purchased SQL Sentry and the or whatever Sentry one and the SQL Sentry monitoring tool was part of that package, of course, two things happened. Both the SQL performance site that that SQL Sentry had hosted and paid people to blog on for years suddenly stopped being a monetary priority, as did the SQL Sentry monitoring tool. So again, as I’ve said before, someone at SolarWinds should be in jail for neglect of these resources for the SQL Server community.
But in a nutshell, that’s what happened. You know, I guess the authors there were not interested in blogging there for free and all sorts of things on the site have deteriorated. Code examples are no longer formatted well.
Everything is a nightmare. I do know that Aaron Bertrand has migrated the I think if all. Well, oh, no, that was from SQL blog that he did that.
I don’t know if Aaron has migrated any of his posts from SQL performance elsewhere, but Paul White has moved all of his posts to his his personal website, SQL dot Kiwi. That’s SQL dot Kiwi. Any posts that Paul had on the SQL performance site is hosted there with a very similar URL just at the SQL dot Kiwi domain.
And I believe for some of them, he has updated the contents to reflect newer changes in SQL Server where were applicable, I suppose. Anyway, that’s about what happened there. It is a shame.
You know, that’s no longer an up to date resource for things and it’s just sort of withering on the vine. But, you know, there’s still some good stuff there from back when people cared about things. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video. I think can never be quite sure. I think I think that’ll happen.
But, you know, maybe maybe I’ll just get drunk and decide to retire. Who knows? That’s still early.
All right. Thank you for watching.
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.