引用文档 MSDN:《Reorganize and Rebuild Indexes》
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.
The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.
|
Column |
Description |
|---|---|
|
avg_fragmentation_in_percent |
The percent of logical fragmentation (out-of-order pages in the index). |
|
fragment_count |
The number of fragments (physically consecutive leaf pages) in the index. |
|
avg_fragment_size_in_pages |
Average number of pages in one fragment in an index. |
After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.
|
avg_fragmentation_in_percent value |
Corrective statement |
|---|---|
|
> 5% and < = 30% |
ALTER INDEX REORGANIZE |
|
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. Very low levels of fragmentation (less than 5 percent) should not be addressed by either of these commands because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index.
 Note |
|---|
|
In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. |
Script1：检测系统中Index Leaf-Level average Fragmentation 程度大于10%的Index
SELECT s.name AS SchemeName, o.name AS TableName, i.name AS IndexName, MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N‘DETAILED‘) AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id WHERE ps.avg_fragmentation_in_percent >= 10 AND i.type IN (1, 2) --1: CLUSTERED, 2: NONCLUSTERED AND o.type = N‘U‘ --U: USER_TABLE AND ps.index_level = 0 --Index leaf-level GROUP BY s.name, o.name, i.name ORDER BY avg_fragmentation_in_percent DESC
Script2：使用cursor逐个进行Index Defragmentation
DECLARE @SchemeName NVARCHAR(MAX)=N‘‘; DECLARE @TableName NVARCHAR(MAX)=N‘‘; DECLARE @IndexName NVARCHAR(MAX)=N‘‘; DECLARE @avg_fragmentation_in_percent FLOAT=0; DECLARE @SQL NVARCHAR(MAX)=N‘‘; DECLARE cur_index CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR SELECT s.name AS SchemeName, o.name AS TableName, i.name AS IndexName, MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N‘DETAILED‘) AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id WHERE ps.avg_fragmentation_in_percent >= 10 AND i.type IN (1, 2) --1: CLUSTERED, 2: NONCLUSTERED AND o.type = N‘U‘ --U: USER_TABLE AND ps.index_level = 0 --Index leaf-level GROUP BY s.name, o.name, i.name ORDER BY avg_fragmentation_in_percent DESC; OPEN cur_index; FETCH NEXT FROM cur_index INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent; WHILE(@@FETCH_STATUS=0) BEGIN IF (@avg_fragmentation_in_percent>30) BEGIN SELECT @SQL = N‘ALTER INDEX ‘ + @IndexName + N‘ ON ‘ + @SchemeName + N‘.‘ + @TableName + N‘ REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, NLINE = OFF, DATA_COMPRESSION = PAGE );‘ END ELSE --@avg_fragmentation_in_percent between 10 and 30 BEGIN SELECT @SQL = N‘ALTER INDEX ‘ + @IndexName + N‘ ON ‘ + @SchemeName + N‘.‘ + @TableName + N‘ REORGANIZE PARTITION=ALL;‘ END EXEC (@SQL) FETCH NEXT FROM cur_index INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent; END CLOSE cur_index; DEALLOCATE cur_index;
在Rebuild Index 时，使用的Index Option是：FILLFACTOR = 95, NLINE = OFF, DATA_COMPRESSION = PAGE
Appendix
在preceded Scripts 中，DMF：sys.dm_db_index_physical_stats 使用Scan mode的Detailed，需要耗费大量时间和系统资源来收集Index 的Fragmentation 信息，如果能够事先规划好需要进行Index Defragmentation的Table Set，避免不必要的Fragmentation scan，能够节省Index Defragmentation的时间。
原文：http://www.cnblogs.com/ljhdo/p/5158596.html