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