patternsqlMajor
Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?
Viewed 0 times
resizingwhythemuchsqluseraddingdatabasemaintenanceperform
Problem
If I want to create 5GB database with
it takes 1 minute on my SSD.
But when I add SQL Server user to
it takes only 1-2 seconds.
Why is that? Can someone explain to me what are the reasons for this?
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
References
How and Why to Enable Instant File Initialization
Instant Initialization – What, Why and How?
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.