August 28, 2003 at 11:25 am
I am just wondering since there will be no insert in between for a clustered index that is on an identity field, should the filfactor be 0 or 100?
I know this is going to go into the topic where 'should we have clustered index on an identity field'. Let's say we forget about that, what is the best fillfactor?
Thanks!
August 28, 2003 at 12:07 pm
From BOL.
"If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.
A fill factor value of 0 is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree."
August 28, 2003 at 12:49 pm
The fillfactor setting for a clustered index on IDENTITY field can be set extremely high (95% - 99%), since there it is HIGHLY unlikely that a clustering key will be updated, causing a possible page split. Probably the best bet is to leave the setting at the default of 0, which has the result that Allen stated. Bear in mind, however, that the fillfactor DOES NOT affect future index pages, only currently existing ones.
--
Putting a clustered index on an IDENTITY field for highly transactional tables can actually be a good idea, since it generally forces a hot spot on the disk. Contrary to the beliefs held by some developers and DBAs who can't seem to get 6.5 out of their minds, a hot spot can be quite desirable for SQL2K (and 7.0) OLTP and mixed OLTP/OLAP databases, because it increases the chance that needed extents will already be spooled into cache; any extents already in cache will, of course, be retrieved much faster than reading from disk. Since 7.0, row-level locking has nullified the previously valid assertion that hot spots were bad because of locking concerns.
August 28, 2003 at 2:53 pm
Thanks - Allen and Jay!!
August 29, 2003 at 9:43 am
I'm a little confused about this. Isn't the clustered index actually on the data pages? If you specify a fill factor to use the full page, wouldn't a high activity of updates potentially cause a lot of page splits. I'm not talking about updating the clustering key; shouldn't you leave some space to allow the non-key fields to expand in size, without forcing a page split?
Winston Peters
Winston Peters
August 29, 2003 at 11:08 am
quote:
I'm a little confused about this. Isn't the clustered index actually on the data pages? If you specify a fill factor to use the full page, wouldn't a high activity of updates potentially cause a lot of page splits. I'm not talking about updating the clustering key; shouldn't you leave some space to allow the non-key fields to expand in size, without forcing a page split?
Yes, you are right. But, of course, only if the table in question has a) variable width fields, b) those variable width fields are frequently updated, and c) the length of those variable width fields during updates differs drastically.
--
As in all these matters, everything is dependent on your specific situation and schema...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply