Indexes Won't Defrag

Question

Indexes Won't Defrag

Viewing 8 posts - 16 through 22 (of 22 total)

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

t.walker (3/17/2009)
Krishna (3/17/2009)
As a genral rule of thumb Fillfactor should be:
100% for no activity
90% for low activity
70% for medium activity
50% for higher activity or more
I'm not sure you can have a general rule of thumb for fill factors other than 100% is fine if the data never changes.
How have you established these figures Krishna?

Yes, t.walker I did and I frequently poll my tables to see what my fragmentation levels are and then set it accordingly. It works fine for me so, the OP could give it a shot.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

I'm afraid I'm with Adi Cohn, it's much more complex than your reply suggests.

Although there are theoretical approaches you can take to this, I find the best bet is to watch the fragmentation develop over time and decide what the fillfactor should be for each index based on that.

i.e. start towards 100% and reduce it is you have a problem.

Quite often a 95% fill factor makes a very significant difference compared to 100% when the data is inserted randomly.

.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

Poll your table using the dmv ' _physical_stats' and organize your maintenance plan.
As a genral rule of thumb Fillfactor should be:
100% for no activity
90% for low activity
70% for medium activity
50% for higher activity or more

Yes, I have advised OP the same. If the table he is working on against is of low actiivty he can set it to the above mentioned setting and then gradually work on it.

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

t.walker (3/17/2009)
I'm afraid I'm with Adi Cohn, it's much more complex than your reply suggests.

Indeed. 50% fill factor is, in general, a very poor idea. It means that pages start at 50% full, which means that your table takes twice the space on disk, twice the space in memory and twice the IOs to read it. That means that your DB is larger and your queries less efficient.

Unless the table in question gets seriously fragmented very fast and that fragmentation causes problems, 50% fill factor is far from ideal. I don't think I've ever used anything less than 90.

Remember fragmentation only causes issues when large portions of a table are read from disk into memory (ie partial or full index scans), not when pages are read from memory, not for singleton seeks.

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

Stamey SSCertifiable Points: 5600 More actions · Answer 5

Gail,

Did you mean that at 50% your index would take twice the space on disk, rather than the table?

I use the general 90% fill factor on all tables because I have not yet come across a situation where I need to get more refined in that number. Unfortunately, or fortunately, depending on your viewpoint, I have not worked on a SQL Server that was busy enough for the fill factor to make a difference. As I started this thread you'll notice the index I was dealing with had 5 pages in it, so you can be pretty certain that a fill factor isn't going to make much difference here.

I do hope to one day have a DB that is busy/big enough to do some real performance tuning, but so far no luck on that.

For now I am stuck with living vicariously through those who have the VLDB's.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

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

Stamey (3/17/2009)
Gail,
Did you mean that at 50% your index would take twice the space on disk, rather than the table?

Depends what type of index. If it's the clustered index that we're suggesting 50% fill factors for (and typically it's the cluster that's most affected by fragmentation), then it is the table.

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

Stamey SSCertifiable Points: 5600 More actions · Answer 7

Ah, I see.

It's not so much that you're teaching me something new as you give me new/different ways to look at it and I thank you for that.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

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

Pleasure

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