Split your I/O

There are two different types of physical disk I/O: sequential and random

For our work it is very helpful to know which type of I/O occurred on our system.

The sql server transaction log is a example for sequential I/O.
This files are written sequentially and there is only a small move of the disk drive head.

On the other side there are our database files.
Due to the need of search data there are many seeks and rotations on the disk needed.
This type of I/O is much slower.

To speed up your performance it is a good solution to split your I/O.

Depending of your budget use as many disks as possible.

Consider to use a own physical disk array for:

• SQL Server Transaction Logs (sequential)
• Backup Files (sequential)
• Program Files, OS Files (random)
• Database Files (random)
• TempDB (random)

 

TempDB – Placement And Sizing

Not only at systems with very high usage of TempDB the correct planing is useful.
Do you exactly know how much workload your system must manage in the future ?

From my experience it could be a difficult task to change system settings during production when no downtime is available.

Therefore here are some rules to prevent issues with impact on the availability and/or performance.

First consider to place TempDB on his own physical disk array (Better more smaller disks as fewer big disk, Keyword: I/O).

Use RAID 1+0 or RAID-1.

As reported by Microsoft create a single data file for every CPU. Note: Dual Core count as 2 CPU’s, Hyperthreading not.
Equal sizing of the data files are required. Note: This apply only to the data files, not to the log files.

About the sizing of TempDB. There is no golden rule. During my research i found various opinions about sizing TempDB.

For my systems i use follwing setup:
40 % of the size of all production databases for the TempDB data files, 40% of the size of TempDB for the log file.

This values i use for the preallocation of the the TempDB data files size.
If you use the autogrowth option take sure to use equal increments for every data file.
Note: Use a reasonable value for the increments.
If the value is to small you will receive a performance hit every time the system expand your files.
This is workload on your server you don’t need.