filter data by row number

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Filter

I need a query that returns every n record from the database and the last record.

For every n-th is easy, but I need a

WHERE Index % n = 0

but I need a last one too.

Any Help.

How about a UNION statement? The new code you want is in green.

SELECT Col1, idx

FROM MyTable

WHERE idx % 12 = 0

UNION

SELECT Col1, idx

FROM MyTable

WHERE idx = (SELECT MAX(idx) FROM MyTable)

|||

In 2005, you can do something like:

select *
from (

select row_number() over (order by object_id) as rowNum,
object_id
from sys.objects

) as objects
where rowNum % 5 = 0
or rowNum = (select count(*) from sys.objects)

There is a (more messy) way to do it in 2000 also. The key is that tables don't have order, so you will need to define an order in order to calculate N.

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

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 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?
>
>.
>

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.
Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.sql

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.
>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
>> hi,
>> i have a table with IDENTITY field.
>> i set a CLUSTERED UNIQUE index on this field.
>> i wonder whether is it right to set this index's Fill-
>> factor to 100?
>> i though that if the values of this field are always
auto-
>> incremented so there would never be a need to split
places
>> between records, so there is no need to save space for
new
>> records(or updated records) that might need to be placed
>> between any other prior inserted records.
>> is it true?
>> is it the optimal choise for this case?
>> thanks,
>> edo.
>
>.
>|||Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
> >hi,
> >
> >thanks for replying me.
> >
> >increasing (decreasing too?) the WIDTH of a row because of
> >an update is a new information for me, and i would also
> >say it is quite shocking for me.
> >
> >i allways thought table structure is a fixed-width, so the
> >space for all of the fields is allocated in the same time
> >and PLACE in advance for each inserted record.
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> >i guess it's a long story to explain why a clustered index
> >is actually needs to contain ALL fileds ...?
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.

fill factor value

I do have table like this

(

uniqueid int

,scandate datetime

,courseno varchar(10)

)

this table has two indexes:

primary key clustered index on uniqueid, scandate

non-clustered index on courseno asc, uniqueid asc, courseno asc

this table can have 10 million records. there are no updates to this table. only data is inserted and selected from this table.

when i created these indexes the default fill factor value is 0. should i change the fill factor.

Thanks

sandeep, see BOL http://msdn2.microsoft.com/en-us/library/ms177459.aspx

Quick answer is probably OK, but read the article to determine if something in your environment (disk space limitation, etc) might want you to change it...

Fill Factor settings

In 2005 Database settings....default fill factor 0

When this is 0 and and index rebuild is performed does that mean there is no space for the indexes to grew and issues a page split immediately.

Would it be better to adjust the fill factor to 5

Thanks

It all depends on how your database is used.
This Books Online 2005 topic discusses the options: Fill Factor

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegroups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !sql

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegroups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !
A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegr oups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !

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% b
y 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...
>|||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 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...[vbcol=seagreen]
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
>|||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...
>

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

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
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...
>
|||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 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...[vbcol=seagreen]
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
|||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...
>
sql

Fill factor for ever increasing clustered index

Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> If I defined the clustered index on identity column, what will be the
> differences between fill factor 100% and 70%?
>
Most people forget about this scenario when choosing to go with 100% fill
factors.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on
> their pages.
> A very common example is "notes" type columns which are very often
> widened, causing page splits even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space
> otherwise your updates might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>
|||Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ovperrb5uhvd1@.corp.supernews.com...
> Most people forget about this scenario when choosing to go with 100% fill
> factors.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||1) it is my understanding that new pages will take the fill factor.
2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.
3) That is my understanding as well.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Yes, if for the case of INSERT only, when the last page is full, any
> further
> INSERT will be put into the new page. So there is no difference between
> 10%,
> 20% or 70% or even 100%?
> If there is DELETE, so there will be some holes in the page? Because the
> column is ever increasing. The holes will never be filled unless
> re-indexing? If re-indexing, then this is my original question, does the
> fill factor affect it?
> In my understanding is when the page is splitted, the new page will be
> filled by determined by fill factor.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
> on
> answer
> INSERTs.
> fill
> on
> space
> the
>
|||No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13p1m1aqmcpbr15@.corp.supernews.com...
> 1) it is my understanding that new pages will take the fill factor.
> 2) Deletes will leave holes in their respective pages. Those holes will
> be 'filled' when you reindex/defrag.
> 3) That is my understanding as well.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>
|||DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an
> index. It would be pointless to always keep a page 70% full for example as
> you are inserting new rows. If a page splits approximately half of the
> rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13p1m1aqmcpbr15@.corp.supernews.com...
>
|||Well that isn't really a split then. That's business as usual.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
> ... unless the page splits at the "end" (the last row according to the
> index). Then now pages are moves, instead just a new page is linked into
> the linked list, at the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then. That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>

