Solved

Questions about rebuil indexs

Posted on 2006-07-09
16
332 Views
Last Modified: 2008-02-01
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?

0
Comment
Question by:motioneye
  • 4
  • 4
  • 2
  • +5
16 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17070476
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
 

Author Comment

by:motioneye
ID: 17070495
Hi,
Yes both iundexs can be fagmented, but will there any benefit if we defrag those noncluster indexes?
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17070561
Yes. Non fragmented indexes will always perfom better than fragmented ones. Check the link from my previous post for more details.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17070614
The performance gain is normally higher when the index is clustered... but both need to be rebuilt...
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17070718
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
 

Author Comment

by:motioneye
ID: 17070979
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 17071029
Hi motioneye,
Did you look at DBCC DBREINDEX  in books online?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17071039
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:pootle_flump
ID: 17071088
Excellent white paper re SQL Server index fragmentation:

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

HTH
0
 
LVL 11

Expert Comment

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

Author Comment

by:motioneye
ID: 17071103
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 17071380
cluster index fragmentation is the same as data fragmentation.  On range queries it makes a difference.  
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17071393
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17075237
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
 

Author Comment

by:motioneye
ID: 17078485
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17078568
>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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now