Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 342

We have also used the DMV sys.dm_db_index_operational_stats that performs for a
length of time that users or an application process must wait to read or write to a table or a
partition. Any delay or lag on these processes is encountered due to significant I/O activity or
hot spots. The important information for latching and locking contention refer to the values
for page_latch_wait_count and page_latch_wait_in_ms columns. Also, the latch
contention from row_lock_count and page_lock_count columns provides further information.
To obtain additional information on how a row or page locks are obtained, refer to the row_
lock_wait_in_ms and page_lock_wait_in_ms columns.
Coming to the index defragmentation and maintenance methods, we used ALTER INDEX
statements that works on the rebuild of indexes on a specified table (clustered index), and
the operation will not cause a rebuild of non-clustered indexes for that table. ALTER INDEX
…REBUILD statement execution will enable any disabled index and it does not rebuild any
associated nonclustered indexes unless the keyword ALL is specified. In case the specified
table is a heap (no index) then the rebuild operation has no effect on the table.
The keyword SORT_IN_TEMPDB option, as it refers the operation, is performed in tempdb
database when it is specified. In this case, if tempdb is on a different set of disks than the
user database then it will reduce the time needed to create an index as the operation is highly
I/O intensive.
The keyword ONLINE has an advantage of not applying long-term table locks for the duration
of the index operation. At the main phase of the index operation only Intent Share lock is held
on the source table, and at the end of the operation the Shared lock is held on the source
object and nonclustered indexes.
The keyword LOB_COMPATION will specify all pages that contain large object (LOB) data are
compacted, by giving an advantage of compacting data can improve the usage of disk space
and default setting is ON .
The index reorganize process, such as using ALTER INDEX..REORGANIZE, will specify the
index leaf level to be reorganized, which is always performed ONLINE . This operation will
reduce the fragmentation on the leaf level of an index (both clustered and nonclustered)
causing the physical order of database pages to match the logical order. During this operation,
the indexes are compacted based on the fill-factor, resulting in free space and smaller
indexes.
The rebuild operation can be minimally logged
if the database recovery model is set to either
bulk-logged or simple.