Fillfactor at a column level ?
-
Fillfactor seems to me to be more "table related" rather than "column related". Example, if I have a table of US States (Alaska, Delaware, Connecticut, etc...) with a surrogate identity key...
Create Table USStates(
IDState int identity(1,1),
StateName varchar(255)
)
I want to:
- make the INT ID column as the Primary Key with an index
- place a unique constraint(which is an index) on the state name to make sure I don't have two or more "Alaska's".
Now, since this table is VERY static - no new states planned - I want the fillfactor to be 100% and save space. But really, this information is "Table Related" and not "Column Related" - it relates to "How many states in total will be inserted". But, I need to specify fillfactor at the index level - in this case twice. Why is this?
- B
-
If you are referring to the fill factor of the index - that is not a column but a separate object. This is to handle data changes in the index just like in a table.
J.
-
Thanks, but missing my point somewhat ...
Whatever it "is" (I know it's not a column but an index on a column), an object, whatever you wish to call it - my decision to change fillfactor is based soloely on the information in the table as a whole. Not based on individual columns, indexes, objects, etc.. within the table.
US States are static so no matter how many indexes I have, I want them all to be 100% fillfactor. So, why not have fillfactor at the table level ? I'm hoping there is some good reason which will help me understand the granularity of fillfactor specs.
- B
-
Fill factor refers to PAGE level.
An index is a structure (BTREE) made up of PAGES, A heap is a table with no clustered index made up of PAGES and a table with a cluestered index is also made up of PAGES!
So if you have a table with a CLUSTERED index on it and you specify a fill factor you are indeed specifying the table level fill factor!!!
If your table is not clustered there is no way to specify fill factor on the "table"
Hope it helps.
* Noel -
Thanks again Noel - I just replied to you on another thread
But, still missing my point a bit. I understand the difference between clustered indexes and heaps - data residing at the leaf level etc...
So perhaps I'll restate my question.
If I have two(2) Non-Clustered indexes on a static table, when/why would I have a different fill factor for them? Or, if I had 32 Non-Clustered indexes - does not matter. Just seems to me that FillFactor is ultimately a table level item that relates to ALL indexes on the table.
Thanks - B
-
Ok back to square one.
If your table is a HEAP you CAN'T specify fill factor at table level.
If your table is CLUSTERED you CAN!!
The Fill factor you specify in how many indexes you have is only controlling the inxexed structures *not the table*
Better,now ?
* Noel -
Thanks but not better yet...
I understand that I "cannot" do such a thing on a heap, or for that matter on a clustered index either - I still specify things at an index level.
Let me restate one other way. If I have one clustered index on the USStates table and 2 non-clustered indexes as well. Why would my fillfactor for the clustered index ever be different from my non-clustered fillfactors ? In Management Studio I can do such a thing - specify 100% fill for the Clustered index, 25% for one on the non-clustered, and 50% for the remaining non-clustered. Why would I do such a thing? I want them all to be 100% all of the time - or do I? And if I do not, why would I not do such a thing?
I'm not trying to argue with you Noel, I just don't think I'm explaining myself clearly enough.
- B
-
Billy,
I think your question is... "Under what circumstances should a table have different fillfactors for different indexes?" For example, 100% FF on primary key pkid, 90% FF on FirstName, 50% FF on LastName, etc.
If that is the question, the determination is based on data changes, including inserts, deletes, and updates.
If a table is static, 100% for all. If a table only has inserts, then maybe one FF across the board (80% for all indexes, for example). If a table has lots of updates on particular columns, you may want to lower the FF on those. However, if you have a dtInsertDate column with an index, it's probably safe to keep it at 100% since its value will never change.
Hope I understood and hope my "strategy" makes sense.
Regards,
Rubes -
A clustered index *is* the data itself so when you specify the fill factor for it you *ARE* specifying the the *table* fill factor.
Fill factor is a completely optional thing, in static tables as yours you want *all* indexes with fill factor = 100% because that minimizes the space that those structures use on disk. In tables that have heavy insert/delete/update load you want to change it so that the page splits do not get in the way with your performance.
I am not argueing with you, I am just not sure that I understand your doubts.
* Noel -
I think Rubes is on to me... and I "kinda" understand his date example.
Rubes makes sense to me in that not only are inserts and deletes important at the ROW level, but per COLUMN the data may get much wider or thinner after an update - nothing to do with inserts or deletes - updates impact as well. Thanks Rubes and thanks Noel as well for your help.
Below is a REAL table I created with different fillfactors on three columns. Fillfactors are 100, 99, and 27 and I could not understand why I would do such a thing - or "be allowed" to do such a thing but Rubes made sense to me.
States will not be inserted or deleted, nor will state names change. So, fill of 100 would be nice for the Primary Key(ID_USState) and the State Name(StateName), but perhaps 80% for the Population column as the population IS dynamic and will get updated each year after a census per say.
I don't know - do I get it ?
CREATE
TABLE [USStates](
[ID_USState] [tinyint]
IDENTITY(1,1) NOT NULL,
[StateName] [varchar]
(50) NOT NULL,
[StatePopulation] [int]
NOT NULL,
[StateIsPretty] [bit]
NOT NULL,
CONSTRAINT [PK_USStates] PRIMARY KEY CLUSTERED
(
[ID_USState]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
)
ON [PRIMARY]
GO
CREATE
NONCLUSTERED INDEX [IX_NC_USStates_StatePopulation] ON [USStates]
(
[StatePopulation]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 27) ON [PRIMARY]
GO
CREATE
UNIQUE NONCLUSTERED INDEX [IX_NCU_USStates_StateName] ON [USStates]
(
[StateName]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-
Bill, you got it!
The only thing is that the "columns" fill factor is not applied to the "table column" is applied to the INDEX BTREES that you created.
* Noel -
Yes, I think you get it.
I think fillfactor is one of those areas where you can definitely answer with "it depends". Low fillfactors avoid page splits and fragmentation. Higher fillfactors take up less space and provide better read performance. Somewhere in between is the right fillfactor, coupled with the question of how often do you reindex? In your example above, population will definitely change. But how often will it be updated in the table? Once a year, month, week? Reindexing straightens that out, so a high fillfactor would be fine.
Also, note that the clustered index is the most critical in regards to fillfactor as it is the most expensive page split.
Regards,
Rubes -
Again thanks to both of you...
I don't know why but my mind simply discarded the possibilty of "Updates" making changes to indexes(pages). I was stuck in an Insert/Delete only mentality but knew in the back of my head that there must be a reason to apply a fillfactor at the more granular level which SQL Server allows. Just didn't know why but now I do - and - it clearly will impact the way I index tables and assign fillfactors.
Regards
B -
One other thought about your table example - given the nature of the content, just how big is this table going to be? Other than the non-indexed column, you've suggested that the most the table would be updated would be annually. That sounds awfully static to me. Also, the last time I looked, the US only had 50 states. Allowing for territories, you're still looking at less than 60 RECORDS.
Question: Doesn't TOTAL table size factor in for considerations of fill factors? It seems to me that you could establish a fill factor of 1% and still have everything loaded in one page. (ok, I'm exaggerating, but you get the idea...)
-
You're correct Steve and thanks for the comments.
However, my "States" example was 100% pure example. It exists only to show that someone can have multiple indexes on a table, with each index having a completely different fillfactor. I simply wished to know why someone would have different factors on one table.
As for the size of the actual table and if indexing was really required in the first place etc, that's another story.
- B
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply