July 24, 2013 at 12:45 pm
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.
July 24, 2013 at 2:04 pm
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.
July 24, 2013 at 4:16 pm
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?
July 24, 2013 at 5:08 pm
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 🙂
July 25, 2013 at 2:42 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 25, 2013 at 8:01 am
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.
July 25, 2013 at 10:19 am
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...
July 25, 2013 at 3:57 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 25, 2013 at 4:06 pm
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....) 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply