reset fill factor back to server default

Question

OK, so some time back we were using the maintenance plan to rebuild our indexes. We decided to change the "free space per page" option to 10%. Which was fine at the time. Now several things have changed and I would like to only have maybe one or two tables with a fill factor 90. Although the problem is , this maintenance plan changed all my indexes to 90 and if I select "reorganize with default server setting" which is still zero, it does not change the fill factor back. So this kind of sucks. i tried to update sys.indexes but that didn't work(haha). It seems the only way to get them back to default is to drop and create all of them.

It there another way to set the fill factor to default so next time our alter index maintenance plan runs it changes the fill factor to 0 (current server default).

Thanks,

Answer 1

Hello,

you can use DBCC DBReIndex to reseed the fill factor of index; select all index, loop through with a cursor and execute a dynamic sql statement to start DBReIndex for all indexes.

Example:

USE [AdventureWorks2008]

GO

 

DBCC DBREINDEX ('Person.Address', '' , 70)

 

SELECT name, fill_factor

FROM sys.indexes

WHERE object_id = OBJECT_ID('Person.Address');

 

DBCC DBREINDEX ('Person.Address', '' , 90)

 

SELECT name, fill_factor

FROM sys.indexes

WHERE object_id = OBJECT_ID('Person.Address');

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

I don't believe that is going to work. Try it. I need to rest all indexes to the server default, not change the fill factor to something else. So if I select * from sys.indexes all columns should show 0.

Answer 3

Of course I run this statement and I added the selects on sys.indexe to validate the results; on my maschine it works pretty fine.
I tested it on Sql Server 2008 and Sql Server 2008 R2 CTP; which version do you use?

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 4

I want the index to just use the server default. Yes i can change the ff using the method above no problem from 70 to 100 or whatever. What i want is to change the fill factor of the index to use the server default. So after if you right click the index and choose options > fill factor is not checked.  or when you select * from sysindexes, fillfactor equals 0 (the default for sql 2005)

I am using 2005 SP3 4035.

Answer 5

Ok, I see what you mean.

But: 0 is equal with 100, the only difference is, 0 means it's not defined explict, so the default = 100 is used.
Refer MSDN fill factor (option)

So it' still the same solution, only a CASE if fill factor = 0 then use default = 100, otherwise the define fill factor e.g. 90%

=>

DBCC DBREINDEX ('Person.Address', '' , 100)

SELECT *

FROM sys.configurations

WHERE configuration_id = 109

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 6

Yea not quite what I want but I guess it will work. MS has been back and forth on the 100 vs 0 debate. Thanks for the help.

Answer 7

I am sorry I can give you a more satisfiable answer; I looked up all configuration settings, but the 100% seems to be hard coded.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 8

no worries, it all seems kind of weird to me. Once you make that selection in the maintenance plans there is almost no way of going back. I was thinking of just going ahead and scripting it out but then I thought about all the primary keys and foreign keys and said forget it:/

Answer 9

 Once you make that selection in the maintenance plans there is almost no way of going back.

You can Modify a Maintenance Plan in Object Explorer. You can edit the Rebuild Index Task and  set Change Free Space Percentage to...

If you set it to 10% (free) that is equivalent to 90% (data) FILLFACTOR. (A source of eternal confusion).

A Maintenance Plan is an SSIS package. Changing it similar to changing Control Flow tasks.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 10

My question would be why do you want to set you indexes up for fragmentation problems by leaving no free space at the leaf level?  Unless you have no data changes occuring at all this doesn't make much sense. 

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!