T-SQL Tuesday #159: SQL Server 2022 & Purvi’s List

Welcome to another edition of T-SQL Tuesday. This month’s blog party is hosted by Deepthi Goguri (b|t) who asks participants to blog about their new favorite feature in SQL Server 2022.

In my opinion, 2022 is absolutely a major release with significant enhancements which should make it compelling to upgrade rather than wait for another release down the line. I’m thrilled for the improvements in Intelligent Query Processing, TempDB, and (after the training helped me ‘get it’) Arc-enabled SQL Servers. But that’s not what I want to blog about today.

It’s Often the Little Things

By sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.

Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.” When Bob put up Purvi’s List, I let out an audible “holy shit,” much to Grant’s amusement.

So what caught me by surprise?

Instant File Initialization (IFI) for Transaction Logs

Okay, that’s cool!

For simplicity’s sake, I’ll just quote the documentation (as written today):

Transaction log files cannot be initialized instantaneously, however, starting with SQL Server 2022 (16.x), instant file initialization can benefit transaction log autogrowth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16

So yeah, it is limited to 64MB growth size. But another entry on Purvi’s List is that the VLF algorithm has also been improved.

If growth is less than 64 MB, create 4 VLFs that cover the growth size (for example, for 1 MB growth, create 4 VLFs of size 256 KB). … and starting with SQL Server 2022 (16.x) (all editions), this is slightly different. If the growth is less than or equal to 64 MB, the Database Engine creates only 1 VLF to cover the growth size.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

So do I think everyone should now change their Transaction Log autogrow sizes to 64MB? Of course not. But do I think that this kind of small but interesting improvement is still notable and will hopefully be expanded on in a future release to a larger scale? Absolutely!

And there are a few other things on Purvi’s List too: reduced buffer pool I/O promotions and enhancements to the spinlock algorithms. All amazing work!

Thanks for reading.

Advertisement