Clustered index

Question

Clustered index

Kangana Beri

SSCrazy

Points: 2184
More actions
April 9, 2008 at 6:11 pm

#120925

Clustered index created on an ID (int) column on a table shows Logical Scan Fragmentation = 92.86% (I ran DBCC showcontig for the table). This column is also the Primary Key for the table.
Is there a way to reduce this fragmentation without dropping and recreating the table?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

you don't need to drop the table - why not just drop and recreate the index? you could do so with ALTER TABLE for example

---------------------------------------
elsasoft.org

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

No need to drop the table or the index

ALTER INDEX <Index name> ON <Table Name> REBUILD

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 3

KB (4/9/2008)
Clustered index created on an ID (int) column on a table shows Logical Scan Fragmentation = 92.86% (I ran DBCC showcontig for the table). This column is also the Primary Key for the table.
Is there a way to reduce this fragmentation without dropping and recreating the table?

If most of queries run on small subset of table, fragmentation doesn't matter.

DBCC dbreindex should fix this.

This should shed some light on fragmentation:

http://www.sqlservercentral.com/articles/Administering/fragmentation101/1346/

Kangana Beri SSCrazy Points: 2184 More actions · Answer 4

Thanks for the replies.

I have another question: since this a clustered index, just rebuilding this will automatically rebuild the other non-clustered indexes on the table or you have to explicitly rebuild them later with ALTER command too?

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 5

Rebuilding a clustered index, does not implicitly rebuild the non clustered indexes. You have to rebuild them too.

You could also use the ALL option to rebuild all indexes on a table.

http://msdn2.microsoft.com/en-us/library/ms188388.aspx

I have a stored procedure to that you can use if you like.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 6

If you rebuild clustered index explicitly with ALTER, you have to rebuild other indexes too.

DBReindex rebuilds them all, in SSMS just run:

DBCC DBReindex ('[database.]schema.table')