Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Fill Factor clarification Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 13, 2008 5:50 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 1,451, Visits: 2,911
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
Posted Wednesday, February 13, 2008 7:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Posted Wednesday, February 13, 2008 8:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124
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
Posted Wednesday, February 13, 2008 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, February 15, 2008 4:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, February 15, 2008 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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
Posted Friday, February 15, 2008 2:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 750, Visits: 3,085
Also, if you have other indexes on the table then they may benefit from lower fillfactor settings than the clustered index.
Post #456534
Posted Sunday, February 17, 2008 5:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:42 PM
Points: 1,851, Visits: 3,575
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 2
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
Posted Sunday, February 17, 2008 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #456727
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse