February 16, 2006 at 10:39 am
I would like to change an index fill factor from 80 to 0. I really do not want to drop and recreate the indexes, as this has to happen on our production servers. How do I do this?
I tried using DBCC DBREINDEX, but when you specify 0 there, that means "use the default", not " set it to 0". When I specify 100, it gets set to 100... but I don't want 100, I want 0.
I'm hoping I don't have to directly modify the system tables...
Philip
February 16, 2006 at 11:04 am
SQL Server uses Fill Factor only while creating indexes. Fill Factor is not maintained after the index has been created. Therefore, the only way to change the fill factor is to rebuild your indexes and specify the Fill Factor value that you wish. Also, you are correct in saying that when you specify '0', it means use default. If you really want your Fill Factor to be set that low, try setting it to 1.
February 16, 2006 at 12:29 pm
Some background: I'm reviewing the (admittedly nonexistant) index rebuilding routines on our main Production database (a bit under 10G). I've identified some "worst performing" indexes (primary key, clustered, identity column, fillfactor = 80?!?), and I'd like to reset the fillfactors so that the overnight DBCC DBREINDEX will use the new fillfactor--and thus compact the data down into less space.
My understanding is that a fillfactor of 0 differs from 100 in that 0 will leave space for 1 "key" in each page, where 100 fills them to the brim. (A handful of bytes per page we can spare, but not 2k.) Alas, published opinions vary on how this actually works. DBCC DBREINDEX will reset the fillfactor to a new value... but not if it's zero. Thus my conundrum.
I'm just looking for a way to change this with minimal impact and interruption on our Production data--which dropping and rebuilding clustered indexes would definitely do.
Philip
February 16, 2006 at 12:45 pm
Ah, I thought you wanted more free space, but you really want to pack your indexes in, leaving only a small space. Zero is the default fill factor. Use sp_configure (with advanced options on) to see what your fill factor is set at, this is what DBCC DBREINDEX will use unless you specify otherwise. (Specifying a Fill Factor of '0' tells SQL Server to use the default fill factor that is set up in sp_configure). Do you have a test environment? Try this out there and check the sysindexes.origfillfactor value for the index you rebuild. It should be zero. Good luck.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply