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

-- Rebuild an index, while keeping it available (Enterprise
Edition specific feature)
ALTER INDEX PK_CreditCard_CreditCardID
ON Sales.CreditCard REBUILD
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)
-- Rebuild an index with page-level data compression enabled
ALTER INDEX AK_CreditCard_CardNumber ON Sales.CreditCard REBUILD
WITH (DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON)
11. Let us perform the multiple variations of index reorganize methods on Person.
BusinessEntity table indexes:
--******************************
-- REORGANIZE a specific index on BusinessEntity table
ALTER INDEX PK_BusinessEntity_BusinessEntityID
ON Person.BusinessEntity REORGANIZE
ALTER INDEX AK_BusinessEntity_rowguid
ON Person.BusinessEntity REORGANIZE
-- Reorganize all indexes for a table and compact LOB data type
ALTER INDEX ALL
ON Person.BusinessEntity
REORGANIZE WITH (LOB_COMPACTION=ON)
This completes the important steps in implementing sustainable index maintenance practices
for a VLDB and 24/7 environment.
How it works...
In this recipe, we will use SQL Server DMVs and system catalogs in addition to INDEX
modification DML statements.
Referring to DMV sys.dm_db_index_physical_stats determines the level of scanning
performed to obtain statistical information based on a specified mode such as LIMITED ,
SAMPLED, or DETAILED . This DMV requires an Intent-Share (IS) table lock, regardless
of the mode that is executed. The LIMITED mode is the fastest mode and obtains the
information on the smallest number of pages. For a heap, the scanning will operate on
PFS and IAM pages. The data pages of the heap are not scanned. The DETAILED mode is
resource intensive, which will take a longer time than expected due to the nature of the scan
performing size or fragmentation level of a table or index that scans all pages and returns all
the statistics.