Look, I don’t blame you if you haven’t dug deep into what SQL Server 2022 has to offer just yet. It’s hard enough to keep up with all the problems fixed and caused by cumulative updates.
One thing you may want to pay attention to is how transaction log files are grown and VLFs are created, especially for new databases.
First, all new databases created on SQL Server 2022 have autogrowth set to 64MB. This allows for two things:
- Those growths can take advantage of instant file initialization
- That 64MB growth increment will create a single VLF
Now, you might be excited about log files getting instant file initialization. But it only works for those small growths. Perhaps only in Azure would a growth of 64MB not be instant anyway.
I don’t know anyone else who spends a lot of time waiting for database growths of 64MB to finish.
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.
Other changes to VLF creation are something along the lines of:
- <= 64MB: 1 VLF
- >64MB and <=1GB: 8 VLFs
- > 1GB: 16VLFs
If your log file autogrowth settings are larger than 1GB, either because you set them that way, or because your files are growing by a percentage, you may end up with some rather large VLFs.
To see what your current settings are, run this query:
SELECT database_name = DB_NAME(mf.database_id), logical_name = mf.name, file_name = mf.physical_name, size_gb = (mf.size * 8) / 1024 / 1024, max_size_gb = CONVERT ( bigint, CASE WHEN mf.max_size = -1 THEN 0 ELSE (mf.max_size * 8.) / 1024 / 1024 END ), autogrowth_mb = CASE WHEN mf.is_percent_growth = 1 THEN RTRIM(mf.growth) + N'%' WHEN (mf.growth * 8 / 1024) < 1024 THEN RTRIM((mf.growth * 8) / 1024) + ' MB' WHEN (mf.growth * 8 / 1024) >= 1024 THEN RTRIM((mf.growth * 8) / 1024 / 1024) + ' GB' END, usage = CASE WHEN mf.type = 0 THEN 'data' WHEN mf.type = 1 THEN 'log' WHEN mf.type = 2 THEN 'filestream' WHEN mf.type = 3 THEN 'nope' WHEN mf.type = 4 THEN 'fulltext' END FROM sys.master_files AS mf WHERE mf.database_id > 4 AND mf.type = 1 ORDER BY mf.database_id, mf.type, mf.file_id OPTION(RECOMPILE);
If you’re planning on moving to SQL Server 2022, now’s the time to make adjustments so you’re not caught off guard.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.