First published on MSDN on Nov 08, 2006
Index Build strategy in SQL Server may vary depending on users needs. Each of these Index Build strategies may have different memory and disc space requirement. These different strategies will be discussed in the next several posts.
For the beginning let’s see what kind of Index Build types exist in SQL Server 2005:
-
Online Index Build
vs.
Offline Index Build:
In SQL Server 2005, you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. When you perform DDL operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.
Example:
Create index idx_t on t(c1, c2)
WITH (ONLINE = ON)
-
Serial Index Build
vs.
Parallel Index Build:
On multiprocessor computers index statements may use more processors to perform the scan, sort, and build operations associated with the index statement just like other queries do. The number of processors employed to run a single index statement is determined by the configuration option max degree of parallelism (set by sp_configure) (default value of 0 - uses all available processors), by MAXDOP index option (set in statements – see example below), by the current workload, and, in non-partitioned case, by data distribution of the first key column. The max degree of parallelism option limits the number of processors to use in parallel plan execution – in other words: it is setting the ceiling, meaning no more than this number but can be anything below it. If Database Engine detects that the system is busy, the degree of parallelism of the index operation is automatically reduced before statement execution starts.
Example:
Create index idx_t on t(c1, c2)
WITH (MAXDOP = 2)
-- limit # of processor to use for index build to 2
-
Building Index storing the intermediate sort result in user’s database
vs.
storing in
tempdb database (SORT_IN_TEMPDB):
When you create or rebuild Index you can choose which database to use to store the intermediate
sort results, generated during index creation. It can be either user’s database (database where index
is being created) or tempdab database.
SORT_IN_TEMPDAB index option is used to set the desirable behavior. When set to ON, the
sort results are stored in
tempdb
. When OFF, the sort results are stored in the filegroup or partition
scheme in which the resulting index is stored.
Example:
Create clustered Index idx_t on t(c1)
WITH (SORT_IN_TEMPDB = ON)
Read in next post: Building partitioned vs.
non-partitioned Indexes.
Posted by: Lyudmila Fokina
