June 2, 2008 at 5:35 am
I have this update SQL :
UPDATE Table SET [Status]=1 WHERE CCID=@CCID
where CCID is the primary key, but the PK index is non-clustered, I have another clustered index because of some optimization issues.
Now for some reason ,this UPDATE is generating a huge Execution Plan involving a lot of other tables (to whom I have foreign keys), on columns that have nothing to do with this query!
If I drop the other clustered index, and then cluster the table on the primary key, then the Execution Plan looks normal.
I have attempted to reproduce this situation by creating a few dummy tables and linking them with foreign keys, but the Execution Plan in my TestDB looks normal. Only in this DB it looks crazy like this (see attachment).
Anyone knows why SQL is doing this? And how I can stop it ?
June 2, 2008 at 6:42 am
Any triggers on the table you are updating?
The query plan seems to be looking at a column called LastCallStatus. Do you have a trigger that does something like set the LastCallStatus when you update the status column?
June 2, 2008 at 6:57 am
Nope, no triggers.
5 Keys: 1 primary and 4 foreign
3 constraints : all of them about default values on some columns
0 triggers
3 indexes :
IX_GetByRandom (clustered) on [RandomOrder],[Status],[CampaignID]
IX_GetByClient(non unique) on [CampaignID],[ClientID]
PK_CCID (unique) on [CCID]
June 2, 2008 at 7:01 am
In addition to the trigger, are any of the columns affected by the update statement (or by the trigger) part of the primary key of the table? If so, you're seeing checks against foriegn key constraints from child tables.
I think the trigger is more likely, but the PK update is possible. BTW, you shouldn't be updating a primary key on a regular basis.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 2, 2008 at 7:25 am
Well, that's what I don't understand:
The columns that I modify are not part of a foreign key or a constraint. .. And they are not the PK either.
The PK is in the WHERE , not in the SET ... if you know what I mean.
June 2, 2008 at 7:34 am
What about a constraint or a rule? Something seems to be affecting the PK or reaching out to these other tables. I just did a couple of tests. I can update columns, even ones included in indexes, without it affecting related tables. The execution plans are very simple. You've got something else in there, you just have to find it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 9:44 am
Are all foreign key columns indexed (the ones in the other tables)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2008 at 11:26 am
radu.poenaru (6/2/2008)
I have this update SQL :UPDATE Table SET [Status]=1 WHERE CCID=@CCID
where CCID is the primary key, but the PK index is non-clustered, I have another clustered index because of some optimization issues.
Now for some reason ,this UPDATE is generating a huge Execution Plan involving a lot of other tables (to whom I have foreign keys), on columns that have nothing to do with this query!
If I drop the other clustered index, and then cluster the table on the primary key, then the Execution Plan looks normal.
I have attempted to reproduce this situation by creating a few dummy tables and linking them with foreign keys, but the Execution Plan in my TestDB looks normal. Only in this DB it looks crazy like this (see attachment).
Anyone knows why SQL is doing this? And how I can stop it ?
Do you have an INDEXED VIEW defined that gets data from that table ?
* Noel
June 3, 2008 at 7:33 pm
Is [Table] really a view?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 4, 2008 at 5:14 am
Yes, all foreign keys in the table are pointing to PKs in the other tables.. and those tables are all clustered on their respective PK. It's just your basic table with 4 foreign keys, nothing fancy.
And, no I have no views on the table (or any other tables).
What worries me more is that I can't seem to duplicate this behaviour in any other test. I'm trying for a few days to re-create the conditions but anything I do manually by hand will not generate this problem.
Strange enough if I generate a schema script from the live DB and then re-create a database from that.. then the UPDATE is again messed up, so I know it has something to do with the schema.
But the schema is several 1000's of lines long, it will take until August to find out the culprit.
I understand that updating the [Status] column, while that status is in the clustered index will make the respective row physically move in the table, right? But why whould that need to generate Index Seek and Asserts on the tables from the foreign keys? Could it have something to do with the Statistics?
June 4, 2008 at 5:51 am
There's a dependency in there somewhere. Why not get a copy of Red Gate's Dependency Checker (or whatever it's called) and run it against the database. It's VERY thorough and will tell you every object that references that table. Because you've got something that is referencing that table. There's no other explanation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 8:04 am
if you don't have indexed views nor Triggers the only thing left for me to guess is that your foreign keys are defined with CASCADE actions can you post the DDL of the "all tables in the plan" ?
* Noel
June 4, 2008 at 9:31 am
I'm making a somewhat educated guess based on index behavior.
There is a possibility that updating the clustered index affects the indexes used for the foreign keys, which might cascade to updates needed in the tables that are referencing those columns.
Every index includes the clustered index in it (assuming there is one). Thus I surmise that possibly the foreign keys have to include that data too. After all, they have to know which row they are pointing at, and that's identified by the clustered index (as far as physical location anyway).
That conjecture would match the observed phenomena anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 1:49 pm
I've tried to duplicate this behavior from the description given, but can't. So I guess I'm not sure what would cause it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 6:21 pm
Please generate a script for table ats_CampaignClient, including all options.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply