Interpreting Statistics IO

This is my orignal statistics io return:
Table 'MyTable'. Scan count 1, logical reads 4338, physical reads 0, read-
ahead reads 0.
This is my revised statistics io return:
Table 'MyTable'. Scan count 117, logical reads 536, physical reads 0, read-
ahead reads 0.
Do I view this as an improvement since the logical reads reduced? Profiler
shows a large reduction in duration and reads when comparing the original to
the revised. The Scan count grew substantially, yet the logical reads dropped
substantially. Based upon Profiler my assumption is that the logical reads
reduction is the best indicator. True?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> Based upon Profiler my assumption is that the logical reads
> reduction is the best indicator. True?
Yes. Logical reads is as close as you can get to seeing how much SQL Server
had to actualy hit the hard drive. The only reason "Physical Reads" are zero
is because the data your query needed is already in the SQL Server data
cache.
You could run
DBCC DROPCLEANBUFFERS
to empty the cache, forcing SQL Server to actually hit the hard drive, and
you'd see how slow your query really could take.
But hard drive access is everything.|||Compare to a query that i wish i could make faster:
Table 'Worktable'. Scan count 155, logical reads 16039, physical reads 0,
read-ahead reads 0.
Table 'Patrons'. Scan count 4, logical reads 12, physical reads 0,
read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 114968, physical reads
0, read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 194430, physical reads
0, read-ahead reads 0.
Table 'Windows'. Scan count 1566, logical reads 3132, physical reads 0,
read-ahead reads 0.
Table 'Sessions'. Scan count 1566, logical reads 9396, physical reads 0,
read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 39312, physical
reads 0, read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 26412, physical
reads 0, read-ahead reads 0.
Table 'LCTs'. Scan count 4, logical reads 40, physical reads 0, read-ahead
reads 0.
Table 'LCTs'. Scan count 4, logical reads 372, physical reads 0, read-ahead
reads 92.
Table 'Worktable'. Scan count 135, logical reads 14779, physical reads 0,
read-ahead reads 0.|||Thanks Ian.
For further clarification on the relation to Reads shown in Profiler and the
logical and physical reads listed for the same procedure in Statistics IO, it
appears the Reads shown in Profiler are greater than the sum of the logical
and physical reads produced for the same procedure in Statistics IO. Is
Profiler showing reads against System tables whereas Statistics IO does not?
Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>is because the data your query needed is already in the SQL Server data
>cache.
>You could run
>DBCC DROPCLEANBUFFERS
>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>you'd see how slow your query really could take.
>But hard drive access is everything.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||> Is
> Profiler showing reads against System tables whereas Statistics IO does not?
Yes. Also, STATISTICS IO doesn't show I/O performed by scalar UDFs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:616e5ea2a1085@.uwe...
> Thanks Ian.
> For further clarification on the relation to Reads shown in Profiler and the
> logical and physical reads listed for the same procedure in Statistics IO, it
> appears the Reads shown in Profiler are greater than the sum of the logical
> and physical reads produced for the same procedure in Statistics IO. Is
> Profiler showing reads against System tables whereas Statistics IO does not?
> Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>>is because the data your query needed is already in the SQL Server data
>>cache.
>>You could run
>>DBCC DROPCLEANBUFFERS
>>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>>you'd see how slow your query really could take.
>>But hard drive access is everything.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1