Do you want to make your database server faster?
If you answered no, you can go back to manually checking that all of last night’s backups succeeded.
If you answered yes, think about all the ways you can do this. From proper database design to the right hardware for your workload to proper indexes, you have many options. One step is to make sure that when your database needs to grow – whether a data file needs to expand, more files are added, or the database is restored – the growth happens quickly.
This can be accomplished by adjusting a security policy at the Windows Server level – Perform volume maintenance tasks. Giving your SQL Server service account rights to this policy allows it to take advantage of instant file initialization (IFI).
Normally, when you need to grow a file, the space to be used will be overwritten with zeros. Depending on how large your file needs to be, this can take some time. With rights to use IFI, SQL Server skips that step – the space is claimed on disk, and the stuff on disk is overwritten as needed.
It makes database growth faster.
I have a SQL Server instance that is using SQL1 as the service account. This account is not a local administrator, nor is it a domain administrator. (Safety first!)
I open secpol.msc, go to my Local Policies and check User Rights Assignment. Only Administrators have permission to Perform volume maintenance tasks.
Now I’m going to create a database. How long does it take to create a file?
SET STATISTICS TIME ON; CREATE DATABASE IFIOffTest ON ( NAME = IFIOffTest_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_dat.mdf', SIZE = 1000, MAXSIZE = 2000, FILEGROWTH = 100 ) LOG ON ( NAME = IFIOffTest_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_log.ldf', SIZE = 100MB, MAXSIZE = 300MB, FILEGROWTH = 20MB ) ; GO
Statistics Time tells me:
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 4665 ms.
Now I go to Local Security Policy and add SQL1 to Perform volume maintenance tasks, and restart the SQL Server service.
I create a second database.
SET STATISTICS TIME ON; CREATE DATABASE IFIOnTest ON ( NAME = IFIOnTest_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_dat.mdf', SIZE = 1000, MAXSIZE = 2000, FILEGROWTH = 100 ) LOG ON ( NAME = IFIOnTest_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_log.ldf', SIZE = 100MB, MAXSIZE = 300MB, FILEGROWTH = 20MB ) ; GO
What does Statistics Time give me here?
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 659 ms.
I created two databases on the same server, the same hard drive, with the same file sizes. With IFI turned on, the database was created in 14% of the time. And that’s just with a 1,000 MB file! Imagine the time savings as your database scales up.
Like Growth Hormones, Without the Facial Hair
This is one of the fundamental steps I always have on a SQL Server database server setup checklist. If you’re not using this option yet, add it to yours!