Determining SQL Server performance abilities

The previous section is used for measuring the performance of the SQL server in operation. You may still want to know the maximum rates that you can expect from your system; this will give you a sense of when your server is reaching its limits. RAM and CPUs operate at predetermined speeds, but storage subsystem performance is dependent on several factors. Several tools can be used to assess the maximum performance of your storage subsystem. Perhaps the two most common tools are Microsoft’s SQLIO (free) and IO Meter (shareware). These tools give you two crucial measurements for your subsystem, the read/write speeds and the disk queue lengths. They will also tell you the maximum throughput rates in MB/s. These tests should be performed on new subsystems in a lab environment, not against an active production server. In general, a well performing subsystem should have disk write speeds of 1 to 5 ms or better for log volumes and 20 ms or better for data volumes. These volumes are discussed in the next section.