March 23, 2012 at 7:52 am
TheSQLGuru (3/23/2012)
I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!
Does that mean that we should not increase the fill factor where we see that indexes are getting fragmented by 60-70 % overnight??
March 23, 2012 at 9:23 am
Divine Flame (3/23/2012)
TheSQLGuru (3/23/2012)
I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!Does that mean that we should not increase the fill factor where we see that indexes are getting fragmented by 60-70 % overnight??
I would almost certainly adjust fill factors for indexes that are getting fragmented even 10% in a day - probably for an even lower percentage depending on some factors.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 24, 2012 at 12:51 pm
Divine Flame (3/23/2012)
TheSQLGuru (3/23/2012)
I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!Does that mean that we should not increase the fill factor where we see that indexes are getting fragmented by 60-70 % overnight??
Let's make sure that everyone knows what is meant by "increasing the fill factor" because some folks can get confused as to what is meant.
If you increase the value of the fill factor (from 80 to 90, for example), you're actually leaving less space on each page for data and that could result in more page splits for Clustered Indexes and more extent splits for Non-Clustered Indexes.
What a lot of people mean by "increasing the fill factor" is that they really want to increase the amount of free row space per page. In order to do that, you actually have to decrease the value of the fill factor (from 90 to 80 for example).
When someone says they want to "increase the fill factor", you need to ask them specifically what they mean.
What Kevin is suggesting is probably to decrease the value of the fill factor which will increase the amount of free space per page which will help prevent index fragmentation on high insert/modification tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2012 at 1:29 pm
Jeff Moden (3/24/2012)
Divine Flame (3/23/2012)
TheSQLGuru (3/23/2012)
I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!Does that mean that we should not increase the fill factor where we see that indexes are getting fragmented by 60-70 % overnight??
Let's make sure that everyone knows what is meant by "increasing the fill factor" because some folks can get confused as to what is meant.
If you increase the value of the fill factor (from 80 to 90, for example), you're actually leaving less space on each page for data and that could result in more page splits for Clustered Indexes and more extent splits for Non-Clustered Indexes.
What a lot of people mean by "increasing the fill factor" is that they really want to increase the amount of free row space per page. In order to do that, you actually have to decrease the value of the fill factor (from 90 to 80 for example).
When someone says they want to "increase the fill factor", you need to ask them specifically what they mean.
What Kevin is suggesting is probably to decrease the value of the fill factor which will increase the amount of free space per page which will help prevent index fragmentation on high insert/modification tables.
Fair enough. Thanks Jeff 🙂
March 28, 2012 at 5:01 am
Chans could be that you are rebuilding indexes of Tables that are i use at that point i time, So u need to setup a profiler to audit what's happeingo the tables. u might have to relook at ur maintenace plan strategy.
Maybe rebuild weekly for instance.
March 29, 2012 at 9:50 am
Jeff Moden (3/24/2012)
Divine Flame (3/23/2012)
TheSQLGuru (3/23/2012)
I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!Does that mean that we should not increase the fill factor where we see that indexes are getting fragmented by 60-70 % overnight??
Let's make sure that everyone knows what is meant by "increasing the fill factor" because some folks can get confused as to what is meant.
If you increase the value of the fill factor (from 80 to 90, for example), you're actually leaving less space on each page for data and that could result in more page splits for Clustered Indexes and more extent splits for Non-Clustered Indexes.
What a lot of people mean by "increasing the fill factor" is that they really want to increase the amount of free row space per page. In order to do that, you actually have to decrease the value of the fill factor (from 90 to 80 for example).
When someone says they want to "increase the fill factor", you need to ask them specifically what they mean.
What Kevin is suggesting is probably to decrease the value of the fill factor which will increase the amount of free space per page which will help prevent index fragmentation on high insert/modification tables.
Precisely.
As a very rough example, what I tend to do is set up my index maintenance scripts to accumulate logs, and those logs include not only the amount of fragmentation found, but also the fillfactor at the time of maintenance. I load up the last N (3-10) index maintenance run logs, and look through for indexes that get rebuilt "too often"; for each of those indexes, I take a look at how wide they generally are, and then make a judgement on how to change the fillfactor. On one index that gets fragmented too fast, I might change it from 95 to 85, or from 85 to 70, and on another, I might change it from 100* to 98, or from 96 to 93. Knowing the business rules and the data itself helps give better guidance.
It can also be useful to watch how many pages indexes take up when fragmented for 1 or 2 index maintenance cycles. In most cases I see of very quickly fragmenting indexes, leaving more free space in an index results in less space used after a little time passes than letting it fragment in the first place.
*My personal opinion is that the default fillfactor of 100 for indexes does more harm than good in most industries; in the systems I work with most often, the vast majority of indexes are on fields that are not no-update, sequential value insert only fields.
March 30, 2012 at 1:22 am
If your indexes are fragmenting like that in such a short period of time - I would suggest that there is something wrong with the db design - normally I would expect "new" data to be appended to the end/tail of tables, not into the middle of an ever growing table. You aren't by chance using GUID's as the primary key/clustered index within this database are you?
Joe
March 30, 2012 at 8:10 am
Joe Clifford (3/30/2012)
If your indexes are fragmenting like that in such a short period of time - I would suggest that there is something wrong with the db design - normally I would expect "new" data to be appended to the end/tail of tables, not into the middle of an ever growing table. You aren't by chance using GUID's as the primary key/clustered index within this database are you?Joe
The OP didn't mention clustered indexes, only indexes in general. It's extremely common to have indexes with leading fields whose data is added or updated in arbitrary order on a few to almost all systems; for instance: names, addresses, dates of birth, ID numbers not assigned by the system, postal codes, dollar amounts, quantities, etc.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply