How to read Statistics time and Statistics I/O

How to read Statistics time and Statistics I/O

Post by Fie Fie Nile » Thu, 29 Jun 2000 04:00:00



I have a question on how to read(understand) the result from "Show Stats
time" and "Show Stats I/O" in SQL Server 7.0 Query Analyzer.

When I run Index Tuning Wizard it suggested me to create a new non clustered
index on my table. I did that.
The following is the result from "Show Stats time" and "Show Stats I/O"
before and after I created the index:

BEFORE CREATING INDEX:

Table 'SCREEN'. Scan count 1, logical reads 23, physical reads 0, read-ahead
reads 0.
SQL Server Execution Times:
    CPU Time = 172 ms, elapsed time = 481 ms
SQL Server parse and compile time:
    CPU Time = 0 ms, elapsed time = 0 ms
SQL Server Execution Times:
    CPU Time = 0 ms, elapsed time = 0 ms

AFTER CREATING INDEX:

Table 'SCREEN'. Scan count 45, logical reads 117, physical reads 1,
read-ahead reads 0.
SQL Server Execution Times:
    CPU Time = 47 ms, elapsed time = 458 ms
SQL Server parse and compile time:
    CPU Time = 0 ms, elapsed time = 0 ms
SQL Server Execution Times:
    CPU Time = 0 ms, elapsed time = 0 ms

1. Is elapsed time = CPU Time + other time? Is elapsed time = total time it
took to execute the query?
2. What are the definition for Scan count, logical reads, physical reads and
read-ahead reads?
3. I notice that after creating the index, scan count, logical reads and
physical reads are higher, but why both CPU Time and elapsed time are lower
if scan count, logical reads and physical reads are higher?

Thank you.