Delete statement hangs

Question

Post reply

Delete statement hangs

wzline

Old Hand

Points: 368

September 8, 2018 at 5:19 am

#408405

Hi all,

Vendor gave me a statement to run to delete records from tables.

The last output of the statement was:

(20000 row(s) affected)
2018-09-08 04:19:23.040 Deleted 20000 rows from <tablename>

(20000 row(s) affected)
2018-09-08 04:19:55.233 Deleted 20000 rows from <tablename>

(20000 row(s) affected)
2018-09-08 04:20:37.263 Deleted 20000 rows from <tablename>

(20000 row(s) affected)
2018-09-08 04:21:16.860 Deleted 20000 rows from <tablename>

(20000 row(s) affected)
2018-09-08 04:21:40.720 Deleted 20000 rows from <tablename>

Now the local time is 2018-09-08 21:13, there has been no progress/update for about 17 hours.

As the database was set into single-user mode, I am not able to find out what the delete statement is. Following is a typical output for sp_lock:

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
62	8	756114842	0	TAB		IX	GRANT
62	8	261575970	0	TAB		X	GRANT
62	8	738101670	0	TAB		IS	GRANT
62	8	2090151633	0	TAB		IS	GRANT
62	8	1271636269	0	TAB		IS	GRANT
62	8	804115013	0	TAB		IS	GRANT
62	8	1338487847	0	TAB		IS	GRANT
62	8	853578079	0	TAB		IS	GRANT
62	8	85575343	0	TAB		IS	GRANT
62	8	0	0	DB		S	GRANT
62	8	0	0	DB		S	GRANT
62	8	462832911	0	TAB		IS	GRANT
62	8	690101499	0	TAB		IS	GRANT

and

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
62	8	853578079	1	PAG	1:13349160	IS	GRANT
62	8	756114842	0	TAB		IX	GRANT
62	8	261575970	0	TAB		X	GRANT
62	8	738101670	0	TAB		IS	GRANT
62	8	2090151633	0	TAB		IS	GRANT
62	8	1271636269	0	TAB		IS	GRANT
62	8	804115013	0	TAB		IS	GRANT
62	8	1338487847	0	TAB		IS	GRANT
62	8	853578079	0	TAB		IS	GRANT
62	8	85575343	0	TAB		IS	GRANT
62	8	0	0	DB		S	GRANT
62	8	0	0	DB		S	GRANT
62	8	462832911	0	TAB		IS	GRANT
62	8	690101499	0	TAB		IS	GRANT

Can you please advise what could be the cause of the hanging? how to investigate further?

Thank you.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 1

How many total rows are you deleting?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wzline Old Hand Points: 368 More actions · Answer 2

Hi Jeff,

Thank you for the reply.

There are 2,875,436 rows to be deleted in Table , 2,780,000 rows were deleted in about 1 hour, another 95,436 records to be deleted (in 5 batches). But the last batch has been running for more than 26 hours.

In total, the table has 17.7 million records, 15.1GB as data space, and 358GB as Index space.

wzline Old Hand Points: 368 More actions · Answer 3

restored the database and now can see the code for deleting:

       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 4

wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item

Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?

Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck and the Optimizer may have picked the worst plan ever for doing the additional deletes.. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.

Also, if you do the rebuild using the BULK LOGGED model, the REBUILD will be minimally logged, making it a lot faster.

Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 5

Shifting gears a bit, I've done rather large deletes on rather large tables before. Since such deletes (you're deleted about 15% of the table) normally require a rebuild of the Clustered Index anyway, it probably would have been better to simply copy the data into a new Clustered Index table to start with (which can certainly be done in a minimally logged fashion), check what's been copied, drop the original table, and rename the copied table back to the original.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wzline Old Hand Points: 368 More actions · Answer 6

Jeff Moden - Saturday, September 8, 2018 3:15 PM
wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item
Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?
Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.
Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).

Hi Jeff,

Great point on table_name2. I will carefully go through the code provided by vendor during the day.

Do you recommend Single User mode? As the application server will be stopped, I don't think it's necessary. Single User mode makes trouble shooting harder.

wzline Old Hand Points: 368 More actions · Answer 7

Jeff Moden - Saturday, September 8, 2018 3:21 PM
Shifting gears a bit, I've done rather large deletes on rather large tables before. Since such deletes (you're deleted about 15% of the table) normally require a rebuild of the Clustered Index anyway, it probably would have been better to simply copy the data into a new Clustered Index table to start with (which can certainly be done in a minimally logged fashion), check what's been copied, drop the original table, and rename the copied table back to the original.

Thanks for the hint, Jeff.

Looks like I will have to rollback this deployment and review the code/plan and come up with a better way, hopefully.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 8

wzline - Saturday, September 8, 2018 3:41 PM
Jeff Moden - Saturday, September 8, 2018 3:15 PM
wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item
Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?
Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.
Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).
Hi Jeff,
Great point on table_name2. I will carefully go through the code provided by vendor during the day.
Do you recommend Single User mode? As the application server will be stopped, I don't think it's necessary. Single User mode makes trouble shooting harder.

If the only thing using the table is that one application and that application has been stopped, then the SINGLE USER mode is unnecessary.

I'll also suggest that 20,000 rows is serious underkill.

Is this the largest table in your database or do you have others of this size?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 9

wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item

Jeff, I'm not sure how could you miss it:

DELETE dbo.<table_name1> WITH (READPAST)
FROM dbo.<table_name1> IPA

The table in DELETE part has no relevance to the aliased table in FROM.
The statement deletes everything from dbo.table_name1 regardless of any joins and conditions in FROM part.
It must be
DELETE IPA WITH (READPAST) FROM dbo.<table_name1> IPA

_____________
Code for TallyGenerator

wzline Old Hand Points: 368 More actions · Answer 10

Jeff Moden - Sunday, September 9, 2018 10:54 AM
wzline - Saturday, September 8, 2018 3:41 PM
Jeff Moden - Saturday, September 8, 2018 3:15 PM
wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item
Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?
Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.
Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).
Hi Jeff,
Great point on table_name2. I will carefully go through the code provided by vendor during the day.
Do you recommend Single User mode? As the application server will be stopped, I don't think it's necessary. Single User mode makes trouble shooting harder.
If the only thing using the table is that one application and that application has been stopped, then the SINGLE USER mode is unnecessary.
I'll also suggest that 20,000 rows is serious underkill.
Is this the largest table in your database or do you have others of this size?

Thanks Jeff.
This morning I got time to review the stored procedures line by line. So far what I noticed is that indexes on one of the tables are very fragmented.
Also "PRINT" is used in the SP, so the output is delayed, thus misleading.
As the code is provided by vendor, I will rebuild indexes in test env and re-run the sp; might ask vendor to change the sp to drop indexes and rebuild them after delete. Will also mention 20,000 rows is an underkill.
I will keep this post updated.
Thanks again.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 11

wzline - Sunday, September 9, 2018 10:28 PM
Jeff Moden - Sunday, September 9, 2018 10:54 AM
wzline - Saturday, September 8, 2018 3:41 PM
Jeff Moden - Saturday, September 8, 2018 3:15 PM
wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item
Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?
Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.
Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).
Hi Jeff,
Great point on table_name2. I will carefully go through the code provided by vendor during the day.
Do you recommend Single User mode? As the application server will be stopped, I don't think it's necessary. Single User mode makes trouble shooting harder.
If the only thing using the table is that one application and that application has been stopped, then the SINGLE USER mode is unnecessary.
I'll also suggest that 20,000 rows is serious underkill.
Is this the largest table in your database or do you have others of this size?
Thanks Jeff.
This morning I got time to review the stored procedures line by line. So far what I noticed is that indexes on one of the tables are very fragmented.
Also "PRINT" is used in the SP, so the output is delayed, thus misleading.
As the code is provided by vendor, I will rebuild indexes in test env and re-run the sp; might ask vendor to change the sp to drop indexes and rebuild them after delete. Will also mention 20,000 rows is an underkill.
I will keep this post updated.
Thanks again.

Just in case you don't know, you can use RAISERROR instead of print. It'll print ever line up to about 500 lines and then it will go into a "wait for 100 lines to accumulate and then print all 100 lines" mode.

RAISERROR('somemessage here',0,0) WITH NOWAIT:

The "0,0" thing means "No Severity" and a second "0" just as a place holder).

You can also use a variable if you need to...

RAISERROR(%s,0,0,@SomeVariable) WITH NOWAIT:

... where the "%s" is a "string placeholder" for @SomeVariable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 12

wzline - Saturday, September 8, 2018 2:54 PM
Hi Jeff,
Thank you for the reply.
There are 2,875,436 rows to be deleted in Table , 2,780,000 rows were deleted in about 1 hour, ='mso-spacerun:yes'>another 95,436 records to be deleted (in 5 batches). But the last batch has been running for more than 26 hours.
In total, the table has 17.7 million records, 15.1GB as data space, and 358GB as Index space.
=black>

='font-size:11.0pt;mso-fareast-font-family:"times>

='font-size:11.0pt;mso-fareast-font-family:"times>

