Your prod DBA may be falling for one of the classical blunders. The first of which, is of course, never to get involved in a land war in Asia. The second (and only slightly less important) is that fillfactor only comes into play, when you are building or rebuilding an index. If the prod DBA has something that automatically rebuilds the indexes, he has a point. I have heard that 80% is a good setting for global fillfactor, but again, it depends on a lot of different things.
Here is an example of an update causing a pagesplit:
Code:
create table testfill
(col1 int identity(1, 1) not null Primary key,
col2 varchar(8000))
go
insert into testfill (col2) values (replicate ('a', 3500)), (replicate ('b', 3500))
go
select page_count
from sys.dm_db_index_physical_stats(db_id(), object_id('testfill'), -1, 0, default)
go
update testfill
set col2 = replicate ('c', 5000)
where col1 = ident_current('testfill')
go
select page_count
from sys.dm_db_index_physical_stats(db_id(), object_id('testfill'), -1, 0, default)
Wow. An actual use for the ident_current() function. Never been able to figure a use for it, before.
dBforums
>
Fill Factor
04-20-12, 09:47
Linear Mode
