Index management - rebuild/reorganize/dbreindex & fillfactor

Question

Post reply

Index management - rebuild/reorganize/dbreindex & fillfactor

Adam Bean

One Orange Chip

Points: 26631
More actions
February 29, 2008 at 7:33 am

#154166

Does issuing a rebuild/reorganize or dbreindex set the fillfactor back to the original value, or the database default?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

It's in Books Online under "DBCC Reindex"...

fillfactor

Is the percentage of space on each index page to be used for storing data when the index is created. fillfactor replaces the original fillfactor as the new default for the index and for any other nonclustered indexes rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created.

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Ok, but what if you don't pass a fillfactor? Does it default to it's original value or the database default?

Does the same thing apply to rebuild/reorganize?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Adam -

From testing I performed a while ago, issuing the rebuild with no specified fillfactor will rebuild using the latest specified Fillfactor.

Meaning - each time you specify a fillfactor - that becomes the new default. That default will be used until you specify a new one.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Perfect, and just to confirm, it is the same between 2k (dbreindex) and 2k5 (rebuild/reorganize)?

Thanks again

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I was testing using the Alter...rebuild, since the DBCC REINDEX is falling out of favor these days.

That probably bears confirmation. Should be easy to verify with a small table/index.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Matt Miller (2/29/2008)
I was testing using the Alter...rebuild, since the DBCC REINDEX is falling out of favor these days.
That probably bears confirmation. Should be easy to verify with a small table/index.

Yeah this is just for the 2k servers.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Adam Bean (2/29/2008)
Ok, but what if you don't pass a fillfactor? Does it default to it's original value or the database default?
Does the same thing apply to rebuild/reorganize?

Heh... Who the heck ever trusts a default that could change with tomorrow's hot fix? 😉 If you're not gonna pass the fillfactor, have the code treat the missing fillfactor as "0".

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

The default is the one that has been passed on the last rebuild command and does not take the systems initial default value.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Adam Bean (2/29/2008)
Matt Miller (2/29/2008)
I was testing using the Alter...rebuild, since the DBCC REINDEX is falling out of favor these days.
That probably bears confirmation. Should be easy to verify with a small table/index.
Yeah this is just for the 2k servers.

As a second thought -

If my memory doesn't fail me - there is no alter index...rebuild syntax in 2000 - so the only syntax that applies is the DBCC syntax. You just drop and create instead of rebuilding in 2000. I would then think that Jeff's BOL quote applies.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 10

So is it best practice than to find the original value of the index fill factor and pass that into the rebuild?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

According to the BOL quote - using 0 to pass in would serve the same purpose as passing the original fillfactor. Meaning 0 = use the original fillfactor.

I'm not sure that's best practice or not - but it sure sounds like that's what it's for.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?