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 that sometimes returns the following information from an Azure SQL database:

enter image description here

This doesn't occur if there are few users. The query takes a very long time to finish, and my DTU % is nearly uncontrollably high, assuming there are any, but if there are, this occurs often.

How do I identify the root of this?

a few general facts:

  • In various areas of the program, I use Dapper as an ORM.
  • I use EF in various contexts.
  • If it hangs, it hangs for more than 30 seconds. Rarely do 1 seconds and 30 seconds coincide.
  • The stylish question hangs below, whereXXXX in both situations, is a list of around 2,500 item IDs:
  • Users with a limited selection ofXXXX Ids never encounter this problem. the locating ofXXXX is seldom a problem, however utilizing a bigXXXX appears to sometimes make performance worse.
  • UserId (PK) and LastOnline are both indexed for [User].

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 little new to the performance forensics field; any guidance would be much appreciated.

Execution Plan Update:

enter image description here

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

Accepted Answer

You may try a couple of the following:

  1. See if you can build a stored procedure that takes @UserID as an argument and returns the list you need. Call this stored method rather than creating a new query each time.
  2. As seen below, swap NOT IN for NOT EXISTS. Check to see if this helps first. The values in the USERID field will have a significant impact.

    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. Consider making NON-CLUSTERED Indexes on the other columns in your WHERE clause as well. Would you kindly provide the query's execution strategy as well? (How: Press CTRL+M in the SQL Query Editor before running your query. Along with the findings, you will also receive an implementation plan.)

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