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
sql

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 DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
Cheers
If an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
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.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
CheersIf an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
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.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>