Databases Reference
In-Depth Information
the creators of this system is to maximize the sequential read activity and to work to increase the size of
each read to again maximize I/O performance. This can be achieved by tuning the T-SQL and indexes
to maximize the use of ReadAhead. The performance can be approximately predicted for each different
I/O type, but coming up with an overall approximation isn't really possible. This is where a real-life test
is necessary to see what will really happen.
Storage Performance
The Performance of the Storage Subsystem is the foundation stone for the performance of the
applications you build in SQL Server. Having a good baseline of the performance your storage sub-
system can deliver is an essential step in the journey to a SQL Server system with good performance.
In this section you will learn about tools to use for running a performance test, and then you will learn
about the various metrics available to measure performance.
StoragePerformanceMeasuring Tools
There are quite a few tools available that measure different aspects of storage performance. Many of
them are specialized to a single application, some for Exchange (LoadSim and JetStress), and others
for file system benchmarking (IOZone) The two most frequently used for pure storage performance
measurement are IOMeter and SQLIO.
IOMeter
This tool is perhaps the most referenced tool for storage subsystem performance measurement. It is
both a performance tool and a stress tool. Originally developed by Intel, it was given to the open source
community. Versions are available from the open source community for Windows, Linux, Netware, and
many variations of Unix. IOMeter's default configuration is to operate as a stress test, and it takes quite
some reconfiguration to get it to operate as a performance tool, but it can be done.
SQLIO
This is a very simple but powerful tool available from Microsoft. No one is really sure why this tool is
called SQLIO as it really doesn't really have anything to do with SQL other than that it was originally
written by a developer in the SQL Server team. SQLIO is a highly configurable I/O performance test tool.
SQLIO requires a good understanding of its various parameters to get any kind of test going, but once
a batch file is written, it's reusable. It does a great job of allowing you to write repeatable performance
tests. It's not designed to act as a stress test tool, and if you try to use it as such you will quickly discover
its limitations.
SQLIOStress/SQLIOSim
SQLIOSim is the new replacement to SQLIOStress. SQLIOStress was originally written by CSS (Customer
Service and Support) to help them troubleshoot troublesome storage reliability issues. SQLIOSim is the
new version of this tool and was written by the SQL Server development team. It's a complete rewrite
and is based upon the I/O code within SQL server, but it doesn't require SQL to be installed to run.
People often mention these tools in the context of performance testing. However, although its possible to
get a performance measure from these tools, their primary purpose is to stress test and to check a storage
subsystem for reliability.
Search WWH ::
Custom Search