HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
resizingwhythemuchsqluseraddingdatabasemaintenanceperform

Problem

If I want to create 5GB database with

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'E:\2012\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)


it takes 1 minute on my SSD.

But when I add SQL Server user to Perform volume maintenance tasks

it takes only 1-2 seconds.

Why is that? Can someone explain to me what are the reasons for this?

Solution

That's because of Instant File Initialization. In short, SQL Server can take advantage of this privilege for database data files (not transaction log files). What this means is that SQL Server does not have to zero out the data file(s) when initializing.

Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the database creation SQL Server needs to zero out "test.mdf" and "test_log.ldf".

With the "Perform volume maintenance tasks" privilege permission set, SQL Server only needs to zero out "test_log.ldf". A significantly less overhead, therefore minimized duration for the CREATE DATABASE in your testing.

References

How and Why to Enable Instant File Initialization

Instant Initialization – What, Why and How?

Context

StackExchange Database Administrators Q#94025, answer score: 27

Revisions (0)

No revisions yet.