Fill factor 100% affects performance

Hi,
I've noticed something strange. I've got a table which contains a clustered
index. Now I've noticed a very big differences in the logical reads of a
complex join when I change the fill factor from 99 to 100. When the fill
factor is less than 100 then the number of logical reads is around 500. In
fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
there are fewer pages go through.
Now, once this changes to over 99 the logical reads jump to 16,000. Can
anyone explain why the dramatic difference? changing the fill factor seems t
o
mess up the whole query plan. And the output of DBCC SHOWCONTIG if it helps
.
- Pages Scanned........................: 124
- Extents Scanned.......................: 16
- Extent Switches.......................: 15
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
- Logical Scan Fragmentation ..............: 8.06%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 52.3
- Avg. Page Density (full)................: 99.35%
Thanks,
PanosFirst off the 16,000 reads are not from the table you listed as it only has
124 pages in it. The other table in the join will also play a big factor in
the outcome of the # of reads. In any case unless the table is read only it
is almost never a good idea to use a 100% fill factor. Otherwise you are
likely to get lots of page splits. I am not sure why the move to 100% is
giving you the results you say. Maybe if we could look at the DDL including
indexes for both tables it would help.
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
> Hi,
> I've noticed something strange. I've got a table which contains a
> clustered
> index. Now I've noticed a very big differences in the logical reads of a
> complex join when I change the fill factor from 99 to 100. When the fill
> factor is less than 100 then the number of logical reads is around 500. In
> fact it imrpoves as I change it from 40 to 60 to 99% which seems normal as
> there are fewer pages go through.
> Now, once this changes to over 99 the logical reads jump to 16,000. Can
> anyone explain why the dramatic difference? changing the fill factor seems
> to
> mess up the whole query plan. And the output of DBCC SHOWCONTIG if it
> helps.
> - Pages Scanned........................: 124
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 15
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 100.00% [16:16]
> - Logical Scan Fragmentation ..............: 8.06%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 52.3
> - Avg. Page Density (full)................: 99.35%
> Thanks,
> Panos|||There are definitely 16,000 logical reads for this table, at least this is
what it shows with statistics set on, unless there is a bug with the logical
reads output?
"Andrew J. Kelly" wrote:

> First off the 16,000 reads are not from the table you listed as it only ha
s
> 124 pages in it. The other table in the join will also play a big factor
in
> the outcome of the # of reads. In any case unless the table is read only
it
> is almost never a good idea to use a 100% fill factor. Otherwise you are
> likely to get lots of page splits. I am not sure why the move to 100% is
> giving you the results you say. Maybe if we could look at the DDL includin
g
> indexes for both tables it would help.
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:3ED85CB0-41B5-4F4D-AF7A-0D5F780114F7@.microsoft.com...
>
>|||What does the join look like? How about the query plan? Can you provide
more details so we don't have to guess?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
> There are definitely 16,000 logical reads for this table, at least this is
> what it shows with statistics set on, unless there is a bug with the
> logical
> reads output?
> "Andrew J. Kelly" wrote:
>|||OK here it is but it looks horrible cause it comes from BusinessObjects!
Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
( Review.REVIEW_ID ),
( Review_Type.REVIEW_TYPE_DESCRIPTION ),
( Review.GID ),
( Review_Risk.TYPE_DESCRIPTION ),
( Review.ULTIMATE_NAME ),
AllocationRef.RB_USER_NAME,
( Review_Process_Status.STATUS_DATE_START ),
( Review.ULTIMATE_GID ),
( AllocationRef.WORKLIST ),
( AllocationRef.ALLOCATED_WORKLIST ),
( Process_Status.PROCESS_STATUS_DESCRIPTION ),
( Review.LEGAL_NAME ),
( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
( Review_Process_Status.PROCESS_STATUS_ID ),
( Entity_Type.TYPE_DESCRIPTION ),
Review_Process_Status.STATUS_DATE_END,
Review_Process_Status.IS_SECONDARY_STATUS,
Process_Status.ORDER_ID,
( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.COMPANY_MEMBER Reviewer,
database_rep.dbo.COMPANY_MEMBER Actioner,
database_rep.dbo.PROCESS_STATUS Process_Status,
database_rep.dbo.ENTITY_TYPE Entity_Type,
database_rep.dbo.RISK_TYPE Review_Risk,
database_rep.dbo.REVIEW_TYPE Review_Type,
database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
WHERE
( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
AND (
Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
AND (
( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
Reassessment'')) )
AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)''))
)
AND ( Review.REVIEW_ID ) IN (SELECT
( Review.REVIEW_ID )
FROM
database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
database_rep.dbo.REVIEW Review,
database_rep.dbo.PROCESS_STATUS Process_Status
WHERE
( Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
)
AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
AND (
( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
''(ALL)'' IN (''(ALL)'')OR
''All'' IN (''(ALL)'')OR
''all'' IN (''(ALL)'')OR
''*'' IN (''(ALL)'')OR
''%'' IN (''(ALL)'')) )
AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
)
)
)
'
"Andrew J. Kelly" wrote:

> What does the join look like? How about the query plan? Can you provide
> more details so we don't have to guess?
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:15565321-C6F5-49E1-B8C8-ED91559B8078@.microsoft.com...
>
>|||For some reason I thought it was only a 2 table join and didn't realize it
was such a messof a join<g>. With a WHERE clause like that it would be hard
pressed not to scan tables. FYI the old join syntax you are using can have
ambiguous results and is not supported moving forward. In any case I
suspect you are hitting some quirky threshold with the statistics between
the 99 and 100% fillfactors. You should choose a fill factor that is
suitable or at least a trade off to minimize pagesplits and keep the pages
as full as possible. Then worry about tuning the query. You can not tune a
query by adjusting the fill factor as it is just not practical. Again 100%
is almost never a good choice anyway so why spend time there in the first
place?
Andrew J. Kelly SQL MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
> OK here it is but it looks horrible cause it comes from BusinessObjects!
> Thank you. The table in question is REP_REVIEW_ALLOCATION_REF
> exec sp_prepexec @.P1 output, NULL, N'SELECT DISTINCT
> ( Review.REVIEW_ID ),
> ( Review_Type.REVIEW_TYPE_DESCRIPTION ),
> ( Review.GID ),
> ( Review_Risk.TYPE_DESCRIPTION ),
> ( Review.ULTIMATE_NAME ),
> AllocationRef.RB_USER_NAME,
> ( Review_Process_Status.STATUS_DATE_START ),
> ( Review.ULTIMATE_GID ),
> ( AllocationRef.WORKLIST ),
> ( AllocationRef.ALLOCATED_WORKLIST ),
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ),
> ( Review.LEGAL_NAME ),
> ( Reviewer.FIRST_NAME ) + '' '' +( Reviewer.LAST_NAME ),
> ( Review_Process_Status.PROCESS_STATUS_ID ),
> ( Entity_Type.TYPE_DESCRIPTION ),
> Review_Process_Status.STATUS_DATE_END,
> Review_Process_Status.IS_SECONDARY_STATUS,
> Process_Status.ORDER_ID,
> ( Actioner.FIRST_NAME ) + '' '' +( Actioner.LAST_NAME )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.COMPANY_MEMBER Reviewer,
> database_rep.dbo.COMPANY_MEMBER Actioner,
> database_rep.dbo.PROCESS_STATUS Process_Status,
> database_rep.dbo.ENTITY_TYPE Entity_Type,
> database_rep.dbo.RISK_TYPE Review_Risk,
> database_rep.dbo.REVIEW_TYPE Review_Type,
> database_rep.dbo.REP_REVIEW_ALLOCATION_REF AllocationRef
> WHERE
> ( Entity_Type.ENTITY_TYPE_ID=*Review.ENTITY_TYPE_ID )
> AND (
> eview_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND ( Review.REVIEW_TYPE_ID=Review_Type.REVIEW_TYPE_ID )
> AND ( Review_Risk.RISK_TYPE_ID=*Review.RISK_TYPE_ID )
> AND ( AllocationRef.OCTOPUS_ID=Review.OCTOPUS_ID )
> AND ( Review_Process_Status.MEMBER_ID=Reviewer.MEMBER_ID )
> AND ( Review_Process_Status.COMPLETED_BY*=Actioner.MEMBER_ID )
> AND (
> ( Process_Status.PROCESS_STATUS_DESCRIPTION ) != ''First Read''
> AND ( (( ( AllocationRef.WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( (( ( Review_Type.REVIEW_TYPE_DESCRIPTION ) ) IN (''Risk
> Reassessment'')) )
> AND ( (( ( AllocationRef.ALLOCATED_WORKLIST ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)''))
> )
> AND ( Review.REVIEW_ID ) IN (SELECT
> ( Review.REVIEW_ID )
> FROM
> database_rep.dbo.REVIEW_PROCESS_STATUS Review_Process_Status,
> database_rep.dbo.REVIEW Review,
> database_rep.dbo.PROCESS_STATUS Process_Status
> WHERE
> (
> Review_Process_Status.PROCESS_STATUS_ID=Process_Status.PROCESS_STATUS_ID
> )
> AND ( Review.REVIEW_ID=Review_Process_Status.REVIEW_ID )
> AND (
> ( (( ( Process_Status.PROCESS_STATUS_DESCRIPTION ) ) IN (''(ALL)'')OR
> ''(ALL)'' IN (''(ALL)'')OR
> ''All'' IN (''(ALL)'')OR
> ''all'' IN (''(ALL)'')OR
> ''*'' IN (''(ALL)'')OR
> ''%'' IN (''(ALL)'')) )
> AND Review_Process_Status.IS_SECONDARY_STATUS = ''N''
> AND Review_Process_Status.STATUS_DATE_END >= ''12/31/9999 00:0:0''
> )
> )
> )
> '
> "Andrew J. Kelly" wrote:
>|||The sql is coming from business objects and can't be changed, even though is
rubbish. The fill factor was changed to 100% automatically when I've switche
d
the index from nonclustered to clustered something that I didn't know. I did
some investigation and found out that the fill factor was 100% so
investigated more to see why performance gets affected little bit. Anyway, I
think I should just accept it and keep in mind that fill factor can affect
the performance of queries sometimes. Thank you for your help.
"Andrew J. Kelly" wrote:

> For some reason I thought it was only a 2 table join and didn't realize it
> was such a messof a join<g>. With a WHERE clause like that it would be ha
rd
> pressed not to scan tables. FYI the old join syntax you are using can hav
e
> ambiguous results and is not supported moving forward. In any case I
> suspect you are hitting some quirky threshold with the statistics between
> the 99 and 100% fillfactors. You should choose a fill factor that is
> suitable or at least a trade off to minimize pagesplits and keep the pages
> as full as possible. Then worry about tuning the query. You can not tune
a
> query by adjusting the fill factor as it is just not practical. Again 100
%
> is almost never a good choice anyway so why spend time there in the first
> place?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:8700DDA6-38CF-478B-A0FE-BCE71A724250@.microsoft.com...
>
>

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
Gracias
We'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias
|||A dbcc showcontig (tableName) is also useful. Could you put that out here as
well.
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
GraciasWe'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias|||A dbcc showcontig (tableName) is also useful. Could you put that out here as
well.
"Jerry Spivey" wrote:
> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> > What fill factor value should I set for index on a table that is hit
> > 10,000
> > times a day? Is there any other tool to help boost performance along with
> > fill factor?
> > Gracias
>
>|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:
> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> > What fill factor value should I set for index on a table that is hit
> > 10,000
> > times a day? Is there any other tool to help boost performance along with
> > fill factor?
> > Gracias
>
>|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
--
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>> We'll need just a "bit" more information to answer that question. Does
>> this
>> table have a clustered index? Is it autoincrementing? >Hit? What is
>> the
>> percentage of reads to writes for the table? Plenty of available disk
>> space?
>> HTH
>> Jerry
>> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
>> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>> > What fill factor value should I set for index on a table that is hit
>> > 10,000
>> > times a day? Is there any other tool to help boost performance along
>> > with
>> > fill factor?
>> > Gracias
>>|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>> We'll need just a "bit" more information to answer that question. Does
>> this
>> table have a clustered index? Is it autoincrementing? >Hit? What is
>> the
>> percentage of reads to writes for the table? Plenty of available disk
>> space?
>> HTH
>> Jerry
>> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
>> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>> > What fill factor value should I set for index on a table that is hit
>> > 10,000
>> > times a day? Is there any other tool to help boost performance along
>> > with
>> > fill factor?
>> > Gracias
>>

Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
GraciasWe'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias|||A dbcc showcontig (tableName) is also useful. Could you put that out here a
s
well.
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does th
is
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does th
is
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
>

Files

Hello Professionals.
We have implemented the RAID 5 so what would be best performance tips for
separating the log and datafiles ? and any other tips for performance would
be highly appreciated.
Thanks
Hi,
How many Disk controllers you have in your server?. If you have multiple
controllers then you can keep MDF and LDF files
seperately in each controller. based on the access you can also create
seperate filegroups to keep the indexes. This will share the I/O
between controllers.
See the below site for Hardware tuning tips (See I/O part):-
http://www.sql-server-performance.co...are_tuning.asp
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:D477C1B8-8B65-4184-897B-D16B338A0EA7@.microsoft.com...
> Hello Professionals.
> We have implemented the RAID 5 so what would be best performance tips for
> separating the log and datafiles ? and any other tips for performance
> would
> be highly appreciated.
> Thanks
sql

filegroup distribution

Hi, I was wondering if anyone could give me a general rundown of filegroup
distribution. What performance monitor counters do you use to determine the
load on the disks and the way you distribute the filegroups. Generally what
i want to do is assess the current load on the disks and determine which
disk is taking the least load, and set the filegroup on that as the default.
Then later on when additional data files are added to the system, further
load analysis is used to determine the least used disk and then set that to
contain the default filegroup. Just what perf. mon. counters does one use to
do this, and what analysis does one go through to determine filegroup
distribution.
Thanks!
Hz.If you only have a single filegroup per disk array there are lots of disk
counters in perfmon to help with determining the load on that array. Check
out all the counters under Physical Disk. You can also use
fn_virtualfilestats to see which files are used most often for reads /
writes etc in conjunction with the perfmon counters.
Andrew J. Kelly
SQL Server MVP
"Hz" <Hz@.nospam.com> wrote in message
news:fFCFb.6016$xm.227280@.nasal.pacific.net.au...
> Hi, I was wondering if anyone could give me a general rundown of filegroup
> distribution. What performance monitor counters do you use to determine
the
> load on the disks and the way you distribute the filegroups. Generally
what
> i want to do is assess the current load on the disks and determine which
> disk is taking the least load, and set the filegroup on that as the
default.
> Then later on when additional data files are added to the system, further
> load analysis is used to determine the least used disk and then set that
to
> contain the default filegroup. Just what perf. mon. counters does one use
to
> do this, and what analysis does one go through to determine filegroup
> distribution.
> Thanks!
> Hz.
>

File placement for optimal performance?

What is the best performance for this configuration:

Files:

Data

Log

Indexes

tempdb

Disk:

A - RAID 10

B - RAID 10 (or should this be RAID 1?)

Whats best?:

A - Data and Indexes

B - tempdb and Log

? Thanks.

check these links

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

http://sql-server-performance.com/Community/forums/t/2337.aspx

Madhu