Percentage of Fragmentation increased after 6 hours

Question

Percentage of Fragmentation increased after 6 hours

coolchaitu

SSChampion

Points: 10334
More actions
August 14, 2015 at 1:04 am

#319837

Good morning,
Yesterday night, I rebuilded all indexes one a table since they were having high percentage of fragmentation . After rebuilding, I immediately checked the fragmentation percentage and it got reduced and I felt happy. However, when i saw the percentage of fragmentation after 6 hours, it increased from 1% to 48%. Please help on how to find what caused the increase and how to fix it. Thanks is advance

Viewing 15 posts - 1 through 15 (of 33 total)

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

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

Could be a shrink operation ran, could be inserts caused page splits. If it's the former, stop shrinking. If it's the latter, try dropping the fill factor of the index slightly.

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

coolchaitu SSChampion Points: 10334 More actions · Answer 2

Gail Shaw Madam,

Thanks for your reply. No shrink operation was performed. Regarding inserts, how to know if any data load or insert operation happened?

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

Not 'an' insert operation. Inserts multiple, probably lots of them. You can use server-side trace to watch what the apps are doing (your only option on SQL 2005), just watch the impact that may have on the server and never use the Profiler GUI.

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

coolchaitu SSChampion Points: 10334 More actions · Answer 4

Dear Gail Madam,

I really salute for your will to help newbies/juniors like me.

coolchaitu SSChampion Points: 10334 More actions · Answer 5

Good morning Gail Madam,

I Rebuild the index again last night. percentage of fragmentation increased for the below index after 6-8 hours.

Here is the definition of the index:

CREATE NONCLUSTERED INDEX [idx_tblJobbooking_C3_C7] ON [dbo].[tblJobBooking]

(

[CustomerMobileNo] ASC,

[CustomerContactMobileNo] ASC

)

INCLUDE ( [JobID],

[PickUpTime],

[CabRegistrationNo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEX]

GO

Fill factor is set to 0. How to confirm if the cause is page splits?

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

Well fragmentation is either caused by a data file shrink or by inserts/updates causing page splits, so if you have no shrink operations then it's probably page splits. Try dropping the fill factor down 10%, see if that helps.

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

coolchaitu SSChampion Points: 10334 More actions · Answer 7

Gail Madam,

It is production. Is it ok to drop the fill factor? Also, i should be doing it during non-business hours, correct?

coolchaitu SSChampion Points: 10334 More actions · Answer 8

Good morning Gail Madam and everyone,

I reduced the fill factor value, set it to 90 and then rebuilded the indexes. It worked for majority of indexes. However, for some indexes it did not work. Fragmentation for those indexes went up to 90% when i checked the next day. Shall i try setting fill factor to 80 or 70 and rebuild these indexes. Please advise.

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

coolchaitu (8/29/2015)
Good morning Gail Madam and everyone,
I reduced the fill factor value, set it to 90 and then rebuilded the indexes. It worked for majority of indexes. However, for some indexes it did not work. Fragmentation for those indexes went up to 90% when i checked the next day. Shall i try setting fill factor to 80 or 70 and rebuild these indexes. Please advise.

Gosh, I hope you didn't rebuild ALL indexes with a FILL FACTOR of 90. For indexs that have ever increasing values (on IDENTITY columns, for example), page splits on inserts or updates will be a fairly rare thing even with a FILL FACTOR of 100. What that means is that you're wasting 10% of the size of such tables.

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

coolchaitu SSChampion Points: 10334 More actions · Answer 10

Dear Jeff sir,

I did not rebuild ALL indexes in the database with fillfactor=90. Only on some tables, index fragmentation was greater than 95%. For these indexes, i rebuilt with fillfactor=90 as it did not work previously with fillfactor=0. The next day, when i checked the fragmentation, for 2 tables it increased to 95%. So, for these2 tables, shall i try rebuilding with fillfactor=80 or 70?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

coolchaitu (8/30/2015)
Dear Jeff sir,
I did not rebuild ALL indexes in the database with fillfactor=90. Only on some tables, index fragmentation was greater than 95%. For these indexes, i rebuilt with fillfactor=90 as it did not work previously with fillfactor=0. The next day, when i checked the fragmentation, for 2 tables it increased to 95%. So, for these2 tables, shall i try rebuilding with fillfactor=80 or 70?

It depends. How big are the indexes and how often are they inserted into? And are they clustered or non-clustered? Are the indexes causing any timeouts during the index page splits? Is just one section of the index being inserted to based on the leading column of the index or is it rather a shotgun hit against all the values of the leading column in the index?

If it's a non-clustered index, you could certainly try 80 and then 70 if 80 doesn't work out. I usually wouldn't go below 70 without doing a serious deep dive on the nature of the table. If it's a clustered index, you might want to consider something else for the clustered index.

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

coolchaitu SSChampion Points: 10334 More actions · Answer 12

Dear Jeff,

Both are non-clustered indexes. I will try setting fill factor=80 or 70. Thanks sir

coolchaitu SSChampion Points: 10334 More actions · Answer 13

Good morning Gail Shaw Madam and Everyone,

To confirm that an index is unused, should the values for seeks,scans,lookups and updates be zero. That is, only if

seeks=0 and scans=0 and updates=o and lookups=0, the index is unused, correct?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 14

coolchaitu (9/1/2015)
Good morning Gail Shaw Madam and Everyone,
To confirm that an index is unused, should the values for seeks,scans,lookups and updates be zero. That is, only if
seeks=0 and scans=0 and updates=o and lookups=0, the index is unused, correct?

Not necessarily especially when it comes to unique indexes that support foreign keys or PKs. It may be that the index is never read but absolutely necessary for the optimizer to make decisions.

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