Setting Fill Factor on Database or Entire SQL Instance

Question

Setting Fill Factor on Database or Entire SQL Instance

aflores1-976495

Valued Member

Points: 66
More actions
February 23, 2010 at 2:13 pm

#211556

I have been asked by our Software Vendor to set the Fill Factor to 75% for their entire database. (their database is the only one that resides the server)
I Selected their data base and ran these commands below then rebooted Server. However the Fill Factor still shows as being zero when I select Modify on any of Tables then index/keys on the table designer.
After running the commands it completed successfully. Is there any to tell for sure if the Fill Factor got reset to 75%.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Fill Factor', 75
GO
RECONFIGURE
Thanks, Andy

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

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

All that command does is sets the default fill factor that will be used when indexes are created and the fillfactor is not specified.

When indexes are rebuilt - the fillfactor that is used will be the fillfactor specified in the command, or the fillfactor that was specified when the index was created.

What are you trying to accomplish here?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

aflores1-976495 Valued Member Points: 66 More actions · Answer 2

I ran a maint. script that was provided for by the software and they stated if the scan density falls below 75% to run a re-index. There is re-index job that runs once a week however the indexes below change only a little.

The Vendor is recommending that all indexes be set to 75% for their entire database. Trying to find a way to set the Fill Factor to 75% for existing Indexes.

Table Name Index Name Page Count Scan Density %

------------------------------ ------------------------------ ----------- ---------------------------------------

PUNCHEVENT X1_PUNCHEVENT 1109 14

SCHEDULEDTOTAL X1_SCHEDULEDTOTAL 1552 21

SHIFTASSIGNMNT X1_SHIFTASSIGNMNT 1694 53

TIMESHEETITEM X1_TIMESHEETITEM 1875 16

WFCTOTAL X1_WFCTOTAL 1704 16

The only way I have found to change the Fill Factor successfully is selecting each Table the in Table Designer, Indexes/Keys and set the Fill Factor there for each Indexes above. If I then run the re-index job then run maint. script the Tables Scan Density is fine.

Also in my Test Env on the Maint Job that is setup for re-indexing I tried checking the Change Free Space per Page to 75%. I see that this changes the TSQL commands to include a FILLFACTOR Switch of 75% but when I check Index/Keys in Table Designer it shows the Fill Factor at 0.

Thanks for you time, Andy

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

When you make that change in the GUI - it performs an index rebuild with the fillfactor defined as 75%. The only way to change the fillfactor on an index is to rebuild or reorganize the index with the new fillfactor defined.

I would question why they want every tables indexes to be rebuilt at that fillfactor. That is going to be too large for most tables and will just waste space in your database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

aflores1-976495 (2/23/2010)
I have been asked by our Software Vendor to set the Fill Factor to 75% for their entire database. (their database is the only one that resides the server)
I Selected their data base and ran these commands below then rebooted Server. However the Fill Factor still shows as being zero when I select Modify on any of Tables then index/keys on the table designer.
After running the commands it completed successfully. Is there any to tell for sure if the Fill Factor got reset to 75%.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Fill Factor', 75
GO
RECONFIGURE
Thanks, Andy

I'd try to convince the software vendor just exactly how wrong that actually is. It means that, once implemented, any and all tables that have a clustered index will be 25% full of nothing but air. That means that static or nearly static tables will also be 25% full of air and every query will have to touch 25% more pages to do the job.

My recommendation would be to set all static and nearly static tables to a FILL FACTOR of 100 and to only set very highly modified indexes to 75%. My feeling is that if the columns for the clustered index of a table are highly modified, then you have the chosen the wrong columns for the clustered index.

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