SQLIO and its Flags

Question

Any feedback on this will be highly appreciated.

1.-t flag(Threads) – If I capture worker thread information using this query

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

and then calculate the average number of threads from this data and use this as the reference point for –t value scaling upto 256 while testing SQLIO - would this be a right approach for determining right number of threads ?

2.Do the below Perfmon counters map upto to SQLIO numbers as follows : ?

i - (Disk Reads / Second & Disk Writes / Second) --> IOPS,

ii- (Average Disk sec / read & Average Disk sec / write) --> Latency,

iii-(Average Disk Bytes / Read & Average Disk Bytes / Write) --> Block Size,

iv- (Avg./Current Disk Queue Length) --> Outstanding or waiting IOPS,

v- (Disk Read Bytes/sec & Disk Write Bytes/sec) --> Throughput or Aggregate Throughput,

3.What values for -i, -f, and -t should be used to test the disk to extremes?

Answer 1

Welcome to Stack. For reference, when you ask a question, keep it to one question per question. You've got a few different questions in here:

1. How many threads should I use?

There's no "right" number. I happen to use 8 in my short SQLIO instructions, and in my longer (overnight) tests, I try lots of combinations (2, 4, 8, 16, 32, etc). The more threads you use, the more load you're throwing at the storage. Most of the time, though, I can hit the storage's limits with just 8 threads as described in that post.

2. Do Perfmon metrics map up to SQLIO numbers?

No, for lots of reasons. You may have different things happening on the storage at the same time, your drive format may not match the IO that SQLIO is doing, you may be testing multiple logical drives on the same physical drives, etc.

3. What SQLIO parameters test the disk to extremes?

You have to try lots of combinations because different types of storage responds to different loads differently. There is no single "worst case scenario" for all types of storage.