Index Build strategy in SQL Server - Introduction (I)

  • Article

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