DELETE statement won't complete

Question

Post reply

DELETE statement won't complete

MissTippsInOz

Hall of Fame

Points: 3160
More actions
September 16, 2012 at 7:27 pm

#267775

This is an odd one. A DELETE statement with a single filter, deletes around 1.5 million records from a table (running a COUNT with NOLOCK verifies that the records are 'gone'), but then the statement never completes. In the sys.dm_tran_locks table, for this SPID, I can see one X lock on each resource partition (this is a 32 CPU server) except that there will always be a partition with one X lock and two further IX locks - it remains this way indefinitely!
I'm not sure how to investigate this in any greater depth. For info, at the moment the 1.5 million records are all that is in the table, so the filter is academic, but it won't remain this way.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

There may be blocking issue. Please run the following query to confirm:

select status, blocking_session_id, wait_type, wait_resource

from sys.dm_exec_requests

where session_id = <spid>

Sudhakar Vallamsetty SSC-Addicted Points: 407 More actions · Answer 2

Create a NON-Clustered index on the filtered column which you are issuing in DELETE statement and see if that fixes the dead locking issue to complete the statement.

It seems other SPID's are trying to process(select / update/ delete) the data in the table which you are trying to delete the data. You can run the profiler to capture deadlockgraph, if you want to see which statements are trying to access the data in that table.

*******:cool:
Sudhakar

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 3

Column is already indexed and no other blocking processes

yuvipoy SSChampion Points: 10469 More actions · Answer 4

Hey look at this Topic [Deleting large number of rows from a table]

http://www.sqlservercentral.com/articles/T-SQL/72606/

Thanks!

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 5

yuvipoy (9/17/2012)
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/
Thanks!

I hope this doesn't come across as rude, but I work for a very large company, and look after many hundreds of databases - a 500GB DB is no big deal. Deleting 1.5million records from a table is not a large number of rows in my organisation and this same process will delete 10 times that number from other tables. Our tables are, generally(!) well indexed and optimised.

What I'm dealing with here is a very specific issue that has just come to light where this one statement is exhibiting odd behaviour and, seeing the odd locking behaviour, using lock partitioning I wondered if this could be pertinent to the issue.

Since originally posting I have been testing the impact of the indexes and also of the FKs that reference this table. I discovered that by disabling one of the FKs I was able to get this process to complete. With the FK in place I see an Update lock placed against the referencing table and the X/IX locks against a single partition in the table I'm deleting from and it goes no further. Just to clarify there are NO referencing records in the referencing table. But why would it get 'stuck' at this point?

MMartin1 One Orange Chip Points: 27799 More actions · Answer 6

Is it possible there is replication whereby both updates need to occurr at the same time (publisher / subscriber) ? If so there could be an issue on the other machine (perhaps a full log file).

----------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

MissTippsInOz (9/17/2012)
yuvipoy (9/17/2012)
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/
Thanks!
I hope this doesn't come across as rude, but I work for a very large company, and look after many hundreds of databases - a 500GB DB is no big deal. Deleting 1.5million records from a table is not a large number of rows in my organisation and this same process will delete 10 times that number from other tables. Our tables are, generally(!) well indexed and optimised.
What I'm dealing with here is a very specific issue that has just come to light where this one statement is exhibiting odd behaviour and, seeing the odd locking behaviour, using lock partitioning I wondered if this could be pertinent to the issue.
Since originally posting I have been testing the impact of the indexes and also of the FKs that reference this table. I discovered that by disabling one of the FKs I was able to get this process to complete. With the FK in place I see an Update lock placed against the referencing table and the X/IX locks against a single partition in the table I'm deleting from and it goes no further. Just to clarify there are NO referencing records in the referencing table. But why would it get 'stuck' at this point?

I think the reason for the suggestion was perhaps a single large transaction versus multiple smaller transactions. A sensible suggestion, even if not what you were looking for here.

Unfortunately, I don't have an answer to your question regarding the FK's. Not having access to the schema to see how things relate makes it a bit difficult. Plus, I haven't run into this issue either, so no first hand experience or knowledge.