Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 12:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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.
Post #1477214
Posted Wednesday, July 24, 2013 2:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 11:45 AM
Points: 275, Visits: 863

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.
Post #1477257
Posted Wednesday, July 24, 2013 4:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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 :)

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?
Post #1477313
Posted Wednesday, July 24, 2013 5:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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 :)
Post #1477320
Posted Thursday, July 25, 2013 2:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 802, Visits: 717
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
Post #1477398
Posted Thursday, July 25, 2013 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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.

Post #1477505
Posted Thursday, July 25, 2013 10:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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...
Post #1477609
Posted Thursday, July 25, 2013 3:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 802, Visits: 717
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
Post #1477786
Posted Thursday, July 25, 2013 4:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 284, Visits: 1,130
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....) :)
Post #1477789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse