REBUILD vs REORGANIZE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

REBUILD vs REORGANIZE

Author

Topic

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 12:39:50

Hi all

I understand the difference between REBUILD and REORGANIZE. Just wondering if you can do both in the same script or do you have to rebuild the index first and later reorganize?

Thanks,
don

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 12:44:25

rebuild drops the existsing index and then recreates it.
this means it allocates completly new data pages.

reorganize only reshzffles the existing pages and doesn't drop anything.

so if you rebuild an index there's no need to reorganize it.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 15:00:44

We use Reorganise (i.e. Defrag) on large tables, and Reindex on small ones.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 15:36:57

I don't think you need to even bother with reorganize with SQL Server 2005, especially if you have Enterprise Edition. ALTER INDEX is rather fast.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 16:09:09

quote:
Originally posted by tkizer

I don't think you need to even bother with reorganize with SQL Server 2005, especially if you have Enterprise Edition. ALTER INDEX is rather fast.

That is most likely due to the ONLINE ability that EE offers over the other versions...

Also, FWIW... ALTER INDEX is used for both REBUILD & REORG. It's the base operation used to defrag. Rebuild & Reorg are merely options for ALTER INDEX.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 16:21:05

Yes that was what I was referring to. Check out my blog for ALTER INDEX stored procedure. It takes advantage of the option if you have EE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 16:44:13

Here's the stored procedure: http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx

As you can see, I'm very familiar with ALTER INDEX. When I mentioned ALTER INDEX is rather fast, I meant it is much faster than DBCC DBREINDEX so you can typically get away with the REBUILD in 2005 whereas in 2000 we used INDEXDEFRAG instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 16:48:09

I was just making sure the point was clear to the OP.

I'm familiar with your script as well - I wrote the procedure you mentioned in your article. ;)

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 16:52:18

Thanks for all the replies. We have separate jobs for separate tables. We have over hundred jobs that cover all the tables. So we cannot use one proc that rebuilds all indexes in one go. So I cant use Tara's proc.
I am manually going to each job and changing the script to ALTER INDEX.

Also, I noticed when you CREATE INDEX you can specify file group but when you ALTER INDEX you cannot.
We have index degrag's on only large tables.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 16:54:09

quote:
Originally posted by shiloh

Thanks for all the replies. We have separate jobs for separate tables. We have over hundred jobs that cover all the tables. So we cannot use one proc that rebuilds all indexes in one go. So I cant use Tara's proc.
I am manually going to each job and changing the script to ALTER INDEX.

In the past, in a similar situation, I created my list of objects to defrag based on a lookup table that the procedure used instead of sysobjects.

You don't always have to use sys.objects to drive the operation...

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 16:55:38

quote:
Originally posted by shiloh
Also, I noticed when you CREATE INDEX you can specify file group but when you ALTER INDEX you cannot.

The ALTER INDEX operation will rebuild/reorg the index on the original filegroup in which it was placed.

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 16:58:13

Also, we cannot use the ONLINe=ON option as it appears to be causing too much overhead. We even have AUTO UPDATE STATS turned off for the same purpose. ours is an OLTP db and cannot afford that.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 17:01:00

quote:
Originally posted by shiloh

Also, we cannot use the ONLINe=ON option as it appears to be causing too much overhead. We even have AUTO UPDATE STATS turned off for the same purpose. ours is an OLTP db and cannot afford that.

Make sure that your tempdb is sized properly and on fast enough spindles. ONLINE operations will make heavy use of tempdb for the online ops. You may want to check your tempdb and see if that may be partially the cause for the overhead...

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 17:04:03

quote:
Originally posted by Haywood

quote:
Originally posted by shiloh
Also, I noticed when you CREATE INDEX you can specify file group but when you ALTER INDEX you cannot.

The ALTER INDEX operation will rebuild/reorg the index on the original filegroup in which it was placed.

Thats what I figured. So if we had to change the filegroup for some reason, we have to change the script back to CREATE INDEX.

so we have to have 2 versions of create index scripts? which likes like too much hassle..

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 17:08:49

quote:
Originally posted by shiloh
Thats what I figured. So if we had to change the filegroup for some reason, we have to change the script back to CREATE INDEX.

so we have to have 2 versions of create index scripts? which likes like too much hassle..

The ALTER INDEX operation will rebuild/reorg the index on whatever filegroup it finds it on. So if it exists on FG_A on Monday, and Tuesday you drop and recreate the index on FG_B, and on Wendesday you run the defrag, it will rebuild/reorg the index on FG_B.

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 17:20:19

quote:
Originally posted by Haywood

The ALTER INDEX operation will rebuild/reorg the index on whatever filegroup it finds it on. So if it exists on FG_A on Monday, and Tuesday you drop and recreate the index on FG_B, and on Wendesday you run the defrag, it will rebuild/reorg the index on FG_B.

That I understand.
Currently our scripts have DROP_EXISTING option. If we have to change the filegroup we do a find/replace in sysjobsteps. But once we change the syntax of the scripts from CREATE INTDEX... ON [IdxGroup1] to
ALTER INDEX ... REBUILD, and if we had to change a file group we have no choice than to manually go into the jobsteps, change the script to CREATE INDEX and use the new file group.

So, we might as well stick to current CREATE INDEX ON [Indexgroup] script. I dont think I am losing anything by not switching to the new ALTER INDEX? right?

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 17:28:06

quote:
Originally posted by shiloh
That I understand.
Currently our scripts have DROP_EXISTING option. If we have to change the filegroup we do a find/replace in sysjobsteps. But once we change the syntax of the scripts from CREATE INTDEX... ON [IdxGroup1] to
ALTER INDEX ... REBUILD, and if we had to change a file group we have no choice than to manually go into the jobsteps, change the script to CREATE INDEX and use the new file group.

Ahh, I see what you mean.

quote:

So, we might as well stick to current CREATE INDEX ON [Indexgroup] script. I dont think I am losing anything by not switching to the new ALTER INDEX? right?

Nothing to lose that I can see. On Enterprise Edition, CREATE INDEX and ALTER INDEX use the same underlying mechanisim for thier ONLINE operations.

shiloh
Starting Member

48 Posts

Posted - 2007-09-06 : 18:09:06

I had a conversation with an MS rep and she said CREATE INDEX with DROP EXISTING will internally drop dependencies and recreate them but ALTER INDEX will not. I did not find it anywhere in BOL regarding this though.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-09-06 : 22:32:30

quote:
Originally posted by shiloh

I had a conversation with an MS rep and she said CREATE INDEX with DROP EXISTING will internally drop dependencies and recreate them but ALTER INDEX will not. I did not find it anywhere in BOL regarding this though.

That makes sense, if the object_id or index_id changes on the CREATE w/DROP_EXISTING. Dependancies would have to be updated for compiled plans (or something else) to point the old to the new object_id and/or index_id.

That's 'bout all I can see as a reason why dependancies would need to be updated with a CREATE w/DROP_EXISTING....

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:45:56

"I don't think you need to even bother with reorganize with SQL Server 2005, especially if you have Enterprise Edition. ALTER INDEX is rather fast."

Thanks Tara

One of my reasons in the past for using Defrag, on large tables, was to avoid the index getting created at the "end" of the MDF, and thus potentially extending the file, only to them free up the space taken by the original.

Do you know if alter index avoids this? Maybe its a physical reorganise, rather than a re-create?

Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources