Sql-server – How to alter the fill factor of an index outside of an ALTER INDEX command

fill-factorindexsql server

I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB (I can set them when scheduling rebuilds on an index-by-index basis, but with 1,600+ indexes, I'd like to set them in advance in bulk). Outside of an ALTER INDEX command, is there a way I can redefine existing index fill factors?

Best Answer

Check your DB Server (or instance) options, it will set every index based on the fill factor you specified.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', x --set the fill factor value here, such as 70 for 70%
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

edit:

Do note, it will not actually rebuild or alter existing indexes. It will fulfill your request that all future index rebuilds will now have this fill factor, unless specified in the rebuild index command. Anything specified in the rebuild command will override this option.

Related Solutions

Sql-server – How to set the index fill factor back to server default

Through testing, it seems as though to get back to the default fillfactor on an index you need to drop and recreate it. When you do an ALTER INDEX ... REBUILD, any unspecified parameters will remain the same in the index's metadata unless otherwise explicitly changed in the ALTER INDEX command.

drop index IX_YourIndex
on YourTable
go

create index IX_YourIndex
on YourTable(YourKeyColumn)
go

To verify this:

select
    name,
    fill_factor
from testdb.sys.indexes
where name = 'IX_YourIndex'

fill_factor should be set to 0 to denote that it is using the instance's default value. (Note: 0 is for the default value, even if the instance configured fill factor is set to 95)

Postgresql – Fill factor for later bigserial + index

Assuming you are using COPY to populate the table I don't see a reason to create the table without the bigserial column.

Just create the table including the bigserial, but don't specify it in the COPY command. It will then automatically be filled with values during the import.

create table foobar (id bigserial, col1 text, col2 text);
copy foobar (col1, col2) 
   from 'foobar.txt'
   with (format csv, header false, delimiter ',');

That would remove steps 3,4 and 5.

I'm not sure what you mean with "range of bigserial" in step 6.

Best Answer

Related Solutions

Sql-server – How to set the index fill factor back to server default

Postgresql – Fill factor for later bigserial + index

Related Question