Index rebuild

Question

Index rebuild

California

SSCommitted

Points: 1888
More actions
August 11, 2008 at 7:35 pm

#126921

I have 4 indexes (1 clustered and 2 non-clustered) on a table which is 105 GB. I want to run the following code to rebuild all the indexes on this table:
ALTER INDEX ALL ON Product REBUILD
My question is during this operation will other users be able to access the product table? What happens when the rebuild is executed?
My SQL version is 2005-64 bit.
Appreciated any input.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 1

California (8/11/2008)
I have 4 indexes (1 clustered and 2 non-clustered) on a table which is 105 GB. I want to run the following code to rebuild all the indexes on this table:
ALTER INDEX ALL ON Product REBUILD
My question is during this operation will other users be able to access the product table? What happens when the rebuild is executed?
My SQL version is 2005-64 bit.
Appreciated any input.

No, the table will not be accessible during the rebuild. However, if you have the Enterprise Edition you can rebuild the indexes online. You can also potentially speed up the rebuild operation by sorting in tempdb.

ALTER INDEX ALL ON Product REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB = ON);

Note: if you have any LOB columns on the table then you cannot rebuild the clustered index online. If any of the non-clustered indexes include any LOB columns then you cannot rebuild those indexes online either.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

California (8/11/2008)
I have 4 indexes (1 clustered and 2 non-clustered) on a table which is 105 GB.

100% support Jeffrey.

By the way, I'm wondering of what kind your fourth index is... 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 3

Jeffrey Williams (8/11/2008)
Note: if you have any LOB columns on the table then you cannot rebuild the clustered index online. If any of the non-clustered indexes include any LOB columns then you cannot rebuild those indexes online either.

This is correct. I have a stored procedure that you can use that supports this logic.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

California SSCommitted Points: 1888 More actions · Answer 4

Thanks for the contribution.

So what happens when i execute the following code:

ALTER INDEX ALL ON Product REBUILD??? I dont see online rebuild option is specified here with the command. But is this command consider as a ONLINE rebuild?

If I can not build the indexes online - what's the best way to build them then?

Appreciated all your assistance.

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 5

California (8/12/2008)
Thanks for the contribution.
So what happens when i execute the following code:
ALTER INDEX ALL ON Product REBUILD??? I dont see online rebuild option is specified here with the command. But is this command consider as a ONLINE rebuild?
If I can not build the indexes online - what's the best way to build them then?
Appreciated all your assistance.

If you do not specify the WITH options, the rebuild will not be performed online or use tempdb for the sort. If you cannot rebuild the indexes online, then you have several options. You can use a procedure that performs either a REORGANIZE or REBUILD based upon fragmentation levels (see Ola's procedure for an example).

If you have the time during your maintenance window to rebuild the indexes, then rebuilding them offline is also an option. For example, if your system is not a 24/7 system - you can rebuild the indexes during the night when nobody is on the system.

Other options are available and really depend upon your system and what your maintenance window is.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

California SSCommitted Points: 1888 More actions · Answer 6

Thanks for the contribution.

Say if i have to rebuild the indexes offline - do i need to put the database in single user mode? My initial question was - what happens when i run the rebuild command without the ONLINE clauses?

Thanks again,

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 7

No, you do not need to put the databases into single user mode to rebuild indexes. If you can rebuild the indexes online, this leaves the table accessible while the index is being rebuilt.

For further information, lookup ALTER INDEX in Books Online.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs