SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing


DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing

Author
Message
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
We have a table, and just noticed if we try to delete a row from it (for example the first row) it runs for over 5 minutes without deleting (finally just killed the process).

Restarted the server, just in case.

Created a copy of the table, and I can delete just fine from that.

Other tables work fine in that database, it is just this particular table.

Only has 500 or so rows.

It has a PK clustered index on it and that is it. (two constraints for default values of 0 for a bit field and getdate for a datetime field)

I've never encountered this before.

Additionally, it works fine on our other database servers, just our dev environment is having this issue.

DBCC CHECKTABLE didn't find anything wrong with that given table.

Any thoughts?

Running the DELETE statement begins to show wait types for CXPacket after a while.... CPU jumps to 40% while it runs... and moves around (normally it is at 2%).

PAGEIOLATCH_SH and CXPacket come up in wait type

However, they don't stay it just suspends the task and juggles between suspended and running.
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 1019
Is there a foreign key constraint, where this table is referenced?
It looks like it may be doing a cascading delete on another large table.


CXPACKET wait indicates paralellism.
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
arnipetursson (7/24/2013)

Is there a foreign key constraint, where this table is referenced?
It looks like it may be doing a cascading delete on another large table.


CXPACKET wait indicates paralellism.



Now that's a terrifying possability Smile

However, I got around it by dropping the table.

Before I did that I had to delete all the FK constraints and I scripted them out so I could recreate them on the new table.

None of them have an action specified for UPDATE or DELETE (or all of them are set as NO ACTION that is to say).


After I dropped the table I recreated it and re-added all the data, and it works fine.

I just don't understand what caused the issue.

As for parallelism, I tried setting it to 1 (vs the default of 0) for Max Degree of Parallelism.

After setting it to 1 and trying again, no change (still ran for a while and I killed it).


Also, a new row was added to the table and I tried a delete on the new row (which would have no corresponding values in other FK tables) and same outcome.


I am MOST perplexed....

I really would like to understand a root cause for it all.

Is there anything I could do if I encounter this again to further troubleshoot exactly what SQL server is doing during the long running query?
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
As I mentioned I took a backup of the database before dropping that table.

I restored that backup to my local, same problem persists there.

So I can examine the issue now going forwards, I just don't know what to look for Smile
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 872
So the problem is exactly the foreign keys. CASCADE or NO ACTION, if you delete a row from a table, and there is an FK constraint from a big table and the FK column is unindexed, the delete will take long time, because SQL Server will have to scan that big table.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
Erland Sommarskog (7/25/2013)
So the problem is exactly the foreign keys. CASCADE or NO ACTION, if you delete a row from a table, and there is an FK constraint from a big table and the FK column is unindexed, the delete will take long time, because SQL Server will have to scan that big table.


Yeah, that was it.

I'm going to add indexes to the FKs on those related tables and see if that sorts it properly.
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
Well adding an index will take forever, just dropped them. Did the deletes then re-created.

Since this table will have deletes perhaps once per quarter, I think that's the way to go...
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 872
Dropping and recreating foreign keys every three months is not defensible. Recreating the FKs takes just as long time as takes to perform the check for the DELETE.

I suggest that you schedule adding the indexes for the next maintenance window.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Maxer
Maxer
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1603
True.

This is a non-production "research" ETL database.

The table in question won't ever be updated by a "end user" so to speak.

However, your point is valid...

I may look at dropping the FK constraints since this is being loaded via an ETL operation additional indexes just create overhead and the ETL process should never encounter FK violations in the first place (...in theory....) Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search