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. This works for SQL Server 2012 SP4, SQL Server 2016 SP1 and any later version of SQL Server.
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
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
- In the following tip is a proof of concept that shows the effects of Instant File Initialization when it’s enabled and when it’s not: Effect of Instant File Initialization within SQL Server.
- If you don’t know how to enable Instant File Initialization take a look at the following tip: Enable SQL Server Instant File Initialization for Time Savings.
- SQL Server 2016 gives you the option to enable Instant File Initialization during installation as you can see on the following tip: SQL Server 2016 RC0 Installation and Configuration Changes.
- As I told you before, fragmentation is an issue when troubleshooting I/O performance. In this tip series you will have an introduction about what fragmentation is as well as other interesting stuff: SQL Server Fragmentation Storage basics and Access Methods Part 1 of 9.
- Also this tip will aid you to identify and solve I/O issues related to Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server.
- In case you have physical fragmentation on any of your databases, check out this tip: Solving SQL Server Database Physical File Fragmentation.
- You need to install a new SQL Server instance? Take a look at SQL Server Installation Best Practices.
