Solved

Questions about rebuil indexs

Posted on 2006-07-09
16
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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
 
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:Scott Pletcher
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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