SQL Server: Instant File Initialization Exceptions
Introduction
DBA’s are always on the lookout for Performance Quick Wins to improve the SQL Server performance under their care, one of the highly recommended performance booster is to have Instant File Initialization (IFI) enabled. IFI is a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation as a result, file allocation requests can occur instantly.
I will not get into the details of IFI, we already have good amount of articles on this topic and I personally recommend Instant Initialization – What, Why and How? by Kimberly Tripp and How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro.
Basic intention of this article is to demonstrate the Instant File initialization exceptions, which means SQL Server engine will go ahead with zeroing out disk space inspite of having IFI enabled.
Performing the test
Let me check if SQL Server is able to use IFI in my environment by creating a database. If it is enabled you will see message in SQL Server error log for the zeroing out of the log files, and if it is not enabled, you will see messages for the zeroing out for both log files and data files.
I am creating a dummy database with 10 GB data file and 1 GB log file total size would be 11 GB.
 Caution |
|---|
| To see which files are being zeroed out, this article uses the undocumented trace flag 3004. Undocumented means it is safe to use in production but any support related to this command cannot be claimed from Microsoft.It also means that any change can be made to this command without officially notifying end user so unexpected results might come if used on same environment after some time. Although their usage is safe, keep in mind to use it in test environments only to avoid any impact to production processes. Trace flag 3004 shows information about backups and file creations, Trace flag 3605 redirects the output to the SQL error log. |
USE master;
GO
--Set Trace Flags 3004 and 3605 to On.
DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);
--Create a dummy database to see what output is sent to the SQL Server Error Log
CREATE DATABASE IFITest ON PRIMARY
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf', SIZE = 10GB)
LOG ON
( NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf', SIZE = 1GB)
It took 4 sec to create above database on my laptop, let me check SQL errorlog.
2014-02-26 19:12:36.090 spid56 Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:12:40.190 spid56 Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:12:40.300 spid56 Starting up database 'IFITest'.
2014-02-26 19:12:40.320 spid56 FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:12:40.320 spid56 Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:12:40.330 spid56 Zeroing completed on c:\IFITest_log.ldf
As you can see in above errorlog result, only log file is zeroing out it confirms IFI is enabled in my environment.
Just to cross verify, I created the database by interchanging data file size and log file size but total size of the database remains the same, i.e. 11 GB.
Use Master
Go
DROP DATABASE IFITest;
Go
CREATE DATABASE IFITest ON PRIMARY
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf', SIZE = 1GB)
LOG ON
( NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf', SIZE = 10GB)
As expected it took me 1 min 35 sec to create database, this also confirms IFI is in action in my environment.
Note
If you want to check if Instant File Initialization is enabled in your environment then I would recommend you to read http://www.johnsansom.com/sqlserver-instant-file-initialization.
Instant File Initialization Exception Scenarios
Now I demonstrate you a few different scenarios where IFI has to zero out data files in spite of having IFI enabled.
Scenario 1: Enable Trace Flag 1806.
Turning on trace flag 1806 will disable instant file initialization.
DROP DATABASE IFITest;
GO
DBCC TRACEON(1806 , -1)
go
CREATE DATABASE IFITest ON PRIMARY
(NAME = N'IFITest', FILENAME = N'C:\IFITest.mdf', SIZE = 10GB)
LOG ON
( NAME = N'IFITest_log', FILENAME = N'c:\IFITest_log.ldf', SIZE = 1GB)
-- 56 sec
DBCC TRACEOFF(1806 , -1)
Let me check errorlog.
2014-02-26 19:05:41.440 spid56 DBCC TRACEON 1806, server process ID (SPID) 56. This is an informational message only; no user action is required.
2014-02-26 19:05:49.240 spid56 Zeroing C:\IFITest.mdf from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:06:32.070 spid56 Zeroing completed on C:\IFITest.mdf
2014-02-26 19:06:32.160 spid56 Zeroing c:\IFITest_log.ldf from page 0 to 131072 (0x0 to 0x40000000)
2014-02-26 19:06:37.190 spid56 Zeroing completed on c:\IFITest_log.ldf
2014-02-26 19:06:37.320 spid56 Starting up database 'IFITest'.
2014-02-26 19:06:37.330 spid56 FixupLogTail(progress) zeroing c:\IFITest_log.ldf from 0x5000 to 0x6000.
2014-02-26 19:06:37.330 spid56 Zeroing c:\IFITest_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2014-02-26 19:06:37.340 spid56 Zeroing completed on c:\IFITest_log.ldf
As you can see in above result set, enabling trace flag 1806 forces SQL Server engine to zero out both data and log files and it took 48 sec to create database.
Scenario 2: Transparent Data Encryption (TDE).
When you have enable Transparent Data Encryption on your database then SQL Server Engine will ignore your IFI settings and go ahead with zero initialization of data files.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Test Certificate';
go
USE IFITest;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE IFITest SET ENCRYPTION ON;
ALTER DATABASE IFITest
MODIFY FILE(NAME = 'IFITest' , SIZE = 15GB)
In my above query I encrypted the database and then I altered data file from 10GB to 15GB, let us see errorlog result.
2014-02-26 19:16:28.770 spid56 Setting database option ENCRYPTION to ON for database IFITest.
2014-02-26 19:16:28.770 spid18s Beginning database encryption scan for database 'IFITest'.
2014-02-26 19:16:42.140 spid56 Zeroing C:\IFITest.mdf from page 1310720 to 1966080 (0x280000000 to 0x3c0000000)
2014-02-26 19:17:06.050 spid56 Zeroing completed on C:\IFITest.mdf
As you can see in result set, SQL Server Engine is zeroed out data file and it took 24 sec to extend data file and without encryption same query took only 1 sec.
Note
Refer technet article Transparent Data Encryption (TDE) to know more about Transparent Data Encryption.
Scenario 3: CHECKDB.
One more case wherein SQL Server Engine has to zero initialize data files is when we run DBCC CHECKDB.
Use IFITest
Go
DBCC CHECKDB
Below errorlog result clearly shows SQL Server Engine is zeroing out data files.
2014-02-26 19:25:08.360 spid53 Zeroing C:\IFITest.mdf:MSSQL_DBCC9 from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:25:08.360 spid53 Zeroing completed on C:\IFITest.mdf:MSSQL_DBCC9
2014-02-26 19:25:08.650 spid53 DBCC CHECKDB (IFITest) executed by \praveen.dsa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000013:00000040:0001 and first LSN = 00000013:0000003f:0001.
Note
Refer technet article DBCC CHECKDB to know more about DBCC CHECKDB.
Scenario 4: Database Snapshot.
When we create database snapshot, SQL Server Engine go ahead and zero initializes data file.
CREATE Database IFITest_SS
ON
(Name= 'IFITest' , Filename = 'C:\IFITest_data.ss')
AS SNAPSHOT OF [IFITest]
And errorlog result indicates zero initialization of data files.
2014-02-26 19:27:46.890 spid53 Zeroing C:\IFITest_data.ss from page 0 to 1310720 (0x0 to 0x280000000)
2014-02-26 19:27:46.890 spid53 Zeroing completed on C:\IFITest_data.ss
2014-02-26 19:27:46.940 spid53 Starting up database 'IFITest_SS'.
Note
Refer technet article Create a Database Snapshot to know more about Database Snapshot.
Conclusion
Basically, with Instant File Initialization turned on it cuts out database creation or data file growth time, most DBA's are aware of this fact, but most of us not aware of the fact that in some exceptional cases Instant File Initialization has to zero initializes data files, I have demonstrated four such scenarios in this article.
References
- How and Why to Enable Instant File Initialization by Cindy Gross and Denzil Ribeiro
- Instant Initialization – What, Why and How? by Kimberly Tripp
- SQL Server Instant File Initialization (PQW) by John Sansom
- Do you have Instant File Initialization? by Tibor Karaszi
- Misconceptions around instant file initialization by Paul Randal
- http://technet.microsoft.com/en-us/library/bb934049.aspx
Note
By default, the output of DBCC PAGE is sent to the errorlog. If you want the output to come back to your current connection, need to turn on trace flag 3604, DBCC TRACEON(3604), refer how to use DBCC Page.
Note
By default, the output of DBCC PAGE is sent to the errorlog. If you want the output to come back to your current connection, need to turn on trace flag 3604, DBCC TRACEON(3604), refer how to use DBCC Page.