Format Alignment: fillfactor

Showing posts with label fillfactor. Show all posts

Tuesday, March 27, 2012

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 droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
Hi
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 droptable.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 droptable.com
> http://www.droptable.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 droptable.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 droptable.com
> http://www.droptable.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 droptable.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.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:[vbcol=seagreen]
>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).
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.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:[vbcol=seagreen]
>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.
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.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:[vbcol=seagreen]
>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.
>[quoted text clipped - 24 lines]
Message posted via http://www.droptable.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 droptable.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:
> --
> Message posted via http://www.droptable.com
>

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 droptable.com
http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 clust
ered 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 droptable.com" <u3288@.uwe> wrote in message news:76a76d9a857ab@.uwe...[vbcol
=seagreen]
>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 droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||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 droptable.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.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 t
he
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:[vbcol=seagreen]
>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 clus
tered index (i.e., your
>data). You say it is an identity column. Assuming your inserts don't specif
y a value for the
>identity column (sing SET IDENTITY_INSERT), something rarely done, then eac
h 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).
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1|||As for UPDATE, you cannot change the identity column value with an UPDATE, s
o 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 ea
sy to see using DBCC
SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebui
ld the clustered index.
But there's no use to leave empty room on the pages since we already determi
ned that you don't get
external fragmentation over such index.
Note that we have discussed an (clustered) index over an identity column. Th
e 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 droptable.com" <u3288@.uwe> wrote in message news:76b38ca86d53a@.uwe...[vbcol
=seagreen]
> 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:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||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 indexe
s
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 onl
y
perform INSERTS, and DELETEs are very rare? And the UPDATES, even though the
y
might effect the non-clustered indexes, they will have no effect on the
clustered index?
Tibor Karaszi wrote:[vbcol=seagreen]
>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 are
n't as full as they could
>be. In the end, you end up with more pages that you would needed. This is e
asy to see using DBCC
>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebu
ild the clustered index.
>But there's no use to leave empty room on the pages since we already determ
ined that you don't get
>external fragmentation over such index.
>Note that we have discussed an (clustered) index over an identity column. T
he table can have other
>NC indexes, over other column combinations. And those B-trees will of cours
e have different
>fragmentation chacteristica.
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...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 somethin
g 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 pag
es. The end result is
... external fragmentation. How frequent this will occur, I can't say, of c
ourse. 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 fr
agmentation increases
over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76bc0008d16b1@.uwe...[vbcol
=seagreen]
> 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 inde
xes
> over an identity column. If I have a clustered index over an identity colu
mn,
> I can be confident in giving such an index a FILLFACTOR of 100, since we o
nly
> perform INSERTS, and DELETEs are very rare? And the UPDATES, even though t
hey
> might effect the non-clustered indexes, they will have no effect on the
> clustered index?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||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 o
f,
or are you talking about an UPDATE on one of the non-clustered indexes?
Tibor Karaszi wrote:[vbcol=seagreen]
>Very close. One situation that can occur is that an UPDATE result in the ne
w row don't fit on the
>current page (say you change a column value from something small to somethi
ng 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 pa
ges. The end result is
>... external fragmentation. How frequent this will occur, I can't say, of c
ourse. Possibly, or even
>probably not frequent enough to worry about. But if you are worried about i
t, I suggest to start
>with 100% and then monitor fragmentation level for the cl index to see if f
ragmentation increases
>over time.
>
>[quoted text clipped - 24 lines]
Message posted via http://www.droptable.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 droptable.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:
> --
> Message posted via http://www.droptable.com
>sql

Monday, March 26, 2012

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
>

FillFactor considerations

I have a table with about 200 million rows of data. I add a couple million rows of data each week to the table in a single load process. The table is used for reporting purposes only and there are never (not intentionally at least) any updates or deletes to the table. The data is always being added to the "end" of the table with the new AsOfDate being the main factor in the clustered index.

My question is this: Since I'm not "inserting" rows that would split pages, should I have my FILLFACTOR for the table set to 100, or am I missing something? I obviously want to save physical hard drive space, but I also don't want to slow down the import process.

BTW, I'm using SQL2000

If you are certain that your data loads is 'always' putting the new data at the end of the clustered index, you may have a good case for NOT leaving empty space on the data pages -and a FILLFACTOR of 100 would be in order.

Fillfactor at 10 took all my free space

I do a lot of bcping and was trying to speed it up by changing the
fillfactor on a clustered index, and now the database has doubled in size
from 200 to 400 gb and there is no free space left but the process keeps
running. Short of restore, what can I do to get the process to end?Have you tried killing the process from QA ? Find out the
spid of the running process and then open up another
window in QA and issue KILL <spid> command to end the
process.
hth.
>--Original Message--
>I do a lot of bcping and was trying to speed it up by
changing the
>fillfactor on a clustered index, and now the database has
doubled in size
>from 200 to 400 gb and there is no free space left but
the process keeps
>running. Short of restore, what can I do to get the
process to end?
>
>.
>

FillFactor and Intermeditory Pages

hi all,
I am trying to understand more about fillfactor. After reading few articiles
on internet, I came to know that, the fillfactor is effective only on leaf
node/page. Does it mean that fillfactor will not have any effect on the
intermediatory pages.
thanks
pradeep_tpHi
You need to understand that lower value of FILL FACTOR leaves more empty
space on data page
Actually FILLFACTOR option means how full is your data page
In my opinion BOL has pretty good explanation about FILL FACTOR.
What is your concern?
"pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
news:DF4F5805-5BFD-4E46-A26C-EA3758FB2FA7@.microsoft.com...
> hi all,
> I am trying to understand more about fillfactor. After reading few
> articiles
> on internet, I came to know that, the fillfactor is effective only on leaf
> node/page. Does it mean that fillfactor will not have any effect on the
> intermediatory pages.
> thanks
> pradeep_tp|||Hi Uri,
My concern is knowledge. I want to fully undestand it fill factor. According
to what you say, fillfactor is effective only for data pages and not for
index pages. Am i correct. Also what is BOL?
pradeep
"Uri Dimant" wrote:
> Hi
> You need to understand that lower value of FILL FACTOR leaves more empty
> space on data page
> Actually FILLFACTOR option means how full is your data page
> In my opinion BOL has pretty good explanation about FILL FACTOR.
> What is your concern?
>
> "pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
> news:DF4F5805-5BFD-4E46-A26C-EA3758FB2FA7@.microsoft.com...
> > hi all,
> >
> > I am trying to understand more about fillfactor. After reading few
> > articiles
> > on internet, I came to know that, the fillfactor is effective only on leaf
> > node/page. Does it mean that fillfactor will not have any effect on the
> > intermediatory pages.
> >
> > thanks
> > pradeep_tp
>
>|||Hi
The fill factor is implemented only when the index is created
BOL says
FILL Factor
An attribute of an index that defines the amount of free space on each page
of the index. FILLFACTOR accommodates future expansion of table data and
reduces the potential for page splits. FILLFACTOR is a value from 1 through
100 that specifies the percentage of the index page to be left empty.
"pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
news:745FF257-569B-4BCA-8BE1-A919129EA44D@.microsoft.com...
> Hi Uri,
> My concern is knowledge. I want to fully undestand it fill factor.
> According
> to what you say, fillfactor is effective only for data pages and not for
> index pages. Am i correct. Also what is BOL?
> pradeep
> "Uri Dimant" wrote:
>> Hi
>> You need to understand that lower value of FILL FACTOR leaves more
>> empty
>> space on data page
>> Actually FILLFACTOR option means how full is your data page
>> In my opinion BOL has pretty good explanation about FILL FACTOR.
>> What is your concern?
>>
>> "pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
>> news:DF4F5805-5BFD-4E46-A26C-EA3758FB2FA7@.microsoft.com...
>> > hi all,
>> >
>> > I am trying to understand more about fillfactor. After reading few
>> > articiles
>> > on internet, I came to know that, the fillfactor is effective only on
>> > leaf
>> > node/page. Does it mean that fillfactor will not have any effect on the
>> > intermediatory pages.
>> >
>> > thanks
>> > pradeep_tp
>>|||Yes, fillfactor applies only to leaf level. You do have the PAD_INDEX option which specifies that
the fillfactor value you specify also should be applied to the non-leaf level. But by default,
fillfactor is only applied at leaf level. In 2005, you can specify PAD_INDEX when you do index
rebuild, in earlier versions, you only had the PAD_INDEX option when you create the index (as far as
I remember).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
news:DF4F5805-5BFD-4E46-A26C-EA3758FB2FA7@.microsoft.com...
> hi all,
> I am trying to understand more about fillfactor. After reading few articiles
> on internet, I came to know that, the fillfactor is effective only on leaf
> node/page. Does it mean that fillfactor will not have any effect on the
> intermediatory pages.
> thanks
> pradeep_tp|||>Also what is BOL?
BOL is short for SQL Server Books Online, the documentation that comes with
the product.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
news:745FF257-569B-4BCA-8BE1-A919129EA44D@.microsoft.com...
> Hi Uri,
> My concern is knowledge. I want to fully undestand it fill factor.
> According
> to what you say, fillfactor is effective only for data pages and not for
> index pages. Am i correct. Also what is BOL?
> pradeep
> "Uri Dimant" wrote:
>> Hi
>> You need to understand that lower value of FILL FACTOR leaves more
>> empty
>> space on data page
>> Actually FILLFACTOR option means how full is your data page
>> In my opinion BOL has pretty good explanation about FILL FACTOR.
>> What is your concern?
>>
>> "pradeep_TP" <pradeepTP@.discussions.microsoft.com> wrote in message
>> news:DF4F5805-5BFD-4E46-A26C-EA3758FB2FA7@.microsoft.com...
>> > hi all,
>> >
>> > I am trying to understand more about fillfactor. After reading few
>> > articiles
>> > on internet, I came to know that, the fillfactor is effective only on
>> > leaf
>> > node/page. Does it mean that fillfactor will not have any effect on the
>> > intermediatory pages.
>> >
>> > thanks
>> > pradeep_tp
>>

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.
Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>
|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>>I agree with the others that it is probably not related to the fill
>>factor. I would guess blocking first, disk queues next (checkpoints etc.)
>>and maybe even file auto growths. Make sure there is plenty of free space
>>in the data and log files.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>>
>

Fillfactor

we have 2 servers with similar tables and columns and indexes with fillfactor
0. My replication is very slow during daytime and comes to normal at night
times.when i check the reads and writes both are more that 90 % high but when
i check the page splits it shows nothing.what do i need to do to fasten my
replication.
Do i need to add fillfactor?
i didn't see any page splits though
Thanks
Pardhi
Message posted via http://www.droptable.com
If you have no page splits, then I'd start monitoring for blocking issues
during synchronization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul i didn't see any blocking either
Paul Ibison wrote:
>If you have no page splits, then I'd start monitoring for blocking issues
>during synchronization.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
Message posted via http://www.droptable.com
|||Then perhaps it's just one of the the usual processor/memory/disk access
issues. You could use profiler to monitor these counters and see if this is
the issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Fill factor in dbcc dbreindex

Hi
I am on sqlserver 2000 and my question is on dbcc dbreindex.
If I say dbcc dbreindex('TABLE_NAME')
what is the fillfactor used on index rebuilds.
Documents does not say anywhere about it.
Tks
MangeshHi
The fill factor that was used when the index was originally created. 90% by
default.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> Hi
> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> If I say dbcc dbreindex('TABLE_NAME')
> what is the fillfactor used on index rebuilds.
> Documents does not say anywhere about it.
> Tks
> Mangesh
>|||I believe that default is 100%, unless you change it with sp_configure...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> The fill factor that was used when the index was originally created. 90% by default.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> Hi
>> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> If I say dbcc dbreindex('TABLE_NAME')
>> what is the fillfactor used on index rebuilds.
>> Documents does not say anywhere about it.
>> Tks
>> Mangesh
>|||I have just tried this on SP3 and the default is 80
"Tibor Karaszi" wrote:
> I believe that default is 100%, unless you change it with sp_configure...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > The fill factor that was used when the index was originally created. 90% by default.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> >> Hi
> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> >> If I say dbcc dbreindex('TABLE_NAME')
> >> what is the fillfactor used on index rebuilds.
> >>
> >> Documents does not say anywhere about it.
> >>
> >> Tks
> >> Mangesh
> >>
> >
> >
>
>|||Hi Marc
What exactly did you try?
I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
shown as completely full pages with DBCC SHOWCONTIG.
This seems to support what Tibor said.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
>> I believe that default is 100%, unless you change it with
>> sp_configure...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Hi
>> >
>> > The fill factor that was used when the index was originally created.
>> > 90% by default.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > IM: mike@.epprecht.net
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
>> > in message
>> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> >> Hi
>> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> >> If I say dbcc dbreindex('TABLE_NAME')
>> >> what is the fillfactor used on index rebuilds.
>> >>
>> >> Documents does not say anywhere about it.
>> >>
>> >> Tks
>> >> Mangesh
>> >>
>> >
>> >
>>|||I agree with Kalen and Tibor. The default should be 0 which is 100% full.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ucTTcPFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Hi Marc
> What exactly did you try?
> I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
> shown as completely full pages with DBCC SHOWCONTIG.
> This seems to support what Tibor said.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>>I have just tried this on SP3 and the default is 80
>> "Tibor Karaszi" wrote:
>> I believe that default is 100%, unless you change it with
>> sp_configure...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Hi
>> >
>> > The fill factor that was used when the index was originally created.
>> > 90% by default.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > IM: mike@.epprecht.net
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> > "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote
>> > in message
>> > news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>> >> Hi
>> >> I am on sqlserver 2000 and my question is on dbcc dbreindex.
>> >> If I say dbcc dbreindex('TABLE_NAME')
>> >> what is the fillfactor used on index rebuilds.
>> >>
>> >> Documents does not say anywhere about it.
>> >>
>> >> Tks
>> >> Mangesh
>> >>
>> >
>> >
>>
>

Fill factor in dbcc dbreindex

Friday, March 23, 2012

Fill Factor

Isn't it true that Sql Server does not maintain the fill
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?
Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?
|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?

>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
fill[vbcol=seagreen]
must[vbcol=seagreen]
this[vbcol=seagreen]
significantly
>
>.
>
|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...[vbcol=seagreen]
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
> write problems. It
> you insert the data.
> fill factor. This is
> or read only. Each
> a new page. This
> message
> fill
> must
> this
> significantly
|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:163401c515e7$89283730$a401280a@.phx.gbl...
disk[vbcol=seagreen]
he[vbcol=seagreen]
he[vbcol=seagreen]
work"
>
>.
>

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.
Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.
|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.co...ng_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Tuesday, March 27, 2012

FillFactor on Clustered Index

FillFactor on Clustered Index

Monday, March 26, 2012

FillFactor on Clustered Index

FillFactor considerations

Fillfactor at 10 took all my free space

FillFactor and Intermeditory Pages

FillFactor and Intermeditory Pages

FillFactor and Intermeditory Pages

FILLFACTOR 100 on Empty Table

FILLFACTOR 100 on Empty Table

Fillfactor

Fillfactor

Fillfactor

Fill factor in dbcc dbreindex

Fill factor in dbcc dbreindex

Fill factor in dbcc dbreindex

Friday, March 23, 2012

Fill Factor

Fill Factor

Format Alignment

Blog Archive

About Me