Strategie SQL per il rilevamento di ciò che causa una query di blocco / lungo

dapper database-performance entity-framework performance sql

Domanda

Ho una query su un database SQL di Azure che occasionalmente genera quanto segue:

inserisci la descrizione dell'immagine qui

Se non ci sono molti utenti, questo non succede. Ma se ci sono, questo accade regolarmente - la query impiega molto tempo per completare e la mia percentuale di DTU è quasi fuori dai grafici.

Come posso determinare cosa sta causando questo?

Alcune informazioni generali:

  • Sto usando dapper come un ORM in alcune parti dell'applicazione
  • Sto usando EF in altre aree
  • Quando si blocca, si blocca per> 30 sec. Raramente è 1 <tempo <30 secondi
  • La query dapper che si blocca è riportata di seguito, dove XXXX è un elenco di circa 2500 ID elemento in entrambi i casi:
  • Sembra che gli utenti con un piccolo set di ID XXXX non abbiano MAI riscontrato questo problema. Il recupero di XXXX non è mai un problema, ma l'uso di un grande XXXX sembra a volte peggiorare le prestazioni.
  • [Utente] è indicizzato su UserId (PK) e LastOnline

Codice:

 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

Sono un po 'nuovo nel mondo delle analisi forensi ... qualsiasi consiglio sarebbe fantastico.

Aggiornamento - Piano di esecuzione:

inserisci la descrizione dell'immagine qui

Risposta accettata

Ecco alcune cose che puoi provare:

  1. Verifica se è possibile creare una stored procedure per restituire l'elenco richiesto passando @UserID come parametro. Chiama questa stored procedure invece di generare una query ogni volta.
  2. Sostituisci NOT IN con NOT EXISTS come sotto. Prima controlla se questo aiuta. Dipende molto dai valori nella colonna USERID.

    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. Potresti anche pensare di creare indici NON-CLUSTERED sulle altre colonne che hai nella clausola WHERE. Potrebbe per favore mostrare anche il piano di esecuzione della query? (Come: nell'editor SQL Query, premi CTRL + M e quindi esegui la query. Riceverai un piano di esecuzione insieme ai risultati.)



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché