Index fragmentation not cutting down to 0

Question

Post reply

Index fragmentation not cutting down to 0

WonderPra

SSCommitted

Points: 1624
More actions
January 20, 2014 at 10:59 am

#303427

hey people,
I am a db trainee at my work n i was trying to execute the reindexing maintenance scripts manuallly on a databases.. When i run alter index all on one of the index (which is unknown index and i cant see that on the table also, dont know where it went) Its not defragmenting to 0 or even little less. It is still 93% and i am horrified to see that. Please help.
--Pra:-):-)--------------------------------------------------------------------------------

Viewing 10 posts - 1 through 9 (of 9 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

is the table a heap (indid = 0). rebuild would not be able to defragment a heap.

---------------------------------------------------------------------

WonderPra SSCommitted Points: 1624 More actions · Answer 2

thanku i just came to know that.. Thanks for the quick response 🙂

--Pra:-):-)--------------------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 3

no problem

---------------------------------------------------------------------

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Or if the data is less than an extent in size (8 pages), then it won't fragment either. Generally you shouldn't even bother defragmenting an index until it's at least 100 pages in size. Some recommendations are to wait until it's at least 1000 pages in size.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (1/20/2014)
On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.

However filtering out tables doesn't contribute towards log backup size, log shipping times, mirroring or always on log transfers.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

GilaMonster (1/21/2014)
Jeff Moden (1/20/2014)
On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.
However filtering out tables doesn't contribute towards log backup size, log shipping times, mirroring or always on log transfers.

Gimme a minute while I try to get the egg off my face. :blush: That's absolutely true. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 8

of course, it depends how many small tables you've got. 😉

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Heh... I know this is an old post but I've found out through experimentation since Nov 2017 that if you're using REORGANIZE for large tables, then you have much bigger things to worry about when it comes to log backup size, log shipping times, mirroring or always on log transfers. 😉 REORGANIZE on a 146GB table suffering only 12% Logical Fragmentation (well within supposed "Best Practices" of reorging between 10 and 30%) caused the log file to explode from 20GB to 227GB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)