Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Check SQL Server Instant File Initialization for all Servers


By:   |   Read Comments (1)   |   Related Tips: More > Database Configurations

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

This is your first day on the job as a Database Administrator and, as what always happens, you are being bombarded with complaints. Just to mention a few you may already be used to: the ERP is slow, the ETL jobs are taking longer than usual, and so on. In this type of situation you need to take a survey of your infrastructure quickly. Since you are not the one who installed the servers, you are not aware if the basic installation guidelines were met, like granting Instant File Initialization permission to the SQL Server service account. In this tip you will learn how sqlcmd can easy check Instant File Initialization status on all your servers.

Solution

One of the key items in order to maintain acceptable database performance is reducing disk I/O activity. There are several ways we can do this. Maybe the first thing that comes to your mind is to add more RAM, let me suggest that this should be your very last choice and, in case you go for it, you must be able to justify this choice mostly because your CTO wonít be happy to spend additional money. There are other things to look into before thinking about adding more memory.

Below is a brief checklist of things you should review before thinking about optimizing disk I/O:

  • Physical and Logical fragmentation of your disk drives: fragmentation adds overhead even if your server uses a NAS because Windows NTFS is not aware of how the hardware handles requests or if your hardware has a proprietary defragmentation algorithm.
  • Multipath (MPIO) configuration on the server and on the NAS: I saw a case when the SQL Server was configured to use Round Robin and the NAS was configured as Fixed Path. I donít need to tell you how bad the disk I/O performance was.
  • Improperly setting autogrowth on your databases: SQL Server will stop all processing until the autogrowth event is finished, and it could lead to the following item.
  • Too many Virtual Log Files (VLF) in the Transaction Log: This makes transactions take longer, for more information take a look at the following tip: Performance Issue with large number of Virtual Log Files in SQL Server Transaction Log.
  • Instant File Initialization Status: Failing to enable this setting will result in larger autogrowth times.

As you see there are many things to evaluate in order to optimize I/O on your SQL Server instances. Most of those things require deeper analysis. But checking Instant File Initialization status is the very first thing you should look at, and also it is faster and easier when talking about digging into the whole infrastructure.

The Importance of Enabling Instant File Initialization

Every time we create or restore a database, add a file or increase a fileís size, SQL Server requests the operating system for disk space like any other application does. When an application asks for disk space the operating system fills the allocated space with zeroes in order to initialize that space. As you may guess, it is a time consuming task, especially when the space being initialized is in the order of tens of gigabytes.

When Instant File Initialization is enabled by granting the Perform Volume Maintenance Tasks permission to the SQL Server service account, SQL Server can reclaim used disk space by instructing the Operating System to not fill that space with zeros. Instead, disk content is overwritten as new data is written to the files.

There are two circumstances on which Instant File Initialization is not used.

Log files cannot be initialized instantaneously. The reason behind this is that log files do not keep track of where the log records ends. When SQL Server does a roll forward operation, like on a restore or an instance startup, it sequentially reads the transaction log files and applies the log records until a log record with an invalid parity is found. Remember that Instant File Initialization leaves garbage on the new allocated space. Guess what may happen to your database if SQL Server applies garbage as log records. If you are interested in the internals, I suggest you take a look at Paul S. Randal article: Why canít the transaction log use instant initialization.

The other case where Instant File Initialization is not used is when your database is encrypted with Transparent Data Encryption (TDE).

Using sqlcmd to check Instant File Initialization

Now that we know the importance of Instant File Initialization, letís see how we can query to determine if this setting is enabled on each server of our infrastructure by using sqlcmd. Something to note is that I will be using the sys.dm_server_services Dynamic Management View to obtain the Instant File Initialization status, which applies to SQL Server 2012 SP4 and with SQL Server 2016 SP1.

The following query returns the server name, the operating system on which the SQL Server instance is running, the SQL Server version and patch level, the service user name and the status of Instant File Initialization.

SELECT  @@SERVERNAME AS [Server Name] ,
        RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
        service_account ,
        instant_file_initialization_enabled
FROM    sys.dm_server_services
WHERE   servicename LIKE 'SQL Server (%'
   

With the previous script you can find out the status of Instant File Initialization on a specific server, but what if we make a report so we can get that information of all our servers by taking advantage of sqlcmd scripts? I will use the same approach as on my previous tip Create SQL Server Disk Space Report for All Servers to build the report. Because of this, I will overlook things related to the report build up, so you should read that previous tip if there is something you donít understand related to the report HTML code.

But if we want to run the report on all the servers we must adjust the previous script so it doesnít crash if there is a SQL Server instance running on Linux or if the instance doesnít have the patch level required to use the instant_file_initialization_enabled column of sys.dm_server_services Dynamic Management View. The following script checks at first if the SQL Server instance is running on Windows, then by using an IF EXIST I check if the instant_file_initialization_enabled column is available. In such cases, the script of the previous paragraph is executed, notice that I enclosed its content in an EXEC statement; otherwise it will crash at compilation time.

IF RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) NOT LIKE 'Windows%'
    BEGIN
        SELECT  SERVERPROPERTY('ServerName') AS [Server Name] ,
                RIGHT(@@version,
                      LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
                LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
                + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
                'N/A' AS [service_account] ,
                'N/A' AS [instant_file_initialization_enabled]
    END
ELSE
    BEGIN
        IF EXISTS ( SELECT  0
                    FROM    sys.all_objects AO
                            INNER JOIN sys.all_columns AC ON AC.object_id = AO.object_id
                    WHERE   AO.name LIKE '%dm_server_services%'
                            AND AC.name = 'instant_file_initialization_enabled' )
            BEGIN
                EXEC('   SELECT  SERVERPROPERTY(''ServerName'') AS [Server Name] ,
                RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX('' ON '', @@VERSION)) AS [OS Info] ,
                LEFT(@@VERSION, CHARINDEX(''-'', @@VERSION) - 2)  + '' '' +  CAST(SERVERPROPERTY(''ProductVersion'') AS NVARCHAR(300) ) AS [SQL Server Version],
                service_account ,
                instant_file_initialization_enabled
                FROM    sys.dm_server_services
                WHERE   servicename LIKE ''SQL Server (%''')
            END
        ELSE
            BEGIN
                SELECT  SERVERPROPERTY('ServerName') AS [Server Name] ,
                        RIGHT(@@version,
                              LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
                        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
                        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
                        service_account AS [service_account] ,
                        'N/A' AS [instant_file_initialization_enabled]
                FROM    sys.dm_server_services
                WHERE   servicename LIKE 'SQL Server (%'
            END  
    END

   

Now that we have a query that can execute without errors on all our servers we must convert its output to HTML format. Save the following script in a file named query.sql.

set nocount on
go
CREATE TABLE #Report
(
[Server Name] SQL_VARIANT NULL,
[OS Info] [nvarchar] (300) NULL,
[SQL Server Version] [nvarchar] (300) NULL,
[service_account] [nvarchar] (300) NOT NULL,
[instant_file_initialization_enabled] [nvarchar] (300) NULL
) 
GO


IF RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) NOT LIKE 'Windows%'
    BEGIN
      INSERT INTO #Report
        ( [Server Name] ,
          [OS Info] ,
        [SQL Server Version],
          service_account ,
          instant_file_initialization_enabled
        )

        SELECT  SERVERPROPERTY('ServerName') AS [Server Name] ,
                RIGHT(@@version,
                      LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
            LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2)  + ' ' +  CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300) ) AS [SQL Server Version],
                'N/A' AS [service_account] ,
                'N/A' AS [instant_file_initialization_enabled]
    END
ELSE
    BEGIN
        IF EXISTS ( SELECT  0
                    FROM    sys.all_objects AO
                            INNER JOIN sys.all_columns AC ON AC.object_id = AO.object_id
                    WHERE   AO.name LIKE '%dm_server_services%'
                            AND AC.name = 'instant_file_initialization_enabled' )
            BEGIN
            INSERT INTO #Report
               ( [Server Name] ,
                 [OS Info] ,
                 [SQL Server Version],
                 service_account ,
                 instant_file_initialization_enabled
               )

                EXEC('   SELECT  SERVERPROPERTY(''ServerName'') AS [Server Name] ,
                RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX('' ON '', @@VERSION)) AS [OS Info] ,
            LEFT(@@VERSION, CHARINDEX(''-'', @@VERSION) - 2)  + '' '' +  CAST(SERVERPROPERTY(''ProductVersion'') AS NVARCHAR(300) ) AS [SQL Server Version],
                service_account ,
                instant_file_initialization_enabled
                FROM    sys.dm_server_services
                WHERE   servicename LIKE ''SQL Server (%''')
            END
        ELSE
            BEGIN
            INSERT INTO #Report
               ( [Server Name] ,
                 [OS Info] ,
                 [SQL Server Version],
                 service_account ,
                 instant_file_initialization_enabled
               )

                SELECT  SERVERPROPERTY('ServerName') AS [Server Name] ,
                        RIGHT(@@version,LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
                  LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2)  + ' ' +  CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300) ) AS [SQL Server Version],
                        service_account AS [service_account] ,
                        'N/A' AS [instant_file_initialization_enabled]
                FROM    sys.dm_server_services
                WHERE   servicename LIKE 'SQL Server (%'
            END  
    END

