Instant Initialization – What, Why and How?

Instant Initialization is a new feature of SQL Server 2005 that is based on an NTFS feature that was added to Windows XP (and therefore is also available in Windows 2003 Server). It's a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly – no matter what the file size. You might wonder why this is interesting or why this make a difference? Most file allocation requests are small requests, with small incremental changes (like .doc files, .xls files, etc.) but database files can be rather large. In fact, they should be rather large as pre-allocation of a reasonable file size is a best practice to reduce file fragmentation. Additionally, autogrowth causes performance delays (more so in 2000 than 2005) but it's generally something that you want to avoid when possible. As as result, database creation times can take minutes to hours to days, depending on file allocation request. But – it's not just for database creation. ALL file requests can leverage this feature: file creation for a new database, adding a file to an existing database, manually or automatically growing a file and (IMO – the best) restoring a database where the file (or files) being restored does not already exist. The reason I think the last feature is the best is that it can reduce downtime if a database is damaged and allow you to get back up and running more quickly. This is especially important for databases that cannot leverage partial database availability, which is an Enterprise Engine feature. So, to give you some motivation, here is a test that I performed just to have some interesting and comparable numbers.

Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
   CREATE DATABASE with 20 GB Data file = 14:02 minutes
   ALTER DATABASE BY 10 GB = 7:01 minutes
   RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
   RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes

Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
   CREATE DATABASE with 20 GB Data file = 1.3 seconds
   ALTER DATABASE BY 10 GB = .4 seconds
   RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
   RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes

SQL Server can leverage this feature for DATA file requests ONLY; the transaction log must be zero initialized because of its circular nature… which brings me to why this is not "on by default" or more specifically – HOW do you get this feature. First, there's absolutely no syntax change required – SQL Server will use it if it has access to it (so what does that mean?). The SQL Server service must have been granted the Windows permission – "Perform Volume Maintenance Tasks". By default, Windows Administrators have this permission but as yet-another-best-practice, we recommend that your SQL Server run under an account that is a "lower privileged" account (i.e. NOT an administrator). Other ideal options include running as "network service" or running as a dedicated user/domain account that has very few permissions except to SQL Server and it's required resources. A lot of folks recommend using network service for its simplicity (it doesn't have a password and it has limited network/local rights) and I agree with this as long as it's truly dedicated to SQL Server. If network service is used by other services on the same machine then you could compromise security of your SQL Server (or the other services) with the elevated permissions that SQL Server grants or visa versa by the permissions that other applications may have granted to network service. Again, I'm not against network service BUT I would check your local permissions to see if there's anything that jumps out at you. If you've installed other applications/services/etc. you may have already compromised the security of the network service account. I would love to know if anyone has a quick/easy way to check windows permissions to see what may have been granted in addition to the default permissions OR even a link to where the defaults are listed online… I've had trouble doing exactly this when searching, etc… feel free to post links/comments in your comments!. Anyway, with a dedicated user account, you can make sure that it's not compromised because you only use it for SQL Server. But, even these have negative issues – like required passwords that networks invalidate after n days and that you must change on a server/service basis. From a management perspective, this can be difficult.

SIDE NOTE: Managing the service account password is a lot easier in SQL Server 2005 with the SQL Server Configuration Manager (SQL-CM). The SQL-CM allows you to change the password to a service without an active connection (meaning even if the service isn't started) and it invalidates the login token so that password changes don't require a restart of the service. SQL-CM also has a command-line interface and is scriptable with WMI. The WMI Provider allows server settings, client and server network protocols, and aliases to be scripted through the WMI Provider by means of simple VBScript code (or by using the command-line tool). What you could end up doing is creating a script that changes the password of your services on all of your servers (for example, when a password policy is enforced that requires that the passwords of service accounts be changed). I've recently completed a whitepaper that highlights the Management tools (it's really just an overview but even then it turned out to be quite large as we looked at the tools in many different ways). I'll certainly let you know when the whitepaper is published (which should be within the next couple of weeks).

Granting the permission "Perform Volume Maintenance Tasks"
To use instant initialization, your SQL Server service must be running with an account that has the required privilege. If your SQL Server service is running as a local administrator this permission already exists. For a service account which is not a local administrator (again, recommended!), the necessary privilege to grant is Perform Volume Maintenance Tasks. This permission can be granted by an administrator through the Local Security Policy tool (Start, All Programs, Administrative Tools) and once granted, SQL Server automatically uses instant initialization. IMPORTANT NOTE: If this permission is given while SQL Server is running, then SQL Server must be stopped and restarted. However, once the server is restarted, no other syntax or permissions are needed. All data file creation/extension options will use instant initialization for data files created on NTFS volumes when SQL Server 2005 is running on Windows XP or Windows 2003 Server.

Why isn't this on by default?
OK, so after all of this… the gains that you see and the lack of changes to syntax, etc. You're probably wondering why this isn't on by default? It's a security issue. The biggest vulnerability is with SQL Server Administrators who are NOT also Windows Administrators. Windows Administrators have access to local files and can easily see all files stored on the local server. For files that are not encrypted (and are not already open to another process), an Administrator can open and/or modify these files using an appropriate editor. For files that are encrypted, an Administrator can at least view the encrypted information using a hex editor. By granting “Perform Volume Maintenance Tasks” to a SQL Server instance, you are giving administrators of the instance the ability to read the encrypted contents of a recently deleted file (ONLY IF the file system decides to use this newly freed space on the creation of a new database – created with instant initialization) with the undocumented DBCC PAGE command.

SIDE NOTE: The format for DBCC PAGE is undocumented in the Books Online but you will find tips and tricks on many “official” Microsoft blogs. The SQL Server Storage Engine blog (http://blogs.msdn.com/sqlserverstorageengine/ has some very good blog posts on internals and often describes undocumented commands. Specifically, check out the blog entry titled: How to use DBCC PAGE. The first three components are fairly straightforward: database id (or name), file id, and page id. The fourth component is the tricky one: printopt (or print option). The print options for DBCC PAGE are as follows (taken almost verbatim from the SQL Server Storage Engine blog – and Paul said I could :):

0 – print just the page header
1 – page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)
2 – page header plus whole page hex dump
3 – page header plus detailed per-row interpretation (in this case, this option is not available – even if the data you are trying to read is from a previously deleted database because the metadata is not accessible only the raw page data)

Bear in mind, even if you can access this [encrypted] information, making sense out of this data will be challenging if not incredibly difficult.

In production environments, database files should NOT be located on file server drives – especially those where restricted and/or sensitive files are stored. As a result of prudent security measures, the true impact of using instant initialization is low. However, because this vulnerability exists, this feature is off by default.

Want to try this?
I've written a lab on Instant Initialization AND it has an interesting sequence of exercises (using multiple instances):

  • You create a database on instance: SQLDev01
  • Populate a large chunk of pages with very contrived (and easy to find) "junk" data

USE TestWithZeroInitialization
go

 

CREATE TABLE JunkData
(
   JunkDataID int identity,
   JunkDataValue char(8000
      DEFAULT REPLICATE('Junk', 2000)
)
go

 

SET NOCOUNT ON
go

 

DECLARE @Counter int
SELECT @Counter = 0
WHILE @Counter < 20000
BEGIN
   INSERT JunkData DEFAULT VALUES
   SELECT @Counter = @Counter + 1
END
go

Drop the database from instance: SQLDev01

  • Create a new database on a different instance: SQLDev02 (which has been configured to use Instant Initialization) and hope that it uses the freed space by having dropped the first database
  • Start walking various pages (using DBCC PAGE) to see if you can view the "junk" data from the dropped database.

DBCC PAGE ('TestSecurityExposure', 1, 200, 2)
DBCC PAGE ('TestSecurityExposure', 1, 400, 2)
DBCC PAGE ('TestSecurityExposure', 1, 600, 2)
DBCC PAGE ('TestSecurityExposure', 1, 800, 2)
DBCC PAGE ('TestSecurityExposure', 1, 1000, 2)
DBCC PAGE ('TestSecurityExposure', 1, 1500, 2)
DBCC PAGE ('TestSecurityExposure', 1, 2000, 2)
DBCC PAGE ('TestSecurityExposure', 1, 2500, 2)
DBCC PAGE ('TestSecurityExposure', 1, 3000, 2)
DBCC PAGE ('TestSecurityExposure', 1, 3500, 2)
DBCC PAGE ('TestSecurityExposure', 1, 4000, 2)
DBCC PAGE ('TestSecurityExposure', 1, 4500, 2)

 

In most cases the very first output (for page 200) will return Junk data. If this is not the case, simply drop the TestSecurityExposure database and recreate it again. Sometimes it’s a timing issue and sometimes it could be a background process (like Windows Update) that uses the expected pages. Regardless, if you do get the same pages again – our contrived data should be easy to find.

You can certainly create the environment on your own and see if you can get it to work. OR, you can get a copy of our AlwaysOn DVD that has the appropriate lab environment. I tend to give away the AlwaysOn DVD at events I speak at (on Availability/Disaster Recovery) but I'm also happy to send a few out over snail mail (it needs 2GB of memory and 10GB of disk space for the virtual environment AND you need to have Virtual PC or Virtual Server installed – which are freely downloadable from Microsoft). Paul asked for DBCC CHECKDB information here (to get a free DVD sent to you) and I'm going to ask for instant initialization numbers and how this has helped. Post a comment here and then send me an email with your snail mail address information as well. I'm willing to do this for the first 10 responses… go!

Have fun…and thanks for reading!
kt

PS – For those of you in our UK (London) Event tomorrow, we're giving away the AlwaysOn DVD (and an even cooler SQLskills pen… lol). Now there's motivation if the content doesn't (NOT!).

26 thoughts on “Instant Initialization – What, Why and How?

  1. I take advantage of Instant File Initialization very often to very quickly grow data files by 20-30GB increments in seconds instead of minutes. I wish Microsoft had done a better job in explaining/publicizing this feature so more people were aware of it. They could modify the SQL Server setup program to prompt the user about whether they wanted to give the SQL Server Service account the "Manage Volume Maintenance Tasks" right during setup.

  2. Here are my results:
    Restore 32GB database (3*8GB data + 8GB tlog) with 25% free space
    Without Instant initialization: between 31 and 34 minutes (test repeated 4 times)
    With Instant initialization: between 24 and 27 minutes (3 measurements)

  3. I turned Instant Initialization on our SQL 2005 Standard servers (2 physical ones mirror, 2 on Microsoft virtual servers)
    but honestly I never did comparison test or performance benchmark.

    Our 32GB.BAK restore last week on the VMs (2GB physical RAM) took 75 mins, 11GB.BAK restore took 25 mins (always around that)
    Same files restore on physical machines (8GB physical RAM) took around 40 and 11 mins

    Somehow I don’t think testing on VMs are any useful, and we don’t want to test on production machines either
    So I can’t say it works on SQL Standard edition for sure

    I even wrote this in my SQL Server install instruction
    but wasn’t sure if last point is valid, is this only enabled if RAM > 4GB?
    and I realized just now that our SQL account is also Local Admin, so this step can be skipped entirely

    Start -> Run… -> gpedit.msc -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment
    • Add the SQL Server AD account (DomainAccount) to “Perform volume maintenance tasks” (skip if account is part of Local Admin group already)
    • ONLY IF RAM > 4GB

  4. we have a EVA attcached EE and even with out the "Perform Volume Maintenance Tasks" permission on the service account it took less than a 5 seconds to create a 20GB database. it took a while to create a 100 GB db though. haven’t tried with the Instant Initialization but will keep posted when I do that.

  5. Kimberly,

    Interesting read. However, I am a bit worried about the security implications of this, with non Windows admins being able to read data on the drives. You also state

    ‘In production environments, database files should NOT be located on file server drives – especially those where restricted and/or sensitive files are stored. As a result of prudent security measures, the true impact of using instant initialization is low. However, because this vulnerability exists, this feature is off by default.’

    Do I understand it correctly that this means that if you have an export/import job running from another (say, Mainframe) you need to run the import not with the SQL Server serice account credentials, but in a seperate security context? If you do not do this, the files you export, would be accessible to the SQL Server service account and any non-Windows admin can read the files because of the ‘Perform Volume Maintenance Tasks’-privilege, no matter where you put the files (on the db volume, Windows fileshare or somewhere else).

  6. Kimberly, from Paul’s blog (and modified a bit), here’s how to tell what permissions have been granted to the service account for your SQL Server instance:

    Logged in as the service account you can run the WHOAMI /PRIV command at the CMD prompt.

    For the T-SQL Version:
    =================================================================================================================
    (Be careful with the xp_cmdshell settings here…it disables it at the end, which may not be what you want in your environment. Remove the sp_configure stmts if you don’t need them)

    EXEC sp_configure ‘xp_cmdshell’, 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO

    CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));
    GO

    INSERT INTO #xp_cmdshell_output EXEC (‘xp_cmdshell ”whoami /priv”’);
    GO

    SELECT * FROM #xp_cmdshell_output

    DROP TABLE #xp_cmdshell_output;
    GO

    EXEC sp_configure ‘xp_cmdshell’, 0;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO

    =================================================================================================================

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.