In SQL Server 2022, set your transaction log file autogrowth rate to 64 MB

A late-breaking change that arrived in SQL Server 2022 is an important one that could be a new line item in your health checks.

The basics:

Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization. 

This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether. 

There's lots more cool new stuff in SQL Server 2022, too.

Impact to health checks:

The default autogrowth rate for transaction log files in new databases is already 64 MB. Unless you have specific needs for your workload, consider keeping this default now, considering the dramatic advantage IFI brings to file space allocation.

In both your database and log files you should proactively pre-allocate enough space to avoid autogrowth events anyway. Regularly monitor the space in database files to prevent autogrowth events. The proactive DBA, outside of busy hours, should grow database data and log files manually, so that they do not autogrow during busy hours. 

And whatever you do, don't shrink your data and log files, only to have them autogrow again the next business cycle.

Details:

• Applies to both manual file size changes and autogrowth events <=64 MB.
• Transaction log growth events larger than 64 MB are unaffected by this performance improvement. No change.
• Still no IFI for database restores or initial transaction log file initialization for new databases.
• This change to autogrowth events up to 64 MB is new for SQL Server 2022, and arrives Azure SQL Database soon if not already, from what I understand. 
• It is not yet available for Azure SQL Managed Instance, but I would guess it will be there soon.
• This feature is available on all editions of SQL Server.

Make sure IFI is enabled:

Of course, this only works if your SQL Server instance is capable of instant file initialization. The SQL Server service account must be granted the Perform Volume Maintenance Tasks permission in Local Security Policy on the Windows server. A quick script works for modern versions of SQL Server: 

SELECT servicename, instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE filename LIKE '%sqlservr.exe%';

I'm speaking at PASS Data Community Summit 2022

 

Looking forward to speaking in Seattle at next week's PASS Data Community Summit by Redgate. I'll be speaking both as a representative of my role on the Microsoft Database Docs team and as a data professional. 

On Wednesday Nov 16, you'll find me in the main exhibit hall in the Microsoft Booth Theater (#217) giving a presentation on contributing to Microsoft Docs, at 530pmPT. Hope to see you there. If prizes are available to give away, you know I'll give em up.

On Thursday afternoon, Christine and I are presenting together on a topic that is important to us, Ethics in Modern Data. I bring my years as a data professional and Christine brings her experience and a Masters degree in Organization Psychology, combined with our passion for history, civil rights, and technology. We'll be discussing issues ripped from the news headlines and in history. These will frame a discussion about bias in data collection and analysis, and our responsibilities as data professionals. I hope we spark your interest in these important topics that shape our data industry.

On Friday morning, I'll be presenting a full hour on Database Docs. We'll discuss how they work behind the scenes and how you can contribute to open-source docs via GitHub. This will also be an interactive feedback panel for the entire Docs platform inside Microsoft Learn. Hope to see you there, inspire you to contribute to the Docs that the entire data community uses daily, and answer any questions.

A simple lab to demonstrate the danger of NOLOCKs in INSERT statements

The READ UNCOMMITTED isolation level, which the NOLOCK table hint activates, is not to be trusted when it comes to writing data. You are not always protected by error 1065 in cases where writes meet NOLOCK. 

Error 1065 states "The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements."  However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren't always scared off by that.)

Here's a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:

--Connection 1
DROP TABLE IF EXISTS dbo.testnolock1
GO
CREATE TABLE dbo.testnolock1
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO dbo.testnolock1 (text1) VALUES (1);
GO

Now we have a table with 1 row. Let's begin a transaction to insert another row, but not commit it.

--Connection 1
BEGIN TRAN
INSERT INTO dbo.testnolock1 (text1) VALUES (2); 

And in a second table, let's do an INSERT based on a SELECT statement with the NOLOCK table hint.

--Connection 2
DROP TABLE IF EXISTS dbo.testnolock2
GO
CREATE TABLE dbo.testnolock2
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO testnolock2
SELECT TEXT1 FROM testnolock1 (NOLOCK)

Without the NOLOCK hint, would be blocked by the INSERT in progress in Connection 1. 

With the NOLOCK hint, the INSERT statement in Connection 2 completes immediately...  however, back in Connection 1:

--Connection 1
ROLLBACK TRAN

What have we got now? From any connection:

SELECT * FROM testnolock1;
SELECT * FROM testnolock2;

One row in testnolock1, two rows in testnolock2

While the INSERT into testnolock1 rolled back (because of one of many different transient errors or failures, not just due to a ROLLBACK), the INSERT with the doomed row where text1=2 succeeded just fine in testnolock2. This is bad! Should Connection 1 retry its INSERT, we could have duplicated data, primary key violations, etc.

If this seems basic to you, great! You have a healthy understanding and fear of the chaos that NOLOCK can unleash.