Index Fragmentation

Question

Index Fragmentation

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
April 20, 2009 at 2:34 pm

#202327

Hi All,
I have few tables having clustered and non-clustered index. Those indexes are heavily fragmented which is as below.
index_type_desc avg_fragmentation_in_percent page_count
---------------------------------------------------------------------
NONCLUSTERED INDEX98.4240687679083 1396
NONCLUSTERED INDEX92.8571428571429 14
NONCLUSTERED INDEX91.6666666666667 12
CLUSTERED INDEX 90.9090909090909 11
CLUSTERED INDEX 90.9090909090909 11
CLUSTERED INDEX 85.7142857142857 7
CLUSTERED INDEX 85.7142857142857 7
NONCLUSTERED INDEX85.7142857142857 7
When I try to rebuild these indexes they are rebuild, but checking avg_fragmentation_in_percent is still around 77 to 65 %.
I tried to rebuild again but stays the same. Can any one help me out to determine what could be the cause?
I am using SQL 2005 Standard Edition, SP2.
I have one thing to ask..... Does rebuilding a clustered index is same as rebuilding non-clustered index (I mean the TSQL used )?
"Alter Index ON [table.schema] REBUILD"
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

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

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

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

Except for the first one, all the others are far too small to even worry about fragmentation.

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

GilaMonster (4/20/2009)
Except for the first one, all the others are far too small to even worry about fragmentation.

Hi,

Thanks for your quick reply. Just curious, how did u tell that "others are far too small to even worry about fragmentation".

index_name avg_fragmentation_in_percent page_count

eg. CLUSTERED INDEX0.675260896255371 1629

Does this require to be defragmented !!

As I have read on many sites that if the average fragmentation level is more than 30% it requires a rebuild.

Should I look also at the page count ?

Please clarify to help me understand better .

Thanks

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

The recommendation is generally anything with less than 1000 pages doesn't need to be rebuilt/reorganized. Of course, you are going to need to test that in your own environment.

The tests that MS performed showed some performance improvement on indexes with greater than 1000 pages, and significant improvement on tables with more than 10,000 pages.

Most of the scripts available (on this site and others) generally filter out tables with less than 1000 pages.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

SQL_Quest (4/20/2009)
index_name avg_fragmentation_in_percent page_count
eg. CLUSTERED INDEX0.675260896255371 1629
Does this require to be defragmented !!

No. The average fragmentation is 0.6% on that one. Well under the 10% reorg, 30% rebuild recommendations that are usually given.

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

radekhatle SSC Eights! Points: 955 More actions · Answer 5

Hi,

I have clustered table with few nonclustered index. I need to do defragment.

Which indexes firs? The clustred ? I think? ,then nonclustered? If I understand it correctly nonclusetred depend on clustred? Thx

Radek

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 6

radek (4/24/2009)
Hi,
I have clustered table with few nonclustered index. I need to do defragment.
Which indexes firs? The clustred ? I think? ,then nonclustered? If I understand it correctly nonclusetred depend on clustred? Thx
Radek

Can you post some fragmentation data as I posted above ?

Thanks,

\\K 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