REINDEX vs DBREINDEX - 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 2000 Forums

SQL Server Administration (2000)

REINDEX vs DBREINDEX

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-19 : 00:43:17

What's the diff?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-19 : 02:54:55

what is reindex? what is the command you are talking about?

-ec

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-19 : 08:49:46

I got DBCC REINDEX from a Stored Proc someone in this forum wrote. BOL has one reference to it... just lookup DBCC REINDEX

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-19 : 09:07:57

on very large tables I perfer to drop and recreate. in off hours of course.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Kristen
Test

22859 Posts

Posted - 2005-07-19 : 10:01:22

I would guess that DBCC REINDEX is deprecated (Google scores 1,200 as against 4,750 for DBCC DBREINDEX)

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-19 : 10:27:40

Sam you asked for "what do other people do" in a post somewhere else. I put it to one side to reply and forgot ...

My approach is based on something posted here:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444&whichpage=2

I cannot remember what the original does, probably much the same as we do now, but our modification of that script does:

Build table of fragmentation of each table in database using:

EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

(This table is a "permanent" table, we review which tables keep getting fragmented periodically, how long the defrags take, etc.)

The SProc has an "Action" parameter:

If "Action" is 'LIST' then display the fragementation data, and exit

If "Action" is 'SQL' then display the SQL necessary to perform fragmentation, but

If "Action" is 'EXEC' then physically perform the fragmentation.

Cursor round the Indexes in the Fragmentation list

The SProc has a "Task" parameter:

If "Task" is specifically set to "REINDEX", OR the numer of pages in the file is less than 10,000, then use the REINDEX method. Otherwise use the DEFRAG method.

(Update fragmentation table to show what method was used, and the Start/End [elapsed] time)

Use
DBCC DBREINDEX
or
DBCC INDEXDEFRAG
using the WITH NO_INFOMSGS option

Perform an
UPDATE STATISTICS TableName WITH FULLSCAN
on each table in the database

Perform a
DBCC UPDATEUSAGE(DatabaseName)
and then a
DBCC CHECKDB(DatabaseName)

I think that's it - in embryo

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-19 : 12:05:20

Sounds good. Too long to post the proc? Confidential? Have to shoot me?

Kristen
Test

22859 Posts

Posted - 2005-07-19 : 12:43:24

All our stuff is a bit reliant on internal gear, and I haven't the time to clean it up for posting just now, (and posting it in raw state would just be a bit pointless for the next guy that comes along) but I'm happy to send it to you direct, as I know you'll be able to pick the meat from the bones.

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-07-21 : 11:16:27

quote:
Originally posted by Thrasymachus

on very large tables I perfer to drop and recreate. in off hours of course.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Doing a manual drop and create can lead to problems if any constraints are violated between the drop and create (i.e. can cause the create to fail). Its best in this case to use create with drop_existing. Is that what you do?

Also, if you do it for a clustered index, dropping it causes all the non-clustered indexes to be rebuilt (to have new physical record locators pointing into the heap) and then creating it again causes them to be rebuilt again (to have new logical record locators - cluster keys - pointing into the clustered index).

Why don't you use DBCC DBREINDEX?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-07-21 : 11:17:15

quote:
Originally posted by Kristen

I would guess that DBCC REINDEX is deprecated (Google scores 1,200 as against 4,750 for DBCC DBREINDEX)

Kristen

Yes, DBCC REINDEX was deprecated in SQL Server 2000. DBCC DBREINDEX is deprecated in SQL Server 2005, in favor of the new ALTER INDEX syntax.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources