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 statement won't complete Expand / Collapse
Author
Message
Posted Sunday, September 16, 2012 7:27 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
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.


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1359940
Posted Monday, September 17, 2012 1:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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>

Post #1360010
Posted Monday, September 17, 2012 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 3, 2013 12:38 AM
Points: 91, Visits: 77
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.


*******
Sudhakar
Post #1360070
Posted Monday, September 17, 2012 4:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Column is already indexed and no other blocking processes

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1360082
Posted Monday, September 17, 2012 4:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/

Thanks!
Post #1360087
Posted Monday, September 17, 2012 5:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
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?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1360116
Posted Tuesday, September 18, 2012 3:26 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:40 PM
Points: 421, Visits: 1,000
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).

Post #1361037
Posted Tuesday, September 18, 2012 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361040
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse