January 16, 2007 at 2:54 pm
Hi All,
I am just wondering if you could tell me how/where to see what the current fill factor of an index is?
Also, How/what measures are taken to determine whether the current fill factor is enough or not?
Thanks,
TK
January 16, 2007 at 3:02 pm
Read up on dbcc showcontig. Fill factor needs to take into account what the clustered key is, and what kind of insert/update/delete activity is going on in the database. For instance, if the table has an Identity as the clustered key then I make my fill factor 100%. There's no reason to have free space on any of the currently used pages since no insert will ever happen on those pages. There are plenty of threads on this topic.
Tom
January 16, 2007 at 5:13 pm
Thanks. I did find this article, and it's pretty good. -
http://www.quest-pipelines.com/newsletter-v5/0204_B.htm
The only thing is I want to see how to find the current fill factor size? This is because the database has been handed over to me and there is no documentation telling what the fill factor was set to - overall database and for individual tables.
January 17, 2007 at 1:21 am
If you script the index any fill factor will be included in the script. You can also examine index properties from within EM to see this.
Setting fill factors is an art in its own right, to be honest if you're asking this question then you probably shouldn't be even thinking about fill factors.
Tom, you're actually wrong as the clustered index is actually the entire row, regardless of how many columns of the table are defined as the clustered index, so the assumption that as the key is a sequential non updateable column there can be no fragmentation is flawed.
To test this create a table of an identity + a 1000 varchar with only 20 chars used of the varchar, this will pack around 300 rows per page so you'd need to populate with at least 5k rows.
If you then start increasing the varchar column up to 1000 chars you will get fragmentation.
Agreed if you only ever insert then there will be no fragmentation, but that's a heap ( with or without a clustered index )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 17, 2007 at 6:35 am
Thanks for the good info. Although if you do just insert you will get fragmentation, only it will come at the end of the page. As the last page fills it will split and continue to split. Thanks again, I wouldn't have thought about packing of the varchar fields.
Tom
January 17, 2007 at 7:32 am
actually I didn't explain myself very clearly I now see. I should have said if you then update selected rows increasing the varchar from 20 to 1000 you'll get fragmentation due to splits.
This is known as deferred vs inplace updates , an inplace update does not create a row, a deferred update has to delete the existing row and create a new row, this is far more expensive in terms of locking, resource, contention etc. and of course creates fragmentation.
I have to say that I can't remember when the last DBA I interviewed for a role could describe or explain this. ( or knew what it was )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 4, 2020 at 10:31 pm
select * from sys.indexes where fill_factor<>0
October 25, 2022 at 2:43 am
select * from sys.indexes where fill_factor<>0
Indeed from Microsoft Docs ---
"> 0 = FILLFACTOR percentage used when the index was created or rebuilt."
The key term to notice is 'rebuilt.' So if in your source control you have a way it should be, the view here will tell you what it actually is.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply