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
nonclustered
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. 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...
>
>
|||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.co...ed_indexes.asp
Andrew J. Kelly SQL MVP
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...[vbcol=seagreen]
> 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:
|||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/pro.../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.co...ed_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 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/pro.../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...
>
>
|||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.co...ed_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
>
>