In this blog I will introduce two new cool features in SQL Server 2016 setup. The first one is the option to enable Volume Maintenance Tasks for the SQL Server Service Account. The second is the option now already included in the setup dialog to set multiple data files for the TempDB.
- Perform Volume Maintenance Task
This is a user right within the windows server. Before SQL Server 2016 you had to enable this option after the installation. What does it do and why should you enable this?
In a nutshell: When the SQL Server extends files (AUTOGROWTH enabled) or places new files on discs during RESTORE or CREATE DATABASE tasks it „zeros out“ all the space it needs for the file. This could take much time especially when starting up the service and allocating the space for the TempDB. So when you grant this user right to the SQL Server Service Account, the SQL Server can use the Instant File Initialization feature and the file extension or new files can be place much faster on the disc.
Here is a detailed description for this user right: TechNet Microsoft
In the setup of SQL 2016 in the Service Accounts dialog you can check the checkbox to „Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service“
For the unattended installation this option is:
; Set to „True“ to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.
I did a before – after screenshot to show you the change, done by the Setup:
I suggest to check this checkbox during the setup.
- Multiple Data files for the TempDB during Setup
I think that you should have a TempDB with number of files equal to the number of available cpus/cores and up to 8 files is already a common best practice. But with SQL Server 2014 and before you had to configure this manually after the installation or have your post installation scripts. Within SQL Server 2016 setup there is a new part of the installation dialog that offers you the chance to setup all files directly during the Setup:
You can define the number of Files (Up to 8 as best practice), define initial and Autogrowth size and data directories. Also placing the TempDB logfile on another drive than the user database log files is possible. In this case (as I only have one drive in my virtual test machine) I gave him only one other path and decided to use eight files.
This could be done for the unattended installation with the following options:
; The number of Database Engine TempDB files.
; Specifies the initial size of a Database Engine TempDB data file in MB.
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
; Specifies the initial size of the Database Engine TempDB log file in MB.
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
; Directories for Database Engine TempDB files.
SQLTEMPDBDIR=“C:\Program Files\Microsoft SQL Server\MSSQL13.TEST\MSSQL\Data“ „C:\TempDB“
After the installation the folder is published with 4 files placed there in round robin for the file numbers and names:
And you can see the created TempDB in the SQL Server Management Studio.
I think these are two new cool features that help you to use best practices already during the initial setup.