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; resultin
g
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 "NO
N-
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" i
ndex]
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 o
n
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" rebuil
d
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 droptable.com
http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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:[vbcol=seagreen]
>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.
>
>[quoted text clipped - 49 lines]
Message posted via http://www.droptable.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 droptable.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:
> --
> Message posted via http://www.droptable.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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 i
n
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 t
o
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:[vbcol=seagreen]
>I am a little confused by what you say. The only way to remove fragmentatio
n
>on any index is to rebuild it regardless of clustered or non-clustered. Th
e
>fragmentation is unique to each index not just the clustered index.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...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 contain
s
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) i
n
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 syste
m
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 syste
m
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 syste
m
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 syste
m
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]([Last
Name], [FirstName]
, [MiddleName], [KeyID]) ON [PRIMARY]
GO
Gert-Jan Strik wrote:[vbcol=seagreen]
>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
>
>[quoted text clipped - 37 lines]
Message posted via http://www.droptable.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 droptable.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 conta
ins
> approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and th
e
> 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 sys
tem
> 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 sys
tem
> 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:145
6]
> - Logical Scan Fragmentation ..............: 0.00%
> DBCC execution completed. If DBCC printed error messages, contact your sys
tem
> 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 sys
tem
> 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]([La
stName], [FirstName]
> , [MiddleName], [KeyID]) ON [PRIMARY]
> GO
> Gert-Jan Strik wrote:
> --
> Message posted via http://www.droptable.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:[vbcol=seagreen]
> 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 droptable.com" wrote:sql