Large deletions blocked by negative SPID

Mike Wright 31

I have a nightly "archive and delete" process that writes all data from a large, busy table >150 days old to Blob storage, and deletes the same from the hosting table. The process performance is terrible - and in fact, it may take more than a day to delete a day's worth of data!

When I check for blocking, I see this process is almost always blocked by SPID "-5" ... my research tells me that this is an orphaned DT. However, when I run this:

SELECT DISTINCT(request_owner_guid) as UoW_Guid FROM sys.dm_tran_locks WHERE request_session_id =-5

It returns no rows.

There have been a few times where the performance of this operation deleted >30k a minute, which is great. So, something going on is transient but I can't pin it down. When it's in this state, the deletions take 10-30x longer.

Some background: This is an Azure SQL Managed Instance. I couldn't find a category just for that, so I posted in Azure SQL Databases, although I understand they are different.

How can I find out what SPID -5 is so I can fix the problem?

Thanks.

Mike Wright 31 Reputation points

2022-03-21T13:12:07.2+00:00

So, I stumbled on this:

https://techcommunity.microsoft.com/t5/sql-server-blog/negative-blocking-session-ids-5-latch-any-task-releasor/ba-p/3061302

If this is waiting on some async action, how can I figure out what that is? This performance is terrible, and in general, the Managed Instance has been a huge disappointment.
Pooja Pakade Konde 20 Reputation points

2023-02-07T09:36:00.4066667+00:00

Have you got any answer for this . I am using Azure SQL MI to run SP and its getting blocked with negative SPID =-5 . These Queries runs fine on OnPrem SQL server.Tried increasing VCores and memory but still facing same issues
Oury Ba-MSFT 16,081 Reputation points Microsoft Employee

2023-03-13T23:39:57.05+00:00

Pooja Pakade Konde Thank you for reaching out.

That's not a root cause. See Negative Blocking Session Ids (-5 = Latch ANY TASK RELEASOR) - Microsoft Community Hub

The session wait stats are a better indicator of why the process is slow: sys.dm_exec_session_wait_stats (Transact-SQL) - SQL Server | Microsoft Learn

Regards,

Oury

1 answer

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T13:16:00.3+00:00

SInce -5 is related to IO latches, as author of the article you shared with us stated. Let's make sure we have configured the storage subsystem well.

IO latches indicate that the database engine is waiting to fetch or save data pages from memory to data file. In General Purpose tier, you might fix this issue by pre-allocating the data file as explained on this article.
Please sign in to rate this answer.
Mike Wright 31 Reputation points

2022-03-21T13:18:46.163+00:00

As I stated in the post, I tried this and nothing is returned. I found something that indicates only -2 is an orphaned transaction, and -5 is "Any task/session can release the latch. I/O latches are the most common user."

I have no idea what this means, or what to do about it.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T14:45:45.637+00:00

Let me see if I can get response for you. Anything that can help you.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T19:53:58.713+00:00

@Mike Wright I updated my answer. Hope it helps.

Mike Wright 31 Reputation points

2022-03-21T20:01:55.06+00:00

Thanks for that information - I read that article about a week ago, and in fact increased the size of the log file to bump the IOPS limit from 500 to 2300, and that helped performance quite a bit. However, I still see -5 a lot, especially with the deletes. I am well below max throughput and IOPS limits on all files.

QPI stats report my read and write IO latencies as under 6ms across the board, which seems pretty good.

Mike Wright 31 Reputation points

2022-03-21T20:02:55.31+00:00

And, not sure if it makes any difference - this is a migrated DB from an on-prem instance to a SQL Managed Instance, and we're coming to grips with what is different in this environment.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T21:04:25.69+00:00

@Mike Wright honestly the feedback I got about this case/scenario they want to know if a lack of configuration is causing this issue.

Can you make those massive delete operations on batches? As explained here.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T21:09:09.637+00:00

@Mike Wright Please consider these IO recomendations

Mike Wright 31 Reputation points

2022-03-21T21:35:45.257+00:00

That article is interesting ... explains some of the nuances of this environment. We ARE performing the deletes in batches ... but we're executing a "delete top(n) from [table_name]" joined to a table with a list of keys to delete, and we loop until there are no more rows. We've tried 4k at a time, and are currently running 12.5k at a time. Didn't really notice much difference in either case. The article says to put them in a transaction, but the single DELETE for each iteration of the loop should be transactional in and of itself, correct?

Mike Wright 31 Reputation points

2022-03-21T21:39:46.05+00:00

What specifically do you mean "lack of configuration" ... we're new to SQL managed Instances and this migration occurred in January, and we've been fighting performance issues from day one. I certainly won't rule out something not configured correctly. We've looked at and made adjustments for total IOPS and throughout (larger file sizes) and we're moved high-volume indexes to their own file. We've also ensured all indexes are re-built / re-orged. Overall, the system started out with 20 or 30 sessions blocking, and today we're running none or almost none most of the time. But, these deletes are still WAY slower than they were on-prem. And, these ran 30k a minute last week, so I was originally thinking there was some transient condition causing the issue, and the "-5" seemed suspect as it was the recurring theme.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T22:11:42.407+00:00

I did not receive specifics of what else should be configured. I have faced myself high CPU usage with Azure Managed Instance but never IO latch issues. I will try to find more ways to help you troubleshoot this issue.

Mike Wright 31 Reputation points

2022-03-21T22:18:09.75+00:00

Thanks, I appreciate your help. I have seen high CPU one time last week where it was pegged at nearly 100% for several hours, and we couldn't explain why in terms of the workload. Nothing special was going on.

Alberto Morillo 32,886 Reputation points MVP

2022-03-21T22:25:26.237+00:00

On the scenario I faced, it was all about missing indexes. Consolidated existing indexes. Removed lookup operators dealing with high number of rows. Implemented daily maintenance of indexes and stats and that's it.

Before that my client was required to scale up the service tier as a workaround. That turned out to be expensive for them.
Sign in to comment