February 13, 2008 at 5:29 am
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
February 13, 2008 at 5:50 am
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.
February 13, 2008 at 7:49 am
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.
February 13, 2008 at 8:08 am
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.
February 13, 2008 at 11:27 am
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.
February 15, 2008 at 4:12 am
Can i have sme more suggestions on this....
February 15, 2008 at 7:22 am
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?
February 15, 2008 at 2:32 pm
Also, if you have other indexes on the table then they may benefit from lower fillfactor settings than the clustered index.
February 17, 2008 at 5:51 am
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 17, 2008 at 8:43 am
How does one determine if a page split has occured during an update?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2008 at 3:42 am
The terms FILLFACTOR and PAD_INDEX are very confusing due to the names and some default behavior. From BOL:
FILLFACTOR specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild.
PAD_INDEX = { ON | OFF } Specifies index padding. The default is OFF.
ON - The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified -The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.
For the clustered index, as the leaf level of the index is the data, FILLFACTOR determines free space for inserts or updates that increase the row size.
For clustered index on a column with an every increasing value ( identity, NEWSEQUENTIALID or current_timestamp) , since inserts are appended, only free space for updates that increase the row size needs to be specified and free space for intermediate-level index is not needed.
In this scenario, set FILLFACTOR appropriately and PAD_INDEX = OFF.
For clustered index on a column without an every increasing value , since inserts are not appended, set FILLFACTOR appropriately and PAD_INDEX = ON
Use the function sys.dm_db_index_physical_stats to determine the degree of fragmentation.
SQL = Scarcely Qualifies as a Language
February 18, 2008 at 8:47 pm
Jeff Moden (2/17/2008)
How does one determine if a page split has occured during an update?
I don't know how to retrospectively figure it out. That being said - if I want to test it - I either turn on the page splits/sec metric in perfmon, or I just run "snapshots" of DBCC SHOWCONTIG. Paul Randal has some way to tell using DBCC PAGE, but that's all greek to me, and since it's undocumented....
----------------------------------------------------------------------------------
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?
February 18, 2008 at 10:59 pm
To retrospectively figure it out, as I wrote earlier:
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.
SELECT avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, @index_id, NULL , 'DETAILED');
Whether or not page splits are occurring is not as much the issue, as whether they are occurring at a statistically significant rate, and if that is the case, you will need to lower your fill factor.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 19, 2008 at 1:59 am
You also may want to consider making your primary key ID column non-clustered and define your clustered index on a more suitable column, eg. a record timestamp.
Queries involving a *range* of values on a certain column/columns, such as a datetime range, benefit greatly from a clustered index defined on that column/columns.
Defining your PK as non-clustered means that page splits due to table UPDATES will NEVER happen - an update of a varchar column will not affect the stacking of the PK pages.
Personally, I find that defining an identity column PK as clustered is wasting a precious resource, unless this is the ONLY index you think you will EVER need on that table.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 19, 2008 at 4:46 am
I do agree that primary key clustered is not a good suggestion for an identity column, instead this can be done as primay key nonclustered and other valid column with clustered index. (so, everything wud be a index seek) One doubt here, suppose it is an retail bank customer search, we ll have to take last name for clustered....:w00t: Your suggestions please...
Also, thanks for the information regarding the PAD_INDEX... as it says it does a defragmentation at the leaf level alone, if it is OFF. But i guess, this will not solve the problem as there will be empty space in all the data pages which will be unused other than the last data page.
Now, one more question here, if we are having identity column as primary key clustered and sme other columns (say 2) as non clustered indexes, is it OK that we do a INDEXDEFRAG instead of DBREINDEX, if the environment is big? and of course a scheduled DBREINDEX in 4 or 6 months... I know i am asking too much... this wud be the last for index... :hehe:
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply