SQL techniques for figuring out what's causing a lock or a lengthy query

dapper database-performance entity-framework performance sql

Question

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

enter image description here

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:

enter image description here

1
1
5/10/2016 4:48:36 AM

Accepted Answer

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.)

2
5/10/2016 4:47:28 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow