Using ALTER INDEX

Alter index is a powerful new command that replaces both the DBCC INDEXDEFRAG and the DBCC DBREINDEX, and at the same time enhances them both.

The following example encapsulates the most common settings used when rebuilding an index. This example is the same as using the DBCC DBREINDEX on an entire table. The settings below are simply detailed so you can uncomment or set them yourself. They are all set to the default:

ALTER INDEX
ALL                                 -- All indexes on Table
ON Sales.SalesOrderDetail
REBUILD                             -- REBUILD OR REORGANIZE
WITH
(
    --FILLFACTOR = 80,              -- Leaf page pad %
    PAD_INDEX = OFF,                -- Pads Intermediate pages
    SORT_IN_TEMPDB = OFF,
    STATISTICS_NORECOMPUTE = OFF,   -- Auto update stats on
    ONLINE = OFF,                   -- ON for concurrency
    ALLOW_ROW_LOCKS = ON,           -- Allow row locking
    ALLOW_PAGE_LOCKS = ON,          -- Allow page locking
    MAXDOP = 0,
    DATA_COMPRESSION = NONE         -- NONE | ROW | PAGE
)

Let’s look at what all this means.

Setting Values Description
[Indexes] ALL | (Specify an index name) All – Applies the settings to all the indexes on the table. Or alternatively specify an index name to isolate one index.
[Alter Type] REBUILD | REORGANIZE REBUILD completely rebuilds the indexes, while REORGANIZE simply defgraments them. This is akin to DBCC DBREINDEX VS DBCC INDEXDEFRAG. Rebuild is more thorough.
FILLFACTOR 0 – 100 Specifies the percentage of free space to be left for pages at the leaf level. Higher the number helps with quicker seeks, but you will need to rebuild more often. Lower the number helps with index page splits. Remove this parameter to use the default fillfactor the index was built with.
PAD_INDEX ON | OFF Indicates whether to leave free space on the intermediate pages of the index. Similar to Fillfactor however not at leaf-level
SORT_IN_TEMPDB ON | OFF If on, the index will be sorted in tempdb rather than the same DB as the MDF. This only applies if the index is so large it will not fit in the current available memory. It may speed up the rebuild if the tempdb disk is fast and on a non busy disk
STATISTICS_NORECOMPUTE ON | OFF Keep to OFF unless you really know what you are doing. This disables the auto-update statistics
ONLINE ON | OFF If set to on, select statements against the indexes are still allowed and will not block. This however requires more disk space if the index(es) are large because it makes a copy of the index, rebuilds, then swaps out the old index for the new index.
ALLOW_ROW_LOCKS ON | OFF Leave to ON unless you know what you are doing. OFF disallows row level locking on the index.
ALLOW_PAGE_LOCKS ON | OFF Leave to ON unless you know what you are doing. OFF disallows page-level locking.
MAXDOP 0 – (max num processors) Indicates to SQL Server how many threads it can use for the operation. 0 defaults to the default, which is equal to the number of processors on the system. Sometimes you will want to limit this to 1 or 2 so it does not strain your disk subsystem.
DATA_COMPRESSION NONE | ROW | PAGE Enables compression for the index(es).

Featured Articles

 Site Author