FillFactor on Clustered Index

I have a clustered index which is created on an Identity column.
Let's say it has 10,000 pages and each row is about 200 bytes.
Each day about 1,000 rows are inserted.
Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1Hi
Ok each day it insterts 1000 rows are pretty small amount of data. What
about reading the data? I suggets you haveing 90 for FILLFACTOR.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
data). You say it is an identity column. Assuming your inserts don't specify a value for the
identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
the end of the clustered index. This mean that you will have no page splits and can leave the
fillfactor to default value (100 percent full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Yes Tibor is 100% correct since the clustered index is on an identity
column. But even if it wasn't there is no easy formula per say to determine
the fill factor. It depends a LOT on the values inserted and in what order.
Without any other info you can usually start with 85 or 90 and then watch it
for a while and go from there.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lgHogk3HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Since you didn't mention anything about what non-clustered indexes you
> have (if any) I assume that you are interested in possible fragmentation
> in the leaf level of your clustered index (i.e., your data). You say it is
> an identity column. Assuming your inserts don't specify a value for the
> identity column (sing SET IDENTITY_INSERT), something rarely done, then
> each row you insert goes to the end of the clustered index. This mean that
> you will have no page splits and can leave the fillfactor to default value
> (100 percent full).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.uwe...
>>I have a clustered index which is created on an Identity column.
>> Let's say it has 10,000 pages and each row is about 200 bytes.
>> Each day about 1,000 rows are inserted.
>> Is there a formula that or method to determine the optimal FillFactor?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Thanks Tibor.
I do have non-clustered indexes on this table too, and the inserts will
rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
end of the clustered index. Along with the inserts, there will also be
UPDATES and DELETES.
Would it still make sense to set the clustered index FILLFACTOR at or near
100? Or do the UPDATES and DELETES cause a need for the clustered index
FILLFACTOR to leave more available space on the page?
Tibor Karaszi wrote:
>Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
>you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
>data). You say it is an identity column. Assuming your inserts don't specify a value for the
>identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
>the end of the clustered index. This mean that you will have no page splits and can leave the
>fillfactor to default value (100 percent full).
>>I have a clustered index which is created on an Identity column.
>[quoted text clipped - 3 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
cause the row to "move" (for a cl ix over the identity column).
As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
But there's no use to leave empty room on the pages since we already determined that you don't get
external fragmentation over such index.
Note that we have discussed an (clustered) index over an identity column. The table can have other
NC indexes, over other column combinations. And those B-trees will of course have different
fragmentation chacteristica.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76b38ca86d53a@.uwe...
> Thanks Tibor.
> I do have non-clustered indexes on this table too, and the inserts will
> rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
> end of the clustered index. Along with the inserts, there will also be
> UPDATES and DELETES.
> Would it still make sense to set the clustered index FILLFACTOR at or near
> 100? Or do the UPDATES and DELETES cause a need for the clustered index
> FILLFACTOR to leave more available space on the page?
> Tibor Karaszi wrote:
>>Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
>>you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
>>data). You say it is an identity column. Assuming your inserts don't specify a value for the
>>identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes
>>to
>>the end of the clustered index. This mean that you will have no page splits and can leave the
>>fillfactor to default value (100 percent full).
>>I have a clustered index which is created on an Identity column.
>>[quoted text clipped - 3 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Just to be clear on what you are saying, as we are now getting to close to
the final answer of my question regarding the FILLFACTOR on clustered indexes
over an identity column. If I have a clustered index over an identity column,
I can be confident in giving such an index a FILLFACTOR of 100, since we only
perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
might effect the non-clustered indexes, they will have no effect on the
clustered index?
Tibor Karaszi wrote:
>As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
>cause the row to "move" (for a cl ix over the identity column).
>As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
>be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
>But there's no use to leave empty room on the pages since we already determined that you don't get
>external fragmentation over such index.
>Note that we have discussed an (clustered) index over an identity column. The table can have other
>NC indexes, over other column combinations. And those B-trees will of course have different
>fragmentation chacteristica.
>> Thanks Tibor.
>[quoted text clipped - 20 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
current page (say you change a column value from something small to something big). SQL Server would
now need to split this page and link in a new page in the linked list. Then move 50% of the rows
from the old page to the new page and the row now fit on either of these pages. The end result is
... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
probably not frequent enough to worry about. But if you are worried about it, I suggest to start
with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
over time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76bc0008d16b1@.uwe...
> Just to be clear on what you are saying, as we are now getting to close to
> the final answer of my question regarding the FILLFACTOR on clustered indexes
> over an identity column. If I have a clustered index over an identity column,
> I can be confident in giving such an index a FILLFACTOR of 100, since we only
> perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
> might effect the non-clustered indexes, they will have no effect on the
> clustered index?
> Tibor Karaszi wrote:
>>As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
>>cause the row to "move" (for a cl ix over the identity column).
>>As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they
>>could
>>be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
>>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered
>>index.
>>But there's no use to leave empty room on the pages since we already determined that you don't get
>>external fragmentation over such index.
>>Note that we have discussed an (clustered) index over an identity column. The table can have other
>>NC indexes, over other column combinations. And those B-trees will of course have different
>>fragmentation chacteristica.
>> Thanks Tibor.
>>[quoted text clipped - 20 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Are you meaning an UPDATE on the clustered index itself, such as an Identity
Insert that updates the identity column the clustered index is created off of,
or are you talking about an UPDATE on one of the non-clustered indexes?
Tibor Karaszi wrote:
>Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
>current page (say you change a column value from something small to something big). SQL Server would
>now need to split this page and link in a new page in the linked list. Then move 50% of the rows
>from the old page to the new page and the row now fit on either of these pages. The end result is
>... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
>probably not frequent enough to worry about. But if you are worried about it, I suggest to start
>with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
>over time.
>> Just to be clear on what you are saying, as we are now getting to close to
>> the final answer of my question regarding the FILLFACTOR on clustered indexes
>[quoted text clipped - 24 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via http://www.sqlmonster.com|||If you update any variable length column with a value larger than it was
originally you run the risk of a page split. Since the row will be larger
overall than it was before the update you may not have enough room on the
page if it was 100% (or close to it) full to begin with to hold the revised
row. So this does not have to be on a column with an index, just any
variable length column.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76cc88bc95e2b@.uwe...
> Are you meaning an UPDATE on the clustered index itself, such as an
> Identity
> Insert that updates the identity column the clustered index is created off
> of,
> or are you talking about an UPDATE on one of the non-clustered indexes?
> Tibor Karaszi wrote:
>>Very close. One situation that can occur is that an UPDATE result in the
>>new row don't fit on the
>>current page (say you change a column value from something small to
>>something big). SQL Server would
>>now need to split this page and link in a new page in the linked list.
>>Then move 50% of the rows
>>from the old page to the new page and the row now fit on either of these
>>pages. The end result is
>>... external fragmentation. How frequent this will occur, I can't say, of
>>course. Possibly, or even
>>probably not frequent enough to worry about. But if you are worried about
>>it, I suggest to start
>>with 100% and then monitor fragmentation level for the cl index to see if
>>fragmentation increases
>>over time.
>> Just to be clear on what you are saying, as we are now getting to close
>> to
>> the final answer of my question regarding the FILLFACTOR on clustered
>> indexes
>>[quoted text clipped - 24 lines]
>>>
>>> Is there a formula that or method to determine the optimal
>>> FillFactor?
> --
> Message posted via http://www.sqlmonster.com
>