SQL – Index Rebuild / Index Reorganize

Index Rebuild will drop the existing Index and it will recreates the index.

ALTER INDEX index_name ON databasename.tablename REBUILD

Index Reorganize physically reorganizes the leaf nodes of the index.

ALTER INDEX index_name ON databasename.tablename REORGANIZE

Index should be rebuild when index fragmentation is most of the time great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.
Index rebuilding process uses more CPU and it acquired locking in database resources. In latest SQL Server version and Enterprise version there has option available i.e. ONLINE mode, through which you can be turned on when Index is rebuilt.
ONLINE option will keep index available during the rebuilding.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: