How to handle negative SPID on Azure SQL MI (SPID=-5)

Pooja Pakade Konde 20 Reputation points
2023-02-07T09:45:01.9866667+00:00

I am using Azure SQL MI and executing SSIS packages through ADF Pipeline. I could see that few SP's being called through SSIS packages are getting delayed as these are blocked by SPID=-5 . We have Azure SQL MI with 16 vCore Capacity . Can anyone suggest on how to deal with this SPID

Azure SQL Database
Accepted answer
  1. Erland Sommarskog 101K Reputation points MVP
    2023-02-13T22:12:43.9433333+00:00

    How we can avoid the SPID=-5 , Do we need to increase any infra to avoid this ?

    As I understand Bob Dorr's article you are waiting for an I/O to complete. The solution to this would be one of 1) Beef up the hardware (that is, what you buy from Azure), 2) Tune your workload.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 13,271 Reputation points Microsoft Employee
    2023-02-08T11:07:12.4433333+00:00

    Hi @Pooja Pakade Konde ,

    Thanks for your patience and reply.

    Regarding negative SPIDs, SQL Server uses negative sessions ids to indicate special conditions.​​ 

    Here, -5 signifies "Any task/session​​ can release the latch. ​​ I/O latches​​ are the​​ most common user."

    Reference Link: Negative Blocking Session Ids (-5 = Latch ANY TASK RELEASOR)

    Other helpful links: How to Kill a Blocking Negative SPID

    Joey D'Antoni on Twitter: "Anyone ever seen a SPID with -5? This was in Azure SQL DB? #sqlhelp" / Twitter

    Please take a look at above mentioned links and let us know if this helped.

    If not, do share more inputs so that we may further assist you.

    0 comments
  2. Pooja Pakade Konde 20 Reputation points
    2023-02-13T07:30:02.3833333+00:00

    How we can avoid the SPID=-5 , Do we need to increase any infra to avoid this ?

    0 comments