Fill Factor

Question

Post reply

Fill Factor

rodmeans

Valued Member

Points: 58
More actions
October 25, 2011 at 11:48 am

#245799

Hello,
I have a highly transactional table with a cluster index on a INT column and a non cluster index on a VARCHAR column. The cluster index has very low fragmentation but the non cluster index fragmentation is always over 90%. I am rebuilding the index every night is the fragmentation is over 30% and reorganized if fragmentation is more than 10%.
My question is if I set the fill factor on my non cluster index will that help reduce the amount of page splits and in turn reduce fragmentation or will the fill factor only affect the cluster index?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Quite possibly.

What's the definition of the columns on that index?

Are those columns getting lots of updates?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

To answer one of your other questions, fill factor is set independently on all indexes, clustered and non-clustered alike. i.e. you can have 100 on your clustered index and 90 or any other value between 0 and 100 on a non-clustered index on the same table and they function independently. Please provide DDL.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

rodmeans Valued Member Points: 58 More actions · Answer 3

The column in the non cluster index is a varchar(255) and new rows are getting inserted roughly ever couple of minutes.

Jared SSC Guru Points: 61793 More actions · Answer 4

rodmeans (10/26/2011)
The column in the non cluster index is a varchar(255) and new rows are getting inserted roughly ever couple of minutes.

Do you know offhand what the typical actual length of the values are in that column? Also, the MAX?

Thanks,

Jared

Jared
CE - Microsoft

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

A lower fill factor should result in lower fragmentation, since you will get more new rows on the pages before they split, but over time, you might continue to get more fragmentation on the NCIs.

I would be careful about getting too wrapped up in the fragmentation of the NCI. Likely if it's being used, you are likely seeking if it's being used most of the time, but I would dig in and look at the queries that use it and see if there are large numbers of reads or issues with that index before I got too worried about it.