Uso de las vistas de administración dinámica para identificar y solucionar problemas de rendimiento de las consultas

  • 8 minutos

Las vistas de administración dinámica proporcionan una experiencia de programación para supervisar la actividad del grupo de SQL de Azure Synapse Analytics mediante el lenguaje Transact-SQL. Las vistas que se proporcionan no solo le permiten solucionar e identificar cuellos de botella de rendimiento en las cargas de trabajo que se ejecutan en su sistema, también se usan en otros servicios como Azure Advisor para proporcionar recomendaciones sobre Azure Synapse Analytics.

Hay más de 90 vistas de administración dinámica que se pueden consultar con los grupos de SQL dedicados para recuperar información acerca de las siguientes áreas del servicio:

  • Información de conexión y actividad
  • Solicitudes de ejecución y consultas de SQL
  • Información de índices y estadísticas
  • Bloqueo de recursos y actividad de bloqueo
  • Actividad de servicio de movimiento de datos
  • Errors

El siguiente es un ejemplo de la supervisión de la ejecución de consultas de los grupos de SQL de Azure Synapse Analytics. El primer paso consiste en comprobar primero las conexiones con el servidor antes de comprobar la actividad de ejecución de la consulta. 

Supervisión de conexiones

Todos los inicios de sesión en el almacenamiento de datos se registran en sys.dm_pdw_exec_sessions. 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. El campo request_id identifica cada consulta de forma única y es la clave principal de esta DMV. Este campo 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.

Paso 1

El primer paso es identificar la consulta que desea 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 la consulta sys.dm_pdw_waits con un tipo de 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 it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Paso 2

Use el identificador de la solicitud para recuperar el plan de SQL distribuido (DSQL) de las consultas 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:

  • Vaya al paso 3 para operaciones SQL: OnOperation, RemoteOperation, ReturnOperation.
  • Vaya al paso 4 para operaciones de movimiento de datos: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

Paso 3

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

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 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 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);

Las vistas de administración dinámica (DMV) solo contienen 10 000 filas de datos. En sistemas de uso intensivo, esto significa que los datos contenidos en esta tabla se pueden perder por horas o incluso minutos, ya que los datos se administran en un sistema "primero en entrar, primero en salir". Como resultado, puede perder información importante que puede ayudarle a diagnosticar los problemas de rendimiento de las consultas en el sistema. En esta situación, debe utilizar el Almacén de consultas.

También puede supervisar aspectos adicionales de los grupos de SQL de Azure Synapse, entre los que se incluyen:

  • Supervisión de esperas
  • Supervisión de tempdb
  • Supervisión de memoria
  • Supervisión de registro de transacciones
  • Supervisión de PolyBase

Puede obtener información sobre la supervisión de estas áreas aquí.