DBCC SHOWCONTIG Questions

On most of the results from a series of DBCC SHOWCONTIG
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?
Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?
|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent[vbcol=seagreen]
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I[vbcol=seagreen]
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
> >I have a large table that I cannot seem to bring the extent fragmentation
> > down to an aceptable level. Fist the logical scan density and logical
> > fragmentation were extreamly high so I rebuit the index. This helped
both
> > the logical scan density and logical fragmentation but the extent
> > fragmentation stayed at 65%. I then did an index defrag and the extent
> > fragmentation stayed at 65%. I then Reindexed again and the extent
> > fragmentation dropped to 33%. I defraged and reindexed once more and it
> > droped to 21%. I could not get this number to drop any more from 21% so
I
> > droped the index and recreated it; - and it came back with an extent
> > fragmentation of 34%. Is there a way, short of moving to another file
> > group
> > to get my extent fragmentation down without jumping through all these
> > hoops?
>

Dbcc Showcontig

DBCC SHOWCONTIG scanning 'MYTAB' table...
Table: 'MYTAB'(50099219); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 150
- Extents Scanned.......................: 25
- Extent Switches.......................: 25
- Avg. Pages per Extent..................: 6.0
- Scan Density [Best Count:Actual Count]......: 73.08% [19:26]
- Logical Scan Fragmentation ..............: 1.33%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 878.5
- Avg. Page Density (full)................: 89.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since Extents Scanned and Extent Switches r equal, why the density is slow, what about pages per Extent, can it be manaully increased?

HowdyI think the reason lies in the fact that you have 2 pages per extent occupied by different objects. If you had 8 pages per extent, the table would take up approx. 19 extents (19*8-Best count). Look up DBCC DBREINDEX and DBCC INDEXDEFRAG in BOL. mojza

DBCC Logical Scan Bytes/Sec

Hi,
I have a process data acquisition system running with SQL 2000
Enterprise Manager. I have a large hard drive Read activity. By using the
performance monitor, I found that this reading activity is clearly associate
with DBCC logical scan bytes/sec counter and is mostly twin with Physical
Hard drive read/sec.
The point is that I didn't find what trigger this scan. I observed that more
my database is growing more this scan take time. Actually the hard drive is
busy at more that 99% of the time to read and data request take more and
more time.
Is those DBCC scans are essential and how can I identify what is triggering
them?
Regards
MarcDo you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
profiler to see what commands are being run at that time.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:I4pFe.52350$mv2.811211@.weber.videotron.net...
> Hi,
> I have a process data acquisition system running with SQL 2000
> Enterprise Manager. I have a large hard drive Read activity. By using the
> performance monitor, I found that this reading activity is clearly
> associate with DBCC logical scan bytes/sec counter and is mostly twin with
> Physical Hard drive read/sec.
> The point is that I didn't find what trigger this scan. I observed that
> more my database is growing more this scan take time. Actually the hard
> drive is busy at more that 99% of the time to read and data request take
> more and more time.
> Is those DBCC scans are essential and how can I identify what is
> triggering them?
>
> Regards
> Marc
>|||Hi Mr. Kelly, thanks for the reply.
That was a thing I was suspecting but I didn't know how to trace it. I used
SQL Profiler, like suggested, and I found many querys who use the DBCC
UPDATEUSAGE command. I think this is another disk intensive command any way
I found some with a duration over 250000 ( milliseconds I guess ).
Is there any reason to use this command frequently?
Thanks again for the help. I'll try to reach to conceptors of the DB to see
why they're using this command intensively.
Regards
Marc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
> Do you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
> profiler to see what commands are being run at that time.
> --
> Andrew J. Kelly SQL MVP
>
> "marc quirion" <mquirion@.videotron.ca> wrote in message
> news:I4pFe.52350$mv2.811211@.weber.videotron.net...
>|||That command certainly would do it. There is no real reason to update it so
often. If they need that kind of accurate data that often they should
rethink what they are doing and why.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:8vnGe.8806$nx3.312875@.wagner.videotron.net...
> Hi Mr. Kelly, thanks for the reply.
> That was a thing I was suspecting but I didn't know how to trace it. I
> used
> SQL Profiler, like suggested, and I found many querys who use the DBCC
> UPDATEUSAGE command. I think this is another disk intensive command any
> way
> I found some with a duration over 250000 ( milliseconds I guess ).
> Is there any reason to use this command frequently?
> Thanks again for the help. I'll try to reach to conceptors of the DB to
> see
> why they're using this command intensively.
> Regards
> Marc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
>

dbcc indexdefrag not doing anything

sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>|||Yes. Thats the index Im trying to defragment.
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> sql2k sp3
>> I keep running dbcc indexdefrag on a small(236,492 rows)
>> table and it doesnt change the output of the Scan
Density
>> (best count:actual count) from dbcc showcontig at all.
>>
>> Any ideas?
>> TIA, Chris
>
>.
>|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> Yes. Thats the index Im trying to defragment.
> >--Original Message--
> >Does your table have a clustered index?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> I keep running dbcc indexdefrag on a small(236,492 rows)
> >> table and it doesnt change the output of the Scan
> Density
> >> (best count:actual count) from dbcc showcontig at all.
> >>
> >>
> >> Any ideas?
> >>
> >> TIA, Chris
> >>
> >
> >
> >.
> >|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
>allocations. Therefore the physical page/extent
fragmentation will remain,
>even though the rows are now in physical order on the
pages. One of the
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
>> Yes. Thats the index Im trying to defragment.
>> >--Original Message--
>> >Does your table have a clustered index?
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >"chris" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> I keep running dbcc indexdefrag on a small(236,492
rows)
>> >> table and it doesnt change the output of the Scan
>> Density
>> >> (best count:actual count) from dbcc showcontig at
all.
>> >>
>> >>
>> >> Any ideas?
>> >>
>> >> TIA, Chris
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
> >--Original Message--
> >Ok, DBCC IndexDefrag does an in-place defrag thus there
> are no new
> >allocations. Therefore the physical page/extent
> fragmentation will remain,
> >even though the rows are now in physical order on the
> pages. One of the
> >hidden limitations of INDEXDEFRAG vs. DBREINDEX.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> >> Yes. Thats the index Im trying to defragment.
> >>
> >> >--Original Message--
> >> >Does your table have a clustered index?
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> >> sql2k sp3
> >> >>
> >> >> I keep running dbcc indexdefrag on a small(236,492
> rows)
> >> >> table and it doesnt change the output of the Scan
> >> Density
> >> >> (best count:actual count) from dbcc showcontig at
> all.
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> TIA, Chris
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
--
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan|||And in addition to that <g>, scan density is useless in the first place if > 1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
> > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> > So if your database has much locking activity during defragmentation,
> > the DBCC command will be less effective.
> >
> > Gert-Jan
>|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > And in addition to that Indexdefrag only works on 1 file at a time.
This
> > means extent fragmentation is useless on multiple files.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:4016C868.AD86E998@.toomuchspamalready.nl...
> > > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked
rows.
> > > So if your database has much locking activity during defragmentation,
> > > the DBCC command will be less effective.
> > >
> > > Gert-Jan
> >
> >
>

dbcc indexdefrag not doing anything

sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...

quote:

---

> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>

---

|||Yes. Thats the index Im trying to defragment.

quote:

---

>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:556301c3e508$94bae040$a501280a@.phx.gbl...
Density[QUOTE]
>
>.
>

---

|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...[QUOTE]
> Yes. Thats the index Im trying to defragment.
>
> message
> Density|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?

quote:

---

>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there

---

are no new

quote:

---

>allocations. Therefore the physical page/extent

---

fragmentation will remain,

quote:

---

>even though the rows are now in physical order on the

---

pages. One of the

quote:

---

>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
rows)[QUOTE]
all.[QUOTE]
>
>.
>

---

|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...[QUOTE]
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
>
> are no new
> fragmentation will remain,
> pages. One of the
> message
> rows)
> all.|||Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 5
NNTP-Posting-Date: 27 Jan 2004 21:22:04 CET
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1075234924 news.xs4all.nl 316 [::ffff:194.109.252.208]:2369
X-Complaints-To: abuse@.xs4all.nl
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!npeer.de.k
pn-eurorings.net!news.cambrium.nl!news.cambrium.nl!news.cambrium.nl!newsgate
.cistron.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:327667
In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...

quote:

---

> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan

---

|||And in addition to that <g>, scan density is useless in the first place if >
1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
>

---

|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...

quote:

---

> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
This[QUOTE]
rows.[QUOTE]
>

---

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
Mike
Well a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike
|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes with higher than a certain level of fragmentation, but I notice that certain indexes are above the t
hreshold every night. If I run a DBCC DBREINDEX, I have much better results (the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is that many indexes seem to have their fragmentation reduced when running DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my data warehouse DBs...
Regards,
Jonathan
|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan
|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I originally reported.
I have a data warehouse that's updated early in the morning. The larger tables are in the order of 5 to 25 million rows. The updates vary in size from 100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the update, and it's after the update that the index appears to be refragmented. So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and not deleting or updating any rows, the fragmentation could go from .01% to 26.34%.
I haven't had a chance to fully document this yet, but it seems to me that DBREINDEX results in a substantially better Scan Density, and that subsequent inserts do not result in such extreme changes in Logical Scan Fragmentation. If you think it's helpfu
l, I'll pursue that.
Regards,
Jonathan
|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...[vbcol=seagreen]
> Paul,
> "Paul S Randal [MS]" wrote:
Can
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan
|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> system administrator.
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split and
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fill
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits and
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative to the size of the table, it's pretty small. So I guess I'm surprised that one in 6 inserts would cause a page split when I'm changing the table by only 1%. I'm also suprised tha
t simply splitting pages would cause the fragmentation to increase so dramatically.
I had originally created the index with no FILLFACTOR specified. (I thought this was supposed to default to a FILLFACTOR of 80). On your suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the great improvement in the index's Scan Density. And I also see this kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into whether my other big indexes have similar FILLFACTOR issues. In the meantime, is Scan Density something that I should be worrying about?
|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP
|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
8[vbcol=seagreen]
8[vbcol=seagreen]
and[vbcol=seagreen]
..01% to[vbcol=seagreen]
before[vbcol=seagreen]
and[vbcol=seagreen]
fill[vbcol=seagreen]
page[vbcol=seagreen]
and
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>
|||Thanks, Andrew. Very helpful stuff.
-- J

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
MikeWell a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes with higher than a certain level of fragmentation, but I notice that certain indexes are above the threshold every night. If I run a DBCC DBREINDEX, I have much better results (the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is that many indexes seem to have their fragmentation reduced when running DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my data warehouse DBs...
Regards,
Jonathan|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> > [...]
> > I first execute DBCC SHOWCONTIG on my database.
> >
> > Which outputs logicaldefrag, scan density, and etc.
> >
> > I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> > every table in my database.
> >
> > Next I apply the sp_updatestats.
> >
> > Lastly, I execute DBCC SHOWCONTIG to the database.
> >
> > When I compare the DBCC SHOWCONTIG before and after
> > results from applying DBCC INDEXDEFRAG. I noticed the
> > logicalfrag has the same values?
> >
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I originally reported.
I have a data warehouse that's updated early in the morning. The larger tables are in the order of 5 to 25 million rows. The updates vary in size from 100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the update, and it's after the update that the index appears to be refragmented. So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and not deleting or updating any rows, the fragmentation could go from .01% to 26.34%.
I haven't had a chance to fully document this yet, but it seems to me that DBREINDEX results in a substantially better Scan Density, and that subsequent inserts do not result in such extreme changes in Logical Scan Fragmentation. If you think it's helpful, I'll pursue that.
Regards,
Jonathan|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
--
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> Paul,
> "Paul S Randal [MS]" wrote:
> > "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> > news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> > What's the reported number of pages moved when running DBCC INDEXDEFRAG?
Can
> > you post sample DBCC SHOWCONTIG output please?
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> > After defrag:
> >
> > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> > LEAF level scan performed.
> > - Pages Scanned........................: 76899
> > - Extents Scanned.......................: 9670
> > - Extent Switches.......................: 37754
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> > - Logical Scan Fragmentation ..............: 0.01%
> > - Extent Scan Fragmentation ...............: 61.40%
> > - Avg. Bytes Free per Page................: 38.3
> > - Avg. Page Density (full)................: 99.53%
> > DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> >
> > After inserting 270,000 rows:
> > DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> > LEAF level scan performed.
> > - Pages Scanned........................: 123077
> > - Extents Scanned.......................: 15464
> > - Extent Switches.......................: 88745
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> > - Logical Scan Fragmentation ..............: 26.34%
> > - Extent Scan Fragmentation ...............: 65.66%
> > - Avg. Bytes Free per Page................: 3009.3
> > - Avg. Page Density (full)................: 62.82%
> >
> > So... it's very strange to me that, after inserting about 1% new rows, and
> > not deleting or updating any rows, the fragmentation could go from .01% to
> > 26.34%.
> >
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split and
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fill
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits and
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative to the size of the table, it's pretty small. So I guess I'm surprised that one in 6 inserts would cause a page split when I'm changing the table by only 1%. I'm also suprised that simply splitting pages would cause the fragmentation to increase so dramatically.
I had originally created the index with no FILLFACTOR specified. (I thought this was supposed to default to a FILLFACTOR of 80). On your suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the great improvement in the index's Scan Density. And I also see this kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into whether my other big indexes have similar FILLFACTOR issues. In the meantime, is Scan Density something that I should be worrying about?|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...
> "Andrew J. Kelly" wrote:
> > "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> > news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> > > After defrag:
> > >
> > > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID:
8
> > > LEAF level scan performed.
> > > - Pages Scanned........................: 76899
> > > - Extents Scanned.......................: 9670
> > > - Extent Switches.......................: 37754
> > > - Avg. Pages per Extent..................: 8.0
> > > - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> > > - Logical Scan Fragmentation ..............: 0.01%
> > > - Extent Scan Fragmentation ...............: 61.40%
> > > - Avg. Bytes Free per Page................: 38.3
> > > - Avg. Page Density (full)................: 99.53%
> > > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > >
> > > After inserting 270,000 rows:
> > > DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> > > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID:
8
> > > LEAF level scan performed.
> > > - Pages Scanned........................: 123077
> > > - Extents Scanned.......................: 15464
> > > - Extent Switches.......................: 88745
> > > - Avg. Pages per Extent..................: 8.0
> > > - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> > > - Logical Scan Fragmentation ..............: 26.34%
> > > - Extent Scan Fragmentation ...............: 65.66%
> > > - Avg. Bytes Free per Page................: 3009.3
> > > - Avg. Page Density (full)................: 62.82%
> > >
> > > So... it's very strange to me that, after inserting about 1% new rows,
and
> > > not deleting or updating any rows, the fragmentation could go from
.01% to
> > > 26.34%.
> > >
> > This isn't strange at all since your pages are basically 100% full
before
> > the inserts. That means that almost any insert will cause a page split
and
> > the results of the showcontig shows that there was a lot of it. If your
> > going to do a lot of inserts you should consider setting an appropriate
fill
> > factor and possibly Padindex as well. Leaving more free space on the
page
> > will mean more pages in the database but will result in less pagesplits
and
> > lots less work during the inserts.
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>|||Thanks, Andrew. Very helpful stuff.
-- J|||Paul,
Thanks for your quick reply.
"Paul S Randal [MS]" wrote:
> Why do you consider Scan Density so important? What's the access pattern
> over this index?
Sorry if I wasn't clear. I don't know whether Scan Density is important or not. The KB articles are great about Logical Fragmentation, but they don't really seem to address Scan Density. I just observed that (a) it was low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as effectively as DBREINDEX. I don't know if the total number of pages is important, but I wonder how the fragmentation can be low and the average page density high when there seem to be a significant difference in the total number of extents... is the reason that DBREINDEX tends to group pages together better physically? And is that important?
> Do you notice query performance dropping after the insert activity? It's
> entirely possible that everything you're doing is a waste of time - akin to
> continually shrinking a database after it grows every day. Please read the
> whitepaper below which explains a bunch about when to take action on
> fragmentation.
Yes, the query performance seems to be inversely proportional to the fragmentation of the indexes. I don't have stats on individual queries, but the process which does the queries has become considerably faster after instituting nightly defragmentations. The insert process has not; however, I'm hoping that tuning the FILLFACTOR will help that.
-- J|||You shouldn't worry about Scan Density or Extent Scan Fragmentation. The
main cause of query slowdown is ineffective readahead during range scans
caused by Logical Scan Fragmentation. Extent Scan Fragmentation has only
minimal effect on readahead and so we're de-emphasizing its importance.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:3D9896FF-FD3A-4DF3-8F14-610E7C856F7F@.microsoft.com...
> Paul,
> Thanks for your quick reply.
> "Paul S Randal [MS]" wrote:
> > Why do you consider Scan Density so important? What's the access pattern
> > over this index?
> Sorry if I wasn't clear. I don't know whether Scan Density is important
or not. The KB articles are great about Logical Fragmentation, but they
don't really seem to address Scan Density. I just observed that (a) it was
low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as
effectively as DBREINDEX. I don't know if the total number of pages is
important, but I wonder how the fragmentation can be low and the average
page density high when there seem to be a significant difference in the
total number of extents... is the reason that DBREINDEX tends to group
pages together better physically? And is that important?
> > Do you notice query performance dropping after the insert activity? It's
> > entirely possible that everything you're doing is a waste of time - akin
to
> > continually shrinking a database after it grows every day. Please read
the
> > whitepaper below which explains a bunch about when to take action on
> > fragmentation.
> Yes, the query performance seems to be inversely proportional to the
fragmentation of the indexes. I don't have stats on individual queries, but
the process which does the queries has become considerably faster after
instituting nightly defragmentations. The insert process has not; however,
I'm hoping that tuning the FILLFACTOR will help that.
> -- J

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
MikeWell a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx
.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the s
ample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes
with higher than a certain level of fragmentation, but I notice that certain
indexes are above the t
hreshold every night. If I run a DBCC DBREINDEX, I have much better results
(the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is th
at many indexes seem to have their fragmentation reduced when running DBCC I
NDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my dat
a warehouse DBs...
Regards,
Jonathan|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? C
an
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I o
riginally reported.
I have a data warehouse that's updated early in the morning. The larger tab
les are in the order of 5 to 25 million rows. The updates vary in size from
100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the u
pdate, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062
]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746
]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and n
ot deleting or updating any rows, the fragmentation could go from .01% to 26
.34%.
I haven't had a chance to fully document this yet, but it seems to me that D
BREINDEX results in a substantially better Scan Density, and that subsequent
inserts do not result in such extreme changes in Logical Scan Fragmentation
. If you think it's helpfu
l, I'll pursue that.
Regards,
Jonathan|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> Paul,
> "Paul S Randal [MS]" wrote:
Can[vbcol=seagreen]
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:880
62]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:3775
5]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:887
46]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> system administrator.
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split an
d
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fi
ll
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits an
d
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative t
o the size of the table, it's pretty small. So I guess I'm surprised that o
ne in 6 inserts would cause a page split when I'm changing the table by only
1%. I'm also suprised tha
t simply splitting pages would cause the fragmentation to increase so dramat
ically.
I had originally created the index with no FILLFACTOR specified. (I thought
this was supposed to default to a FILLFACTOR of 80). On your suggestion, I
tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC
SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114
]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
Note the great improvement in the index's Scan Density. And I also see this
kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into wh
ether my other big indexes have similar FILLFACTOR issues. In the meantime,
is Scan Density something that I should be worrying about?|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...
> "Andrew J. Kelly" wrote:
8[vbcol=seagreen]
8[vbcol=seagreen]
and[vbcol=seagreen]
.01% to[vbcol=seagreen]
before[vbcol=seagreen]
and[vbcol=seagreen]
fill[vbcol=seagreen]
page[vbcol=seagreen]
and[vbcol=seagreen]
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>|||Thanks, Andrew. Very helpful stuff.
-- J