I often make recommendations to IT and Software Development teams as to the best disk configuration for Microsoft SQL Server 2005. There is so much information available on this topic; I wanted to condense what I have learned into a simple, visual view of my recommendation for the logical disks for SQL Server 2005:
Microsoft SQL Server 2005 |
| |||
|
|
|
|
|
|
Disk 0 |
RAID 1 (mirror) |
| |
|
40 GB |
(C:\) |
| |
|
15K RPM |
Use: OS |
| |
|
|
|
|
|
|
Disk 1 |
RAID 1 (mirror) |
| |
|
40 GB |
(E:\) |
| |
|
15K RPM |
Use: SQL Server exe |
| |
|
|
|
|
|
|
Disk 2 |
RAID 10 (mirror, stripe) |
| |
|
5 TB |
(D:\) |
| |
|
15K RPM |
Use: Data |
| |
|
|
|
|
|
|
Disk 3 |
RAID 1 (mirror) |
| |
|
100 GB |
(L:\) |
| |
|
15K RPM |
Use: DB Tx Logs |
| |
|
|
|
|
|
|
Disk 4 |
RAID 10 (mirror, stripe) |
| |
|
100 GB |
(T:\) |
| |
|
15K RPM |
Use: Temp DB |
| |
|
|
|
|
|
They key lessons I have learned for optimal performance:
1. Separate the Operating System, SQL Server exe, Database Data, Database Transaction Logs, and Temp database onto different physical I/O controllers and disk subsystems.
2. Use RAID 1 (mirror) for the disk drives being used for the: Operating System, SQL Server exe, and Database Transaction Logs.
3. Use RAID 10 (mirror, than stripe) for the disk drives being used for the: Database Data and Temp database.
Also, the fastest the drives (or spindles as they commonly referred to when discussing disk subsystems), the better.
