Database Reference
In-Depth Information
Parallel Index Creation
SQL Server supports parallel plans for a
CREATE INDEX
statement, as supported in other SQL queries. On a
multiprocessor machine, index creation won't be restricted to a single processor but will benefit from the multiple
processors. You can control the number of processors to be used in a
CREATE INDEX
statement with the
max degree
of parallelism
configuration parameter of SQL Server. The default value for this parameter is
0
, as you can see by
executing the
sp_configure
stored procedure.
EXEC sp_configure
'max degree of parallelism' ;
The default value of
0
means that SQL Server can use all the available CPUs in the system for the parallel
execution of a T-SQL statement. On a system with four processors, the maximum degree of parallelism can be set to
2
by executing
spconfigure
.
EXEC sp_configure
'max degree of parallelism',
2 ;
RECONFIGURE WITH OVERRIDE ;
This allows SQL Server to use up to two CPUs for the parallel execution of a T-SQL statement. This configuration
setting takes effect immediately, without a server restart.
The query hint
MAXDOP
can be used for the
CREATE INDEX
statement. Also, be aware that the parallel
CREATE
INDEX
feature is available only in SQL Server Enterprise editions.
Online Index Creation
The default creation of an index is done as an offline operation. This means exclusive locks are placed on the table,
restricting user access while the index is created. It is possible to create the indexes as an online operation. This
allows users to continue to access the data while the index is being created. This comes at the cost of increasing the
amount of time and resources it takes to create the index. Introduced in SQL Server 2012, indexes with
varchar(MAX)
,
nvarchar(MAX)
, and
nbinary(MAX)
can actually be rebuilt online. Online index operations are available only in SQL
Server Enterprise editions.
Considering the Database Engine Tuning Advisor
A simple approach to indexing is to use the Database Engine Tuning Advisor tool provided by SQL Server. This tool
is a usage-based tool that looks at a particular workload and works with the query optimizer to determine the costs
associated with various index combinations. Based on the tool's analysis, you can add or drop indexes as appropriate.
■
Note
I will cover the database engine tuning advisor tool in more depth in Chapter 5.
Summary
In this chapter, you learned that there are a number of additional functions in and around indexes that expand on the
behavior defined the preceding chapter.
In the next chapter, you will learn more about the Database Engine Tuning Advisor, the SQL Server-provided tool
that can help you determine the correct indexes in a database for a given SQL workload.
