deadlock with Win2003 and Clustered SQL Server 2000

Hi!
We've encountered a strange deadlock problem when migrating to a loadbalance
d Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on
two Windows 2000 servers.
The scenario is that we receive 10.000 records in XML which need to be inser
ted into - or updated in the database depending on whether we already know t
he record or not. The problem is that the system suddenly deadlocks while ru
nning a simpel UPDATE. The
UPDATE does however results in triggering an update trigger which in turn tr
iggers another trigger. This has not been a problem on 3 other production se
rvers running the same software and receiving the same amount of data. The o
nly difference is the use o
f Win2003 as frontend and a clustered SQL Server.
We do not currently use Explicit Transactions - but has tried it without any
luck. We've done traces which show the records involved in the deadlock has
n't got any obvious relations - other than being in the same table (differen
t primary keys al around).
A friend of mine has experienced a similary problem - and said it had someth
ing to do with Win2003 and SQL2000 together, but can't find any information
regarding this particullar problem...
Any hint would be greatly appriciated
Best regards,
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs s
ome fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND d
eletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Lef
t(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupi
d;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap
a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two diff
erent records that you are sure of doesn't link to any shared records throug
h foreign keys!
Regards,
Michael B. Hansen
-- Michael B. Hansen wrote: --
Update(!)
We've found a way to reproduce the problem in a consistent maner - and the p
roblem only shows itself on a Windows Server 2003. Neither of our other 5 se
rver setups can reproduce the problem - on the almost identical machine setu
p running Windows 2000|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...

quote:

---

> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two

---

different records that you are sure of doesn't link to any shared records
through foreign keys!

quote:

---

>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -

---

and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.

quote:

---

> The way to reproduce the problem is to have an UPDATE-trigger that

---

SELECTs some fields from the updated row and UPDATEs another row:

quote:

---

> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE

---

personid=797 AND deletetime IS NULL AND userpoolid=0

quote:

---

>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,

---

50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;

quote:

---

> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections

---

and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:

quote:

---

> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen

---

sql

deadlock with Win2003 and Clustered SQL Server 2000

Hi
We've encountered a strange deadlock problem when migrating to a loadbalanced Windows Server 2003 frontend webserver and a clustered SQL Server 2000 on two Windows 2000 servers
The scenario is that we receive 10.000 records in XML which need to be inserted into - or updated in the database depending on whether we already know the record or not. The problem is that the system suddenly deadlocks while running a simpel UPDATE. The UPDATE does however results in triggering an update trigger which in turn triggers another trigger. This has not been a problem on 3 other production servers running the same software and receiving the same amount of data. The only difference is the use of Win2003 as frontend and a clustered SQL Server
We do not currently use Explicit Transactions - but has tried it without any luck. We've done traces which show the records involved in the deadlock hasn't got any obvious relations - other than being in the same table (different primary keys al around)
A friend of mine has experienced a similary problem - and said it had something to do with Win2003 and SQL2000 together, but can't find any information regarding this particullar problem...
Any hint would be greatly appriciated :
Best regards
Michael B. HansenUpdate(!)
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000.
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row:
initial UPDATE:
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=0
trigger:
(...) start of trigger (...)
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
BEGIN
SELECT fname, lname, groupid, email INTO #tmp FROM inserted
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid;
END;
(...) end of trigger (...)
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
while 1=1 begin
UPDATE gc_persons ....
end
Do anyone know of a fix for this?
Regards,
Michael B. Hansen|||Oh yes - to reproduce the problem in a true maner, do the UPDATE on two different records that you are sure of doesn't link to any shared records through foreign keys
Regards
Michael B. Hanse
-- Michael B. Hansen wrote: --
Update(!
We've found a way to reproduce the problem in a consistent maner - and the problem only shows itself on a Windows Server 2003. Neither of our other 5 server setups can reproduce the problem - on the almost identical machine setup running Windows 2000
The way to reproduce the problem is to have an UPDATE-trigger that SELECTs some fields from the updated row and UPDATEs another row
initial UPDATE
UPDATE gc_persons SET fname='test34',lname='test34' WHERE personid=797 AND deletetime IS NULL AND userpoolid=
trigger
(...) start of trigger (...
IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email
BEGI
SELECT fname, lname, groupid, email INTO #tmp FROM inserte
UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname, 50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE gc_groups.groupid=#tmp.groupid
END
(...) end of trigger (...
To reproduce the problem run the UPDATE on 2 different connections and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE
while 1=1 begi
UPDATE gc_persons ...
en
Do anyone know of a fix for this
Regards
Michael B. Hansen|||Could you provide the code to the second trigger that fires on the gc_groups
table?
-Lars
"Michael B. Hansen" <anonymous@.discussions.microsoft.com> wrote in message
news:A7CE66EC-162D-4FE3-8D39-5E0E2078A907@.microsoft.com...
> Oh yes - to reproduce the problem in a true maner, do the UPDATE on two
different records that you are sure of doesn't link to any shared records
through foreign keys!
>
> Regards,
> Michael B. Hansen
> -- Michael B. Hansen wrote: --
> Update(!)
> We've found a way to reproduce the problem in a consistent maner -
and the problem only shows itself on a Windows Server 2003. Neither of our
other 5 server setups can reproduce the problem - on the almost identical
machine setup running Windows 2000.
> The way to reproduce the problem is to have an UPDATE-trigger that
SELECTs some fields from the updated row and UPDATEs another row:
> initial UPDATE:
> UPDATE gc_persons SET fname='test34',lname='test34' WHERE
personid=797 AND deletetime IS NULL AND userpoolid=0
>
> trigger:
> (...) start of trigger (...)
> IF UPDATE(fname) OR UPDATE(lname) OR UPDATE(email)
> BEGIN
> SELECT fname, lname, groupid, email INTO #tmp FROM inserted
> UPDATE gc_groups SET name=Left(#tmp.fname + ' ' + #tmp.lname,
50), email=Left(IsNull(#tmp.email, ''), 100) FROM #tmp WHERE
gc_groups.groupid=#tmp.groupid;
> END;
> (...) end of trigger (...)
>
> To reproduce the problem run the UPDATE on 2 different connections
and wrap a "while 1=1 begin" UPDATE "end" around the UPDATE:
> while 1=1 begin
> UPDATE gc_persons ....
> end
>
> Do anyone know of a fix for this?
> Regards,
> Michael B. Hansen|||I've found a workaround for the problem
It seems that SP3 has some kind of a bug with regards to update-triggers
1) The 'UPDATE(field)' function doesn't seem to give do the check correctly - it seems to always return true
eg.
IF UPDATE(field1
BEGI
END
is always execute
2) The "UPDATE <table> SET field=value FROM inserted" seems to lock a whole page (or something like that) instead of the individual rows it updates. The workaround to this is to include WITH (UPDLOCK) in the UPDATE-clause
eg.
UPDATE <table> WITH (UPDLOCK) SET field=value FROM inserte
These two issues first showed themselves after(!) we updated to SP3 - and we've only been able to reproduce the second issue on a clustered SQLServer2000 running on a Windows Server 2003
Regards
Michael B. HAnsen

Deadlock on simple update related to clustered index

I am encountering a deadlock situation, with two threads
running two updates to the same table, if that table has a
clustered index on it that is NOT the primary key, and the
where clause of the UPDATE statement is on the primary
key. Very strange... Here is the test case:
create table x
(id int not null,
lastModified datetime not null,
altkey varchar(3) not null)
GO
CREATE UNIQUE CLUSTERED INDEX IAK_X ON x (altKey)
GO
alter table x add constraint pk_x primary key(id)
GO
insert into x
values (1, getdate(), 'XXX');
Then, from 2 ISQL threads, ran the following code...
begin tran
waitfor time '15:06' -- ensure they start at same time
update x
set lastModified = getdate()
where id = 1;
waitfor delay '000:00:10' -- wait to ensure lock occurs
update x
set lastModified = getdate()
where id = 1;
commit tran;
When I monitor the locks, the following happens:
Thread A successfully performs Update 1
Thread B is blocked as A has Exclusive lock on the row.
Thread B, however, successfully gets a KEY UPDATE lock on
PK_X
Thread A, when it tries to perform Update 2, is blocked
trying to get a KEY UPDATE lock on PK_X, causing the
deadlock.
Even if I then drop the clustered index with
DROP INDEX x.iak_x;
the problem remains.
However, if I NEVER create the clustered index. The
threads both work fine and never encounter a deadlock.
Neither transaction attempts to get a KEY UPDATE lock on
the primary key index PK_X.
We try to use clustered indexes on fields other than the
ID # (one-up number, but NOT an IDENTITY column in this
case), because we encountered deadlocks on Inserts in the
past.
Any help would be greatly appreciated.On Thu, 24 Jul 2003 14:02:58 -0700, "Corey" <chorton1@.austin.rr.com>
wrote:
>Any help would be greatly appreciated.
That's pretty humorous.
I've never gotten into the detailed guts of SQLServer like some around
here, but it sounds to me like you've hit a SQLServer bug. It looks
like SQLServer locks the data record in thread A, but allows thread B
to begin and get a lock on an index entry while waiting for the data,
and voila, deadlock. If that is the case, I'd call it a bug.
I assume it works correctly if you have the PK on id?
J.sql

DBReindex on Clustered Idex

If the rebuild causes root node of clustered index to be changed then it is
a must for SQL Server to update(rebuild) nonclustered index no matter wich k
ind!!!
Example:
Suppose that you're library (clustered index) is in Street One, and your pat
h to the library(nonclustered) is Way 1. If library moves to Street Two ther
e is no point to go through Way 1 as we never get to Street One, but Way 2.
Eh, funny, isn't it?
More info "Indexing Architecture" BOL
Hope it HelpsThat is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...

quote:

---

> If the rebuild causes root node of clustered index to be changed then it

---

is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!

quote:

---

> Example:
> Suppose that you're library (clustered index) is in Street One, and your

---

path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.

quote:

---

> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps

---

|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:

quote:

---

> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by t
he
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each r
ow
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link ba
ck
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine

---

<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...

quote:

---

> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
clustered[QUOTE]
the[QUOTE]
added[QUOTE]
row[QUOTE]
clustered[QUOTE]
back[QUOTE]
non-unique[QUOTE]
per[QUOTE]
> <snip>

---

|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:[QUOTE]
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values wil
l
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> clustered
> the
> added
> row
> clustered
> back
> non-unique
> per

DBReindex on Clustered Idex

I'm trying to reconcile a difference of information
regarding the effect of running a DBReindex on clustered
index when non-clustered indexes exist.
According to the MSCE Training Kit (SQL Server Database
Design and Implementation), the following sentence
reads "To rebuild all indexes, instruct DBCC DBReindex to
rebuild the clustered index, thereby causing a rebuild of
all indexes on a table or view".
However according to Microsoft Knowledge Base Article
304519, the symptom of bug 354670 is that using either the
Create/Drop existing syntax or DBCC DNReindex syntax on a
clustered index results in both clustered and non-
clustered indexes being rebuilt. This is consistent with
the Training Kit, but here is is mentioned as being a bug
that has been corrected with the service pack. As the
article mentions, unless a non-unique clustered index is
being rebuilt, there should be no impact on non-clustered
indexes.
Can someone point out the correct result then of applying
a rebuild on a clustered index? From my perspective, if
the condition is a bug, why was it presented not so in the
Training Kit? And if the effect of applying a service pack
does change the behaviour, it would make answering any
questions on exams difficult unless one knew the service
pack level. After all, both publications are through
Microsoft.i believe what they're calling a "bug" is that when you rebuilt a unique
clustered index before sp2, it rebuilt all of the non-clustered indexes
when it didn't need to rebuild the non-clustered indexes. see the
"more info" section of
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519
Baz Star wrote:
> I'm trying to reconcile a difference of information
> regarding the effect of running a DBReindex on clustered
> index when non-clustered indexes exist.
> According to the MSCE Training Kit (SQL Server Database
> Design and Implementation), the following sentence
> reads "To rebuild all indexes, instruct DBCC DBReindex to
> rebuild the clustered index, thereby causing a rebuild of
> all indexes on a table or view".
> However according to Microsoft Knowledge Base Article
> 304519, the symptom of bug 354670 is that using either the
> Create/Drop existing syntax or DBCC DNReindex syntax on a
> clustered index results in both clustered and non-
> clustered indexes being rebuilt. This is consistent with
> the Training Kit, but here is is mentioned as being a bug
> that has been corrected with the service pack. As the
> article mentions, unless a non-unique clustered index is
> being rebuilt, there should be no impact on non-clustered
> indexes.
> Can someone point out the correct result then of applying
> a rebuild on a clustered index? From my perspective, if
> the condition is a bug, why was it presented not so in the
> Training Kit? And if the effect of applying a service pack
> does change the behaviour, it would make answering any
> questions on exams difficult unless one knew the service
> pack level. After all, both publications are through
> Microsoft.|||That is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...
> If the rebuild causes root node of clustered index to be changed then it
is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!
> Example:
> Suppose that you're library (clustered index) is in Street One, and your
path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.
> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:
> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by the
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each row
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link back
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
> >
> > That is not true.
> >
> > Non-clustered indexes do not contain physical links back to the
clustered
> > index, just logical links (i.e. the cluster keys). The root node is only
> > stored in database metadata and so is irrelevant to this discussion.
> >
> > For a unique clustered index, the cluster keys are exactly as defined by
the
> > table schema and rebuilding a clustered index does not change any of the
> > cluster keys so none of the non-clustered indexes must be rebuilt.
> >
> > For a non-unique clustered index, an artificial uniquifier column is
added
> > to the defined cluster keys to give a unique logical identifier for each
row
> > in the clustered index. Non-clustered indexes over a non-unique
clustered
> > index have to include the uniquifier column as part of the logical link
back
> > to the clustered index. The uniquifier is regenerated when the
non-unique
> > clustered index is rebuilt, so if any non-clustered indexes must also be
> > rebuilt to pick up the new uniquifier values.
> >
> > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
per
> > se, but a performance drain. The problem was fixed in SP2.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> <snip>|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values will
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> > Paul,
> >
> > if a non-unique clustered index contains no duplicate keys, will other
> > indexes still be reindexed, or is SQL-Server smart enough to only
> > reindex after finding a duplicate in the clustered index tree?
> >
> > Thanks,
> > Gert-Jan
> >
> >
> > "Paul S Randal [MS]" wrote:
> > >
> > > That is not true.
> > >
> > > Non-clustered indexes do not contain physical links back to the
> clustered
> > > index, just logical links (i.e. the cluster keys). The root node is only
> > > stored in database metadata and so is irrelevant to this discussion.
> > >
> > > For a unique clustered index, the cluster keys are exactly as defined by
> the
> > > table schema and rebuilding a clustered index does not change any of the
> > > cluster keys so none of the non-clustered indexes must be rebuilt.
> > >
> > > For a non-unique clustered index, an artificial uniquifier column is
> added
> > > to the defined cluster keys to give a unique logical identifier for each
> row
> > > in the clustered index. Non-clustered indexes over a non-unique
> clustered
> > > index have to include the uniquifier column as part of the logical link
> back
> > > to the clustered index. The uniquifier is regenerated when the
> non-unique
> > > clustered index is rebuilt, so if any non-clustered indexes must also be
> > > rebuilt to pick up the new uniquifier values.
> > >
> > > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
> per
> > > se, but a performance drain. The problem was fixed in SP2.
> > >
> > > Regards.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > <snip>