index and key: clustered

Showing posts with label clustered. Show all posts

Wednesday, March 28, 2012

Index scan

If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>

Index related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

Monday, March 26, 2012

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

Wednesday, March 28, 2012

Index scan

Index scan

Index related problems? Whats happening here?

Monday, March 26, 2012

Index Rebuilds on Clustered NON-UNIQUE Index

Index Rebuilds on Clustered NON-UNIQUE Index

Index Rebuilds on Clustered NON-UNIQUE Index

index and key

Blog Archive

About Me