Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ias0nas
Starting Member
36 Posts |
 Posted - 2010-09-29 : 08:01:34
|
| Hello,I have a table that looks like this:CREATE TABLE test(job_id int IDENTITY(1,1) PRIMARY KEY,job_number int)When records are added to this table, job_number is always NULL.At some point later on, the column is updated with a sequential integer (not necessarily in the same order as the job_id)I have an index on job_number and I was wondering if it's a good idea to leave the fillfactor to 100% because the integers are sequential or the fact that there are null values and that the order of this column doesn't follow exactly the job_id would result in a lot of page splits and generally a not working index.Thanks,Jason |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 09:47:00
|
| I'll probably leave 100 fillfactor in this case. Make sure auto update statistics is on (which it should be anyway).Update statistics and/or rebuild indexes as necessary. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-29 : 12:58:39
|
| Microsoft actually recommends that all fillfactors be set to 100 starting with SQL Server 2005. I did an extensive performance test on one of my systems where fillfactor of less than 100 made more sense due to the clustered index (not monotically increasing/decreasing). No matter what fillfactor I select, select queries completely degraded unless I used 100 fillfactor. And my DML queries slowed down too!I was shocked to say the least. We stuck with 100 for every single index as a result of my testing. I should've just gone with Microsoft's recommendation (it's clearly stated in BOL) and not wasted many hours of testing!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 13:24:07
|
| Yeah, but it was nice to see for yourself. After reading your saying so a while back I ran some tests too. At best, I saw no difference. At worst, some degradation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-29 : 14:34:14
|
I think it makes sense. If you leave, say, 10% free space then that's on every index page which, in my mind, raises two questions:1) How many pages will have NO addition index entries added before the next index rebuild? My guess is that is a very high percentage, and thus the number of extra index pages, to accomodate 10% slack, has resulted in a lot of extra reads.2) How many index pages get split anyway because more index keys were added than would fit in the slack space? I reckon that where 1 or 2 index keys are added the likihood is (using Broad Brush here!!) that more will be clustered close to that value. So many of the pages that got extra index keys, in Slack space, also then got split.If my hunch is right then might as well go for 100% fill and split a page the moment that a new key is added (and additional keys clustered close to that will use the just-split pages.But I never tested that theory  |
|
|
|
|
|
|
|
|
Fillfactor for an Index on column with NULL