I have a table in a database that has six indexes.
Four of those indexes have a FillFactor = 90.
Two of those indexes have a FillFactor = 0.
My Reads in relation to Writes are approximately 100:1.
Does it make sense that some indexes would be set at 90 and other indexes set
at zero? It seems like, regardless of the Read/Write ratio that all of the
FillFactors should be the same. Please help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Where did you get these numbers? From sysindexes or from DBCC SHOWCONTIG (or if 2005, the 2005
counterparts)? Or from the CREATE INDEX scripts?
Note that 0 is same as 100%.
Also the value specified when index is created is one thing. This value isn't maintained
automatically by the system (that would defeat the purpose of leaving free space). Not until you
re-apply the value (by reorg or rebuild). See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for good info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649d3ad7c7a1a@.uwe...
>I have a table in a database that has six indexes.
> Four of those indexes have a FillFactor = 90.
> Two of those indexes have a FillFactor = 0.
> My Reads in relation to Writes are approximately 100:1.
> Does it make sense that some indexes would be set at 90 and other indexes set
> at zero? It seems like, regardless of the Read/Write ratio that all of the
> FillFactors should be the same. Please help.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||Don't forget that FillFactor=0 is equivalent to 100, so the 2 values are not
so disimilar. Perhaps a blanket value of 95 would be suitable - it really
depends on whether your writes are causing page-splits (see dbcc showcontig
/ sys.dm_db_index_physical_stats for more info).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||I am on SQL2K and got these numbers from sysindexes.OrigFillFactor.
Sorry if I was not clear. Let me try to explain better.
My question is NOT that some indexes were created explicitly at 90 and others
defaulting with zero. It just seems to me that you would want all your index
fillfactors to be the same. For example, if I have determined that 90 is the
optimal fillfactor for my environment, would I not want all my indexes to be
90?
I understand that if the default index fillfactor is set to zero, and your
optimal fillfactor is 90, then you need to be careful when creating new
indexes and explicitly specify 90 as the fillfactor, or else change the
default from zero to 90. So I can see that if the default is zero and the
optimum is 90, where some indexes might end up as 90 and others are at zero
due to sloppiness.
So, getting back to the root of my question, if my optimal fillfactor is 90,
then am I correct in stating that all my indexes should be at 90? If my
optimal fillfactor is 90, then to me, it does not make sense that there are
some indexes in my environment set at zero. Is that correct?
Tibor Karaszi wrote:
>Where did you get these numbers? From sysindexes or from DBCC SHOWCONTIG (or if 2005, the 2005
>counterparts)? Or from the CREATE INDEX scripts?
>Note that 0 is same as 100%.
>Also the value specified when index is created is one thing. This value isn't maintained
>automatically by the system (that would defeat the purpose of leaving free space). Not until you
>re-apply the value (by reorg or rebuild). See
>http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for good info.
>>I have a table in a database that has six indexes.
>[quoted text clipped - 6 lines]
>> at zero? It seems like, regardless of the Read/Write ratio that all of the
>> FillFactors should be the same. Please help.
--
Message posted via http://www.sqlmonster.com|||Thanks Paul. If I read between the lines in your response I believe my
question got answered. Am I correct in interpreting your response as saying,
"If a fillfactor of 95 is optimal, then make all the index fillfactors for
all the indexes 95, rather than having some at zero and some at 90?"
If so you have answered my question, which was, "Should not all my index
fillfactors be the same, instead of being a mix, even though the values are
not that dissimilar?"
Please verify my understanding.
Paul Ibison wrote:
>Don't forget that FillFactor=0 is equivalent to 100, so the 2 values are not
>so disimilar. Perhaps a blanket value of 95 would be suitable - it really
>depends on whether your writes are causing page-splits (see dbcc showcontig
>/ sys.dm_db_index_physical_stats for more info).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||That makes the picture a lot clearer.
You can specify the default fillfactor with sp_configure, FYI. I.e., the value to be used if you
don't specify a value when you create the index.
I would *not* say that there's "one setting fits all" foe an environment.
Imagine an index over, say, an identity or an OrderDate column. This value will be ever increasing
(except for very rare special situations). Here you will have no page splits, and would want a
fillfactor of 0 (100).
And then you have the index over the LastName column. If you do heavy inserting into this table, you
might want a lower fillfactor than 90, perhaps 80 or even 70.
So, you want the be really thorough, set the fillfactor for the larger tables on a per case basis.
Did that make sense?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649d88fbf8c01@.uwe...
>I am on SQL2K and got these numbers from sysindexes.OrigFillFactor.
> Sorry if I was not clear. Let me try to explain better.
> My question is NOT that some indexes were created explicitly at 90 and others
> defaulting with zero. It just seems to me that you would want all your index
> fillfactors to be the same. For example, if I have determined that 90 is the
> optimal fillfactor for my environment, would I not want all my indexes to be
> 90?
> I understand that if the default index fillfactor is set to zero, and your
> optimal fillfactor is 90, then you need to be careful when creating new
> indexes and explicitly specify 90 as the fillfactor, or else change the
> default from zero to 90. So I can see that if the default is zero and the
> optimum is 90, where some indexes might end up as 90 and others are at zero
> due to sloppiness.
> So, getting back to the root of my question, if my optimal fillfactor is 90,
> then am I correct in stating that all my indexes should be at 90? If my
> optimal fillfactor is 90, then to me, it does not make sense that there are
> some indexes in my environment set at zero. Is that correct?
> Tibor Karaszi wrote:
>>Where did you get these numbers? From sysindexes or from DBCC SHOWCONTIG (or if 2005, the 2005
>>counterparts)? Or from the CREATE INDEX scripts?
>>Note that 0 is same as 100%.
>>Also the value specified when index is created is one thing. This value isn't maintained
>>automatically by the system (that would defeat the purpose of leaving free space). Not until you
>>re-apply the value (by reorg or rebuild). See
>>http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for good info.
>>I have a table in a database that has six indexes.
>>[quoted text clipped - 6 lines]
>> at zero? It seems like, regardless of the Read/Write ratio that all of the
>> FillFactors should be the same. Please help.
> --
> Message posted via http://www.sqlmonster.com
>|||It makes sense, but I still am not sure if my question is being answered.
Maybe my question is being answered, but I am too thick headed.
Let me try to explain further.
Let's say I have one table that has 6 indexes. The following are the indexes
and fillfactor values:
IDX1 = 0
IDX2 = 95
IDX3 = 90
IDX4 = 85
IDX5 = 80
IDX6 = 75
Let's say I have discovered that the optimal fillfactor for this table is 90.
While those values might average close to 90, would it not be better to set
them all at 90, rather than having a variety of fillfactor values?
Tibor Karaszi wrote:
>That makes the picture a lot clearer.
>You can specify the default fillfactor with sp_configure, FYI. I.e., the value to be used if you
>don't specify a value when you create the index.
>I would *not* say that there's "one setting fits all" foe an environment.
>Imagine an index over, say, an identity or an OrderDate column. This value will be ever increasing
>(except for very rare special situations). Here you will have no page splits, and would want a
>fillfactor of 0 (100).
>And then you have the index over the LastName column. If you do heavy inserting into this table, you
>might want a lower fillfactor than 90, perhaps 80 or even 70.
>So, you want the be really thorough, set the fillfactor for the larger tables on a per case basis.
>Did that make sense?
>>I am on SQL2K and got these numbers from sysindexes.OrigFillFactor.
>[quoted text clipped - 33 lines]
>> at zero? It seems like, regardless of the Read/Write ratio that all of the
>> FillFactors should be the same. Please help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||As always it depends<g>. But in this case it depends on how much free time
you have or how much trouble the fill factors are causing you. Having a
different fill factor for each index may be appropriate since they are for
different indexes with different expressions. Let me explain. The clustered
index is the one to worry about most because it has the largest amount of
"data" it deals with. Since the leaf level of the clustered index is the
entire row you may get pages splits a lot more often with a given fill
factor than a non-clustered index. If you have say a fill factor of 90 then
you have 10% or roughly 800 bytes of free space left. If your rows average
400 bytes you can add only two more rows before you may have a page split.
But on a non-clustered index the new rows are actually just the column(s) in
the index expression. If this is an INT you can add about 200 new rows
before the page was full. It also depends on the order in which you add
items to the indexes since that will determine where the rows need to go. So
the value and size of what is going on the leaf level of the index will
dictate how much free space you need before you get page splits. That can
vary quite a bit between the different nonclustered indexes and the
clustered index.
With that said you have to really know your data and usage to get all those
fill factors correct. So most people would get the most bang for the buck by
choosing one fill factor for the clustered index and one for all the
non-clustered indexes (or even one for all). It won't be the most optimal
but probably good enough for the average system. I have some systems where
the indexes need to be highly tuned for performance reasons due to the
extreme loads but that is the exception not the norm.
Hope that answers your question.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:649e6e6c6a2e4@.uwe...
> It makes sense, but I still am not sure if my question is being answered.
> Maybe my question is being answered, but I am too thick headed.
> Let me try to explain further.
> Let's say I have one table that has 6 indexes. The following are the
> indexes
> and fillfactor values:
> IDX1 = 0
> IDX2 = 95
> IDX3 = 90
> IDX4 = 85
> IDX5 = 80
> IDX6 = 75
> Let's say I have discovered that the optimal fillfactor for this table is
> 90.
> While those values might average close to 90, would it not be better to
> set
> them all at 90, rather than having a variety of fillfactor values?
>
>
> Tibor Karaszi wrote:
>>That makes the picture a lot clearer.
>>You can specify the default fillfactor with sp_configure, FYI. I.e., the
>>value to be used if you
>>don't specify a value when you create the index.
>>I would *not* say that there's "one setting fits all" foe an environment.
>>Imagine an index over, say, an identity or an OrderDate column. This value
>>will be ever increasing
>>(except for very rare special situations). Here you will have no page
>>splits, and would want a
>>fillfactor of 0 (100).
>>And then you have the index over the LastName column. If you do heavy
>>inserting into this table, you
>>might want a lower fillfactor than 90, perhaps 80 or even 70.
>>So, you want the be really thorough, set the fillfactor for the larger
>>tables on a per case basis.
>>Did that make sense?
>>I am on SQL2K and got these numbers from sysindexes.OrigFillFactor.
>>[quoted text clipped - 33 lines]
>> at zero? It seems like, regardless of the Read/Write ratio that all of
>> the
>> FillFactors should be the same. Please help.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||You might want to set them all the same for convenience/neatness, but the
problem is that what is optimal for one table might be a problem for
another, so dbcc showcontig needs to be run for each table in turn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks Andrew, Tibor, and Paul. The three of you finally got through my thick
skull. I appreciate getting my question answered well.
Andrew J. Kelly wrote:
>As always it depends<g>. But in this case it depends on how much free time
>you have or how much trouble the fill factors are causing you. Having a
>different fill factor for each index may be appropriate since they are for
>different indexes with different expressions. Let me explain. The clustered
>index is the one to worry about most because it has the largest amount of
>"data" it deals with. Since the leaf level of the clustered index is the
>entire row you may get pages splits a lot more often with a given fill
>factor than a non-clustered index. If you have say a fill factor of 90 then
>you have 10% or roughly 800 bytes of free space left. If your rows average
>400 bytes you can add only two more rows before you may have a page split.
>But on a non-clustered index the new rows are actually just the column(s) in
>the index expression. If this is an INT you can add about 200 new rows
>before the page was full. It also depends on the order in which you add
>items to the indexes since that will determine where the rows need to go. So
>the value and size of what is going on the leaf level of the index will
>dictate how much free space you need before you get page splits. That can
>vary quite a bit between the different nonclustered indexes and the
>clustered index.
>With that said you have to really know your data and usage to get all those
>fill factors correct. So most people would get the most bang for the buck by
>choosing one fill factor for the clustered index and one for all the
>non-clustered indexes (or even one for all). It won't be the most optimal
>but probably good enough for the average system. I have some systems where
>the indexes need to be highly tuned for performance reasons due to the
>extreme loads but that is the exception not the norm.
>Hope that answers your question.
>> It makes sense, but I still am not sure if my question is being answered.
>> Maybe my question is being answered, but I am too thick headed.
>[quoted text clipped - 43 lines]
>> the
>> FillFactors should be the same. Please help.
--
Message posted via http://www.sqlmonster.com
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment