Defrag and Index

I am by no means an SQL expert, I need to figure out if doing a nightly
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx