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.