Pages

Saturday, June 25, 2011

Index Fill factor

Index Fill factor is the percent of page you want to fill and thus percent of free space you want to have in a page. A page is 8k bytes. Depending on columns in index, index can be a few bytes. Lets take 1000 bytes in our case.
A default fill factor is 100% and thus one can fill 8 records on a page.

If a default fill factor is 80% would mean, 8000*0.8=6400 bytes to fill, which would allow probably 6 records and leave part of page free.
When inserts updates happen, if there is a free space on the page, there is no need for a page split/new page to accomodate the new record. Thus fill factor is important, since it reduces the number of page splits.
The fill factor, is important only when indexes are created, reorganized or rebuild. Since that is when, sql server keeps this percentage of space free on every page.

Outdated Statistics

If we get an actual execution plan for a query and the estimated versus actual rows vary by a factor of 10 indicates the optimizer is using an outdated statistics. ANd an indication that stats need to be updated if query is slow.

links: extract from SQL Server 2008 query performance tuning distilled - Grant fritchey and Sajal Dam