August 17, 2009 at 9:47 am
I set the default fill factor at %90. Then for tables which have static data that does not change much on a daily basis I explicitly set the fill factor to %100 for those tables. For tables that are updated frequently and which have indexes that fragment more quickly I set the fill factor to either %70 or %80. I don't have any tables for which the fill factor is less than %70.
August 17, 2009 at 5:16 pm
If the FillFactor applies to a PKey (clustered index) on a table, the value of the Pkey is ever-increasing, and row inserts are at the end of the table, then a FillFactor of 100 or 0 makes sense. Doing this ensures that the data blocks SQL server uses to store table data is filled to its theoretical limit, which reduces the number of data blocks read when reading any data is from the disk.
If an index is:
(a composite key (index definition contains more than one column))
or (a PKey that is not ever-increasing)
then other FillFactors are almost always desired.
For instance, given this table definition:
CREATE TABLE [dbo].[Client](
[ClientId] [bigint] IDENTITY(1000,1) NOT NULL,
[FirstName] [nvarchar](20) NOT NULL,
[MiddleName] [nvarchar](20) NULL,
[LastName] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
The clustered index is defined by the column ClientId, which is of data-type bigint, has a seed of 1000 and increments by one, for each new row, upon a row insert. This table meets the definition specified in the first paragraph as long as all new rows are inserted at the end of the table (this should be the case if replication is not used by the database).
August 21, 2009 at 5:13 am
Recently i was having to reindex nightly on certain tables, i changed the fill factor from 100 to 90 and now i get a couple of days before i need to reindex, i am going to try and change it again to 80 and see what effect that has on performance, i am gueeeing 80 will get me through the week between maintenance and not really impact performance to much.
Cheers, Paul
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply