Instant File Initialization for SQL Server 2008 (and 2005)

I will do anything to reduce the IO load on large databases, which is why I am a great fan of compression in SQL Server 2008. However, another way of reducing IO is to ask SQL Server to format new data extents without writing zeroes out to all pages. If your database takes a new extent (by default it is 10% of the file size, which typically isn't good) SQL Server will want to write binary zeroes to all the pages to ensure that some old data isn't hidden in your database. SQL Server does not need the binary zeroes, it is done to ensure data security. Someone else may have deleted a file and perhaps they don’t not want you seeing the data.

Very often, the data security is not an issue and you would like to avoid the writing of binary zeroes. This can be achieved (on NTFS drives) by ensuring that the account running SQL Server has SE_MANAGE_VOLUME_NAME privilege. You can grant this privilege in User Rights Assignment on Windows 2003, XP or above. To do this, grant the SQL Service account Perform volume maintenance tasks local security rights. Alternatively, if the SQL service account has administrator privileges, it will automatically have this privilege. That's all you need to do to avoid having SQL Server write all those binary zeros. By the way, SQL Server always needs to write binary zeroes to the log files, we are only avoiding binary zeroes on the data files.

If you don't want to grant this privilege to the SQL service account, and you want to avoid the IO load during peak times, I suggest that you extend (manually or automatically) your datasets during an off peak times (i.e. before SQL does it for you).

One of the most noticeable tasks affected by binary zeros is database restores. If you watch the restore progress, it will typically not move off 0% until it has written out the entire data files with binary zeroes. After writing every data page, it will then write the backup files over the data pages, during which time, you will see the progress percent increase. So writing binary zeroes can approximately double full database restore times.

Hope that helps you reduce IO load and increase SQL Server performance.

For real-time SQL demonstrations, including Perfmon, which is writing over 300 records every second, see http://RichardLees.com.au/Sites/Demonstrations