The (Windows) Performance Monitor counters for SQLServer:ReadAheads give totally irreconcilable results compared to Query Analyzer "Show Statistics IO" (Current Connection Options).
(This is SQLServer2000; I did similar measurements on SQL7 long ago, without this enigma.)
EG
-PM-
Title Maximum Average SUM
Page Reads/sec 436.97 356.56 4635.24
Readahead Pages/sec 133.85 10.60 137.85
-QA-
Table 'xxxtran'. Scan count 2, logical reads 48194, physical reads 2040, read-ahead reads 2572.
This query took about 11 secs. Nothing else is active on this database. PerformanceMonitor was running on 1sec interval, so "SUM" (calculated total of all data points) should be roughly the right total, also avg x time = total.
In PM, Readahead pg/s was 134 for 1 sec, then 4 , then none the rest of the time, there is no way that matches with 2572 readaheads from QA. And yet:
PM Page Reads = QA physical reads + QA read-ahead reads.
(over a wide range of queries) which suggests that "ReadAhead" means something different in QA vs PM, but the total disk reads are the same. It seems to happen for any index-lookup query, more obvious with big resultsets (I was using a query hint). Table scans are fine - same read-aheads in PM as QA.
Anybody else seen this ?
(This is SQLServer2000; I did similar measurements on SQL7 long ago, without this enigma.)
EG
-PM-
Title Maximum Average SUM
Page Reads/sec 436.97 356.56 4635.24
Readahead Pages/sec 133.85 10.60 137.85
-QA-
Table 'xxxtran'. Scan count 2, logical reads 48194, physical reads 2040, read-ahead reads 2572.
This query took about 11 secs. Nothing else is active on this database. PerformanceMonitor was running on 1sec interval, so "SUM" (calculated total of all data points) should be roughly the right total, also avg x time = total.
In PM, Readahead pg/s was 134 for 1 sec, then 4 , then none the rest of the time, there is no way that matches with 2572 readaheads from QA. And yet:
PM Page Reads = QA physical reads + QA read-ahead reads.
(over a wide range of queries) which suggests that "ReadAhead" means something different in QA vs PM, but the total disk reads are the same. It seems to happen for any index-lookup query, more obvious with big resultsets (I was using a query hint). Table scans are fine - same read-aheads in PM as QA.
Anybody else seen this ?
