Instant file initialization

A call came in to increase a database datafile by 7-8GB. I was asked if there were any implications of doing the increase.

Of course the biggest concern was around creating IO load. One way to mitigate this would be to enable instant file initialization. This esentially allows SQL Server to create and initialize data files instantly (it doesn’t affect transaction log files) – without having to zero the data.

The next question was could the setting be enabled without a server restart. So … time to test.

I installed a named instance of SQL Server 2008R2 on my Windows 7 box, running the service under a local account with minimal permissions. I switched on a couple of trace flags to track file initialization into the sql logs and created a database with a 5GB data file.

-- trace file zeroing to track initialisation
dbcc traceon(3004,-1);
-- force output to error log
dbcc traceon(3605,-1);
-- if instant initialisation is on then only the log files will be zeroed.
create database zerotest
on(name = zerotest_data,
	filename = N'C:\MSSQL\SQLInstance\zerotest_data.mdf',
	size = 5GB);

The create took 1 minute 22 seconds. Here are the logs:

Time to enable instant file initialization.

Click start>run>secpol.msc /s.

Go to Local Policies>User Rights Assignment>Perform Volume Maintenance Tasks and add the SQL Server Service account. Click apply.

Creating a second database confirmed that instant file initialization is still not enabled. So open a command window and run gpupdate.

The gotcha: and deal breaker for the client site, is this still requires a SQL Server restart to take effect.

To check whether the privilige has taken effect run:

exec master..xp_cmdshell 'whoami /priv'

After this has taken effect creating a second test database of the same size took 16 seconds…

create database zerotest2
on(name = zerotest2_data,
	filename = N'C:\MSSQL\SQLInstance\zerotest2_data.mdf',
	size = 5GB);

… and this time only the log file is zero initialized:

Quick clean up:

drop database zerotest;
drop database zerotest2;
dbcc traceoff(3004,-1);
dbcc traceoff(3605,-1);
dbcc tracestatus;

Conclusion: Because of the SQL Server restart this is not much good in a tight spot or for a quick bit of live maintenance. However a useful item for the wishlist if any major file level operations are on the horizon.

About these ads