DBCC DBREINDEX

When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index shows
Table: 'tablename' (437576597); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 15721
- Extents Scanned.......................: 2027
- Extent Switches.......................: 2026
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
- Extent Scan Fragmentation ...............: 43.96%
- Avg. Bytes Free per Page................: 202.3
- Avg. Page Density (full)................: 97.
When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
but the first on remains unchanged. Any ideas? TIA PS: All the indexes are
nonclusteredThat is because index 0 is not an index at all. Index ID of 0 indicates
this is a HEAP. Heaps can not be reindexed. You should add a clustered
index to the table and that will change the ID from 0 to 1 and allow you to
defragment and reindex the table.
--
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> shows
> Table: 'tablename' (437576597); index ID: 0, database ID: 8
> TABLE level scan performed.
> - Pages Scanned........................: 15721
> - Extents Scanned.......................: 2027
> - Extent Switches.......................: 2026
> - Avg. Pages per Extent..................: 7.8
> - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> - Extent Scan Fragmentation ...............: 43.96%
> - Avg. Bytes Free per Page................: 202.3
> - Avg. Page Density (full)................: 97.
> When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> are
> nonclustered|||Thanks for your response. A follow up question. Does a heap eventually get so
fragmented that it gets corrupt? Is there a way to defrag or reindex a heap
or do we need to go to clustered indexes. TIA
"Andrew J. Kelly" wrote:
> That is because index 0 is not an index at all. Index ID of 0 indicates
> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> index to the table and that will change the ID from 0 to 1 and allow you to
> defragment and reindex the table.
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> > shows
> >
> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> > TABLE level scan performed.
> > - Pages Scanned........................: 15721
> > - Extents Scanned.......................: 2027
> > - Extent Switches.......................: 2026
> > - Avg. Pages per Extent..................: 7.8
> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> > - Extent Scan Fragmentation ...............: 43.96%
> > - Avg. Bytes Free per Page................: 202.3
> > - Avg. Page Density (full)................: 97.
> >
> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other indexes
> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> > are
> > nonclustered
>
>|||Fragmentation does not cause corruption but it can affect performance. The
only way to defrag a HEAP is to export all the data, truncate the table and
import it back in again. Or you can create a clustered index and then drop
it. But it will immediately start to get fragmented again with more
activity. Bottom line is that the recommendation is to have a clustered
index on each and every table. Have a look at this:
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> Thanks for your response. A follow up question. Does a heap eventually get
> so
> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> heap
> or do we need to go to clustered indexes. TIA
> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
>> > are
>> > nonclustered
>>|||Also check out the whitepaper below which explains the options for removing
fragmentation and when it actually makes sense to remove it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table
> and import it back in again. Or you can create a clustered index and then
> drop it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
>> Thanks for your response. A follow up question. Does a heap eventually
>> get so
>> fragmented that it gets corrupt? Is there a way to defrag or reindex a
>> heap
>> or do we need to go to clustered indexes. TIA
>> "Andrew J. Kelly" wrote:
>> That is because index 0 is not an index at all. Index ID of 0 indicates
>> this is a HEAP. Heaps can not be reindexed. You should add a clustered
>> index to the table and that will change the ID from 0 to 1 and allow you
>> to
>> defragment and reindex the table.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
>> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
>> > index
>> > shows
>> >
>> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 15721
>> > - Extents Scanned.......................: 2027
>> > - Extent Switches.......................: 2026
>> > - Avg. Pages per Extent..................: 7.8
>> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
>> > - Extent Scan Fragmentation ...............: 43.96%
>> > - Avg. Bytes Free per Page................: 202.3
>> > - Avg. Page Density (full)................: 97.
>> >
>> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
>> > indexes
>> > but the first on remains unchanged. Any ideas? TIA PS: All the
>> > indexes
>> > are
>> > nonclustered
>>
>|||Thanks for your response and I will check out the article.
"Paul S Randal [MS]" wrote:
> Also check out the whitepaper below which explains the options for removing
> fragmentation and when it actually makes sense to remove it.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u5KKBAYEGHA.524@.TK2MSFTNGP09.phx.gbl...
> > Fragmentation does not cause corruption but it can affect performance. The
> > only way to defrag a HEAP is to export all the data, truncate the table
> > and import it back in again. Or you can create a clustered index and then
> > drop it. But it will immediately start to get fragmented again with more
> > activity. Bottom line is that the recommendation is to have a clustered
> > index on each and every table. Have a look at this:
> >
> > http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Greg" <Greg@.discussions.microsoft.com> wrote in message
> > news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> >> Thanks for your response. A follow up question. Does a heap eventually
> >> get so
> >> fragmented that it gets corrupt? Is there a way to defrag or reindex a
> >> heap
> >> or do we need to go to clustered indexes. TIA
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first
> >> > index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the
> >> > indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
> >
> >
>
>|||Thanks for your response and I will check out the article.
"Andrew J. Kelly" wrote:
> Fragmentation does not cause corruption but it can affect performance. The
> only way to defrag a HEAP is to export all the data, truncate the table and
> import it back in again. Or you can create a clustered index and then drop
> it. But it will immediately start to get fragmented again with more
> activity. Bottom line is that the recommendation is to have a clustered
> index on each and every table. Have a look at this:
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
> > Thanks for your response. A follow up question. Does a heap eventually get
> > so
> > fragmented that it gets corrupt? Is there a way to defrag or reindex a
> > heap
> > or do we need to go to clustered indexes. TIA
> >
> > "Andrew J. Kelly" wrote:
> >
> >> That is because index 0 is not an index at all. Index ID of 0 indicates
> >> this is a HEAP. Heaps can not be reindexed. You should add a clustered
> >> index to the table and that will change the ID from 0 to 1 and allow you
> >> to
> >> defragment and reindex the table.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:A5312425-B102-4CA3-8E78-8BD00B246EFB@.microsoft.com...
> >> > When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index
> >> > shows
> >> >
> >> > Table: 'tablename' (437576597); index ID: 0, database ID: 8
> >> > TABLE level scan performed.
> >> > - Pages Scanned........................: 15721
> >> > - Extents Scanned.......................: 2027
> >> > - Extent Switches.......................: 2026
> >> > - Avg. Pages per Extent..................: 7.8
> >> > - Scan Density [Best Count:Actual Count]......: 96.99% [1966:2027]
> >> > - Extent Scan Fragmentation ...............: 43.96%
> >> > - Avg. Bytes Free per Page................: 202.3
> >> > - Avg. Page Density (full)................: 97.
> >> >
> >> > When I run DBCC DBREINDEX (tablename,'',0) it cleans up the other
> >> > indexes
> >> > but the first on remains unchanged. Any ideas? TIA PS: All the indexes
> >> > are
> >> > nonclustered
> >>
> >>
> >>
>
>