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 CREAT
E
INDEX on the nonclustered index?
Message posted via http://www.droptable.comcbrichards via droptable.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 ind
ex.
> 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 inde
x,
> 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 CRE
ATE
> 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 syste
m
administrator.
Tracy McKibben wrote:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...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:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>
Showing posts with label nonclustered. Show all posts
Showing posts with label nonclustered. Show all posts
Wednesday, March 7, 2012
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
>
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
>
Saturday, February 25, 2012
dbcc dbreindex and tables with no clustered index
i have tables in my data base with no clustered indexes just some non
clustered ones. is there any benefit to running dbcc dbreindex on
these tables?
DBCC DBREINDEX will not do anything for your heap (data pages in a table without a clustered index).
It can defrag your non-clustered indexes, though. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199551.534111.4840@.h2g2000hsg.googlegroup s.com...
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
>
|||Derek wrote:
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
If any of your nonclustered indexes covers 100 disk pages or more, and
it is highly fragmented, and you have some SELECTS that require a full
or partial index scans, then yes, it could be beneficial. Otherwise you
probably won't see any difference.
Please note that a nonclustered index usually occupies only a few
percent when compared to the table size. So you would have to have a
really big table, or a very wide nonclustered index to notice any
effect.
HTH,
Gert-Jan
clustered ones. is there any benefit to running dbcc dbreindex on
these tables?
DBCC DBREINDEX will not do anything for your heap (data pages in a table without a clustered index).
It can defrag your non-clustered indexes, though. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199551.534111.4840@.h2g2000hsg.googlegroup s.com...
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
>
|||Derek wrote:
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
If any of your nonclustered indexes covers 100 disk pages or more, and
it is highly fragmented, and you have some SELECTS that require a full
or partial index scans, then yes, it could be beneficial. Otherwise you
probably won't see any difference.
Please note that a nonclustered index usually occupies only a few
percent when compared to the table size. So you would have to have a
really big table, or a very wide nonclustered index to notice any
effect.
HTH,
Gert-Jan
dbcc dbreindex and tables with no clustered index
i have tables in my data base with no clustered indexes just some non
clustered ones. is there any benefit to running dbcc dbreindex on
these tables?DBCC DBREINDEX will not do anything for your heap (data pages in a table wit
hout a clustered index).
It can defrag your non-clustered indexes, though. See
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199551.534111.4840@.h2g2000hsg.googlegroups.com...
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
>|||Derek wrote:
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
If any of your nonclustered indexes covers 100 disk pages or more, and
it is highly fragmented, and you have some SELECTS that require a full
or partial index scans, then yes, it could be beneficial. Otherwise you
probably won't see any difference.
Please note that a nonclustered index usually occupies only a few
percent when compared to the table size. So you would have to have a
really big table, or a very wide nonclustered index to notice any
effect.
HTH,
Gert-Jan
clustered ones. is there any benefit to running dbcc dbreindex on
these tables?DBCC DBREINDEX will not do anything for your heap (data pages in a table wit
hout a clustered index).
It can defrag your non-clustered indexes, though. See
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199551.534111.4840@.h2g2000hsg.googlegroups.com...
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
>|||Derek wrote:
> i have tables in my data base with no clustered indexes just some non
> clustered ones. is there any benefit to running dbcc dbreindex on
> these tables?
If any of your nonclustered indexes covers 100 disk pages or more, and
it is highly fragmented, and you have some SELECTS that require a full
or partial index scans, then yes, it could be beneficial. Otherwise you
probably won't see any difference.
Please note that a nonclustered index usually occupies only a few
percent when compared to the table size. So you would have to have a
really big table, or a very wide nonclustered index to notice any
effect.
HTH,
Gert-Jan
Subscribe to:
Posts (Atom)