As a production DBA , I get to start diving into the internals of the SQL Server environment. Over the last year, we had an opportunity to ‘let loose’ on a new clustered environment with a dedicated SAN. Zoom… Zoom…Zoom! It was like performing sea trials or a shakedown tour in a new ship. Much of the testing centered around the execution of existing SQL code and processes. We were able to set-up a series of tests using the Microsoft ‘s SQLIO Disk Subsystem Benchmark Tool to saturate the disk subsystems.
The tool does not actually test your SQL Server configuration , but provides the means to script out and execute a controlled load against the disk subsystem. Scripts can be executed to create a variety of read and write operations. Parameters allow the load to use a variety of file sizes, number of threads, latency, outstanding requests in random or sequential reads/writes for specific durations. I am not going to get into the specifics of the scripts we used. The links below provide a much better examples than I could tap out in this post.
The testing was conducted in two main iterations. The first was to run a script against one drive at a time with increasing load. Next, executed the scripts all at the same time against all the drives. Establishing times with our SAN group, we were able to capture activity with SAN monitoring tools. Another neat find was how to import the SQLIO output to SQL server. The output is a bit cryptic, but the import cleaned up the information for analysis. We had a limited window to test. I wish wish we had added a few more iterations with multiple scripts on the same drive at one time.
Below are the links I used to setup the tests:
SQLServerpedia.com – SAN Performance Tuning with SQLIO
Probably the best collection of information and application of the tool. There is a How-to One on importing the output into the SQL Server.
Brent Ozar – SQLIO Tutorial: How to Test Disk Performance
The above link has information and even videos by Brent, but this link does a deeper dive into the tool.
SQLCrunch.com has some great articles on monitoring and understanding I/O bottlenecks.
Tiny URL for this post:
Comments are closed.