Tuesday, February 17, 2015

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

-- Once you have added the SQL Server security account to this security policy -- you need to then execute gpupdate /force from the command prompt and restart -- the SQL Service for this change to take effect.
USE master
SET NOCOUNT ON
-- If a database named DummyTestDB_VerifyIFI already exists drop it.
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
DROP DATABASE DummyTestDB_VerifyIFI
-- Temp table to hold output from sp_readerrorlog.
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL
DROP TABLE #SqlLogs
GO
CREATE TABLE #SqlLogs(LogDate datetime, ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))
-- Turn on trace flags 3004 and 3605.
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS
-- Create a dummy database to see the output in the SQL Server Errorlog.
CREATE DATABASE DummyTestDB_VerifyIFI
GO
-- Turn off trace flags 3004 and 3605.
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS
-- Remove the dummy database.
DROP DATABASE DummyTestDB_VerifyIFI
-- Now go check the output in the current SQL Server errorlog File. -- This can take a while if you have a large errorlog file.
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
EXEC sp_readerrorlog 0, 1, 'Zeroing'
IF EXISTS (SELECT * FROM #SqlLogs
WHERE TEXT LIKE 'Zeroing completed%'
AND TEXT LIKE '%DummyTestDB_VerifyIFI.mdf%'
AND LogDate > DATEADD(HOUR, -1, LogDate))
PRINT 'We do NOT have instant file initialization.'
ELSE
PRINT 'We have instant file initialization.'

0
comments:

Post a Comment

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.