Supervisión de la carga de trabajo del grupo de SQL dedicado de Azure Synapse Analytics mediante DMV

Artículo
06/01/2023

En este artículo se describe cómo usar las vistas de administración dinámica (DMV) para supervisar la carga de trabajo e investigar la ejecución de la consulta en un grupo de SQL dedicado.

Permisos

Para consultar las DMV de este artículo, necesita el permiso VER ESTADO DE BASE DE DATOS o CONTROL. Normalmente la concesión VER ESTADO DE BASE DE DATOS es el permiso preferido ya que es mucho más restrictivo.

GRANT VIEW DATABASE STATE TO myuser;

Supervisión de conexiones

Todos los inicios de sesión en el almacenamiento de datos se registran en sys.dm_pdw_exec_sessions. Esta DMV contiene los 10.000 últimos inicios de sesión. El campo session_id es la clave principal y se asigna de forma secuencial para cada nuevo inicio de sesión.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Supervisión de ejecuciones de consultas

Todas las consultas ejecutadas en el grupo de SQL se registran en sys.dm_pdw_exec_requests. Esta DMV contiene las últimas 10.000 consultas ejecutadas. El campo request_id identifica cada consulta de forma única y es la clave principal de esta DMV. Este campo request_id se asigna de forma secuencial para cada nueva consulta y lleva el prefijo QID, que representa el identificador de consulta. Al consultar en esta DMV sobre un campo session_id determinado, se muestran todas las consultas de un inicio de sesión concreto.

Nota

Los procedimientos almacenados utilizan varios identificadores de solicitud. Los identificadores de solicitud se asignan en orden secuencial.

Estos son los pasos que deben seguirse para investigar los planes de ejecución de consultas y las horas de una consulta determinada.

Paso 1: Identificación de la consulta que quiere investigar

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

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

En los resultados de la consulta anterior, fíjese en el id. de solicitud de la consulta que quiere investigar.

Las consultas en estado suspendido se pueden poner en cola si hay un gran número de consultas activas en ejecución. Estas consultas también aparecen en sys.dm_pdw_waits. En ese caso, busque esperas como UserConcurrencyResourceType. Para obtener información sobre los límites de simultaneidad, consulte Límites de memoria y simultaneidad o Clases de recursos para administración de carga de trabajo. Las consultas también pueden esperar por otros motivos, como los bloqueos de objetos. Si la consulta está esperando un recurso, consulte la sección Supervisión de consultas en espera más adelante en este artículo.

Para simplificar la búsqueda de una consulta en la tabla sys.dm_pdw_exec_requests, use LABEL para asignar un comentario a la consulta que se pueda buscar en la vista sys.dm_pdw_exec_requests.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Paso 2: investigación del plan de consulta

Use el identificador de la solicitud para recuperar el plan de SQL distribuido (DSQL) de la consulta desde sys.dm_pdw_request_steps.

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Si un plan DSQL tarda más de lo esperado, es posible que sea un plan complejo con muchos pasos DSQL o con un solo paso que tarda mucho tiempo. Si el plan tiene muchos pasos con varias operaciones de movimiento, considere la posibilidad de optimizar las distribuciones de la tabla para reducir el movimiento de datos. En el artículo Distribución de tablas se explica por qué los datos deben moverse para resolver una consulta. En el artículo también se explican algunas estrategias de distribución para minimizar el movimiento de datos.

Para investigar más detalles acerca de un solo paso, compruebe la columna operation_type del paso de consulta de larga ejecución y anote el valor de Índice de pasos:

Para las operaciones SQL: (OnOperation, RemoteOperation, ReturnOperation), continúe con el PASO 3
Para las operaciones de movimiento de datos: (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), continúe con el PASO 4.

Paso 3: Investigar SQL en las bases de datos distribuidas

Use el identificador de solicitud y el índice de pasos para recuperar información de sys.dm_pdw_sql_requests, que contiene detalles sobre la ejecución del paso de la consulta en todas las instancias distribuidas.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Si la consulta se está ejecutando, se puede utilizar DBCC PDW_SHOWEXECUTIONPLAN para recuperar el plan estimado de SQL Server de la caché de planes de SQL Server para el paso que se está ejecutando en una distribución particular.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Paso 4: Investigar el movimiento de datos en las bases de datos distribuidas

Use el identificador de solicitud y el índice de paso para recuperar información sobre el paso de movimiento de datos que se ejecuta en cada distribución desde sys.dm_pdw_dms_workers.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

Compruebe la columna total_elapsed_time para ver si una distribución determinada tarda bastante más que otras en el movimiento de datos.
Para la distribución de larga ejecución, compruebe la columna rows_processed para ver si el número de filas que se mueven desde esa distribución es mucho mayor que para las demás. En ese caso, este hallazgo puede indicar un sesgo de los datos subyacentes. Una causa para el sesgo de los datos es la distribución en una columna con muchos valores NULL (cuyas filas se colocarán en la misma distribución). Evite las consultas lentas evitando la distribución en estos tipos de columnas o filtrando la consulta para eliminar los valores NULL cuando sea posible.

Si la consulta se está ejecutando, puede usar DBCC PDW_SHOWEXECUTIONPLAN para recuperar el plan estimado de SQL Server de la caché de planes de SQL Server para el paso de SQL que se está ejecutando actualmente en una distribución particular.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Supervisión de consultas en espera

Si observa que la consulta no avanza porque está esperando un recurso, a continuación puede encontrar una consulta que muestra todos los recursos que está esperando una consulta.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Si la consulta espera activamente recursos de otra consulta, el estado será AcquireResources. Si la consulta tiene todos los recursos necesarios, el estado será Concedido.

Supervisión de tempdb

La base de datos tempdb se usa para almacenar resultados intermedios durante la ejecución de la consulta. Un uso alto de la base de datos tempdb puede dar lugar a una disminución del rendimiento de la consulta. Para cada DW100c que se configure, se asignan 399 GB de espacio de tempdb (DW1000c tendría 3,99 TB de espacio total de tempdb). A continuación se muestran sugerencias para supervisar el uso de tempdb y para reducir el uso de tempdb en las consultas.

Supervisión de tempdb con vistas

Para supervisar el uso de tempdb, instale primero la vista microsoft.vw_sql_requests desde el kit de herramientas de Microsoft para grupos de SQL. A continuación, puede ejecutar la consulta siguiente para ver el uso de tempdb por nodo para todas las consultas ejecutadas:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Nota

El movimiento de datos usa tempdb. Para reducir el uso de tempdb durante el movimiento de datos, asegúrese de que la tabla usa una estrategia de distribución que distribuya los datos de manera uniforme. Use el asesor de distribución de Azure Synapse SQL para obtener recomendaciones sobre el método de distribución adecuado para las cargas de trabajo. Use el kit de herramientas de Azure Synapse para supervisar tempdb mediante consultas T-SQL.

Si tiene una consulta que consume una gran cantidad de memoria, o ha recibido un mensaje de error relacionado con la asignación de tempdb, podría deberse a una instrucción CREATE TABLE AS SELECT (CTAS) o INSERT SELECT muy grande que da error en la operación de movimiento de datos final. Normalmente esto se identifica como una operación ShuffleMove en el plan de consulta distribuida justo antes de la instrucción INSERT SELECT final. Use sys.dm_pdw_request_steps para supervisar las operaciones de ShuffleMove.

La solución más común consiste en dividir la instrucción CTAS o INSERT SELECT en varias instrucciones de carga para que el volumen de datos no supere el límite de tempdb de 399 GB por cada 100 DWUc. También puede escalar el clúster a un tamaño mayor para aumentar el espacio que tiene en tempdb.

Además de las instrucciones CTAS e INSERT SELECT, las consultas grandes y complejas que se ejecutan con memoria insuficiente pueden desbordarse en tempdb, lo que provoca un error en las consultas. Considere la posibilidad de realizar la ejecución con una clase de recurso mayor para evitar el desbordamiento en tempdb.

Supervisión de memoria

La causa principal de los problemas relacionados con el rendimiento lento y la falta de memoria puede ser la memoria. Si observa que el uso de memoria de SQL Server llega a su limite durante la ejecución de consultas, considere la posibilidad de escalar el almacenamiento de datos.

La consulta siguiente devuelve el uso y la presión de memoria de SQL Server por nodo:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

Supervisión del tamaño del registro de transacciones

La siguiente consulta devuelve el tamaño del registro de transacciones en cada distribución. Si uno de los archivos de registro está llegando a 160 GB, debería considerar la posibilidad de escalar verticalmente su instancia o limitar el tamaño de transacción.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

Supervisión de la reversión del registro de transacciones

Si las consultas producen errores o tardan mucho tiempo en continuar, puede comprobar y supervisar si tiene alguna reversión en las transacciones.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

Supervisión de la carga de PolyBase

La siguiente consulta proporciona una estimación aproximada del progreso de la carga. La consulta solo muestra los archivos que se están procesando actualmente.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Supervisión de bloqueos de consultas

La consulta siguiente proporciona las 500 consultas bloqueadas principales en el entorno.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
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
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Recuperación de texto de consulta de consultas en espera y de bloqueo

La consulta siguiente proporciona el texto y el identificador de la consulta para resolver fácilmente los problemas de las consultas en espera y de bloqueo.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
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
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Pasos siguientes

Para más información acerca de las DMV, consulte Vistas del sistema.