改进SQL Server 性能 - 索引碎片重建

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

 

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)