T-SQL Tuesday #159 – Favorite SQL 2022 Feature

It’s another T-SQL Tuesday! This month, it’s hosted by the one and only Deepthi Goguri (b | t). This month, Deepthi has two question for us:

1. Blog about your new favorite feature in SQL Server 2022 or in Azure. 
2. What are your new year resolutions and how do you keep the discipline doing it day after day?

Deepthi’s first question is an easy one for me:

A couple of months ago, I wrote about my initial thoughts to SQL Server 2022. I think a lot of what I wrote still holds true. It feels like it’s building on the functionality from previous versions, especially when it comes to Intelligent Query Processing and its feedback capabilities.

The feature I’m most curious to find out more about is the Degree of Parallelism Feedback. (You can read more about it here.) One of the main reasons for this is I know very little about the settings.

Several years ago, I was troubleshooting performance. The database had about 1 TB of data and the server specs were a dream – super fast, multi core, etc. But the max degree of parallelism was set to 1. I was stunned. Why would such a high powered machine with a massive database have parallelism turned off? Apparently, parallelism caused performance problems and someone found a post somewhere saying that if you turned it off, it wouldn’t be a problem. So they did. It was before my time so I couldn’t ask questions like, did you try tuning the query or turning it off just for that one query or try adjusting other settings?

Here’s the thing – I’m a development database professional. I spend my time in development so I don’t get to see these sort of performance tuning issues in the wild. If I do, I tend to rewrite a query or figure out missing index; I don’t figure out how to fine tune the configuration settings for parallelism because our clients controlled the hardware. We could give clients specs for what we felt the minimum configuration should be, like your server should have X amount of RAM and this much set to the Max Memory setting for the SQL Server. But I never really used parallelism settings for tuning performance because it wasn’t something I really had access to or could really take advantage of. Or even know that I should take advantage of.

Which is why that one server threw me for a loop. Naturally, I did some digging to learn more. I didn’t find a lot about this. I found that there was a recommendation that you change the cost threshold for parallelism setting from 5 to either 35 or 50, depending on the post. Also, the recommendation was to set the MAXDOP to 8, regardless of the number of cores. But beyond that, I don’t feel like I found a lot of information. There were some posts that got more involved but unfortunately, I couldn’t look into more.

But now SQL Server can help us. We’ll still want to set up limits as we have before but the engine will figure out if it would be more efficient to use a lower MAXDOP in certain cases and try to stabilize the degree of parallelism for queries as needed. It won’t use more than what’s allotted, which probably caused the issues in the first place. But we no longer have to play with these configuration settings to figure out what’s going to be most optimal for performance.

But it’s going to be one small thing that makes the difference for someone like me. The execution plan will include reasons why our query can’t go parallel. If I wrote SQL in a way that prevents parallelism and causes performance issues, that’s something I want to know about so I can fix. It’s one small thing that’s going to be really useful.

As for the second question, I’m horrible about resolutions. But there are a lot of things that I’ve been working on for awhile. It’s not a resolution per se, but I’m just looking forward to getting some projects out there with you all. So stay tuned.

Looking forward to reading everyone’s post. And thanks for hosting, Deepthi!