Happy to inform all my blog readers this particular post has won me SILVER in TechNet Guru Award under SQL Server General and Database Engine Technical Guru – February 2014.
Visit TechNet Guru Awards – February 2014 for winner list
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.
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.
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.
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 create above database by interchanging data file size and log file size but total size of the database remains the same i.e. 11 GB.
As expected it took me 1 min 35 sec to create database, this also confirms IFI is in action in my environment.
|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.|
Now I demonstrate you few different scenarios wherein 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.
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.
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.
|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.
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 \********* 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.
|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.
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'.
|Refer technet article Create a Database Snapshot to know more about Database Snapshot.|
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.
- 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