dbcc reindex issue - - I dont understand!

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%

With this scan density, defragmentation may be no be very useful.

> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177

I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***

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

DBCC DBREINDEX

When I run DBCC SHOWCONTIG (tablename) WITH ALL_INDEXES the first index show
s
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 s
o
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 t
o
> 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.c...red_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/pr...n/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.c...red_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 removin
g
> fragmentation and when it actually makes sense to remove it.
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> 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 an
d
> import it back in again. Or you can create a clustered index and then dro
p
> 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.c...red_indexes.asp
>
> --
> Andrew J. Kelly SQL MVP
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:710E38A1-BE1E-4753-A1D5-D371F9416E7B@.microsoft.com...
>
>