SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

December 22, 2007 by pinaldave

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REORGANIZE GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 31 Comments

31 Responses

on December 23, 2007 at 3:07 am JIJO(jUMP iN jUMP oUT)

Hey Pinal Dave,

U are awesome man. This site is very use-full to me .. God bless u man
on December 28, 2007 at 4:36 am Derek

That preety neat.
thanks
on December 28, 2007 at 4:40 am Derek

Hey dave,
i wanted to find out that in my databases( tables, all) which indexed is being often used, and by which queries.
some hints to that,
i know i can run execution plan for queries and find what indexed are being used, but since then i have to run for all the queries.
on January 7, 2008 at 6:27 pm FLOUVAT

Hi,
thank’s for your very helpfull site.
just one remark : it seems to me that you have reversed the 2 sql ‘alter index’ statements in relation to paragraphs.

best regards,
JM
on January 17, 2008 at 7:18 pm Dugi

this very OK!

thnx!
Dugi
on February 18, 2008 at 8:44 pm SQL SERVER - Index Reorganize or Index Rebuild Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script [...]
on February 19, 2008 at 6:11 pm Rana

You are too charming.

God bless you.
on February 19, 2008 at 6:48 pm Rana

In the live server, Is it recommendable to do it.

Should we make two databases and do it on one and then take a backup and restore to another.

What you suggest in the ideal situation.

Please advise.

Rana
on February 21, 2008 at 9:54 pm Roy

I think the T-SQL statement was switched between REORGANIZE and REBUILD in (SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script). Can you update them to avoid confusion.
Thanks.
on February 21, 2008 at 10:04 pm pinaldave

Thank you Roy,
I have updated original post and done switching. Original post is now correct.

Regards,
Pinal
on February 26, 2008 at 5:25 pm VENKAT

Thank you Pinal, I understood difference between Rebuild & Reorgnanize
on February 28, 2008 at 11:58 pm Sam

Hi Dave,
Very helpful info!
What’s the easiest way to find out index fragmentation percentage?

Thanks
Sam
on February 29, 2008 at 4:21 pm Oana

Hi,

it was very easy to understand the difference.

Thanks so much,
Oana.
on March 30, 2008 at 4:00 pm SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance Journey to SQL Authority with Pinal Dave

[...] can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do [...]
on May 15, 2008 at 10:41 pm Ajay Patel

USE asg
GO
ALTER INDEX ALL ON dbo.city_area REBUILD
GO

I executed above rebuild statement, but avg_fragmentation_in_percent did not change.

please help.
on June 3, 2008 at 5:56 pm caspi

Hi and thank you for your so useful and well organized site!

I have a question regarding indexes rebuild/reorganization: if I modify one of the table columns (just increasing its length), should I rebuild/reorganize index based on this column?

Thanks!
on August 7, 2008 at 8:18 am N'Jay

Thx..
on August 22, 2008 at 3:07 am Dhaka

Job Name Reorganize Index task MaintenancePlan for User DB
Step Name Subplan_1
Duration 00:00:15
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: MICROSOFT\SQLAdmin. …n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:25:38 PM Progress: 2008-08-21 16:25:47.04 Source: {F2EF9F9E-923D-4BE6-B6A1-376E9171553A} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp”.: 100% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 5% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_DEF__07F6335A] ON [dbo]“.: 11% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 16% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_ENTRY__0BC6C43E] ON [db”.: 22% complete End Progress Progress… The package execution fa… The step failed.
on August 22, 2008 at 3:08 am Dhaka

Any one know whay above jobs failed
on November 17, 2008 at 6:18 pm Ajith

HI dhaka,

please post the error message that was displayed in the particular step which failed. only that will give a clear picture.
on January 23, 2009 at 11:02 pm Luis Simoes

Rebuilding Partitioned Index with ONLINE = ON is this possible? How?

Regards
on January 29, 2009 at 10:12 am Dennies

Hi Dave,

Thank you very much! this site is a very big help keep it up (^^,)
on February 13, 2009 at 2:47 am Jose

HI, i have a index with 93% fragmented, then rebuild it, and now this same index has 73%, why ?
What can i do to have percentage more low?
Drop and Create?
When rebuild were made with ONLINE=ON, affect that?

Thanks for your help!!!
on May 3, 2009 at 9:14 pm Joshua

how will i know the percentage of my fragmented database?
on May 3, 2009 at 9:20 pm Joshua

when i was rebuilding my database i had this error number -1073548784..
Executing the query “ALTER INDEX [KBA_ODPV_PR] ON [dbo].[@BA_ODPV] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
” failed with the following error: “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
The statement has been terminated.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
on May 4, 2009 at 10:46 am Imran Mohammed

@Joshua

I strongly suggest, you take a look at BOL about Indexes.

A clustered index (leaf level page) contains real data of that object.

In simple words, when you Rebuild Clustered Index it will put a lock on the table, because Index has to be rebuild meaning data was stored in one fashion earlier but by issuing Rebuild Index (for Clustered Index) command, you are asking SQL Server to again rebuild whole index i.e. store data in another fashion that is easily accessible . When data is being organized in another fashion, no user can access that object (Tables) until rebuild index process completes.

Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object.

Error message you posted is self explanatory, explanation is, Rebuild Index command is trying to put a lock on that table, but that table is being used, that is why sql server is not able to put a lock on that table. Until SQL Server dont put a lock on that table, it cannot actually rebuild clustered Index.

For Non-Clustered Index, there is no such restriction. You can rebuild Non-Clustered Indexes when users are connected to the database. Reason behind this is, Clustered Index ( Leaf level Pages) do not real data, but contains pointer to Clustered Index.

Solution:
1. Either Reorganize index instead of Rebuilding Index.
2. If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.
3. Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.

~ IM.
on May 5, 2009 at 11:03 am Joshua

@ Imran Mohammed

sir thanks for your reply actually here’s what i did,
i had the maintenance last May 2 @7PM up to May 3 @midnight, the error occured at around 5PM on May 3, i had 5 Maintenance,

Check Database Integrity Done 8sec
Shrink Database Error 27sec
Reorganize Index Done 15:14:27
Rebuild Index Error 0sec
Update Statistics Done 6:23:46

“Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object. ”
“Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.”
- NO USERS CONNECTED DURING THE MAINTENANCE. all users are locked and cannot access the database.

“If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.”
- I ALSO SET ONLINE = ON, but with different error message, and still was not able to rebuild the index.

here are the actual errors for SHRINK DATABASE AND REBUILD INDEX:

REBUILD INDEX ERROR
ERROR NUMBER: -1073548784
ERROR MESSAGE:
Executing the query “ALTER INDEX [KBA_CCLSDPA_PR] ON [dbo].[@BA_CCLSDPA] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON ) ” failed with the following error: “Online index operation cannot be performed for index ‘KBA_CCLSDPA_PR’ because the index contains column ‘U_DprTypID’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

SHRINK DATABASE ERROR
ERROR NUMBER: -1073548784
ERROR MESSAGE:
Executing the query “DBCC SHRINKDATABASE(N’ALC’, 10, TRUNCATEONLY) ” failed with the following error: “A severe error occurred on the current command. The results, if any, should be discarded.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

i appreciate the solution sir, but i already did it but still it didn’t work.

thank you so much again…
on May 20, 2009 at 11:49 pm Sandy

@Joshua

As per BOL,while rebuilding index ,option

ONLINE=ON will fai if there is XML index or index consist of Large object data type columns inclyding image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml.

Check your indexes to figure out if you really need ONLINE=ON option
on July 1, 2009 at 8:38 am Joshua

@Imran Mohammed
@Sandy

Thanks for your responses, i did everything you said but unfortunately, it didn’t, do u think there is an issue with MS SQL Server 2005? do i need to upgrade to SP3 since i am only using SP2?

thanks…
on July 2, 2009 at 12:35 am jack

I clearly read the dis-advantages of REBUILD – and MS says Reorganize if between 5-40% and rebuild if higher.

1. What are the disadvantage(s) of REORGANIZE
2. Why wouldn’t you REORGANIZE if the fragmentation was over 50% (I assume the answer is poor use of resources, but I don’t see that written anywhere)
3. Wouldn’t a REORGANIZE of a Clustered Index also take a table lock?

Other index question – we have a (PK) Clustered Index on table that is 24×7 – we start from scratch (empty) every quarter – and by week 4 the fragmentation is over 80% (yes, i know its a poorly designed PK and Clustered Key – its a vendor product). The kicker is they don’t want me take the app offline for a short maint window – is there any way to define the index with 4 leaf levels – since thats how many it will grow to?
on July 2, 2009 at 6:31 pm Herb

Hello,

My question is as follows: I have created a script to list all indexes with a fragmentation percent greater than 10. Next, this script will rebuild or reorganize these indexes based upon the fragmentation percent. However, the same indexes keep showing up in this report. So, I tried to manually rebuild with the same results, the framentation percent remain the same. These indexes are not clustered indexes. Would you be able to explain why this may be happening?

Herb