Microsoft® SQL Server® 2012 Bible

596

Part V: Enterprise Data Management

The sys.dm_db_index_physical_stats function requires an Intent-Shared (IS) table lock regardless of the

mode it runs in. The DETAILED mode is the most rigorous of the scan levels that can be used with this function. There

is another mode called LIMITED that can give a rough idea of the fragmentation in less time with less potential

impact to the database.

ALTER INDEX REORGANIZE defragments the leaf level index pages of both clustered and

nonclustered indexes. It reorders the leaf level index pages and compacts the index pages

(based on the fill factor value in the sys.indexes catalog view) for faster index

scanning performance:

ALTER INDEX IndexName ON TableName REORGANIZE;

Performing the ALTER INDEX REORGANIZE operation is similar to rebuilding an

index (ALTER INDEX REBUILD, which is covered in the section, “Index density”),

with the distinct advantage that defragmenting an index is performed in a series

of small transactions that do not block users from performing inserts, updates, and

deletes.

ALTER INDEX REORGANIZE and ALTER INDEX REBUILD commands are equivalent to

DBCC INDEXDEFRAG and DBCC DBREINDEX respectively. The sys.dm_db_index_physical_stats

dynamic management function replaces DBCC SHOWCONTIG. It is recommended to stop using

DBCC INDEXDEFRAG, DBCC DBREINDEX, and DBCC SHOWCONTIG because they will be removed in a future

version of Microsoft SQL Server.

The following commands defrag both indexes:

USE tempdb;

ALTER INDEX PK_Frag ON Frag REORGANIZE;

ALTER INDEX ix_col ON Frag REORGANIZE;

A sys.dm_db_index_physical_stats dynamic management function examines the

index structure after defragmenting the index. Both the logical-fragmentation and page-

density problems created by the insertion of one-half million rows are resolved:

USE tempdb;

GO

SELECT * FROM sys.dm_db_index_physical_stats ( db_id('tempdb'),

object_id('Frag'), NULL, NULL, 'DETAILED');

GO

c22.indd 596c22.indd 596 7/31/2012 9:24:30 AM7/31/2012 9:24:30 AM

http://www.it-ebooks.info