Perfmon Counters that relate to SQLIO

benchmarkperformancestorage

I am trying to benchmark a new virtual SQL server. this will be the first time I have virtualized a production SQL server. I don't think this is a supper busy server. 100-200 users, of varying activity.

I have been capturing statistics from the current physical server for quite a while.

I am running SQLIO on the new server. The full scope, read/write random/sequential, 2-64 Threads 1-128 outstanding requests. I captured 5 minute runs with 1 minute pauses in between against a 20GB dat file.
…none of this is really relevant to my question.

I am able to capture the physical disk counters and I can see the latency (avg disk sec/read or write) statistics, that match. I am also checking out the (avg disk byte/read or write) to see how much activity SQLIO produces. It produces way more disk activity than our production server.

My question is, are there counters I can also grab, to verify the thread and outstanding request options in SQLIO? I have not been able to find a clear answer. What I am wanting to do, is look at the statistics from our production server, and gauge what SQLIO settings are most representative of our actual server.

SQLIO is really stress testing the storage system, and I would like to be able to set it in a way, so that it is a real representation.

I am not sure how to identify the real (avg) thread or (avg) outstanding requests of the current physical server.

Thank you for your time,

Best Answer

SQLIO is technically deprecated - it's been replaced by the newer utility Diskspd. However, the same basic answer applies.

If you look at an existing server and guess how much storage throughput it's USING, then look at the counters:

  • IOPs - Physical Disk Reads/Sec and Writes/Sec
  • IO size - Physical Disk Read Bytes/sec divided by Reads/sec (and same for writes)
  • Latency - Physical Disk Avg Sec/Read and Sec/Write

However, this only reflects what your current storage is able to deliver, not what your server would rather have. Think about it as drinking water through a small straw. If I ask how fast you want to drink, and I measure your consumption through that tiny straw, it doesn't tell me how fast you could actually drink if you weren't constrained by such a small straw. Instead, I need to hook you up to a pipe (or straw) with unlimited throughput, and then measure how much you actually consume - and then use that to size the storage you need. (Or want, anyway.)

Related Question