Load Testing a Database attached to a Network storage device

Question

We are looking at stress testing a NAS system for a Database, basically was want to see how much abuse it can take and how much it affects the Database performance. here is what we have planed

• I have a test Tool I'm building that will kickoff a configurable number of threads that run a sql query (also configurable, and thinking about having it able to run multiple quires)
• using the SQLIOSim utility to simulate SQL Server activity
• Copying very large amounts of Data onto and off of the device (at the same time)

Can anyone think of anything else we could do (that's repeatable) for placing a load on the system.

Answer 1

You'll also want to simulate network conditions between your database and the NAS. As more traffic hits a network, its realizable utilization drops, and this will seriously affect your performance.

By way of example. If you have 50 machines on a 1Gbps network and the network is approaching 100% utilization, packet collisions and retries at the data link layer mean that your effective total transfer is a fraction of the network potential that would be realized if you had only two communicators on the net. Worse yet, as retries increase, so does effective load. You get an ugly feedback loop in the face of peak demand.

There are a number of network traffic simulators and generators out there, though I'm afraid I've never used any of them.

Answer 2

Depending on the goals you wish to achieve with your load testing you may also want to look at using SQLIO & not just SQLIOSim. SQLIOSim is very good for stress testing & simulating SQL Server load & will go from green to red if it detects any IO errors. It's output is a bit cryptic though although KKline gives some insight.

SQLIO is useful if you want to perform one operation continuously such as large random reads, or large sequential writes & anything in between. It will also give you some useful output stats which you can graph & use as a comparison.

Answer 3

You could look at Pole Position, a generic database performance testing suite. http://www.polepos.org/

Answer 4

You can try IoMeter from Intel