<img src="https://track.adform.net/Serving/TrackPoint/?pm=268420" width="1" height="1" alt="">

SQL serve file initialization

13 min. læsetid
17. februar 2014 Skrevet af: Martin Schmidt Del med en ven     

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

  • Create a database
  • Add files, log or data, to an existing database
  • Increase the size of an existing file (including autogrow operations)
  • Restore a database or filegroup

SQL Server performs the zeroing process via a single thread that issues successive writes (of zeroes) to the file. The space cannot be used until the process completes, meaning that the allocation that triggered it will pause until it is done

Instant File Initialization

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

Instant file initialization is only available if the SQL Server service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

What about performance?

Let’s run some tests to see if the difference in performance is measurable. In TEST1 I’ll try to create a database without the instant file initialization (IFI) enabled, here is the code that I’m using:

<span style="color:blue;">USE master

DECLARE </span><span style="color:teal;">@myTimer </span><span style="color:blue;">time </span><span style="color:gray;">= </span><span style="color:magenta;">SYSDATETIME</span><span style="color:gray;">() 

</span><span style="color:green;">--Create the InspariDatabased Database
</span><span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">InspariDatabased
</span><span style="color:blue;">ON PRIMARY
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'InspariDatabased'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:DatabasesInspariDatabased.mdf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>10<span style="color:teal;">GB
</span><span style="color:gray;">)
</span><span style="color:magenta;">LOG </span><span style="color:blue;">ON
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'InspariDatabased_log'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:DatabasesInspariDatabased_log.ldf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>10<span style="color:teal;">GB
</span><span style="color:gray;">) 

</span><span style="color:green;">--Remove the InspariDatabased Database
</span><span style="color:blue;">DROP DATABASE </span><span style="color:teal;">InspariDatabased

</span><span style="color:blue;">SELECT </span><span style="color:magenta;">DATEDIFF</span><span style="color:gray;">(</span><span style="color:teal;">ms</span><span style="color:gray;">, </span><span style="color:teal;">@myTimer</span><span style="color:gray;">, </span><span style="color:magenta;">CAST</span><span style="color:gray;">(</span><span style="color:magenta;">SYSDATETIME</span><span style="color:gray;">() </span><span style="color:blue;">AS time</span><span style="color:gray;">)) </span><span style="color:blue;">AS </span><span style="color:teal;">TimeinMS

</span>

And of course as you might already have guessed, TEST2 is with the IFI enabled.

  Run1 Run2 Run3 Run4 Run5 Avg
TEST1 43.374 43.801 43.192 43.292 43.051 43.342
TEST2 21.782 21.730 21.649 21.669 21.901 21.746

The numbers don’t lie, my create database statement is much quicker with IFI. All numbers are in milliseconds.

How can I Check if this is enabled on my server?

If you have access to the server, the easy way is to check weather the service account running your SQL Server instance has been added to the Perform Volume Maintenance Tasks. If you for some reason cannot access the server,  you can use two trace flags as I'll demonstrate here.

Trace flag 3004 & 3605

If you create a dummy database with these two trace flags enabled you will be able to check if SQL Server is able to use IFI in your environment. If IFI is enabled you will see messages in the SQL Server Errorlog for zeroing out the LOG / LDF file and the LOG file only. If it is not enabled you will see some additional lines for zeroing out the DATA (mdf / ndf) file(s) as well. I’ll use the same code as above

<span style="color:blue;">USE master

</span><span style="color:green;">--Turn on traceflags
</span><span style="color:blue;">DBCC </span><span style="color:teal;">TRACEON</span><span style="color:gray;">(</span>3004<span style="color:gray;">,</span>3605<span style="color:gray;">,-</span>1<span style="color:gray;">) 

</span><span style="color:green;">--Create Test Database
</span><span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">InspariDatabased
</span><span style="color:blue;">ON  PRIMARY
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'InspariDatabased'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:DatabasesInspariDatabased.mdf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>10<span style="color:teal;">GB
</span><span style="color:gray;">)
</span><span style="color:magenta;">LOG </span><span style="color:blue;">ON
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:red;">N'InspariDatabased_log'</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">N'C:DatabasesInspariDatabased_log.ldf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>10<span style="color:teal;">GB
</span><span style="color:gray;">) 

</span><span style="color:green;">--Turn the two Trace Flags
</span><span style="color:blue;">DBCC </span><span style="color:teal;">TRACEOFF</span><span style="color:gray;">(</span>3004<span style="color:gray;">,</span>3605<span style="color:gray;">,-</span>1<span style="color:gray;">)

</span><span style="color:green;">--Remove the InspariDatabased Database
</span><span style="color:blue;">DROP DATABASE </span><span style="color:teal;">InspariDatabased
</span>

If IFI is enabled you will see something similar to this is the SQL Server Errorlog

Capture1

Notice that there is no reference to the data (mdf / ndf) file = IFI is doing the magic. This is what you will see if SQL Server is not able to use IFI.

Capture2

So now go ahead and run some tests on Your systems, if IFI is not enabled You should enable it, and boost your SQL Servers performance.