Factor which Influence Index Rebuilt Strategy

Leave a comment (0) Go to comments

Choosing options for an index create or rebuild involves deciding

  • whether to do it online or offline,
  • whether to use the temporary space in TEMPDB or in the user database

Here is the summary of options for index rebuilding and their effects.

ONLINE

Concurrency

Versioning

Time

SORT_IN_TEMPDB

YES

high

Yes

Slow

YES

YES

high

Yes

Slow

NO

NO

Low

No

Fast

YES

NO

Low


No

Fast

NO

Index create/rebuild recommendations

  • Make sure that the TEMPDB is on a disk subsystem that provides sufficient I/O throughput, and that the TEMPDB is big enough to accommodate the temporary space that is required for the index create or rebuild operation. By default, the TEMPDB is created in the Data directory under the SQL Server installation folder (for example, C:\SQL2005\MSSQL.1\MSSQL\Data). In this configuration, there might not be enough space for the TEMPDB, and the storage might not have adequate I/O throughput. Therefore, it is a best practice to move the TEMPDB to a storage area with sufficient space and performance after you install SQL Server. Also, note that the TEMPDB database is a common resource for the entire instance of SQL Server. You should consider the activities in all the user databases that might be using TEMPDB while you plan for the TEMPDB.
  • To achieve the least time to create or rebuild an index, use the offline option (ONLINE=OFF). However, this prevents all user access to the underlying table for the duration of the index create or rebuild.
  • To achieve the least effect on other users accessing the table, use the online option (ONLINE=ON). However, an online operation takes more time and uses more TEMPDB space as compared to an offline operation.
  • To use the least amount of space in TEMPDB while you rebuild a clustered index, use the offline (ONLINE=OFF) option. However, this affects concurrency because access to the table is prevented for the duration of the index rebuild.
  • To use the least amount of space in TEMPDB while you rebuild a non-clustered index, use the online (ONLINE=ON) option. The online rebuild also provides the best concurrency, but takes longer to complete.
  • If there are transactions on the table that are concurrent with the online index create or rebuild, you need to plan for additional space in TEMPDB for the version store.

I have also posted an another post, which I discussed about "How much database space is required during Index rebuild?"

EOF - Factor which Influence Index Rebuilt Strategy, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.