Enable Instant File Initialization

In SQL Server 2005 (and higher versions), data files can be initialized instantaneously.
Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.

This can lead to a significant performance boost of ‘create database’, restore and autogrow operations.
For example, when a thread executing a query and is a victim of an autogrow, less disk activity needs to be done and it can finish much quicker.

Unfortunately this option is not available for Log files.

How to assign ‘Perform volume maintenance tasks’ to your SQL Server instance:

Start the gpedit.msc utility, browse to ‘user rights assignment’ and add your SQL Server service account to the ‘Perform volume maintenance tasks’ policy.
Restart the SQL Server service (not the windows server), for changes to take effect.

(click to enlarge):



Check the assigned privileges:

Open SQL Server Management Studio under the same account the SQL Server service is running. Open a query window, set result to out put to text and paste/run the following code:

EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC ('xp_cmdshell ''whoami /priv''');

EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

Result in SSMS should contain SeManageVolumePrivilege with state Enabled

(click to enlarge):

SQL Server disk configuration tips

Most important metrics for SQL Server: ‘throughput’: how many MB/s can the disks handle and what is the load placed on the disks.

If you’re buying a standard package your vendor should know this.
Surprisingly however in the hardware proposal (top end server(s) / high rpm disks right?) this figure isn’t mentioned anywhere in most situations.
What you can do, is ask your vendor if they could run a fn_virualfilestats() function (see my fn_virtualfilestats post) on a similar production configuration of another customer (better: talk to them yourself!) . This gives you insight on the I/O pattern (more read of more write activity) and the load on TempDB, to determine if it’s a good candidate –which it usually is- to store on it’s own disk spindles.

Run the Microsoft SQLIO tool to determine a baseline on MB/s and latency per disk partition.
Store it for later reference, the Windows performance tool can also measure the throughput and it gives you the possibility to compare the numbers and check if there might be a disk throughput performance bottleneck in your production system.

Disk configuration tips:

Use as many disk spindles as you can, and try spreading data / log and tempdb on different raid arrays.
Stripe your RAID-config at 256k instead of 64k / 128k.This will increase performance of SQL Server read-aheads.
If you use Windows 2000 / 2003 align the disk partitions!!!:
(see Miscrosoft knowledge base article KB300415 on how to use Diskpart).
At the Diskpart command prompt,
type: Create Partition Primary Align=X,
where X is either 32 or 64, depending on the recommendation from your storage vendor.
If your storage vendor does not have any specific recommendations, it is recommended that
you use 64.
This step is not needed in Windows 2008

Full (not quick) format with a 64kb stripe size for disks holding data/log and backup files.

Full (not quick) format with a 4kb stripe size for OS and program/swap devices

Data raid5 or raid10 when there’s more write then read activity (check with fn_virualfilestats),
Log (raid1 or raid10 under heavy load), separate backup disk (raid5)

Put TempDB on its own physical disks (raid1, raid10 under heavy load).
In a non-clustered environment, I normally use the local server storage for TempDB.

-->
>
Use NTFS volumes

Do not use more then 80% of the volume

Defragment the disks on a regular basis

How to configure SQL Server TempDB

As a general guideline, create one data file for each CPU on the server.
Note that a dual-core CPU is considered to be two CPUs. logical procs (hyperthreading) do not

Only create one Log file

Do not use autoshrink on TempDB

If your'e using SQL Server 2008 set Page verify to ChecksumInvestigate the possibility if you can switch off the properties ‘Auto create’ and ‘Auto Update’ statistics,it can speed up the creation of objects in TempDB. Be careful however: measure the impact these changes can have on production! Change and measure only one parameter at the time.

After installation move the TempDB database to its own disks that differ from those that are used by user databases

ALTER DATABASE TempDB
MODIFY FILE (NAME=tempdev, FILENAME= '\tempdb.mdf');
GO

ALTER DATABASE TempDB
MODIFY FILE (NAME=templog, FILENAME= '\TempLog.ldf');
GO

Restart SQL Server service (not the Windows server), to make changes permanent

Pre-allocate data and log device sizes (create files with same equal size), do not rely on small auto growth steps

If you use auto growth set it to a reasonable size in MB, not a percentage

TempDB file size default FILEGROWTH increment
0 to 100 MB growth:10 MB
100 to 200 MB growth:20 MB
200 to 1000 MB growth:50 to 75 MB
1 GB or More growth:150 to 250 MB

Measure TempDB usage over time with fn_virtualfilestats
New releases of your –vendor- software could have a different impact on TempDB load.