Guidelines for enabling indexes and online index operations
As a DBA you should now how
locking works with tables and how best you can make use of indexing enhancements
within SQL Server 2005 version.
Until SQL 2000 there was a
limited availability of documentation on the guidelines even in the Books online
and most of the times we have to depend upon the web resources or forums. There
are few gotchas that every new DBA should be aware of about enabling indexes and
online index operations, few of them are extracted from SQL 2005 Books online
and reliable resources:
- Clustered indexes must be created, rebuilt, or
dropped offline when the underlying table contains large object (LOB) data
types: image, ntext, text, varchar(max),
nvarchar(max), varbinary(max), and xml.
- Nonunique nonclustered indexes can be created
online when the table contains LOB data types but none of these columns are used
in the index definition as either key or nonkey (included) columns. Nonclustered
indexes defined with LOB data type columns must be created or rebuilt
offline.
- Indexes on local temp tables cannot be created,
rebuilt, or dropped online. This restriction does not apply to indexes on global
temp tables.
- The online option setting (ON or OFF) specified
when you create or drop a clustered index is applied to any nonclustered indexes
that must be rebuilt. For example, if the clustered index is built online by
using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered
indexes are re-created online also.
Be aware that the online index operation can cause
a deadlock during its process, when the same time if there are any insert and/or
update operation activities. Thus causes a deadlock victim from the process it
is generated. To avoid this you could take care of concurrent online index DDL
operations with non-clustered indexes or reorganizing those indexes too. Also
the online index operation is unique and you cannot perform a create index while
rebuilding an existing index online on the same table.
Further you must know about disk space requirements
in this indexing operation, both for online and offline index operations. An
exception is additional disk space required by the temporary mapping index. This
temporary index is used in online index operations that create, rebuild, or drop
a clustered index. Dropping a clustered index online requires as much space
as creating a clustered index online.
When a update activity that is concurrent is
persistent then avoid using online index operation at the same time, as it will
not create deadlock operation and transction log & tempdb usage will be
quite heavy. Few times this will lead to disk free space issues causing a total
downtime to the SQL Server services. So be wise to use online index operation as
this could cause a decrease in performance and greater
resource usage, especially CPU time, during the index operation. Online index
operations are fully logged.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
