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:

[cc lang=”sql”]
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
)
[/cc]

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).