I ran showcontig statement on the same database few times and found logical and extent fragmenation changes on each occasion. Quite abnormally I found auto improvement without doing any maintenance. The sample below shows results of the same table on consecutive days(day1, day2). How can you explain this situation
Day1 ( First day)
Table: 'WORKPERMIT' (142115747); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 274
- Extents Scanned..............................: 36
- Extent Switches..............................: 35
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 97.22% [35:36]
- Logical Scan Fragmentation ..................: 9.49%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 704.4
- Avg. Page Density (full).....................: 91.30%
Day2 (second day)
Table: 'WORKPERMIT' (142115747); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 274
- Extents Scanned..............................: 35
- Extent Switches..............................: 34
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 100.00% [35:35]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 14.29%
- Avg. Bytes Free per Page.....................: 703.5
- Avg. Page Density (full).....................: 91.31%
Sql version : SQL SERVER 7 SP3