DBCC DBReindex on NonClustered

If I execute the following statement on a nonclustered index there is no
reduction in fragmentation on the index:
DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
NO_INFOMSGS
The table on which I am performing the above statement has a clustered index.
Is that the reason the above DBCC DBReindex would not reduce fragmentation
using the above statement? In other words, if a table has a clustered index,
then in order to reduce fragmentation on a nonclustered index in the same
table that has the clustered index, I would need to perform a DROP and CREATE
INDEX on the nonclustered index?
--
Message posted via http://www.sqlmonster.comcbrichards via SQLMonster.com wrote:
> If I execute the following statement on a nonclustered index there is no
> reduction in fragmentation on the index:
> DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
> NO_INFOMSGS
> The table on which I am performing the above statement has a clustered index.
> Is that the reason the above DBCC DBReindex would not reduce fragmentation
> using the above statement? In other words, if a table has a clustered index,
> then in order to reduce fragmentation on a nonclustered index in the same
> table that has the clustered index, I would need to perform a DROP and CREATE
> INDEX on the nonclustered index?
>
Please post the output of DBCC SHOWCONTIG for this index...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (862501003); index ID: 43, database ID: 61
LEAF level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 85.71%
- Extent Scan Fragmentation ...............: 42.86%
- Avg. Bytes Free per Page................: 488.7
- Avg. Page Density (full)................: 93.96%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Since your last reply I have performed the following operations:
I have dropped and recreated this nonclustered index, and there is no
difference in fragmentation.
I have dropped and recreated the clustered index, and there is no difference
in fragmentation on the clustered or nonclustered index.
Any ideas?
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Your index only has 7 pages in it. Anything less than 8 pages will use a
mixed extent. That means it will share as much as 8 extents and will always
show as fragmented. Only after you get more than 8 pages can you make them
all contiguous. But this is pretty moot anyway. I wouldn't worry about
anything with such few pages as I am sure there are more pressing issues
that you can address. I usually don't pay much attention to indexes with
less than several hundred pages at least.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64eb85d653ff4@.uwe...
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (862501003); index ID: 43, database ID: 61
> LEAF level scan performed.
> - Pages Scanned........................: 7
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
> - Logical Scan Fragmentation ..............: 85.71%
> - Extent Scan Fragmentation ...............: 42.86%
> - Avg. Bytes Free per Page................: 488.7
> - Avg. Page Density (full)................: 93.96%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
> administrator.
> Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and
>> CREATE
>> INDEX on the nonclustered index?
>>Please post the output of DBCC SHOWCONTIG for this index...
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>