KEMBAR78
Optimizing SQL Index Fill Factor | PDF
Scribd
Upload
2K views1 page

Optimizing SQL Index Fill Factor

The fill factor specifies how full SQL Server should make the leaf level pages of an index during creation. It is useful when creating a new index on an existing table with predictable future changes. A properly chosen fill factor value yields better update performance than the default by reducing page splits required as the index fills. The default fill factor of 80% is usually efficient, but an explicit setting only applies at index creation - SQL Server does not dynamically maintain a certain percentage of empty space in pages.

Uploaded by

vijaysingh
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
2K views1 page

Optimizing SQL Index Fill Factor

The fill factor specifies how full SQL Server should make the leaf level pages of an index during creation. It is useful when creating a new index on an existing table with predictable future changes. A properly chosen fill factor value yields better update performance than the default by reducing page splits required as the index fills. The default fill factor of 80% is usually efficient, but an explicit setting only applies at index creation - SQL Server does not dynamically maintain a certain percentage of empty space in pages.

Uploaded by

vijaysingh
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 1

File Factor, view,

File factor –it is seldom necessary to specify a fill factor when you create a index. the
option is provided for fine tuning performance .It is useful when u are creating a new
index on a table with existing data and particularly when you accurately predict the
future changes in that data .

FILLFACTOR is specified as a percentage and it indicates how full sqlserver should


make the leaf level of each index page during index creation .AS a table is updated and
index is fills.sql server must split the index page to make rooms for new rows. which is
require time for update intensive tables a properly chosen fill factor value yields better
update performance than an improper fill factor value .the default fill factor is usually is
usually efficient for most database usage. an explicit fill factor setting applies only when
the index is first created sql server does not dynamically keep the specified percentage of
empty space in the pages .the default fill factor is 80.

Cluster index and non-clustered index

The with recompile option will force a recompilation of the execution plan every time the
stored procedure run.

You might also like