How many indexes are on this table - and how do you get to 358GB of index space from a table with only 15.1GB of data? Not only should you drop or disable the non-clustered indexes prior to the delete, but you really need to review the indexes and remove any that are not useful or being utilized.

I can only imagine that most of these indexes are made up of almost all columns - as either keys in the index or included columns.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

wzline Old Hand Points: 368 More actions · Answer 13

Jeff Moden - Monday, September 10, 2018 6:06 AM
wzline - Sunday, September 9, 2018 10:28 PM
Jeff Moden - Sunday, September 9, 2018 10:54 AM
wzline - Saturday, September 8, 2018 3:41 PM
Jeff Moden - Saturday, September 8, 2018 3:15 PM
wzline - Saturday, September 8, 2018 3:00 PM
restored the database and now can see the code for deleting:
       DELETE dbo.<table_name1> WITH (READPAST)
        FROM dbo.<table_name1> IPA
            JOIN dbo.[table_name2] AU WITH (NOLOCK) ON IPA.AuditID = AU.AuditID
            JOIN @RowsToBeDeleted TI ON AU.AccountID = TI.Item
Why are you joining to table_name2? @RowsToBeDeleted is a table valued function that contains the AuditID's to delete, correct?
Also, you restarted the delete job after most of the rows you wanted to delete have been deleted. That's left gaping holes in your table_name1 table and your stats on that table are likely a complete train wreck. If it were me, I'd rebuild the Clustered Index on table_name1 (and I'm assuming that AuditID is the Clustered Index on that table), rebuild the content of @RowsToBeDeleted , and then try again without the join to table_name2, which I currently see absolutely no reason for the join to it in any of this.
Also, since you're in the SINGLE USER mode, change the WITH (READPAST) to WITH(TABLOCK).
Hi Jeff,
Great point on table_name2. I will carefully go through the code provided by vendor during the day.
Do you recommend Single User mode? As the application server will be stopped, I don't think it's necessary. Single User mode makes trouble shooting harder.
If the only thing using the table is that one application and that application has been stopped, then the SINGLE USER mode is unnecessary.
I'll also suggest that 20,000 rows is serious underkill.
Is this the largest table in your database or do you have others of this size?
Thanks Jeff.
This morning I got time to review the stored procedures line by line. So far what I noticed is that indexes on one of the tables are very fragmented.
Also "PRINT" is used in the SP, so the output is delayed, thus misleading.
As the code is provided by vendor, I will rebuild indexes in test env and re-run the sp; might ask vendor to change the sp to drop indexes and rebuild them after delete. Will also mention 20,000 rows is an underkill.
I will keep this post updated.
Thanks again.
Just in case you don't know, you can use RAISERROR instead of print. It'll print ever line up to about 500 lines and then it will go into a "wait for 100 lines to accumulate and then print all 100 lines" mode.
RAISERROR('somemessage here',0,0) WITH NOWAIT:
The "0,0" thing means "No Severity" and a second "0" just as a place holder).
You can also use a variable if you need to...
RAISERROR(%s,0,0,@SomeVariable) WITH NOWAIT:
... where the "%s" is a "string placeholder" for @SomeVariable.

Hi Jeff,

Thanks for the reminder. Yes I realised that Raiserror buffers lines after 500 lines. It is indeed annoying. I will ask vendor either reduce the lines or generate lines more frequently so I know the progress.

Never realised this until had this problem. Some people say other tool might not have the limit, like linqpad. Beside SSMS, do you have any other tool to recommend?

wzline Old Hand Points: 368 More actions · Answer 14

Jeffrey Williams 3188 - Monday, September 10, 2018 2:51 PM
wzline - Saturday, September 8, 2018 2:54 PM
Hi Jeff,
Thank you for the reply.
There are 2,875,436 rows to be deleted in Table , 2,780,000 rows were deleted in about 1 hour, ='mso-spacerun:yes'>another 95,436 records to be deleted (in 5 batches). But the last batch has been running for more than 26 hours.
In total, the table has 17.7 million records, 15.1GB as data space, and 358GB as Index space.
=black>

='font-size:11.0pt;mso-fareast-font-family:"times>

='font-size:11.0pt;mso-fareast-font-family:"times>
How many indexes are on this table - and how do you get to 358GB of index space from a table with only 15.1GB of data? Not only should you drop or disable the non-clustered indexes prior to the delete, but you really need to review the indexes and remove any that are not useful or being utilized.
I can only imagine that most of these indexes are made up of almost all columns - as either keys in the index or included columns.

My apology Jeff, index was 358MB.

All indexed in this database has fill factor set to 80

For sure I will disable the non-clustered indexes and rebuild them after delete. Will check if any indexes are not being used.

Thank you again Jeff, your help is very appreciated.