TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
SQL Server Best Practices Article
Technical Reviewers: Tom Davidson, Mark Souza, Prem Mehra, Sunil Agarwal, Mike Ruthruff
Project Editor: Suzanne Bonney
Download
|
Introduction
Creating and rebuilding indexes are some of the most common database management tasks. Depending on application requirements and data volume changes, the database administrators develop periodic index maintenance schedules. Some of the key factors to consider while you devise an index maintenance plan are performance, concurrency, and required resources. SQL Server 2005 provides you with several options for index create and rebuild operations that you can use to effectively meet the requirements of performance, concurrency, and resources.
SQL Server 2005 provides an option (ONLINE = ON or OFF) to help manage the performance and concurrency requirements while creating or rebuilding an index. Online index create or rebuild (ONLINE=ON) provides maximum concurrency, but uses more resources and takes longer to complete. During an online index create or rebuild, queries, inserts, updates, and deletes are allowed on the underlying table. Offline index create or rebuild (ONLINE=OFF, the default) uses less resources and takes less time, but restricts concurrency. An offline index create or rebuild restricts user access to the underlying table for the duration of the operation. For more information about online index operations, see SQL Server Books Online (http://go.microsoft.com/fwlink/?LinkId=73380).
To help control how SQL Server manages the temporary space during an index operation, SQL Server provides another option: SORT_IN_TEMPDB. SQL Server uses temporary storage for sorting and other intermediate tasks while creating or rebuilding an index. This temporary storage can be used from the user database, or it can be used from the TEMPDB database. When the SORT_IN_TEMP option of the CREATE INDEX or ALTER INDEX statement is set to OFF (the default), the temporary storage is used from the user database. When SORT_IN_TEMP is set to ON, the temporary storage is used from the TEMPDB database.
The same amount of temporary storage is required whether you are using the temporary space from the user database or from the TEMPDB database. The advantages of using the temporary space from the TEMPDB database (SORT_IN_TEMPDB=ON) are described in the tempdb and Index Creation topic in SQL Server 2005 Books Online (http://go.microsoft.com/fwlink/?LinkId=71051).
These two options (ONLINE and SORT_IN_TEMPDB) together influence the amount of space that is used in the user database and in the TEMPDB database. Other factors that influence the amount of temporary space needed are the following:
| • | Whether you are dealing with a clustered index or a non-clustered index. |
| • | Whether you are creating a new index or rebuilding an old index. |
Included in This Document
| • | Introduction |
| • | Overview |
| • | Test Objective |
| • | Test Methodology |
| • | Test Results and Observations |
| • | Recommendations |
| • | Conclusion |