Disk Configuration

When optimizing SQL Server it is highly recommended to set the tempDB, the data disk and the log disk on seperate drives (preferably SSDs) [1].

TempDB

Place the tempDB database on a separate disk and adjust the number of secondary data files according to how many (logical) processors the host features [2]. If the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. For TempDB it usually is sufficient to create 8 equally sized data files. As a general rule of thumb:

• Number of logical processors < 8

  –> Set the number of data files equal to the number of logical processors

• Number of logical processors >= 8

  –> Set the number of data files to 8

Data & Log files

Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.

Also format your data disk to use 64-KB allocation unit size for all data files placed on a drive [3]. Use SSDs with high IOPS where possible.

[1]

Place Data and Log Files on Separate Drives

[2]

TempDB data files

[3]

Best practice storage