0

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!

2

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.

  • Thanks! So you are saying even for the same query execution, the IO size issued each second also varies? Is it some number that SQL server internal decides? – user3610199 Jul 6 '16 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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