How To Enable Instant File Initializating And Improving Sql Server Performance

What is ‘File Initialization’ ?  When the database data files talk to the disk, they have to be ‘initialized’.  This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:

  • Create new database
  • Auto-growth of any data file
  • Backup or restore

Ok. So what is ‘Instant File Initialization’ ?  This is a Windows setting that will allow SQL Server to skip the zero initialization.  Instant File Initialization will claim the space to be used WITHOUT pre-filling it with zeros.  The impact?  This setting will make data file growth faster.  Much faster.

Instant File Initialization

If you don’t want that SQL Server is doing the zero-initialization of your data files, you can reconfigure SQL Server. If you grant the service account, under which SQL Server is running – the privilege Performance Volume Maintenance Task, SQL Server will skip the zero-initialization of the data files, if you have restarted SQL Server afterwards. As I have said this only applies to data files – log files are ALWAYS zero-initialized in SQL Server! There is NO WAY around that!!! Without the zero-initialization of the log file, the crash recovery process would have no idea where to stop, when the log file was wrapped around. Crash Recovery stops where it finds zero values in the header of the next log record to be processed.

You can grant the permission Performance Volume Maintenance Task through secpol.msc to the service account of SQL Server.

Also Read:   26 Amazing Animals That You Won’t Believe Actually Exist

Start / Run – type in ‘secpol.msc’

Left  side under Security Settings, go to Local Policies, then User Rights Assignment.

secpol_fileinitializing_500

Right side under Policy, double-click ‘Perform volume maintenance tasks’.  
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.

performvolumemaint_fileinitializing_450

Add in your SQL Server Service account.

sqlserverserviceacct_fileinitializing_450

Restart your SQL Server service.

 

After a restart, SQL Server is now able to skip the zero-initialization of data files. When I’m running the CREATE DATABASE statement from about again, it takes around 250ms – that’s a huge difference!

If you want to know, if your SQL Server is running with this permission, or not, you can enable the trace flags 3004 and 3605DBCCTRACEON(3605) ). With these trace flags enabled, SQL Server reports in the error log which files are zero initialized. When you afterwards create a new database, and the permission wasn’t granted to SQL Server, you can see from the error log, that data AND log files were zero-initialized:

dataloginitialization_500

If SQL Server has the permission Perform Volume Maintenance Task,you can see from the error log, that ONLY the log file was zero-initialized:

loginitialization_500

 

 

 

 

Sources : sqlfingers , sqlpassion

No votes yet.
Please wait...
Naser Olia

Naser Olia

Software Engineer, Programmer and web designer. I am passionate about healthy lifestyle, Science and knowledge in general and Personal development is one of the most important things to me.

Leave a Reply

Be the First to Comment!

avatar
wpDiscuz
More in Computer Technical Issues, HardText +
Lunula And Fingernail Reveals Your Health Condition

      Sources : naturalhealthtechniques , legitfeed ,   34-menopause-symptoms , organicolivia , yourchineseastrology , mayoclinic

Close