Fill factor for ever increasing clustered index

If I defined the clustered index on identity column, what will be the
differences between fill factor 100% and 70%?Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> If I defined the clustered index on identity column, what will be the
> differences between fill factor 100% and 70%?
>|||In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
ever increasing so there will be no page splits in such an index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
> Alan
> Fillfactoe specifies how full each page should be. 70 means 70%
> full, 100 means 100% full. The only special case is 0, which means the leaf
> level is full, but there is room for one or two rows per page in the upper
> levels of the index tree.
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>|||Except where rows are updated with wider values so they no longer fit on
their pages.
A very common example is "notes" type columns which are very often widened,
causing page splits even on ID based CIXs.
For these scenarios, you might still want to leave some fillfactor space
otherwise your updates might be slowed down badly
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
> In addition, I recommend 0 (same as 100) or 100 for an index on an
> identity column. The value is ever increasing so there will be no page
> splits in such an index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||> Except where rows are updated with wider values so they no longer fit on their pages.
Good point, Greg. One should consider what other columns are in the index (for a cl index, of course
all column) and whether any of those column risk being "widened" by updates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on their pages.
> A very common example is "notes" type columns which are very often widened, causing page splits
> even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space otherwise your updates
> might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
>> ever increasing so there will be no page splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the leaf
>> level is full, but there is room for one or two rows per page in the upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ovperrb5uhvd1@.corp.supernews.com...
> Most people forget about this scenario when choosing to go with 100% fill
> factors.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Except where rows are updated with wider values so they no longer fit on
>> their pages.
>> A very common example is "notes" type columns which are very often
>> widened, causing page splits even on ID based CIXs.
>> For these scenarios, you might still want to leave some fillfactor space
>> otherwise your updates might be slowed down badly
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>>
>|||Most people forget about this scenario when choosing to go with 100% fill
factors.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on
> their pages.
> A very common example is "notes" type columns which are very often
> widened, causing page splits even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space
> otherwise your updates might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, if for the case of INSERT only, when the last page is full, any further
INSERT will be put into the new page. So there is no difference between 10%,
20% or 70% or even 100%?
If there is DELETE, so there will be some holes in the page? Because the
column is ever increasing. The holes will never be filled unless
re-indexing? If re-indexing, then this is my original question, does the
fill factor affect it?
In my understanding is when the page is splitted, the new page will be
filled by determined by fill factor.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Yes, so this is most of the answer to the OP. I believe he was wondering
on
> an increasing value if there was any reason to go with 70 vs 100. The
answer
> has to do with what else are you going to do with the table besides
INSERTs.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ovperrb5uhvd1@.corp.supernews.com...
> > Most people forget about this scenario when choosing to go with 100%
fill
> > factors.
> >
> >> Except where rows are updated with wider values so they no longer fit
on
> >> their pages.
> >>
> >> A very common example is "notes" type columns which are very often
> >> widened, causing page splits even on ID based CIXs.
> >>
> >> For these scenarios, you might still want to leave some fillfactor
space
> >> otherwise your updates might be slowed down badly
> >>
> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
> >> identity column. The value is ever increasing so there will be no page
> >> splits in such an index.
> >>
> >> Alan
> >> Fillfactoe specifies how full each page should be. 70 means 70%
> >> full, 100 means 100% full. The only special case is 0, which means
the
> >> leaf
> >> level is full, but there is room for one or two rows per page in the
> >> upper
> >> levels of the index tree.|||1) it is my understanding that new pages will take the fill factor.
2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.
3) That is my understanding as well.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Yes, if for the case of INSERT only, when the last page is full, any
> further
> INSERT will be put into the new page. So there is no difference between
> 10%,
> 20% or 70% or even 100%?
> If there is DELETE, so there will be some holes in the page? Because the
> column is ever increasing. The holes will never be filled unless
> re-indexing? If re-indexing, then this is my original question, does the
> fill factor affect it?
> In my understanding is when the page is splitted, the new page will be
> filled by determined by fill factor.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
> on
>> an increasing value if there was any reason to go with 70 vs 100. The
> answer
>> has to do with what else are you going to do with the table besides
> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>|||No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13p1m1aqmcpbr15@.corp.supernews.com...
> 1) it is my understanding that new pages will take the fill factor.
> 2) Deletes will leave holes in their respective pages. Those holes will
> be 'filled' when you reindex/defrag.
> 3) That is my understanding as well.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>|||DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an
> index. It would be pointless to always keep a page 70% full for example as
> you are inserting new rows. If a page splits approximately half of the
> rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||> If a page splits approximately half of the rows stay on the existing page and half on the new
> page.
... unless the page splits at the "end" (the last row according to the index). Then now pages are
moves, instead just a new page is linked into the linked list, at the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
> splits approximately half of the rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||Well that isn't really a split then:). That's business as usual.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page
>> and half on the new page.
> ... unless the page splits at the "end" (the last row according to the
> index). Then now pages are moves, instead just a new page is linked into
> the linked list, at the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing
>> page and half on the new page.
>> ... unless the page splits at the "end" (the last row according to the
>> index). Then now pages are moves, instead just a new page is linked into
>> the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes
>> will be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference
>> between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with
>> > 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on
>> >> an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||> Well that isn't really a split then:). That's business as usual.
Tues, but it is sometimes referred to as a " page split" (even though it really isn't such). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page and half on the new
>> page.
>> ... unless the page splits at the "end" (the last row according to the index). Then now pages are
>> moves, instead just a new page is linked into the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
>> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
>> splits approximately half of the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||If the page is full and a new record is insert, no matter at the beginning,
middle or end, then page split is the result. Then if the fill factor is for
example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
has effect?|||Fillfactor only applies when building an index. It has no affect on page
splits.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> If the page is full and a new record is insert, no matter at the
> beginning,
> middle or end, then page split is the result. Then if the fill factor is
> for
> example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
> has effect?
>|||Thanks.
So if I rebuild the index, do 'all' pages will be filled up determined by
the fill factor?
eg. 70%.
All pages will be filled up 70 %?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
> Fillfactor only applies when building an index. It has no affect on page
> splits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> > If the page is full and a new record is insert, no matter at the
> > beginning,
> > middle or end, then page split is the result. Then if the fill factor is
> > for
> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
factor
> > has effect?
> >
> >
>|||As far as possible, all leaf level pages will be filled 70%. The upper
levels of indexes will be filled almost full, unless you ALSO specify the
PAD_INDEX option.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:%234AgUbjYIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Thanks.
> So if I rebuild the index, do 'all' pages will be filled up determined by
> the fill factor?
> eg. 70%.
> All pages will be filled up 70 %?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
>> Fillfactor only applies when building an index. It has no affect on page
>> splits.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> > If the page is full and a new record is insert, no matter at the
>> > beginning,
>> > middle or end, then page split is the result. Then if the fill factor
>> > is
>> > for
>> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
> factor
>> > has effect?
>> >
>> >
>>
>