Compartir a través de

Solución de problemas de una consulta lenta en un grupo de SQL dedicado

  • Artículo

Se aplica a: Azure Synapse Analytics

Este artículo le ayuda a identificar los motivos y a aplicar mitigaciones para problemas comunes de rendimiento con consultas en un grupo de SQL dedicado de Azure Synapse Analytics.

Siga los pasos para solucionar el problema o ejecute los pasos del cuaderno a través de Azure Data Studio. Los tres primeros pasos le guiarán por la recopilación de telemetría, que describe el ciclo de vida de una consulta. Las referencias al final del artículo le ayudarán a analizar las posibles oportunidades encontradas en los datos recopilados.

Nota:

Antes de intentar abrir este cuaderno, asegúrese de que Azure Data Studio está instalado en el equipo local. Para instalarlo, vaya a Aprenda a instalar Azure Data Studio.

Apertura de un cuaderno en Azure Data Studio

Importante

La mayoría de los problemas de rendimiento notificados se deben a:

  • Estadísticas obsoletas
  • Índices de almacén de columnas agrupados incorrectos (CC)

Para ahorrar tiempo de solución de problemas, asegúrese de que se han creado y actualizado las estadísticas y que se han vuelto a generar los CCIs.

Paso 1: Identificar el request_id (también conocido como QID)

La request_id de la consulta lenta es necesaria para investigar posibles motivos de una consulta lenta. Use el siguiente script como punto de partida para identificar la consulta que desea solucionar. Una vez identificada la consulta lenta, anote el request_id valor.

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Para dirigirse mejor a las consultas lentas, use las siguientes sugerencias al ejecutar el script:

  • Ordene por submit_time DESC o total_elapsed_time DESC para que las consultas de ejecución más largas estén presentes en la parte superior del conjunto de resultados.

  • Use OPTION(LABEL='<YourLabel>') en las consultas y filtre la label columna para identificarlas.

  • Considere la posibilidad de filtrar los QID que no tengan un valor para resource_allocation_percentage cuando sepa que la instrucción de destino está contenida en un lote.

    Nota: Tenga cuidado con este filtro, ya que también puede filtrar algunas consultas bloqueadas por otras sesiones.

Paso 2: Determinar dónde tarda la consulta

Ejecute el siguiente script para encontrar el paso que puede provocar el problema de rendimiento de la consulta. Actualice las variables del script con los valores descritos en la tabla siguiente. Cambie el @ShowActiveOnly valor a 0 para obtener la imagen completa del plan distribuido. Tome nota de los StepIndexvalores , Phasey Description del paso lento identificado a partir del conjunto de resultados.

