Questions about rebuil indexs

Hi,
I've read an article in BOL dbcc showconting saying that :

When an index is heavily fragmented, there are two choices for reducing fragmentation:

Drop and re-create a clustered index -- will only cluster indexes need to be reindexs?, how about noncluster indexes?

motioneyeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
>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.

If the table doesnot have a Clustured Index, the non clustured indexes will point to the Curresponding  ROWID in the page where the data is actually stored.

DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.

0
 
DireOrbAntCommented:
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
0
 
motioneyeAuthor Commented:
Hi,
Yes both iundexs can be fagmented, but will there any benefit if we defrag those noncluster indexes?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DireOrbAntCommented:
Yes. Non fragmented indexes will always perfom better than fragmented ones. Check the link from my previous post for more details.
0
 
Einstine98Commented:
The performance gain is normally higher when the index is clustered... but both need to be rebuilt...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
motioneyeAuthor Commented:
Hi anesshattingal,
Is that means only cluster index need to be defrag? and the nonclsuter index inside that particualr table also will get defragement?
0
 
imran_fastCommented:
Hi motioneye,
Did you look at DBCC DBREINDEX  in books online?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
pootle_flumpCommented:
Excellent white paper re SQL Server index fragmentation:

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

HTH
0
 
pootle_flumpCommented:
Not much more but some stuff from Tara's blog.
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx
0
 
motioneyeAuthor Commented:
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
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
cluster index fragmentation is the same as data fragmentation.  On range queries it makes a difference.  
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
motioneyeAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.