One feature that (in my opinion) hasn't gotten the attention that it deserves is Instant File Initialization. As documented in the
Siemens Teamcenter and SQL Server Best Practices
whitepaper is it recommended to have this options enabled but you might be asking yourself what this is and why you should do it.
With Instant File Initialization turned on SQL Server can initialize database files instantly, without physically zeroing out each and every 8K page in the file. The space is just allocated to SQL Server by the Operating System thus saving your a great deal of time (depending on the size of files you are working with).
So what operations can take advantage of this feature:
Creating a new database
Increasing the size of data file (manual and autogrowth)
Creating tempdb when SQL Server gets restarted
Restoring a database or filegroup
Transaction Log files cannot make use of Instant File Initialization
Just how much time can be saved? I tested creating 3 different new databases
Start the local security policy tool (as an administrator)
Expand Local Policies
Expand User Rights Assignments
Find the "Perform Volume Maintenance" Policy
Right click on "Perform Volume Maintenance" and select Properties
Add the SQL Server service account
Restart the SQL Server Service
Windows Server 2003, 2008, or 2008 R2
SQL Server 2005, 2008, or 2008 R2 (any edition)
MSDN on Database File Initialization
: Because the deleted disk content is overwritten only as new data is written to the files, the deleted content might be accessed by an unauthorized principal. While the database file is attached to the instance of SQL Server, this information disclosure threat is reduced by the discretionary access control list (DACL) on the file. This DACL allows file access only to the SQL Server service account and the local administrator. However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME. A similar threat exists when the database is backed up. The deleted content can become available to an unauthorized user or service if the backup file is not protected with an appropriate DACL.
What does that mean?
Because Instant File Initialization doesn't zero out the data when allocating it to SQL Server, if the disk being allocated previously belonged to a now deleted database (with the right tools) the data could potentially be accessed. This is a VERY low risk and almost a non-issue but it important to call it out as something to just be aware of.