Estrategias SQL para detectar lo que está causando un bloqueo / consulta larga

dapper database-performance entity-framework performance sql

Pregunta

Tengo una consulta en una base de datos de Azure SQL que ocasionalmente da como resultado lo siguiente:

enter image description here

Si no hay muchos usuarios, esto no sucede. Pero si los hay, esto sucede regularmente; la consulta lleva mucho tiempo en completarse y mi porcentaje de DTU está casi fuera de los gráficos.

¿Cómo determino qué está causando esto?

Alguna información general:

  • Estoy usando dapper como ORM en algunas partes de la aplicación
  • Estoy usando EF en otras áreas
  • Cuando se cuelga, se cuelga durante> 30 segundos. Rara vez es 1s <tiempo <30s
  • La consulta de dapper que se cuelga está debajo, donde XXXX es una lista de aproximadamente 2.500 ID de artículo en ambos casos:
  • Parece que los usuarios con un pequeño conjunto de XXXX Ids SIEMPRE no responden a este problema. La recuperación de XXXX no siempre es un problema, pero el uso de un gran XXXX parece agravar el rendimiento a veces.
  • [Usuario] está indexado en UserId (PK) y LastOnline

Código:

 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

Soy un poco nuevo en el mundo del rendimiento forense ... cualquier consejo sería increíble.

Actualización - Plan de ejecución:

enter image description here

Respuesta aceptada

Aquí hay algunas cosas que puedes probar:

  1. Vea si puede crear un procedimiento almacenado para devolver su lista requerida pasando @UserID como parámetro. Llame a este procedimiento almacenado en lugar de generar consultas cada vez.
  2. Reemplace NO EN CON NO EXISTE como a continuación. Primero verifica si esto ayuda. Depende mucho de los valores en la columna 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. También puede pensar en crear índices NO CLUSTERADOS en las otras columnas que tiene en la cláusula WHERE. ¿Podría mostrar el plan de ejecución de la consulta también? (Cómo: en el editor de consultas SQL, presione CTRL + M y luego ejecute su consulta. Obtendrá un plan de ejecución junto con los resultados).



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué