Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Questions about rebuil indexs

Posted on 2006-07-09
16
Medium Priority
?
349 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 70

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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