我们先来看一个用户表上的索引碎片情况： 

 

DBCC SHOWCONTIG scanning 'Lead' table...

Table: 'Lead' (1422628111); index ID: 1, database ID: 90

TABLE level scan performed.

- Pages Scanned................................: 135008

- Extents Scanned..............................: 17511

- Extent Switches..............................: 109860

- Avg. Pages per Extent........................: 7.7

- Scan Density [Best Count:Actual Count].......: 15.36% [16876:109861]

- Logical Scan Fragmentation ..................: 91.29%

- Extent Scan Fragmentation ...................: 30.66%

- Avg. Bytes Free per Page.....................: 3082.4

- Avg. Page Density (full).....................: 61.92%

DBCC SHOWCONTIG scanning 'Lead' table...

Table: 'Lead' (1422628111); index ID: 1, database ID: 90

TABLE level scan performed.

- Pages Scanned................................: 117309

- Extents Scanned..............................: 14847

- Extent Switches..............................: 36638

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 40.02% [14664:36639]

- Logical Scan Fragmentation ..................: 31.94%

- Extent Scan Fragmentation ...................: 17.39%

- Avg. Bytes Free per Page.....................: 3385.8

- Avg. Page Density (full).....................: 58.17%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

碎片很严重，会导致性能低下， 可以重建索引来改进性能， 脚本如下：

use [AdvantureWorks]

declare @table_id int

set @table_id=object_id('Lead')

dbcc showcontig(@table_id)

dbcc dbreindex('SFNewOrg.dbo.Lead','',70)