SQL Strategies for detecting what is causing a lock / long query

Question

I have a query against an Azure SQL database that occasionally results in the following:

If there aren't many users, this doesn't happen. But if there are, this happens regularly - the query takes a very long time to complete and my DTU percentage is almost off the charts.

How do I determine what is causing this?

Some general info:

• I'm using dapper as an ORM in some portions of the application
• I'm using EF in other areas
• When it hangs, it hangs for >30sec. Rarely is 1s < time < 30s
• The dapper query that hangs is below, where XXXX is a list of about 2,500 item IDs in both cases:
• It seems like users with a small set of XXXX Ids don't EVER hit this issue. The retrieval of XXXX is not ever an issue - but using a large XXXX seems to aggravate performance sometimes.
• [User] is indexed on UserId (PK), and LastOnline

Code:

 select USERID, USERNAME, NICKNAME, BIRTHDATE, LASTONLINE 
  from [User]  
  where AccountDisabled <> 1 and Banned <> 1 and 
     (ABOUTME <> '' OR ProvidedPhoto = 1) and 
     USERID <> @userId  and ProvidedPhoto = 1  AND 
     USERID IN (-1)  AND USERID NOT IN (-1)  
     AND USERID NOT IN (XXXX) UNION ALL  
          select * from (select USERID, USERNAME, NICKNAME, BIRTHDATE, LASTONLINE from [User] where 
                AccountDisabled <> 1 and 
                Banned <> 1 and (ABOUTME <> '' OR ProvidedPhoto = 1) and 
                USERID <> @userId  and ProvidedPhoto = 1  AND USERID NOT IN (-1)  
                AND USERID NOT IN (XXXX)  AND USERID NOT IN (-1)  
                order by LastOnline asc offset 0 rows fetch next + 20 rows only)          
     as dt

I'm a bit new to the world of performance forensics... any advice would be awesome.

Update - Execution Plan:

Answer 1

Here are a few things you can try:

1. See if you can create a stored procedure to return your required list by passing @UserID as parameter. Call this stored procedure instead of generating query every-time.

2. Replace NOT IN with NOT EXISTS as below. First check if this helps. It very much depends on the values in USERID column.

   SELECT USERID,USERNAME,NICKNAME,BIRTHDATE,LASTONLINE
   FROM [User]
   WHERE AccountDisabled <> 1
   AND Banned <> 1
   AND (
   ABOUTME <> ''
   OR ProvidedPhoto = 1
   )
   AND USERID <> @userId
   AND ProvidedPhoto = 1
   AND USERID IN (- 1)  --How will these two conditions ever be true together?
   AND USERID NOT IN (- 1) --Be sure about your conditions
   AND NOT EXISTS (SELECT USERID FROM [USER] U2 WHERE U1.USERID = U2.USERID)
   
   UNION ALL
   
   SELECT *
   FROM (  SELECT USERID,USERNAME,NICKNAME,BIRTHDATE,LASTONLINE
   FROM [User] U1
   WHERE AccountDisabled <> 1
   AND Banned <> 1
   AND (
       ABOUTME <> ''
       OR ProvidedPhoto = 1
       )
   AND USERID <> @userId
   AND ProvidedPhoto = 1
   AND USERID NOT IN (- 1)
   AND NOT EXISTS (SELECT USERID FROM [USER] U2 WHERE U1.USERID = U2.USERID)
   -- AND USERID NOT IN (- 1) WHY AGAIN??
   ORDER BY LastOnline ASC offset 0 rows FETCH NEXT + 20 rows ONLY
   ) AS dt
   

3. You may also think of creating NON-CLUSTERED Indexes on the other columns you have in WHERE clause. Could you please show execution plan of the query as well? (How: In SQL Query editor, press CTRL+M and then execute your query. You will get an execution plan along with the results.)