Smaller disks <> more spindles which one is better for performance?
I have gone through a question within the forums asking "smaller disks or more spindles" which is better for performance. The simple answer is more disks to handle the data growth and also mostly depends upon the number of transactons that are handled with a sensible normalization, as the transactions method & the CHECKPOINT method are important to assess such information. Further if that database is currently in live and if you are looking to upgrade the hardware then the best option is to go with SYSMON by collecting relevant hardware disk, CPU, memory related counters for further analysis.
IN this regard I'm going into basics that we miss always, some disks and disk configurations perform better when reading than when writing. You can compare the reading and writing capabilities of your disks by reading from a physical disk and then writing to the same physical disk. You might see some variations in the time it takes to read from or write to disk on standard disk configurations, also when you start writing to the disk during a read operation that you are monitoring, you will notice some dips in the curves of graphed data for read activity. This is because the application doing the reads must stop briefly to allow the write operation to proceed and then, when the write is finished, the read operation resumes. You can observe this as Performance Logs and Alerts service logs data.
If your configuration contains different types of disks, controllers, and buses, the differences in their designs can have an influence on throughput rates. You might want to test throughput using these different disk systems to determine if some components produce less favorable results overall or for certain types of activity, and replace those components as needed. Be aware of the seek time, rotational speed, access time, and the data transfer rate of your disks by consulting manufacturer documentation. Also consider the bandwidth of cabling and controllers. The slowest component determines the maximum possible throughput, so be sure to monitor each component.
So think ahead before hosting such an important database within your SQL Server environment.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.