Parámetro Descripción
@QID Valor obtenido en el request_id paso 1
@ShowActiveOnly 0- Mostrar todos los pasos de la consulta
1 - Mostrar solo el paso activo actualmente		 
DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
   AND ([type] LIKE 'Shared-%' OR
      [type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
   AND [state] = 'Queued'
GROUP BY session_id 
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
   + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
   waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
   COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits waiting
   INNER JOIN sys.dm_pdw_waits blocking
      ON waiting.object_type = blocking.object_type
      AND waiting.object_name = blocking.object_name
   INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
      ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
   AND blocking.state = 'Granted' AND waiting.type != 'Shared' 
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
       'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime], end_time AS [EndTime],
       total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
   AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

Paso 3: Revisar los detalles del paso

Ejecute el siguiente script para revisar los detalles del paso identificado en el paso anterior. Actualice las variables del script con los valores descritos en la tabla siguiente. Cambie el @ShowActiveOnly valor a 0 para comparar todos los intervalos de distribución. Tome nota del wait_type valor de la distribución que puede provocar el problema de rendimiento.

Parámetro Descripción
@QID Valor obtenido en el request_id paso 1
@StepIndex Valor identificado en el StepIndex paso 2
@ShowActiveOnly 0- Mostrar todas las distribuciones para el valor especificado StepIndex
1 - Mostrar solo las distribuciones activas actualmente para el valor especificado StepIndex		 
DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

Paso 4: Diagnóstico y mitigación

Problemas de la fase de compilación

Bloqueado: simultaneidad de compilación

Los bloques de compilación de simultaneidad rara vez se producen. Sin embargo, si encuentra este tipo de bloque, significa que se envió un gran volumen de consultas en un breve tiempo y se han puesto en cola para comenzar la compilación.

Mitigaciones

Reduzca el número de consultas enviadas simultáneamente.


Bloqueado: asignación de recursos

Bloquearse para la asignación de recursos significa que la consulta está esperando a ejecutarse en función de:

  • Cantidad de memoria concedida en función de la asignación de grupo de cargas de trabajo o clase de recursos asociada al usuario.
  • Cantidad de memoria disponible en el sistema o grupo de cargas de trabajo.
  • (Opcional) Importancia del clasificador o grupo de cargas de trabajo.

Mitigaciones

  • Espera a que se complete la sesión de bloqueo.
  • Evalúe la opción de clase de recurso. Para más información, consulte Límites de simultaneidad.
  • Evalúe si es preferible eliminar la sesión de bloqueo.
Consulta compleja o sintaxis JOIN anterior

Es posible que encuentre una situación en la que los métodos predeterminados del optimizador de consultas sean ineficaces, ya que la fase de compilación tarda mucho tiempo. Puede producirse si la consulta:

  • Implica un gran número de combinaciones o subconsultas (consulta compleja).
  • Utiliza combinaciones en la FROM cláusula (no combinaciones de estilo ANSI-92).

Aunque estos escenarios son atípicos, tiene opciones para intentar invalidar el comportamiento predeterminado para reducir el tiempo que tarda el optimizador de consultas en elegir un plan.

Mitigaciones

  • Use combinaciones de estilo ANSI-92.
  • Agregue sugerencias de consulta: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Para obtener más información, vea FORCE ORDER and Cardinality Estimation (SQL Server).
  • Divida la consulta en varios pasos menos complejos.
DROP TABLE o TRUNCATE TABLE de larga duración

En el caso de las eficiencias del tiempo de ejecución, las DROP TABLE instrucciones y TRUNCATE TABLE aplazarán la limpieza del almacenamiento a un proceso en segundo plano. Sin embargo, si la carga de trabajo realiza un gran número de DROP/TRUNCATE TABLE instrucciones en un breve período de tiempo, es posible que los metadatos se llenan y haga que las instrucciones posteriores DROP/TRUNCATE TABLE se ejecuten lentamente.

Mitigaciones

Identifique una ventana de mantenimiento, detenga todas las cargas de trabajo y ejecute DBCC SHRINKDATABASE para forzar una limpieza inmediata de las tablas eliminadas o truncadas previamente.


CCIs incorrectos (por lo general)

Un estado deficiente del índice de almacén de columnas agrupado (CCI) requiere metadatos adicionales, lo que puede hacer que el optimizador de consultas tarde más tiempo en determinar un plan óptimo. Para evitar esta situación, asegúrese de que todas las CCIs estén en buen estado.

Mitigaciones

Evalúe y corrija el estado del índice de almacén de columnas agrupado en un grupo de SQL dedicado.


Retraso de las estadísticas de creación automática

La opción de creación automática de estadísticas esON, de forma predeterminada, AUTO_CREATE_STATISTICS para ayudar a garantizar que el optimizador de consultas pueda tomar buenas decisiones de plan distribuido. Sin embargo, el propio proceso de creación automática puede hacer que una consulta inicial tarde más tiempo que las ejecuciones posteriores de la misma.

Mitigaciones

Si la primera ejecución de la consulta requiere que se creen estadísticas de forma coherente, deberá crear manualmente estadísticas antes de la ejecución de la consulta.


Tiempos de espera de creación automática de estadísticas

La opción de creación automática de estadísticas esON, de forma predeterminada, AUTO_CREATE_STATISTICS para ayudar a garantizar que el optimizador de consultas pueda tomar buenas decisiones de plan distribuido. La creación automática de estadísticas se produce en respuesta a una instrucción SELECT y tiene un umbral de 5 minutos para completarse. Si el tamaño de los datos o el número de estadísticas que se van a crear requieren más tiempo que el umbral de 5 minutos, se abandonará la creación automática de estadísticas para que la consulta pueda continuar con la ejecución. El error al crear las estadísticas puede afectar negativamente a la capacidad del optimizador de consultas para generar un plan de ejecución distribuido eficaz, lo que da lugar a un rendimiento deficiente de las consultas.

Mitigaciones

Cree manualmente las estadísticas en lugar de confiar en la característica de creación automática para las tablas o columnas identificadas.

Problemas de fase de ejecución

  • Use la tabla siguiente para analizar el conjunto de resultados en el paso 2. Determine su escenario y compruebe la causa común para obtener información detallada y los posibles pasos de mitigación.

    Escenario Causa común
    EstimatedRowCount/ActualRowCount< 25% Estimaciones inexactas
    El Description valor indica y la consulta hace BroadcastMoveOperation referencia a una tabla replicada. Tablas replicadas sin almacenar en caché
    1. @ShowActiveOnly = 0
    2. Se observa un número elevado o inesperado de pasos (step_index).
    3. Los tipos de datos de las columnas de combinación no son idénticos entre las tablas.    		 Tipo o tamaño de datos no coincidendo
    1. El Description valor indica HadoopBroadcastOperation, HadoopRoundRobinOperation o HadoopShuffleOperation.
    2. El total_elapsed_time valor de un determinado step_index es incoherente entre ejecuciones.    		 Consultas de tabla externa ad hoc

  • Compruebe el valor obtenido en el total_elapsed_time paso 3. Si es significativamente mayor en algunas distribuciones en un paso determinado, siga estos pasos:

    1. Compruebe la distribución de datos de cada tabla a la que se hace referencia en el TSQL campo correspondiente step_id mediante la ejecución del siguiente comando en cada una de ellas:

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. Si <el valor mínimo de las filas o< el valor>>> máximo de las filas es 0,1, vaya a Asimetría de datos (almacenado) .

    3. De lo contrario, vaya a Asimetría de datos en curso.

Estimaciones inexactas

Haga que las estadísticas estén actualizadas para asegurarse de que el optimizador de consultas genera un plan óptimo. Cuando el recuento de filas estimado es significativamente menor que los recuentos reales, es necesario mantener las estadísticas.

Mitigaciones

Crear o actualizar estadísticas.


Tablas replicadas sin almacenar en caché

Si ha creado tablas replicadas y no puede calentar correctamente la caché de tablas replicadas, un rendimiento deficiente inesperado dará lugar a movimientos de datos adicionales o a la creación de un plan distribuido poco óptimo.

Mitigaciones

  • Caliente la caché replicada después de las operaciones DML.
  • Si hay operaciones DML frecuentes, cambie la distribución de la tabla a ROUND_ROBIN.
Tipo o tamaño de datos no coincidendo

Al combinar tablas, asegúrese de que el tipo de datos y el tamaño de las columnas de combinación coincidan. De lo contrario, se producirán movimientos de datos innecesarios que reducirán la disponibilidad de cpu, E/S y tráfico de red al resto de la carga de trabajo.

Mitigaciones

Vuelva a generar las tablas para corregir las columnas de tabla relacionadas que no tienen un tamaño y tipo de datos idénticos.


Consultas de tabla externa ad hoc

Las consultas en tablas externas están diseñadas con la intención de cargar datos de forma masiva en el grupo de SQL dedicado. Las consultas ad hoc en tablas externas pueden sufrir duraciones variables debido a factores externos, como las actividades simultáneas del contenedor de almacenamiento.

Mitigaciones

Cargue primero los datos en el grupo de SQL dedicado y, a continuación, consulte los datos cargados.


Asimetría de datos (almacenada)

La asimetría de datos significa que los datos no se distribuyen uniformemente entre las distribuciones. Cada paso del plan distribuido requiere que se completen todas las distribuciones antes de pasar al paso siguiente. Cuando los datos están sesgados, no se puede lograr el potencial completo de los recursos de procesamiento, como la CPU y la E/S, lo que da lugar a tiempos de ejecución más lentos.

Mitigaciones

Revise nuestras instrucciones para las tablas distribuidas para ayudar a elegir una columna de distribución más adecuada.


Asimetría de datos en curso

La asimetría de datos en curso es una variante del problema de asimetría de datos (almacenado). Pero no es la distribución de datos en el disco que está sesgada. La naturaleza del plan distribuido para determinados filtros o datos agrupados provoca una ShuffleMoveOperation operación de tipo. Esta operación genera una salida sesgada que se va a consumir de bajada.

Mitigaciones

  • Asegúrese de que las estadísticas se creen y estén actualizadas.
  • Cambie el orden de las GROUP BY columnas para que se dirija con una columna de cardinalidad superior.
  • Cree estadísticas de varias columnas si las combinaciones cubren varias columnas.
  • Agregue una sugerencia OPTION(FORCE_ORDER) de consulta a la consulta.
  • Refactorización de la consulta.

Problemas de tipo de espera

Si ninguno de los problemas comunes anteriores se aplica a la consulta, los datos del paso 3 ofrecen la oportunidad de determinar qué tipos de espera (en wait_type y wait_time) interfieren con el procesamiento de consultas para el paso de ejecución más largo. Hay un gran número de tipos de espera y se agrupan en categorías relacionadas debido a mitigaciones similares. Siga estos pasos para buscar la categoría de espera del paso de consulta:

  1. Identifique en el wait_type paso 3 que tarda más tiempo.
  2. Busque el tipo de espera en la tabla de asignación de categorías de espera e identifique la categoría de espera en la que se incluyó.
  3. Expanda la sección relacionada con la categoría de espera de la lista siguiente para las mitigaciones recomendadas.
Compilación

Siga estos pasos para mitigar los problemas de tipo de espera de la categoría Compilación:

  1. Vuelva a generar índices para todos los objetos implicados en la consulta problemática.
  2. Actualice las estadísticas de todos los objetos implicados en la consulta problemática.
  3. Vuelva a probar la consulta problemática para validar si el problema persiste.

Si el problema persiste, haga lo siguiente:

  1. Cree un archivo .sql con:

    SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;

  2. Abra una ventana del símbolo del sistema y ejecute el siguiente comando:

    sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt

  3. Abra <output_file_name>.txt en un editor de texto. Busque y copie pegue los planes de ejecución de nivel de distribución (líneas que comienzan por <ShowPlanXML>) del paso de ejecución más largo identificado en el paso 2 en archivos de texto independientes con una extensión .sqlplan .

    Nota: Cada paso del plan distribuido normalmente tendrá registrados 60 planes de ejecución de nivel de distribución. Asegúrese de que está preparando y comparando los planes de ejecución del mismo paso de plan distribuido.

  4. La consulta paso 3 muestra con frecuencia algunas distribuciones que tardan mucho más que otras. En SQL Server Management Studio, compare los planes de ejecución de nivel de distribución (de los archivos .sqlplan creados) de una distribución de ejecución prolongada a una distribución de ejecución rápida para analizar posibles causas de diferencias.

Bloqueo, subproceso de trabajo
  • Considere la posibilidad de cambiar las tablas que se someten a cambios frecuentes y pequeños para usar un índice de almacén de filas en lugar de CCI.
  • Redondifique por lotes los cambios y actualice el destino con más filas con menos frecuencia.
E/S de búfer, otra E/S de disco, E/S de tran log

CC incorrectos

Los CCIs incorrectos contribuyen a aumentar la asignación de E/S, CPU y memoria, lo que, a su vez, afecta negativamente al rendimiento de las consultas. Para mitigar este problema, pruebe uno de los métodos siguientes:

Estadísticas obsoletas

Las estadísticas obsoletas pueden provocar la generación de un plan distribuido no optimizado, lo que implica más movimiento de datos de lo necesario. El movimiento de datos innecesario aumenta la carga de trabajo no solo en los datos en reposo, sino también en tempdb. Dado que la E/S es un recurso compartido en todas las consultas, toda la carga de trabajo puede sentir los efectos en el rendimiento.

Para solucionar esta situación, asegúrese de que todas las estadísticas estén actualizadas y que haya un plan de mantenimiento para mantenerlos actualizados para las cargas de trabajo de usuario.

Cargas de trabajo de E/S intensivas

La carga de trabajo general puede estar leyendo grandes cantidades de datos. Los grupos de SQL dedicados de Synapse escalan los recursos de acuerdo con DWU. Para lograr un mejor rendimiento, tenga en cuenta o ambos:

CPU, paralelismo
Escenario Mitigación
Estado de CCI deficiente Evaluación y corrección del estado del índice de almacén de columnas agrupado en un grupo de SQL dedicado
Las consultas de usuario contienen transformaciones. Mueva todo el formato y otras lógicas de transformación a procesos ETL para almacenar las versiones con formato.
Carga de trabajo con prioridad incorrecta Implementación del aislamiento de carga de trabajo
DWU insuficiente para la carga de trabajo Considere la posibilidad de aumentar los recursos de proceso

E/S de red

Si el problema se produce durante una RETURN operación en el paso 2,

  • Reduzca el número de procesos paralelos simultáneos.
  • Escale horizontalmente el proceso más afectado a otro cliente.

Para todas las demás operaciones de movimiento de datos, es probable que los problemas de red parezcan internos para el grupo de SQL dedicado. Para intentar mitigar rápidamente este problema, siga estos pasos:

  1. Modificar la escala del grupo de SQL dedicado a DW100c
  2. Volver a modificar la escala al nivel de DWU deseado
SQL CLR

Evite el uso frecuente de la función mediante la FORMAT() implementación de una forma alternativa de transformar los datos (por ejemplo, CONVERT() con estilo).