SELECT '<TR>' +
       '<TD>' + CAST ([Server Name] AS  [nvarchar] (300)) +'</TD>' +
       '<TD>' + left ([OS Info], len ([OS Info]) -1) +'</TD>' +
       '<TD>' + [SQL Server Version] + '</TD>' +
       '<TD>' + service_account +'</TD>' +
       '<TD>' + instant_file_initialization_enabled +'</TD>' +
       '</TR>'
FROM #Report

DROP TABLE #Report
GO
   

The following step is to build a list of your SQL Server instances by using sqlcmd with the /L switch as explained in the following tip: Find SQL Servers On Your Network with OSQL and SQLCMD. And then create a file named servers.sql to hold a list of your servers, but with the following modifications to use the :r and :CONNECT commands. If you donít know what these commands are I suggest you read my previous tips about sqlcmd.

:r ./query.sql
GO 
:CONNECT SQL-A.ATLAS.COM\TEST
:r ./query.sql
GO
:CONNECT LINUXSQL.ATLAS.COM
:r ./query.sql
GO
:CONNECT SQL-B.ATLAS.COM
:r ./query.sql
GO
   

Now we create a PowerShell script that creates the HTML report and sends it by email.

$header = "<HTML>
<BODY>
<h1 align=""center"">Instant File Initialization Status Report</h1>
<TABLE>
<TR>
<TH bgcolor=""#4f81bd"">Server Name</TH>
<TH bgcolor=""#4f81bd"">OS Info</TH>
<TH bgcolor=""#4f81bd"">SQL Server Version</TH> 
<TH bgcolor=""#4f81bd"">Service Account</TH>
<TH bgcolor=""#4f81bd"">instant_file_initialization_enabled</TH>
</TR>"

$bottom="</TABLE></BODY></HTML>"

$var= sqlcmd -S SQL-A.ATLAS.COM -i servers.sql

$rows = echo $var | Select-String -Pattern "^Sqlcmd*", "^----*" -NotMatch | Sort

$secpasswd = ConvertTo-SecureString "YourSMTPPassword" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("UserName", $secpasswd)

Send-MailMessage -From "sender@email.com" -Subject "Report" -To "your@email.com" -Body " $header  $rows  $bottom" -BodyAsHtml -SmtpServer "your.smtp.server" -Credential $mycreds
   
Instant File Initialization Status Report - Description: This is how it looks the report on your mailbox.

In case you want to run the report on Linux, you can use the following BASH script.

#!/bin/bash
header="<HTML>
<BODY>
<h1 align=""center"">Instant File Initialization Status Report</h1>
<TABLE>
<TR>
<TH bgcolor=""#4f81bd"">Server Name</TH>
<TH bgcolor=""#4f81bd"">OS Info</TH>
<TH bgcolor=""#4f81bd"">SQL Server Version</TH> 
<TH bgcolor=""#4f81bd"">Service Account</TH>
<TH bgcolor=""#4f81bd"">instant_file_initialization_enabled</TH>
</TR>"

bottom="</TABLE></BODY></HTML>"

rows=$(sqlcmd -S SQL-A.ATLAS.COM -i servers.sql | grep -vwE "(Sqlcmd|----)" | sort)

html=$header$rows$bottom

echo  $html | mail -s "Database Disk Usage Report" -a "MIME-Version: 1.0"  -a "Content-Type: text/html"  yourmail@address.com
   
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, November 17, 2017 - 6:50:52 AM - Nick La Roche Back To Top

Thanks for the tip Daniel

You mentioned 2012 & 2016

Do you know why 2014 was excluded? Column [instant_file_initialization_enabled] is not available in [sys].[dm_server_services] on my 2014 installation (SP2-CU3)

 


Learn more about SQL Server tools