SQL Server 索引维护 - Alter Index

1. Alter index可以用来执行如下任务:

(1) Rebuilding an index

(2) Disabling an index
disable之后必须通过rebuild启用

(3) Changing index options
ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY, and STATISTICS_NORECOMPUTE

(4) Reorganizing an index
用于碎片整理


2. 碎片整理

(1)碎片的类型

a.Internal Fragmentation
索引页没有被填满

b.External Fragmentation
索引页的逻辑顺序和物理顺序不一致,或者一个索引的extents不连续

(2) 检测碎片

SELECT * FROM sys.dm_db_index_physical_stats (Database_ID, Object_ID , Index_id, Partition_number, Mode );

Mode的可选值: DEFAULT, NULL, LIMITED, SAMPLED, and DETAILED

缺省是NULL,相当于limited.

LIMITED: 对于heap,所描所有的页; 对于index,只扫描nodes页
SAMPLED: 只扫描一定百分比的索引页
DETAILED: 扫描所有页面

(3) 碎片扫描报告


(4) 去除碎片

ALTER INDEX { index_name | ALL }
ON
REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]

ALTER INDEX PK_TransactionHistory_TransactionID
ON Production.TransactionHistory REBUILD WITH (ONLINE = ON);;

3. Rebuiling an index online

(1). A shared lock is taken on the index, which prevents any data modification queries, and an Intent-Shared lock is taken on the table.

(2). The index is created with the same structures as the original and marked as write-only.

(3). The Shared lock is released on the index, leaving only the Intent-Shared lock on the table.

(4). A versioned scan is started on the original index, which means modifications made during the scan will be ignored.
The scanned data is copied to the target.

(5). All subsequent modifications will write to both the source and the target. Reads will use only the source.

(6). The scan of the source and copy to the target continues while normal operations are performed. SQL Server uses a proprietary method for reconciling obvious problems such as a record being deleted before the scan has inserted it into the new index.

(7). The scan completes.

(8). A Schema-Modification lock, the strictest of all types of locks, is taken to make the source completely unavailable.

(9). The source is dropped, metadata is updated, and the target is made to be read-write.

(10). The Schema-Modification lock is released.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/100682/viewspace-1021796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/100682/viewspace-1021796/