Microsoft SQL Server disk IO

  • Like most databases, most the of disk IO are reads.
  • The data is written once, but it is read, reviews, printed, and analyzed more than once.
  • Do you know what kind of work does Microsoft SQL Server has to do, to read your data?

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Disk reads

  1. USE sql911;
  2. go
  3. DBCC showcontig('nums');
  4. go
  5. SET STATISTICS io ON
  6. go
  7. SELECT * FROM nums
  8. go
  9. SET STATISTICS io OFF
  10. go
DBCC SHOWCONTIG scanning 'Nums' table...
Table: 'Nums' (117575457); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 1686
- Extents Scanned..............................: 213
- Extent Switches..............................: 212
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.06% [211:213]
- Logical Scan Fragmentation ..................: 0.42%
- Extent Scan Fragmentation ...................: 0.47%
- Avg. Bytes Free per Page.....................: 10.9
- Avg. Page Density (full).....................: 99.87%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Num
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
...
1048567
1048568
1048569
1048570
1048571
1048572
1048573
1048574
1048575
1048576
1048577

(1048577 row(s) affected)

Table 'Nums'. Scan count 1, logical reads 1693, physical reads 3, read-ahead reads 1689, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • I truncated the output, there is no need to display 1 million rows!
  • dbcc showcontig will not be supported by SQL Server 2008 and onward. What a pity!
  • Don't forget to reset the set statistics io off

Compare the output of dbcc showcontig versus set statistics io on

  1. There is almost no overhead: 1686 pages and only 1693 logical reads.
  2. Only 3 separate reads, with 1689 read-ahead reads.
    And all that because of the Scan Density is 99.06% and the Avg. Page Density is 99.87%.
    Great performance!