Instant File Initialization

Instant File Initialization 

Konto usługi SQL powinno posiadać uprawnienia systemowe ”Wykonaj zadania konserwacyjne woluminów” (ang. Perform Volume Maintenance Tasks).

Powód:
W czasie rozszerzania pliku danych (lub tworzenia) plik nie tylko zwiększa swoją wielkość, ale także cała jego nowa zawartość jest "czyszczona" - wypełniana zerami. Zajmuje to pewien czas. W czasie rozszerzania plik jest niedostępny.
Nadanie uprawnienia "Perform Volume Maintenance Tasks" powoduje, że zawartość nie jest czyszczona, a jedynie alokowane jest miejsce.  
Nie dotyczy to plików logu transakcyjnego SQL Server, który zawsze jest zerowany.

Instant File Initialization jest niedostępne, gdy włączone jest TDE. 

Skutki pozytywne:
1. Znakomite skrócenie czasu rozszerzania (tworzenia) pliku danych
2. Skrócenie czasu niedostępności pliku danych podczas rozrzerzania
3. W przypadku startu serwera - skrócenie czasu startu (bo pliki Tempdb nie są czyszczone)
4. W przypadku przywracania bazy danych - skrócenie czasu, bo plik bazy nie jest czyszczony.

Skutki negatywne
1. Możliwość odczytania starej zawartości przez Administratora SQL Server. Tyle tylko, że i tak zazwyczaj jest adminem Windows i może zewnętrznymi narzędziami odczytać dowolny fragment dysku.

Jak włączyć:

• W SQL2016 - możliwość w czasie instalacji - opcja "Grant Perform Volume Maintenance privilege to SQL Server Database Engine Service". Jeśli pominiemy - wciąż pozostaje metoda ręcznego nadania uprawnienia.

• Dla SQL Server 2008, 2008 R2, 2012 i 2014 - metoda ręcznego nadania uprawnienia:
  Uruchomić secpol.msc i dodać konto, na którym działa usługa SQL Server do listy kont uprawnionych. Konieczny restart usługi SQL Server !

Uwagi:
1. Uprawnienie może być nadpisywane przez uprawnienia z domeny AD.
2. Dostępne od Windows XP i Windows Server 2003 z uwagi na https://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx
3. Może być blokowane w SQL Server przez TraceFlag 1806 

Sprawdzenie:

 USE [master]   
 GO  
 IF DB_ID('test') IS NOT NULL DROP DATABASE test   
 GO  
 DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS  
 CREATE DATABASE [test] CONTAINMENT = NONE   
 ON PRIMARY (   
 NAME = N'test',   
 FILENAME = N'D:\MSSQL\test.mdf' , SIZE = 1048576KB , FILEGROWTH = 65536KB )   
 LOG ON (   
 NAME = N'test_log',   
 FILENAME = N'L:\MSSQL\test.ldf' , SIZE = 1048576KB , FILEGROWTH = 65536KB   
 )  
 GO  
 DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS  
 IF DB_ID('test') IS NOT NULL DROP DATABASE test   
 GO  
 EXEC sp_readerrorlog 0, 1  

Wynik przed włączeniem:
2016-03-25 17:49:38.990 spid57 Zeroing D:\MSSQL\test.mdf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:49:39.030 spid57 Zeroing L:\MSSQL\test.ldf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:50:36.040 spid57 Zeroing completed on D:\MSSQL\test.mdf (elapsed = 57050 ms)
2016-03-25 17:51:28.310 spid57 Zeroing completed on L:\MSSQL\test.ldf (elapsed = 109275 ms)

Wynik po włączeniu:

2016-03-25 17:56:06.540 spid52 Zeroing L:\MSSQL\test.ldf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:56:50.170 spid52 Zeroing completed on L:\MSSQL\test.ldf (elapsed = 43619 ms)

Różnice:

Czas utworzenia bazy spadł o 60% , w logu wpisy jedynie o zerowaniu pliku log.

Dokumentacja: 
https://msdn.microsoft.com/pl-pl/library/ms175935(v=sql.110).aspx