some indexes got fragmented quickly

Question

some indexes got fragmented quickly

WhiteLotus

SSChampion

Points: 12122
More actions
June 8, 2017 at 1:03 am

#321946

Hi All,

I notice there are some indexes that got fragmented quickly .is it better to lower down the fill factor or do more rebuild in a day ?
At the moment I have maintenance plan running every day at 9PM but starting from 3 AM some indexes got fragmented( above 90%)

Any feedback are much appreciated

Cheers

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

WhiteLotus - Thursday, June 8, 2017 1:03 AM
lower down the fill factor

Fragmentation is not typically a huge problem, but the page splits that cause it can be, so you want to avoid them, not cause them to happen more often, which your other option would do.

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

DimUser Ten Centuries Points: 1043 More actions · Answer 2

the fill factor leans more towards internal fragmentation, or page fullness.
as already mentioned, it's the page splits you want to watch out for (external fragmentation), as this process is resource intensive.
how big is your index? if it's small (less than 8 pages) then you will inherently see quite high fragmentation, due to the index being split over mixed extents.
what keys are in your index? if you have a guid then this will also give you inherently high fragmentation... perhaps rethink your index strategy!?
there's an argument that up to date stats are more important than fixing index fragmentation, so IF you are seeing a performance problem perhaps look to update stats more regularly with a sample and not a full scan.
If you have an out of hours workload that is running through the night, trying running Adam Mechanic's sp_WhoIsActive at regular intervals to record the workload. Then you may be able to see what it causing your problem, and fix it at source.

EDIT: it's also worth noting that if your index is less than 1000 pages it is recommended to not rebuild your index, as it can have little effect.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

GilaMonster - Thursday, June 8, 2017 1:15 AM
WhiteLotus - Thursday, June 8, 2017 1:03 AM
lower down the fill factor
Fragmentation is not typically a huge problem, but the page splits that cause it can be, so you want to avoid them, not cause them to happen more often

How would lowering the fill factor increase the number of page splits, Gail?

Thanks
John

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

John Mitchell-245523 - Thursday, June 8, 2017 2:59 AM
GilaMonster - Thursday, June 8, 2017 1:15 AM
WhiteLotus - Thursday, June 8, 2017 1:03 AM
lower down the fill factor
Fragmentation is not typically a huge problem, but the page splits that cause it can be, so you want to avoid them, not cause them to happen more often
How would lowering the fill factor increase the number of page splits, Gail?

It won't, it'll decrease them.

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

WhiteLotus SSChampion Points: 12122 More actions · Answer 5

GilaMonster - Thursday, June 8, 2017 3:30 AM
John Mitchell-245523 - Thursday, June 8, 2017 2:59 AM
GilaMonster - Thursday, June 8, 2017 1:15 AM
WhiteLotus - Thursday, June 8, 2017 1:03 AM
lower down the fill factor
Fragmentation is not typically a huge problem, but the page splits that cause it can be, so you want to avoid them, not cause them to happen more often
How would lowering the fill factor increase the number of page splits, Gail?
It won't, it'll decrease them.

Thanks Gail .Appreciate your feedback .
So I would need to lower down the fill factor
Moreover what do you think If I run SP_updatestats after the rebuild process in a maintenance plan?

Cheers

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

Rebuilding an index updates that index's statistics with the equivalent of FULLSCAN, so you won't want to update the rebuilt index's statistics, since you'll be duplicating work (that's at best; if you just run a default update statistics, it'll be sampled, and you'll have done extra IO just to worsen the quality of the statistics).

Column statistics won't get updated, though. Those you would have to take care of separately.

Cheers!