Index Rebuilds on Clustered NON-UNIQUE Index

I have read (elsewhere, not in this forum) that if you have a clustered "NON-
UNIQUE" index, that if you rebuild this type of index, the associated non-
clustered indexes on this same table are rebuilt automatically.
The basis for this is that when a "NON-UNIQUE" clustered index is created, a
8-byte unique identifier is added to non-unique key for each duplicate row,
making it a unique "clusturing key value". When this index is rebuilt, this 8-
byte identifier is updated, if necessary. This causes the "clustering key
value" to change, and there-by effecting the non-clustered indexes; resulting
in their rebuild.
So this is what I have done:
1. I issue a DBCC ShowContig to get before reindexing results.
2. I then execute the following on a table with a clustered "NON-UNIQUE"
index:
DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
3. I issue a DBCC ShowContig to get the after reindexing results.
When I compare the Before Showcontig results to the After Showcontig results,
comparing the ScanDensity and LogicalFragmentation values, the Clustered "NON-
UNIQUE" index has been rebuilt and the fragmentation no longer exists, where
the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
So far, all is well and good. However, from what I have read, as stated in
the second paragraph above, "When this index [a clustered "NON-UNIQUE" index]
is rebuilt, this 8-byte identifier is updated, if necessary. This causes the
"clustering key value" to change, and there-by effecting the non-clustered
indexes; resulting in their rebuild." How can this be true, when the Before
and After Showcontig results show the clustered "NON-UNIQUE" index was
rebuilt (the fragmentation no longer exists), yet the nonclustered indexes on
the table remain fragmented? It appears the rebuild of the clustered "NON-
UNIQUE" index DID NOT rebuild the non-clustered indexes simply by rebuilding
the clustered "NON-UNIQUE" index. What this tells me is that simply
rebuilding the clustered "NON-UNIQUE" index, does not "automatically" rebuild
the nonclustered indexes on the table. The nonclustered indexes on the table
have to be rebuilt too in order to reduce their individual fragmentation. Is
that correct?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1What version and service pack are you using? In SQL2005 this is no longer
true and may also not be true of the latest sp in 2000 (but I am not
positive on that one). They changed the way in which they assign the
uniqueafier and it is not necessary to rebuild the non-clustered indexes
when the clustered is rebuilt. If your goal is to rebuild them all you
should simply just put the table name in the DBREINDEX command and do not
specify any indexes. Then they will all be rebuilt in the most efficient
order.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:680e3c18982e8@.uwe...
>I have read (elsewhere, not in this forum) that if you have a clustered
>"NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created,
> a
> 8-byte unique identifier is added to non-unique key for each duplicate
> row,
> making it a unique "clusturing key value". When this index is rebuilt,
> this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes;
> resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig
> results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered
> "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
> where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
> index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
> the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the
> Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes
> on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
> rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
> rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the
> table
> have to be rebuilt too in order to reduce their individual fragmentation.
> Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I am running SQL 2000, standard edition, SP4.
I was trying to get away from rebuilding them all, if it eliminated the
fragmentation too. But from my findings, the only way to eliminate the
fragmentation on nonclustered indexes on the same table as a clustered NON-
UNIQUE index is to rebuild them all.
Andrew J. Kelly wrote:
>What version and service pack are you using? In SQL2005 this is no longer
>true and may also not be true of the latest sp in 2000 (but I am not
>positive on that one). They changed the way in which they assign the
>uniqueafier and it is not necessary to rebuild the non-clustered indexes
>when the clustered is rebuilt. If your goal is to rebuild them all you
>should simply just put the table name in the DBREINDEX command and do not
>specify any indexes. Then they will all be rebuilt in the most efficient
>order.
>>I have read (elsewhere, not in this forum) that if you have a clustered
>>"NON-
>[quoted text clipped - 49 lines]
>> Is
>> that correct?
--
Message posted via http://www.sqlmonster.com|||I am a little confused by what you say. The only way to remove fragmentation
on any index is to rebuild it regardless of clustered or non-clustered. The
fragmentation is unique to each index not just the clustered index.
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6810fddec11f9@.uwe...
>I am running SQL 2000, standard edition, SP4.
> I was trying to get away from rebuilding them all, if it eliminated the
> fragmentation too. But from my findings, the only way to eliminate the
> fragmentation on nonclustered indexes on the same table as a clustered
> NON-
> UNIQUE index is to rebuild them all.
> Andrew J. Kelly wrote:
>>What version and service pack are you using? In SQL2005 this is no longer
>>true and may also not be true of the latest sp in 2000 (but I am not
>>positive on that one). They changed the way in which they assign the
>>uniqueafier and it is not necessary to rebuild the non-clustered indexes
>>when the clustered is rebuilt. If your goal is to rebuild them all you
>>should simply just put the table name in the DBREINDEX command and do not
>>specify any indexes. Then they will all be rebuilt in the most efficient
>>order.
>>I have read (elsewhere, not in this forum) that if you have a clustered
>>"NON-
>>[quoted text clipped - 49 lines]
>> Is
>> that correct?
> --
> Message posted via http://www.sqlmonster.com
>|||I'm wondering why you want to rebuild the CIX without rebuilding the NCIXs?
Given that most queries are serviced by NCIXs & they fragment faster than
CIXs, it's usually more important to rebuild NCIXs than CIXs so I'm
wondering why you'd want to rebuild CIXs without rebuilding NCIXs?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:680e3c18982e8@.uwe...
>I have read (elsewhere, not in this forum) that if you have a clustered
>"NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created,
> a
> 8-byte unique identifier is added to non-unique key for each duplicate
> row,
> making it a unique "clusturing key value". When this index is rebuilt,
> this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes;
> resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig
> results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered
> "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
> where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
> index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
> the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the
> Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes
> on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
> rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
> rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the
> table
> have to be rebuilt too in order to reduce their individual fragmentation.
> Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||When I run the repro script below, I definitely see the elimination of
logical fragmentation in the nonclustered index.
When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
these are the relevant lines in the output. Notice there are two DBCC
messages, indicating the rebuilt of the NCIX (which isn't part of the
repro script).
Table: 'test' (1504827452); index ID: 1, database ID: 7
- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
- Logical Scan Fragmentation ..............: 48.72%
Table: 'test' (1504827452); index ID: 2, database ID: 7
- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
- Logical Scan Fragmentation ..............: 48.70%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Table: 'test' (1504827452); index ID: 1, database ID: 7
- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
- Logical Scan Fragmentation ..............: 0.06%
Table: 'test' (1504827452); index ID: 2, database ID: 7
- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
- Logical Scan Fragmentation ..............: 0.07%
There is the repro script:
create table test
(id int not null
,rest varchar(16) not null
)
create clustered index test_id on test(id)
create index test_rest on test(rest)
go
set nocount on
declare @.i int
set @.i=100
while @.i>0
begin
set @.i=@.i-1
insert into test
select id,checksum(newid())
from sysobjects
end
dbcc showcontig("test",'test_id')
dbcc showcontig("test",'test_rest')
go
dbcc dbreindex("test",'test_id',90)
go
dbcc showcontig("test",'test_id')
dbcc showcontig("test",'test_rest')
go
drop table test
By the way: AFAIK, the uniquefier that is added to the nonunique
clustered key is 4 bytes, not 8 bytes.
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created, a
> 8-byte unique identifier is added to non-unique key for each duplicate row,
> making it a unique "clusturing key value". When this index is rebuilt, this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes; resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists, where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE" index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically" rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the table
> have to be rebuilt too in order to reduce their individual fragmentation. Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||Gert-Jan
>> > By the way: AFAIK, the uniquefier that is added to the nonunique
> clustered key is 4 bytes, not 8 bytes.
>
This is correct, and can be easily observed by running DBCC PAGE.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:453BE6C4.2DD97820@.toomuchspamalready.nl...
> When I run the repro script below, I definitely see the elimination of
> logical fragmentation in the nonclustered index.
> When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> these are the relevant lines in the output. Notice there are two DBCC
> messages, indicating the rebuilt of the NCIX (which isn't part of the
> repro script).
> Table: 'test' (1504827452); index ID: 1, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> - Logical Scan Fragmentation ..............: 48.72%
> Table: 'test' (1504827452); index ID: 2, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> - Logical Scan Fragmentation ..............: 48.70%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Table: 'test' (1504827452); index ID: 1, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> - Logical Scan Fragmentation ..............: 0.06%
> Table: 'test' (1504827452); index ID: 2, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> - Logical Scan Fragmentation ..............: 0.07%
> There is the repro script:
> create table test
> (id int not null
> ,rest varchar(16) not null
> )
> create clustered index test_id on test(id)
> create index test_rest on test(rest)
> go
> set nocount on
> declare @.i int
> set @.i=100
> while @.i>0
> begin
> set @.i=@.i-1
> insert into test
> select id,checksum(newid())
> from sysobjects
> end
>
> dbcc showcontig("test",'test_id')
> dbcc showcontig("test",'test_rest')
> go
> dbcc dbreindex("test",'test_id',90)
> go
> dbcc showcontig("test",'test_id')
> dbcc showcontig("test",'test_rest')
> go
> drop table test
>
> By the way: AFAIK, the uniquefier that is added to the nonunique
> clustered key is 4 bytes, not 8 bytes.
> HTH,
> Gert-Jan
>
> "cbrichards via SQLMonster.com" wrote:
>> I have read (elsewhere, not in this forum) that if you have a clustered
>> "NON-
>> UNIQUE" index, that if you rebuild this type of index, the associated
>> non-
>> clustered indexes on this same table are rebuilt automatically.
>> The basis for this is that when a "NON-UNIQUE" clustered index is
>> created, a
>> 8-byte unique identifier is added to non-unique key for each duplicate
>> row,
>> making it a unique "clusturing key value". When this index is rebuilt,
>> this 8-
>> byte identifier is updated, if necessary. This causes the "clustering key
>> value" to change, and there-by effecting the non-clustered indexes;
>> resulting
>> in their rebuild.
>> So this is what I have done:
>> 1. I issue a DBCC ShowContig to get before reindexing results.
>> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
>> index:
>> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index',
>> 90)
>> 3. I issue a DBCC ShowContig to get the after reindexing results.
>> When I compare the Before Showcontig results to the After Showcontig
>> results,
>> comparing the ScanDensity and LogicalFragmentation values, the Clustered
>> "NON-
>> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
>> where
>> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
>> So far, all is well and good. However, from what I have read, as stated
>> in
>> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
>> index]
>> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
>> the
>> "clustering key value" to change, and there-by effecting the
>> non-clustered
>> indexes; resulting in their rebuild." How can this be true, when the
>> Before
>> and After Showcontig results show the clustered "NON-UNIQUE" index was
>> rebuilt (the fragmentation no longer exists), yet the nonclustered
>> indexes on
>> the table remain fragmented? It appears the rebuild of the clustered
>> "NON-
>> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
>> rebuilding
>> the clustered "NON-UNIQUE" index. What this tells me is that simply
>> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
>> rebuild
>> the nonclustered indexes on the table. The nonclustered indexes on the
>> table
>> have to be rebuilt too in order to reduce their individual fragmentation.
>> Is
>> that correct?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||Sorry Andrew. Let me try to clarify. I have a procedure (on SQL 2000, SP4)
that rebuilds all indexes (clustered and nonclustered) using DBCC DBReindex
based upon Scan Density and Logical Fragmentation. I have a window of time in
order to perform this every week. As the database grows, the rebuilding of
indexing takes longer, but my window of time to perform this weekly index
rebuild stays the same. The day will come when the rebuild will take longer
than the window of time allotted.
As I stated in my original email, I read that if you rebuild a "NON-UNIQUE"
Clustered Index, this would automatically rebuild all the nonclustered
indexes on the table too. That would save a little bit of time, not having to
loop through stored procedure code to redundantly rebuild the nonclustered
indexes individually, since they would get rebuilt automatically.
So what I meant when I said getting "away from rebuilding them all", was
actually getting away from looping through stored procedure code to rebuild
the nonclustered indexes individually, when they are already getting rebuilt
when the "NON-UNIQUE" clustered index is getting rebuilt.
Andrew J. Kelly wrote:
>I am a little confused by what you say. The only way to remove fragmentation
>on any index is to rebuild it regardless of clustered or non-clustered. The
>fragmentation is unique to each index not just the clustered index.
>>I am running SQL 2000, standard edition, SP4.
>[quoted text clipped - 18 lines]
>> Is
>> that correct?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||I ran your script and came up with the same results.
I then ran your script, to go against an existing table of mine that contains
approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
results show the "NON-UNIQUE" clustered index was rebuilt, but the
nonclustered index was not rebuilt. I have included the DBCC Showcontig
results and the Create statement for the table and indexes below. Can you
explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
your example, but does not work for my existing table?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 1, database ID: 13
- Pages Scanned........................: 12364
- Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
- Logical Scan Fragmentation ..............: 20.71%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 42, database ID: 13
LEAF level scan performed.
- Pages Scanned........................: 2988
- Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
- Logical Scan Fragmentation ..............: 29.79%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
dbcc showcontig("MyTable",'PK_MyTable')
dbcc showcontig("MyTable",'IDX_Sort')
go
dbcc dbreindex("MyTable",'PK_MyTable',90)
go
dbcc showcontig("MyTable",'PK_MyTable')
dbcc showcontig("MyTable",'IDX_Sort')
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 11645
- Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
- Logical Scan Fragmentation ..............: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 42, database ID: 13
LEAF level scan performed.
- Pages Scanned........................: 2988
- Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
- Logical Scan Fragmentation ..............: 29.79%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
CREATE TABLE [dbo].[MyTable](
[KeyID] [int] NOT NULL,
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](35) NOT NULL,
[FirstName] [varchar](35) NOT NULL,
[MiddleName] [varchar](35) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[PersonID] ASC,
[KeyID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
, [MiddleName], [KeyID]) ON [PRIMARY]
GO
Gert-Jan Strik wrote:
>When I run the repro script below, I definitely see the elimination of
>logical fragmentation in the nonclustered index.
>When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
>these are the relevant lines in the output. Notice there are two DBCC
>messages, indicating the rebuilt of the NCIX (which isn't part of the
>repro script).
>Table: 'test' (1504827452); index ID: 1, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
>- Logical Scan Fragmentation ..............: 48.72%
>Table: 'test' (1504827452); index ID: 2, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
>- Logical Scan Fragmentation ..............: 48.70%
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>Table: 'test' (1504827452); index ID: 1, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
>- Logical Scan Fragmentation ..............: 0.06%
>Table: 'test' (1504827452); index ID: 2, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
>- Logical Scan Fragmentation ..............: 0.07%
>There is the repro script:
>create table test
>(id int not null
>,rest varchar(16) not null
>)
>create clustered index test_id on test(id)
>create index test_rest on test(rest)
>go
>set nocount on
>declare @.i int
>set @.i=100
>while @.i>0
>begin
> set @.i=@.i-1
> insert into test
> select id,checksum(newid())
> from sysobjects
>end
>dbcc showcontig("test",'test_id')
>dbcc showcontig("test",'test_rest')
>go
>dbcc dbreindex("test",'test_id',90)
>go
>dbcc showcontig("test",'test_id')
>dbcc showcontig("test",'test_rest')
>go
>drop table test
>By the way: AFAIK, the uniquefier that is added to the nonunique
>clustered key is 4 bytes, not 8 bytes.
>HTH,
>Gert-Jan
>> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
>> UNIQUE" index, that if you rebuild this type of index, the associated non-
>[quoted text clipped - 37 lines]
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
--
Message posted via http://www.sqlmonster.com|||The only explanation I can think of, is that the clustered index does
not contain any duplicate value. Because then, rebuilding the NC indexes
would not be possible. It is quite likely that there are no duplicates
because of the use of the Identity.
You can check your table with the query below. If it returns nothing,
then there are no duplicates.
SELECT PersonID, KeyID
FROM MyTable
GROUP BY PersonID, KeyID
HAVING COUNT(*)>1
You could try adding two duplicate dummy rows...
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> I ran your script and came up with the same results.
> I then ran your script, to go against an existing table of mine that contains
> approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
> results show the "NON-UNIQUE" clustered index was rebuilt, but the
> nonclustered index was not rebuilt. I have included the DBCC Showcontig
> results and the Create statement for the table and indexes below. Can you
> explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
> your example, but does not work for my existing table?
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> - Pages Scanned........................: 12364
> - Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
> - Logical Scan Fragmentation ..............: 20.71%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> LEAF level scan performed.
> - Pages Scanned........................: 2988
> - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> - Logical Scan Fragmentation ..............: 29.79%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> dbcc showcontig("MyTable",'PK_MyTable')
> dbcc showcontig("MyTable",'IDX_Sort')
> go
> dbcc dbreindex("MyTable",'PK_MyTable',90)
> go
> dbcc showcontig("MyTable",'PK_MyTable')
> dbcc showcontig("MyTable",'IDX_Sort')
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> TABLE level scan performed.
> - Pages Scanned........................: 11645
> - Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
> - Logical Scan Fragmentation ..............: 0.00%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> LEAF level scan performed.
> - Pages Scanned........................: 2988
> - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> - Logical Scan Fragmentation ..............: 29.79%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> CREATE TABLE [dbo].[MyTable](
> [KeyID] [int] NOT NULL,
> [PersonID] [int] IDENTITY(1,1) NOT NULL,
> [LastName] [varchar](35) NOT NULL,
> [FirstName] [varchar](35) NOT NULL,
> [MiddleName] [varchar](35) NOT NULL,
> CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
> (
> [PersonID] ASC,
> [KeyID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
> , [MiddleName], [KeyID]) ON [PRIMARY]
> GO
> Gert-Jan Strik wrote:
> >When I run the repro script below, I definitely see the elimination of
> >logical fragmentation in the nonclustered index.
> >
> >When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> >these are the relevant lines in the output. Notice there are two DBCC
> >messages, indicating the rebuilt of the NCIX (which isn't part of the
> >repro script).
> >
> >Table: 'test' (1504827452); index ID: 1, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> >- Logical Scan Fragmentation ..............: 48.72%
> >
> >Table: 'test' (1504827452); index ID: 2, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> >- Logical Scan Fragmentation ..............: 48.70%
> >
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >
> >Table: 'test' (1504827452); index ID: 1, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> >- Logical Scan Fragmentation ..............: 0.06%
> >
> >Table: 'test' (1504827452); index ID: 2, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> >- Logical Scan Fragmentation ..............: 0.07%
> >
> >There is the repro script:
> >
> >create table test
> >(id int not null
> >,rest varchar(16) not null
> >)
> >create clustered index test_id on test(id)
> >create index test_rest on test(rest)
> >go
> >
> >set nocount on
> >declare @.i int
> >set @.i=100
> >while @.i>0
> >begin
> > set @.i=@.i-1
> >
> > insert into test
> > select id,checksum(newid())
> > from sysobjects
> >end
> >
> >dbcc showcontig("test",'test_id')
> >dbcc showcontig("test",'test_rest')
> >go
> >
> >dbcc dbreindex("test",'test_id',90)
> >go
> >
> >dbcc showcontig("test",'test_id')
> >dbcc showcontig("test",'test_rest')
> >
> >go
> >drop table test
> >
> >By the way: AFAIK, the uniquefier that is added to the nonunique
> >clustered key is 4 bytes, not 8 bytes.
> >
> >HTH,
> >Gert-Jan
> >
> >> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> >> UNIQUE" index, that if you rebuild this type of index, the associated non-
> >[quoted text clipped - 37 lines]
> >> Message posted via SQLMonster.com
> >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
> --
> Message posted via http://www.sqlmonster.com|||Gert-Jan Strik wrote:
> The only explanation I can think of, is that the clustered index does
> not contain any duplicate value. Because then, rebuilding the NC indexes
> would not be possible. It is quite likely that there are no duplicates
> because of the use of the Identity.
I meant to say "necessary" here, instead of "possible".
> You can check your table with the query below. If it returns nothing,
> then there are no duplicates.
> SELECT PersonID, KeyID
> FROM MyTable
> GROUP BY PersonID, KeyID
> HAVING COUNT(*)>1
> You could try adding two duplicate dummy rows...
> HTH,
> Gert-Jan|||Ahh, forget this theory. I just added an identity column to the repro
script (and to the clustered index), and a DBCC DBREINDEX of the
clustered index would still cause the nonclustered index to be rebuilt.
I have no idea why it doesn't work for you. Are you sure you have SQL
Server SP4a installed? What version are you running (the output of
SELECT @.@.version)? I think it should be 8.00.2039 or 8.00.2040.
Gert-Jan
Gert-Jan Strik wrote:
> The only explanation I can think of, is that the clustered index does
> not contain any duplicate value. Because then, rebuilding the NC indexes
> would not be possible. It is quite likely that there are no duplicates
> because of the use of the Identity.
> You can check your table with the query below. If it returns nothing,
> then there are no duplicates.
> SELECT PersonID, KeyID
> FROM MyTable
> GROUP BY PersonID, KeyID
> HAVING COUNT(*)>1
> You could try adding two duplicate dummy rows...
> HTH,
> Gert-Jan
> "cbrichards via SQLMonster.com" wrote:
> >
> > I ran your script and came up with the same results.
> >
> > I then ran your script, to go against an existing table of mine that contains
> > approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
> > results show the "NON-UNIQUE" clustered index was rebuilt, but the
> > nonclustered index was not rebuilt. I have included the DBCC Showcontig
> > results and the Create statement for the table and indexes below. Can you
> > explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
> > your example, but does not work for my existing table?
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> > - Pages Scanned........................: 12364
> > - Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
> > - Logical Scan Fragmentation ..............: 20.71%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> > LEAF level scan performed.
> > - Pages Scanned........................: 2988
> > - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> > - Logical Scan Fragmentation ..............: 29.79%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > dbcc showcontig("MyTable",'PK_MyTable')
> > dbcc showcontig("MyTable",'IDX_Sort')
> > go
> >
> > dbcc dbreindex("MyTable",'PK_MyTable',90)
> > go
> >
> > dbcc showcontig("MyTable",'PK_MyTable')
> > dbcc showcontig("MyTable",'IDX_Sort')
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> > TABLE level scan performed.
> > - Pages Scanned........................: 11645
> > - Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
> > - Logical Scan Fragmentation ..............: 0.00%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> > LEAF level scan performed.
> > - Pages Scanned........................: 2988
> > - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> > - Logical Scan Fragmentation ..............: 29.79%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > CREATE TABLE [dbo].[MyTable](
> > [KeyID] [int] NOT NULL,
> > [PersonID] [int] IDENTITY(1,1) NOT NULL,
> > [LastName] [varchar](35) NOT NULL,
> > [FirstName] [varchar](35) NOT NULL,
> > [MiddleName] [varchar](35) NOT NULL,
> >
> > CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
> > (
> > [PersonID] ASC,
> > [KeyID] ASC
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
> > , [MiddleName], [KeyID]) ON [PRIMARY]
> > GO
> >
> > Gert-Jan Strik wrote:
> > >When I run the repro script below, I definitely see the elimination of
> > >logical fragmentation in the nonclustered index.
> > >
> > >When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> > >these are the relevant lines in the output. Notice there are two DBCC
> > >messages, indicating the rebuilt of the NCIX (which isn't part of the
> > >repro script).
> > >
> > >Table: 'test' (1504827452); index ID: 1, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> > >- Logical Scan Fragmentation ..............: 48.72%
> > >
> > >Table: 'test' (1504827452); index ID: 2, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> > >- Logical Scan Fragmentation ..............: 48.70%
> > >
> > >DBCC execution completed. If DBCC printed error messages, contact your
> > >system administrator.
> > >DBCC execution completed. If DBCC printed error messages, contact your
> > >system administrator.
> > >
> > >Table: 'test' (1504827452); index ID: 1, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> > >- Logical Scan Fragmentation ..............: 0.06%
> > >
> > >Table: 'test' (1504827452); index ID: 2, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> > >- Logical Scan Fragmentation ..............: 0.07%
> > >
> > >There is the repro script:
> > >
> > >create table test
> > >(id int not null
> > >,rest varchar(16) not null
> > >)
> > >create clustered index test_id on test(id)
> > >create index test_rest on test(rest)
> > >go
> > >
> > >set nocount on
> > >declare @.i int
> > >set @.i=100
> > >while @.i>0
> > >begin
> > > set @.i=@.i-1
> > >
> > > insert into test
> > > select id,checksum(newid())
> > > from sysobjects
> > >end
> > >
> > >dbcc showcontig("test",'test_id')
> > >dbcc showcontig("test",'test_rest')
> > >go
> > >
> > >dbcc dbreindex("test",'test_id',90)
> > >go
> > >
> > >dbcc showcontig("test",'test_id')
> > >dbcc showcontig("test",'test_rest')
> > >
> > >go
> > >drop table test
> > >
> > >By the way: AFAIK, the uniquefier that is added to the nonunique
> > >clustered key is 4 bytes, not 8 bytes.
> > >
> > >HTH,
> > >Gert-Jan
> > >
> > >> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> > >> UNIQUE" index, that if you rebuild this type of index, the associated non-
> > >[quoted text clipped - 37 lines]
> > >> Message posted via SQLMonster.com
> > >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
> >
> > --
> > Message posted via http://www.sqlmonster.com|||Yes if you want to rebuild all of them as I stated before just specify the
table name and NOT the index name. That will rebuild all indexes on the
table regardless of if the clustered index is unique or not. If you specify
the index name and it happens to be the clustered index it works in this
fashion.
DBCC DBREINDEX(TableName, CI IndexName)
CI = Unique
Rebuilds only the CI
CI = Not Uniquie
Rebuilds all indexes
I suspect that the clustered index is Unique even though you may not have
specified it as such. Can you show the actual DDL for the table with all the
indexes?
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6833048040ad2@.uwe...
> Sorry Andrew. Let me try to clarify. I have a procedure (on SQL 2000, SP4)
> that rebuilds all indexes (clustered and nonclustered) using DBCC
> DBReindex
> based upon Scan Density and Logical Fragmentation. I have a window of time
> in
> order to perform this every week. As the database grows, the rebuilding of
> indexing takes longer, but my window of time to perform this weekly index
> rebuild stays the same. The day will come when the rebuild will take
> longer
> than the window of time allotted.
> As I stated in my original email, I read that if you rebuild a
> "NON-UNIQUE"
> Clustered Index, this would automatically rebuild all the nonclustered
> indexes on the table too. That would save a little bit of time, not having
> to
> loop through stored procedure code to redundantly rebuild the nonclustered
> indexes individually, since they would get rebuilt automatically.
> So what I meant when I said getting "away from rebuilding them all", was
> actually getting away from looping through stored procedure code to
> rebuild
> the nonclustered indexes individually, when they are already getting
> rebuilt
> when the "NON-UNIQUE" clustered index is getting rebuilt.
> Andrew J. Kelly wrote:
>>I am a little confused by what you say. The only way to remove
>>fragmentation
>>on any index is to rebuild it regardless of clustered or non-clustered.
>>The
>>fragmentation is unique to each index not just the clustered index.
>>I am running SQL 2000, standard edition, SP4.
>>[quoted text clipped - 18 lines]
>> Is
>> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I ran the statement you included in your earlier post, and indeed, the
clustered index does
not contain any duplicate values. Your theory appears to be correct.
By the way, I am running 8.00.2040.
Gert-Jan Strik wrote:
>Ahh, forget this theory. I just added an identity column to the repro
>script (and to the clustered index), and a DBCC DBREINDEX of the
>clustered index would still cause the nonclustered index to be rebuilt.
>I have no idea why it doesn't work for you. Are you sure you have SQL
>Server SP4a installed? What version are you running (the output of
>SELECT @.@.version)? I think it should be 8.00.2039 or 8.00.2040.
>Gert-Jan
>> The only explanation I can think of, is that the clustered index does
>> not contain any duplicate value. Because then, rebuilding the NC indexes
>[quoted text clipped - 166 lines]
>> > --
>> > Message posted via http://www.sqlmonster.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1