Stratégies SQL pour détecter ce qui provoque une requête de verrouillage / longue

dapper database-performance entity-framework performance sql

Question

J'ai une requête sur une base de données SQL Azure qui entraîne parfois les éléments suivants:

entrer la description de l'image ici

S'il n'y a pas beaucoup d'utilisateurs, cela n'arrive pas. Mais s'il y en a, cela se produit régulièrement - la requête prend beaucoup de temps et mon pourcentage de DTU est presque hors des graphiques.

Comment est-ce que je détermine ce qui cause ceci?

Quelques infos générales:

  • J'utilise dapper en tant qu'ORM dans certaines parties de l'application
  • J'utilise EF dans d'autres domaines
  • Quand il se bloque, il se bloque pendant> 30sec. Rarement 1s <heure <30s
  • La requête dapper qui se bloque est ci-dessous, où XXXX est une liste d'environ 2 500 ID d'éléments dans les deux cas:
  • Il semble que les utilisateurs avec un petit ensemble d'ID XXXX ne rencontrent JAMAIS ce problème. La récupération de XXXX n'est jamais un problème, mais l'utilisation d'un grand XXXX semble parfois aggraver les performances.
  • [Utilisateur] est indexé sur UserId (PK) et 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

Je suis un peu nouveau dans le monde de la criminalistique de la performance ... tout conseil serait génial.

Mise à jour - Plan d'exécution:

entrer la description de l'image ici

Réponse acceptée

Voici quelques choses que vous pouvez essayer:

  1. Voyez si vous pouvez créer une procédure stockée pour renvoyer votre liste requise en transmettant @UserID en tant que paramètre. Appelez cette procédure stockée au lieu de générer une requête à chaque fois.
  2. Remplacez NOT IN par NOT EXISTS comme ci-dessous. Vérifiez d'abord si cela vous aide. Cela dépend beaucoup des valeurs de la colonne 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. Vous pouvez également penser à créer des index NON CLUSTERED sur les autres colonnes de la clause WHERE. Pourriez-vous s'il vous plaît montrer aussi le plan d'exécution de la requête? (Comment: Dans l'éditeur de requête SQL, appuyez sur CTRL + M, puis exécutez votre requête. Vous obtiendrez un plan d'exécution avec les résultats.)



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi