Understanding Show Stats I/O output 
Author Message
 Understanding Show Stats I/O output

Hi all,

I am attempting to understand the output of the SQL 7 "show stats I/O" QA option in determining which of two sets of queries performs the best. Does scan count affect performance more than logical reads even if the scans are index (and not table) oriented? Based on the stats, can someone please tell me which queries are better performers in #1 (a or b) and in #2 (a or b) below or would you need to know what type of scan is being performed via the "display execution plan"?  Is there any documentation on this other than in BOL?

Thanks for any assistance,
Jim

1.

a. Table 'CustomerPhone'. Scan count 7, logical reads 6681, physical reads 0, read-ahead reads 0.
Table 'ReferenceParameters'. Scan count 2405, logical reads 5134, physical reads 0, read-ahead reads 0.
Table 'Individual'. Scan count 897, logical reads 5711, physical reads 0, read-ahead reads 0.

b.  Table 'CustomerPhone'. Scan count 6279, logical reads 18927, physical reads 0, read-ahead reads 0.
Table 'ReferenceParameters'. Scan count 30, logical reads 60, physical reads 0, read-ahead reads 0.
Table 'Individual'. Scan count 897, logical reads 5855, physical reads 0, read-ahead reads 0.

2.  

a.  Table 'CustomerPhone'. Scan count 489, logical reads 1653, physical reads 0, read-ahead reads 0.
Table 'ReferenceParameters'. Scan count 1719, logical reads 3918, physical reads 0, read-ahead reads 0.
Table 'Individual'. Scan count 242, logical reads 1243, physical reads 0, read-ahead reads 0.
Table 'Company'. Scan count 242, logical reads 1616, physical reads 0, read-ahead reads 0.

b.  Table 'ReferenceParameters'. Scan count 73, logical reads 147, physical reads 0, read-ahead reads 0.
Table 'CustomerPhone'. Scan count 484, logical reads 1477, physical reads 0, read-ahead reads 0.
Table 'Individual'. Scan count 148, logical reads 797, physical reads 0, read-ahead reads 0.
Table 'Company'. Scan count 242, logical reads 1616, physical reads 0, read-ahead reads 0.

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 05:48:51 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Understanding Show Stats I/O Output

2. Understanding stats time output in query analyzer?

3. Understanding the output from SHOW SGA

4. Computer with Linux OS not shown on Computers with Windows OS

5. show stats time

6. Does Profiler show IO stats ?

7. lsnrctl services listener_name outputs double the stats

8. BCP Performance Stats to output file

9. Need help understanding sp_sysmon output

10. Understanding TKPROF output

11. Understand output of DBCC show_statistics

12. HELP : Understanding sp_spaceused output


 
Powered by phpBB® Forum Software