Questions about rebuil indexs

Both index type can become fragmented. Droping and recreating can cause locking on your database, but it then rebuilds a clean index.
SQL 2005 will now do this with a copy of the original index still in place.

You can read more on index fragmentation here:
http://www.sql-server-performance.com/sj_detect_fragmentation.asp

Hi,
Yes both iundexs can be fagmented, but will there any benefit if we defrag those noncluster indexes?

Yes. Non fragmented indexes will always perfom better than fragmented ones. Check the link from my previous post for more details.

The performance gain is normally higher when the index is clustered... but both need to be rebuilt...

motioneye,
>  how about noncluster indexes?
The non clustured indexes will usually points to a clustured index ( if any). So droping and recreating a CI will also rebuilds the Non CIs

Hi anesshattingal,
Is that means only cluster index need to be defrag? and the nonclsuter index inside that particualr table also will get defragement?

Hi motioneye,
Did you look at DBCC DBREINDEX  in books online?

motioneye,
> Is that means only cluster index need to be defrag?

No, I meant to say, if you are dropping and recreating the CIs, the NCIs will be automatically recreated

Excellent white paper re SQL Server index fragmentation:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

HTH

Not much more but some stuff from Tara's blog.
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx

Hi,
Of coz when we run the dbcc dbreindexes, it does drop and create the indexes, as for my current practice, I will rebuilt the indexes regardless they are CI or NCI's,
aneeshattingal,
U r saying that drop and recreating CI will automatically recreated NCI, so it sounds to me that NCI will dont need to be reindexes...

correct me if I'm wrong

cluster index fragmentation is the same as data fragmentation.  On range queries it makes a difference.  

motioneye,
> U r saying that drop and recreating CI will automatically recreated NCI

This is true,  because the nonclustered index contains the clustering key, if you drop a clustered index on a table that has nonclustered indexes, SQL Server will also drop the nonclustered indexes and recreate them.

www.sql-server-performance.com/ rd_index_fragmentation.asp

If you want to rebuild all indexes, use DBCC DBREINDEX, as noted above.

If you want to rebuild *only* the clustered index, use CREATE INDEX ... WITH DROP_EXISTING; for example:

CREATE UNIQUE CLUSTERED INDEX index_name
    ON tableName ( col1, col2 )
WITH DROP_EXISTING


>> The non clustured indexes will usually points to a clustured index ( if any). <<

If a clus index exists, a non-clus index will *ALWAYS* point to the clus index, never directly to the table itself.

Ok,
Now I'm understand,,, so if let say my tables has CI, I will only drop and rebuilt this indexes  or run dbcc reiendex so that NCI's also will be recreated.
And if let says my table has no CI mean that I have to rebuild the indexes on every NCI created for that particular table.