What kind of disk storage is on your database server? It does matter, depending on the size of your database, and the distribution of the data it contains. It also matters how much memory is available for SQL Server to do work.
As you probably know, SQL Server does all of its work in memory. New records are created in memory. Data is updated in memory. Data is deleted in memory. Once all of the transaction has been completed in memory, a different process with persist the changes to disk, assuring your modifications are retained. That’s a really simplistic explanation, but, it gives you the general idea that changes are not written to the disk immediately.
When the data you need is not in memory, then disk performance does matter. Sometimes you can overcome slow disk performance with a lot of cache. Sometimes you can’t. Purchasing IAAS you’ll find there are different models where the disks available may be different kinds with different performance characteristics. You may have Solid State Disks (SSDs) which are finding themselves in SANs, and servers for really fast access. Others may have large arrays of disk in a SAN, often with a large cache buffer of its own. You may find direct attached disk controllers with many different configurations.
Even choosing disks is another performance defining factor. High Speed SCSI disks, slower SATA disks, and different RAID configurations have a high impact on the disk performance for your database. How much does it matter? Usually, not much at all. A database with less than 250 gig will often perform nicely on simple SATA drives. When you start dealing with larger databases, then the design of the database tables, the disk configuration, and the memory can all play huge parts in the database performance.
This week we’ll break this down. If you want to get in ahead of the curve, share your story in our comments or by Email to btaylor@sswug.org.
Cheers,
Ben