Every few months I get a (sometimes panicked) call or email about SQLIOSim that goes like this:
“We’re doing some testing of a new disk array. SQL Server’s going to be one of the workloads, so we downloaded SQLIOSim and are running it, but the disk latency is terrible. Can you help?”
Well, the disk latency is terrible because SQLIOSim is designed to test IO stability and functional correctness, not to model a particular workload. SQLIOSim actually attempts to break the storage (create page corruption or stale reads) so as to expose configuration or hardware problems that can cause data corruption. It doesn’t really try to create a workload that “looks” like, say, an OLTP or DW workload.
So you can use SQLIOSim to make sure your storage is healthy, but keep in mind:
- It is a pass/fail test. Either you have stale reads/corrupt pages or you don’t.
- Performance data like queue length and latency are irrelevant during SQLIOSim runs
- You cannot compare the performance results of one SQLIOSim run to another
- Be careful about other workloads on the array while you’re running SQLIOSim
For more discussion on SQLIOSim, there are a bunch of good links referenced here.
You might wonder how you actually see how a new storage environment is going to perform under a particular workload. There are several options, and I’ll list them in terms of accuracy of the model.
Use SQL Profiler to capture and replay traces
This is by far and away the most accurate way of seeing how a given storage configuration will perform under a production workload. After all, it is precisely the same workload as production. You can also use something like Benchmark Factory to modify the workload to anticipate growth and so forth.
Use Perfmon to gather data and Iometer to create the workload
This is somewhat less accurate than traces, but can be easier to set up. You gather all the information needed to create iometer workloads using perfmon and educated guesses.
- Disk Reads & Disk Writes/sec – use that to figure the r:w ratio
- Data file sizes – use that to figure the working set
- Disk Bytes/Read & Write – use that to figure the IO sizes
- Random vs Sequential – there is no way to determine randomness of the workload from perfmon. Logs will always be 100% sequential, DB files will be a mix. Think about the workload a bit to determine the mix (100% random will usually be safe).
Aside from the educated guesses around randomness and thread counts, there are two other big issues with iometer: It goes as fast as it can. There’s no way to configure Iometer to only issue a specific number of IOs/second. So it will reflect the workload type, but not the workload rate. The other thing it will not approximate is the skew. Most database environments will have certain tables or parts of the dataset that are more active than others. In some cases, over 90% of the IO can be directed at less than 5% of the total data set (low skew). Consider an order/inventory OLTP database that has decades of data in it – nearly all the IO will be directed at the latest data – this is a low skew environment. If you believe you have a low skew environment, you can set the file size to what you estimate the total skew to be, and create a fully random worker (but preferably use a trace instead of iometer).
Use the SQLIO Disk Subsystem Benchmark Tool
This is actually my least favorite method. SQLIO is indeed extremely simply to use, but there is no way to make this look like a production workload. You choose one workload type – all reads, or all writes. One IO size. Random or sequential. Although it can expose some of the limits around a disk configuration, there is no way to make it look like a “real” SQL workload.
At the end of the day, the only workload generator that will create a workload that looks like yours is SQL Profiler and traces. And it’s more important than ever to capture and recreate the workload accurately, including such features of the workload such as skew, since they directly impact the effectiveness of modern storage techniques such as extended cache and storage auto-tiering.