Is it possible to set fill-factor on partition level?

Question

Is it possible to set fill-factor on partition level?

SQL Guy 1

SSCoach

Points: 15817
More actions
January 13, 2016 at 11:24 am

#399567

We have a partitioned table, where all partitions except the most recent 3, are set to read-only. They are never updated. However, the most recent partition is frequently updated. Fillfactor for all indexes are set to 80%. I want to set 100% (or 0) for all non-recent partitions, and 80% (or lower) for 3 most recent ones. Is it possible?
Thanks

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

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

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

Not directly, no. FILLFACTOR is not an option for single partition rebuilds https://msdn.microsoft.com/en-us/library/ms188388.aspx.

You could try to achieve this indirectly using the method in Thomas Kejser's answer here: http://dba.stackexchange.com/questions/86588/shrinking-old-partitions

Cheers!

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 2

Smart idea. I will try it. I also have secondary indexes partitioned.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Heh... "All that is "better" might not be".

Oddly enough, the "old" method of using partitioned VIEWS allows you to do exactly want you want.

This might also be a good place to use a combination of partitioned views and tables.

The question is, is it actually worth even worrying about since you can rebuild the indexes on separate partitions of a partitioned table, meaning that they should occur very quickly.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Jacob Wilkins (1/13/2016)
Not directly, no. FILLFACTOR is not an option for single partition rebuilds https://msdn.microsoft.com/en-us/library/ms188388.aspx.
You could try to achieve this indirectly using the method in Thomas Kejser's answer here: http://dba.stackexchange.com/questions/86588/shrinking-old-partitions
Cheers!

Use with care... Using those methods, if you rebuild the clustered index for anything over 128 extents (just 8MB) but have forgotten to do the other steps , a copy of the original index will be preserved until the new index is committed. If your filegroup contains just the one table, that filegroup will be twice the size it needs to be and half of it will be empty space.

Other than that caution, making a copy of the table for a large table (mine is a half terra byte) seems a bit crazy. I have a method that will do it by partition. I'll try to remember to post the steps I use when I get home (that's where the steps are written down).

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

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 5

My table is 200 GB, and also other tables and indexes are partitioned in the same file-groups. Anyway, I will test it in dev environment before doing it in prod.

ScottPletcher SSC Guru Points: 100948 More actions · Answer 6

As noted, you could compress the earlier partitions and not the current ones.

Also, carefully review the fillfactor and make sure it really should be as low as 80%. That's very low for a large table.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.