Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  SQL Server Tips by Burleson

Indexes and Fillfactor

Some never look at or consider fillfactor when creating indexes for a table, which may be just fine in some cases. In other situations, pausing to consider the fillfactor effect for indexes is time well spent.

As a quick review, the fillfactor effect specifies the percentage of space filled on index data pages when an index is initially created. The default fillfactor setting of zero, which can be altered at the global server level through a configuration option, will cause an index to be almost filled to capacity, with only a small amount of space being left at the upper level region of the index. A 100% setting completely fills each index page.

One important thing to remember is that this amount is not adhered to after the index is first built. An index can be rebuilt and the original fillfactor setting re-instituted with the variety of DBCC index rebuild or ALTER INDEX (SQL Server 2005) commands.

So, what exactly are the considerations with fillfactor? Higher fillfactor settings should result in less index pages which in turn should result in fewer pages read during scan operations. As been mentioned many times already, less I/O generally equates to better performance.

However, high fillfactor settings can also result in page splits for clustered indexes when SQL Server enforces the sort order of the clustered index during INSERT or UPDATE actions. This happens because SQL Server does not have room on an index page for the requested change, so it has to split the page to perform the modification. This can result in performance degradation and can be confirmed by carefully watching the page splits counter, available from the page_splits query below:

* page_splits.sql

-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact rtp AT rampant.cc
-- *************************************************

select
cntr_value
from
master.dbo.sysperfinfo
where
counter_name = 'Page Splits/sec' and
object_name like '%Access methods%'


What rules of thumb should be followed with respect to fillfactor? If there are tables present that are primarily read only, a fillfactor setting of 100 should be used to reduce the number of produced index pages.

If, however, there are tables present with high rates of INSERT, UPDATE and DELETE activity, lower fillfactor settings of 50-60% should be used. This will need to be coupled with periodic index rebuilds that will re-establish the fillfactor setting to keep DML running smooth through the indexes.

A mixed environment can work well with fillfactor settings in the neighborhood of 75%. One last piece of advice: for small indexes that have few pages, time should not be wasted worrying about fillfactor as it will not be capable of impacting the database’s performance for the worse.


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter