Today I was reading a whitepaper by 3PAR regarding SQL 2008 performance on their storage arrays. It’s an interesting comparison of wide striping, RAID levels, mixed workloads, and number of disks. While browsing through the document I stumbled upon a SQL 2005/2008 feature which I was unaware of, but is important in thin provisioned environments.
Typically with SQL 2005/2008 when you create a database, say 100GB, SQL creates the entire file and zeros out all of the contents. Depending on the database size, this can take a long time. While this is happening, your storage array sees all of this data being written and allocates storage to that volume. Even though the database is empty, since zeros were written the array thinks it is holding data. Clearly, this defeats thin provisioning.
3PAR mentioned a feature in SQL 2005/2008 called Instant File Initialization. If you enable this feature, when you create a new database no zeros are written so creation is nearly instant! Imagine that. This is thin provisioning friendly, since virtually no data is written until the application needs to store data. So if you create a 100GB SharePoint database, your storage array will only allocate storage as SharePoint fills up the database.
Pretty nifty! So how do you enable this? According to the SQL 2005 security best practices whitepaper, you should grant the group SQLServer2005MSSQLUser$MachineName$MSSQLSERVER “Perform Volume Maintenance tasks” user right on the SQL server. This can be done via GPO, or editing the machine’s local security policy. Be sure to restart the SQL services, so it knows you granted this right. If you are using SQL 2008, then group is called SQLServerMSSQLUser$MachineName$MSSQLSERVER.
Now when you create a 100GB database, it could literally take a couple of seconds instead of many minutes. Even if your storage array isn’t thin provisioning enabled, if you are using VMware vSphere 4.0 and thin provisioned virtual disks, you can gain the same benefit.
The feature works on standard as well as enterprise edition versions of SQL, and on Server 2003 and later. Log files are not instantly created and will be fully zeroized. But your log files are generally just 10 to 20 percent of the total database size, so you still save a lot of space.
Nothing is free in life, so there may be a slight performance penalty when new data is written to the database since space was not previously allocated. Microsoft discusses the feature here, and encourages using this feature to enhance performance.
