This month’s invitation from Glenn is to write about what we’ve been doing with SQL Server 2022 as we’ve now had a release candidate unleashed upon the world.
Personally I haven’t had the time to adventure through the new features or put any stress onto the version as of yet – the exception being testing some of the scripts for this blog against the earlier CTP builds to make sure that it doesn’t break anything that I’m writing about.
That said, this invitation has at least given me the incentive to review some of the new features being touted so I thought I’d cover some of these areas which are of more or less interest to look into further when the time is available.
Start with the good stuff…
Typically when it comes to new features within SQL Server I look for the ones with a performance tilt to them. Excluding any standout features within the query processing or execution the one area where the gains can be felt consistently is in the underlying engine processes.
A couple of these within the upcoming SQL Server 2022 release which caught my eye are the improvements to page latch concurrency and parallelism in buffer pool scans. Whilst I can’t say that I’ve ever hit issues with these before I wanted to take a dive into what benefits these may present day to day.
The page latch concurrency immediately jumped out particularly regarding TempDB performance and is discussed by David Pless over on YouTube. With TempDB potentially getting heavily used for temporary tables, worktables, RCSI versioning amongst other things there is a lot of opportunity for contention of the Shared / Global Allocation Map (SGAM / GAM). My understanding from David’s presentation is that in previous versions of SQL Server, the latches on these are effectively serialised.
In the SQL Server 2022 release the way that latching is performed means that multiple operations can now be completed in parallel across multiple threads. This sounds like a win regardless of the situation but those with the higher concurrency workloads – where this contention is typically seen – may see some major improvements to performance.
For the buffer pool scan improvements there’s another talk with David on YouTube where he discusses this feature. Essentially in prior versions of SQL Server the buffer pool operations are serial processes so in environments where more memory is available some operations – such as log restores, DBCC operations, etc. can appear to be slow.
With the arrival of SQL Server 2022 we’ll see these operations utilise multiple cores. Their internal (likely best case) scenarios evidenced up to 15x performance increase. Some later examples showed a significant benefit on smaller tasks such as database creation or offlining and then onlining a database, with slightly less – but still significant – improvements to CHECKDB where the key bottleneck isn’t necessarily the buffer pool.
Both of these features are on for default with a SQL Server 2022 and will likely show benefits to most workloads regardless of their size, with the most benefits seen for larger workloads / environments or providing additional scalability to growing workloads.
Not so sure about…
Staying with the performance side, there are usually features which I feel more skeptical about from the outset – particularly when they’re branded ‘intelligent’.
Listed under the title of ‘intelligent query processing’ we’ll find a couple of features listed which I’m not quite as sure about but yet still optimistic for the opportunities. These would be the degree of parallelism feedback and parameter sensitive plan optimisation. These are discussed by Pedro Lopes in the same YouTube playlist.
The degree of parallelism feedback is designed to help reduced unneeded parallelism for a query by dynamically adjusting how parallel a query may go. The engine will look for parallel inefficiencies based on timings and wait stats, and where it finds these it will start to reduce the degree of parallelism down from the default value for future executions. This will then get re-evaluated on subsequent executions which may further increase or decrease that level of parallelism.
When looking at this feature it sounds similar to the memory grant feedback introduced in SQL Server 2019 which has its own challenges. My concern is that the execution of this may be somewhat similar and you may end up with subsequent executions of queries having erratic performance based on the query which came before it.
Next up was the parameter sensitive plan optimisation – which as we could infer from the name is designed to help mitigate the ‘parameter sniffing’ challenge. This feature allows multiple (up to 3) plans to be cached for a single query. The multiple plans which are cached aren’t arbitrarily taken, they’re generated based on the predicates in the query and an analysis of them by the engine to determine which of them may cause variances in the ideal query plan. The result is potentially multiple plans being available at execution time and a more appropriate one potentially being chosen than simply the first one being cached.
Parameter sniffing is one of those challenges which we inevitably hit as systems scale up and particularly as some of our data distributions get skewed. Simply the ability to have more-than-one plan in the cache for a query may resolve a good portion of cases where this may be creeping in. With that said it’s still true that even with multiple plans, if our data is skewed in just the right (or wrong) way then we may end up with parameter sniffing now having a negative impact across multiple plans due to this feature.
Wrap up
With each new version of SQL Server we’ve got a plethora of new features being touted. Through the more recent released there’s more lean towards the cloud even for the on-premises solution, but we can see continual features to assist a variety of use cases.
My favorite features are the ones which ‘just work’ out of the box which on face value the changes to page latch and the buffer pool may well do for us. That’s not saying that the other features don’t have their place, but they may need to be implemented more sparingly.
Both the parallelism feedback and the plan optimisation are great features which will likely help in general usage right out of the box. My concern for these would be more in environments which have extremely high throughput or are already highly optimised where these may add elements of uncertainty into an already-stable environment. This is the behavior you may be wanting to avoid with an upgrade to the new engine.
With that all said, this is purely my initial impressions and I wholeheartedly understand that I haven’t tried these features out in real-world scenarios so I’m certainly not discounting anything. In fact if you check out the query processing enhancements in the What’s New release notes for SQL Server 2022 you’ll find a raft of additional features available extending the work already done in SQL Server 2017 and 2019 versions.
I look forward to seeing these materialise in some real workloads and getting some hands on time with these in the future!
Andy Brownsword 