SQL Server IOPS

Question

I'm trying to evaluate the IOPS of TPCH queries on SQL Server. Specifically, for TPCH query 13, using "set statistics io on" and "set statistics time on", I got:

Table 'orders'. Scan count 9, logical reads 257826, physical reads 2, read-ahead reads 237643, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'customer'. Scan count 9, logical reads 33816, physical reads 1, read-ahead reads 33662, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I calculated the total physical reads issued by this query as the sum of "physical reads" and "read-ahead reads" for each table. For this example it is (2+237643+1+33662)=271308

The elapsed time for this query is: 43901 ms = 43.901 s

 SQL Server Execution Times:  CPU time = 21795 ms,  elapsed time = 43901 ms.

So per my understanding, the average IOPS of this query is (271308/43.901 sec=6180/sec).

However, I used windows performance counter to get IOPS for sqlserver:

Get-Counter -Counter "\process(sqlservr)\io read operations/sec" -SampleInterval 1 -MaxSamples 3600

And the IOPS shown for that query is only hundreds:

"(Pacific Daylight Time)"   "\process(sqlservr)\io read operations/sec"
"07/01/2016 14:15:55.782"   "470.67705754299271"
"07/01/2016 14:15:56.792"   "380.1531707549322"
"07/01/2016 14:15:57.809"   "463.43367654026179"
"07/01/2016 14:15:58.818"   "488.44764282221558"
"07/01/2016 14:15:59.828"   "457.38267181343429"
....

So here is what I'm confused:

1. What should be calculated as IOPS of this query? Is it total physical reads divided by total elapsed time, or is it the average of windows performance counter results?
2. Per my understanding, each physical read reads one 4k physical page from disk, then each "io read operations" shown in windows performance counter could read multiple pages? How many pages are read for each IO and where this number is configured?

Thanks!

Answer 1

Point 2 is where your confusion lies, 1) Pages in SQL Server are 8KB 2) SQL Server does not only issue 8KB (or 4KB reads) it will issue them at varying sizes, which can be bigger so your IOPS would be smaller.