DBREINDEX/INDEXDEFRAG a few questions

I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...

"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."

I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-

"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?

Regards,

Clive"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

I just did a test of this, and the answer is no, the non-clustered indexes will not be defragged as well. This works out as a slight benefit to indexdefrag, as it saves you the time of rebuilding the non-clustered indexes at the same time as the clustered index.

If you have all non-clustered indexes, then there would be a slight benefit in space-savings by running dbcc dbreindex on each index separately. If there is a clustered index, then running dbreindex on the clustered index rebuilds all of the indexes. This would be a waste of any time spent on each index being rebuilt individually. Does this help?|||Yes, that helps. Thank you. In fact, I just put together a test myself and found the same result. Not unsurprisingly, the DBREINDEX produced 100% scan density on the clustered index and the same or close to it on the non-clustered indexes. However, I was surprised that INDEXDEFRAG on the clustered index actually lowered scan-density by a few percent!

Regards,

Clive|||What was the scan density when you started?

The main problem with indexdefrag is that it is only moving pages from and to page locations that are already allocated to the index being defragmented. It may coalesce some unused space among these allocations, but it does not touch anything that is already allocated to another index page or data page of the table. Since it has to work around all of these prior allocations, you almost never get a nice 100% result from indexdefrag, unless all you had to start with was the clustered index.

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it wly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a wly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it wly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML