Microsoft SQL Server Development Customer Advisory Team

Deploying SQL Server 2005 with SAN #3

Prem Mehra and Mike Ruthruff

We recommend that prior to deploying SAN in a SQL Server production environment you conduct a bench-mark to establish a performance base-line. There are several benefits: 1) Establish a base-line so that you know ahead of time the maximum throughput and response time to expect 2) Identify any performance issues quickly and resolve them. This process is quicker because the issues are only due to the I/O subsystem since the DBMS has not been deployed yet.    

Tools such as SQLIO.exe or IOMeter can be used for this base lining.  SQLIO.exe is an unsupported tool provided by Microsoft to accomplish this and is available for download from here. http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi.

IOMeter is a tool available at: http://sourceforge.net/projects/iometer/

The name - SQLIO.exe - implies that the tool is designed specifically for SQL Server. That is not the case; you as a user have to design your tests to simulate the type of I/Os that SQL Server is designed to issue.  That said, keep in mind that the purpose of running SQLIO.exe is not to accurately simulate SQL Server IO workload but rather test the limits of the IO subsystem for different basic types of IO in order to determine if performance of the chosen configuration is acceptable.

Characteristics for most general types of SQL Server IO are given in the below table. 

Note these values may change as optimizations are made to take advantage of modern storage enhancements.

The SQLIO.msi download for SQLIO tool contains an .rtf file which has some details about the considerations one should make when benchmarking a disk subsystem and how to interpret the results.  We would recommend reviewing this file before running tests.  Some of the key points to keep in mind are the following:

• Use test files which are significantly larger than SAN cache.
• Make sure to test a combination of different IO types and sizes.  We generally try to include the following in the tests:
  • Large amount of random 8K writes (checkpoint).
  • Sequential IO’s of 2K – 64K in size (log).
  • Sequential reads/writes of 8 – 256K (read-ahead/bulk load).
• Test all data paths independently or, if multipathing is used, make sure to monitor this and ensure it is working as expected during tests.
• Understand the theoretical limits of your configuration prior to running tests.  Remember each component in the path from host to disks has an associated throughput (i.e., HBA, fiber channel switch ports, fiber channel array ports, service processors, disks…).
• Adjust test duration so that enough time elapses to exhaust cache on the array.  On a dedicated array tests will generally run very fast at the beginning and slow as the array cache is filled.   Similarly, wait for some time between tests to allow the array cache to flush. 

Also included as part of the SQLIO download is a sample script.  Keep in mind this is purely a sample and can/should be modified as needed based on the type of IO workloads you expect to run when SQL Server is deployed.

To summarize, the idea is to base-line performance of the IO subsystem prior to SQL Server deployment so that any disk subsystem issues are identified and limits of disk subsystem performance are understood.  Sharing the results of these tests with storage vendors can be helpful as well in determining if a given configuration is performing as it should be.

For a more in depth look at the internals of SQL Server IO see the following paper:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

In addition, please review the following paper on disk subsystem performance:

http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx