Fill Factor clarification

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Fill Factor clarification

24 posts, Page 1 of 3

1 2 3 »»»

Fill Factor clarification

Rate Topic

Display Mode

Topic Options

Author

Message

DionoZeus

Posted Wednesday, February 13, 2008 5:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 01, 2010 8:12 AM
Points: 9, Visits: 43

Hi,
if we have an identity column in a table and if that is a primary key, is it fine that to have a fill factor of 90. Assume frequent updations and insertions are happening to this table.

Thanks,
Ciju

Post #454976

Carolyn Richardson

Posted Wednesday, February 13, 2008 5:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

Totally depends on the volume of reads and writes on the table, and how it is being used, size of database, performance of hardware etc.. If the database is heavy on reads a low percentage of available space means less pages to read when full table scans are performed, if heavy on writes then a lot of space available means less page splits so writes could be quicker.

Its almost a suck it and see as you have to monitor and adjust as required depending on your database use.

For small low use databases 90% is a good starting point, which is why the SQL Server 2000 maintenance plans defaulted to this.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists

Post #454985

Steve Jones - SSC Editor

Posted Wednesday, February 13, 2008 7:49 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

It also depends on the updates. Updates don't force page splits. Updates that increase the amount of data, beyond what fits on the page, force page splits.

If I have integers, dates, or CHAR in my tables, then I can update all day long and not cause page splits because the data size hasn't changed. If I insert a single character into varchar fields and then update it with 1,000 char, I might be causing splits.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #455078

Scott Coleman

Posted Wednesday, February 13, 2008 8:08 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 2,818, Visits: 1,038

You can rebuild the indexes on the table, then check the table fragmentation regularly (daily?) with sys.dm_db_index_physical_stats. If the table becomes fragmented quickly after reindexing, you should probably lower the fill factor to leave more space for updates.

If your identity primary key is clustered (the default), all inserts occur at the end of the table and cannot cause fragmentation. It would only be caused by updates that increase the size of variable-length columns (varchar, varbinary).

Keep in mind that fill factor only takes effect when an index is created or rebuilt. If you don't have a maintenance plan that periodically rebuilds indexes, it doesn't matter what the fill factor setting is.

Post #455096

DionoZeus

Posted Wednesday, February 13, 2008 11:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 01, 2010 8:12 AM
Points: 9, Visits: 43

Hi,
Kindly let me know whether my understanding is incorrect...
A word regarding the environment, the database comes to around few GBs and daily reindex process is done!
If we are having an identity column as primary key(clustered), all the inserts will be happening to the end of the last insterted data and index. fcourse i admit that we are not doing forcefull updation (set identity_insert off). So, if frequent insert process happens, it will continue from the last index page. (We can leave update process as it will not do any changes in the pages). Hence, we are keeping extra page free space unnecessarily, for all the index pages (which is not at all used) if we keep the fill factor as 75 or 80 and wasting space (i admit space is not a constraint now). Let the page split happens, so that it will get enough free space for further inserts.

Post #455257

DionoZeus

Posted Friday, February 15, 2008 4:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 01, 2010 8:12 AM
Points: 9, Visits: 43

Can i have sme more suggestions on this....

Post #456236

Matt Miller (#4)

Posted Friday, February 15, 2008 7:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

What do you need further suggestions on? It sounds like you have a pretty good handle on things...

The one suggestion I can make (which was made by one of the indexing and storage gurus on here, Paul Randal) is to figure out whether you really need to reindex daily. His point was that most databases see no advantage in something that frequent, and that the incurred overhead, log space wasted, etc., were not worth it. Just like hard drives, some amount of fragmentation is assumed, and the system doesn't suffer until you get into lots and lots of fragmentation. Unless you're on a system where a substantial portion of a table is changed each day - there's really no rationale to rebuilding each day. At best -perhaps look at being more selective, and rebuild the one or two indexes in a database that get changed/fragmented a lot.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #456287

matt stockham

Posted Friday, February 15, 2008 2:32 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:22 AM
Points: 750, Visits: 2,937

Also, if you have other indexes on the table then they may benefit from lower fillfactor settings than the clustered index.

Post #456534

Marios Philippopoulos

Posted Sunday, February 17, 2008 5:51 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Matt Miller (2/15/2008)

At best -perhaps look at being more selective, and rebuild the one or two indexes in a database that get changed/fragmented a lot.

Absolutely true!
Do not re-index the entire database blindly, but target only those indexes that need to be rebuilt, based on a pre-determined threshold. You will be placing an unnecessary amount of load on your system.

There is no hard and fast rule on threshold values - in SQL 2000 environments I have been targetting indexes with under 40% scan density and over 10% logical fragmentation. In SQL 2005, the measures of interest are avg_fragmentation_in_percent and avg_page_space_used_in_percent from view sys.dm_db_index_physical_stats.

To get a feel on whether a 90% fill factor is adequate for your index, you can monitor
sys.dm_db_index_physical_stats.avg_page_space_used_in_percent over a period of time.
If you find that it quickly and significantly deviates from the 90%-space-used value enforced originally by the fillfactor setting, then that means the 90% fillfactor value is probably too high.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #456711

Jeff Moden

Posted Sunday, February 17, 2008 8:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

How does one determine if a page split has occured during an update?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #456727

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions