sqlserver重建索引

1.DBCC SHOWCONTIG
USE yourDatabase
GO
DBCC SHOWCONTIG (“yourTable”);
GO

result:
DBCC SHOWCONTIG scanning ‘yourTable’ table…
Table: yourTable(412579487); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned…………………………..: 1298
- Extents Scanned…………………………: 163
- Extent Switches…………………………: 162
- Avg. Pages per Extent……………………: 8.0
- Scan Density [Best Count:Actual Count]…….: 100.00% [163:163]
- Logical Scan Fragmentation ………………: 0.00%
- Extent Scan Fragmentation ……………….: 17.18%
- Avg. Bytes Free per Page…………………: 1481.8
- Avg. Page Density (full)…………………: 81.69%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

信息 描述
Pages Scanned 表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches DBCC遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent 相关区域中的页数
Scan Density[Best Count:Actual Count] (越接近100%越说明没有，低了则说明有外部碎片。)
Best Count是连续链接时的理想区域改变数，Actual Count是实际区域改变数，Scan Density为100%表示没有分块。
Logical Scan Fragmentation 扫描索引页中失序页的百分比(该百分比应该在0％到10％之间，高了则说明有外部碎片。)
Extent Scan Fragmentation 不实际相邻和包含链路中所有链接页的区域数(百分比应该是0％，高了则说明有外部碎片)
Avg. Bytes Free per Page 扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度，表示页有多满(每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片)

2.若是碎片过多，则可以选择重建索引
2.1
USE yourDatabase
GO
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ’ ‘, 80)”
GO
EXEC sp_updatestats
GO

2.2
USE yourDatabase
GO
if (exists (select * from sys.objects where name = ‘spUtil_ReIndexDatabase_UpdateStats’))
drop proc spUtil_ReIndexDatabase_UpdateStats
GO
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT ‘[‘+TABLE_SCHEMA+’].[‘+TABLE_NAME+’]’
FROM information_schema.tables
WHERE table_type = ‘base table’
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Reindexing Table: ’ + @MyTable
DBCC DBREINDEX(@MyTable, ”, 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO
EXEC spUtil_ReIndexDatabase_UpdateStats
GO