0

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,

Improve this question

1 Answer 1

Reset to default
1

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.)

Improve this answer
1
  • Thank you for the response! I will look into Diskspd. I would agree with your thoughts, re "Tiny Straw," but I, at least, know what database, and then application experience I can expect through that tiny straw. I then just need to demonstrate the new storage will be an equal or greater straw. I am looking at virtualizing the server on an existing SAN, so I am not really building the storage specifically for the Server. (EQL, so there is really only one underlying pool of disks) I am just trying to confirm through testing that I can expect as good or better performance.
    – Ryan Roberts
    Commented Jan 21, 2016 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.