Ajustar el rendimiento con el almacén de consultas

Artículo
06/11/2024

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

La característica del almacén de consultas de SQL Server le permite descubrir y optimizar las consultas de su carga de trabajo a través de la interfaz visual de SQL Server Management Studio y mediante consultas T-SQL. En este artículo se detalla cómo puede obtener información procesable para mejorar el rendimiento de las consultas en su base de datos, incluido cómo identificar las consultas en función de sus estadísticas de uso y planes de forzado. También puede usar la característica de sugerencias del almacén de consultas para identificar consultas y dar forma a sus planes de consulta sin cambiar el código de la aplicación.

Para obtener más información sobre cómo se recopilan estos datos, consulte Cómo recopila datos el almacén de consultas.
Para obtener más información sobre cómo configurar y administrar el almacén de consultas, consulte Optimización del rendimiento mediante el almacén de consultas.
Para obtener más información sobre cómo funciona el almacén de consultas en Base de datos SQL de Azure, vea Funcionamiento del almacén de consultas de Base de datos SQL de Azure.

Ejemplo de consultas de ajuste del rendimiento

El Almacén de consultas mantiene un historial de las métricas de compilación y tiempo de ejecución en todas las ejecuciones de consulta, lo que le permite realizar preguntas sobre la carga de trabajo.

Las consultas de ejemplo siguientes pueden ser útiles en la línea de base de rendimiento y la investigación del rendimiento de las consultas:

Últimas consultas ejecutadas en la base de datos

Las últimas n consultas ejecutadas en la base de datos en la última hora:

SELECT TOP 10 qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;

Recuento de ejecuciones

Número de ejecuciones para cada consulta en la última hora:

SELECT q.query_id,
    qt.query_text_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
    qt.query_text_id,
    qt.query_sql_text
ORDER BY total_execution_count DESC;

Tiempo medio de ejecución más largo

Número de consultas con la duración media más alta en la última hora:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

Media máxima de lecturas de E/S físicas

El número de consultas que han tenido la media máxima de lecturas de E/S físicas durante las últimas 24 horas, con la correspondiente media del número de filas y el número de ejecuciones:

SELECT TOP 10 rs.avg_physical_io_reads,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.runtime_stats_id,
    rsi.start_time,
    rsi.end_time,
    rs.avg_rowcount,
    rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Consultas con varios planes

Las consultas con más de un plan son especialmente interesantes, ya que pueden ser candidatas para una regresión en el rendimiento debido a un cambio en la elección del plan.

La consulta siguiente identifica las consultas con el número más alto de planes en la última hora:

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

La siguiente consulta identifica estas consultas junto con todos los planes en la última hora:

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    GROUP BY q.query_id
    HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    query_sql_text,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
    p.plan_id;

Duraciones de espera más altas

Esta consulta devolverá las primeras 10 consultas con las duraciones de espera más altas de la última hora:

SELECT TOP 10 qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
    q.query_id,
    p.plan_id
ORDER BY sum_total_wait_ms DESC;

Nota:

En Azure Synapse Analytics, las consultas de Almacén de consultas de ejemplo de esta sección se admiten con la excepción de las estadísticas de espera, que no están disponibles en las DMV del Almacén de consultas de Azure Synapse Analytics.

Consultas que se han devuelto recientemente por motivo de rendimiento

El siguiente ejemplo de consulta devuelve todas las consultas para las que se duplicó el tiempo de ejecución en las últimas 48 horas debido a un cambio de elección del plan. Esta consulta compara todos los intervalos de estadísticas en tiempo de ejecución en paralelo:

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

Si quiere ver el rendimiento de todas las regresiones (no solo de aquellas relacionadas con el cambio de elección de plan), elimine la condición AND p1.plan_id <> p2.plan_id de la consulta anterior.

Consultas con regresión histórica en el rendimiento

Al comparar la ejecución reciente con la ejecución histórica, la siguiente consulta compara los períodos de ejecución basados en la ejecución de consultas. En este ejemplo concreto, la consulta compara la ejecución en el período reciente (1 hora) con el período histórico (último día) e identifica las que han presentado additional_duration_workload. Esta métrica se calcula como una diferencia entre la media de ejecuciones recientes y la media de ejecuciones históricas multiplicada por el número de ejecuciones recientes. Representa la duración adicional que han introducido estas ejecuciones recientes, en comparación con el historial:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;

SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;

SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH hist AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @history_start_time
        AND rs.last_execution_time < @history_end_time
    )
    OR (
        rs.first_execution_time <= @history_start_time
        AND rs.last_execution_time > @history_start_time
    )
    OR (
        rs.first_execution_time <= @history_end_time
        AND rs.last_execution_time > @history_end_time
    )
    GROUP BY p.query_id
),
recent AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @recent_start_time
        AND rs.last_execution_time < @recent_end_time
    )
    OR (
        rs.first_execution_time <= @recent_start_time
        AND rs.last_execution_time > @recent_start_time
    )
    OR (
        rs.first_execution_time <= @recent_end_time
        AND rs.last_execution_time > @recent_end_time
    )
    GROUP BY p.query_id
    )
SELECT results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
    SELECT hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER JOIN sys.query_store_query_text AS qt
        ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

Mantener la estabilidad del rendimiento de las consultas

En el caso de las consultas ejecutadas varias veces, es posible que observe que SQL Server usa planes diferentes, lo que se traduce en el uso de recursos y una duración diferentes. Con el Almacén de consultas puede detectar cuándo ha retrocedido el rendimiento de las consultas y determinar el plan óptimo en un período de interés. Luego puede forzar ese plan óptimo para futuras ejecuciones de consultas.

También puede identificar el rendimiento incoherente de una consulta con parámetros (ya sea con parámetros automáticos o con parámetros manuales). Entre los distintos planes puede identificar el plan que es lo suficientemente rápido y óptimo para todos o la mayoría de los valores de parámetros y forzar ese plan. Esto mantiene un rendimiento predecible para el conjunto más amplio de escenarios de usuario.

Forzar un plan para una consulta (aplicar directiva de forzado)

Cuando se fuerza un plan para una determinada consulta, SQL Server intenta forzar el plan en el optimizador. Si se produce un error al exigir el plan, se producirá un evento extendido y el optimizador de consultas realizará su trabajo de optimización de forma normal.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Al usar sp_query_store_force_plan solo puede forzar los planes que se grabaron por el Almacén de consultas como un plan para esa consulta. Es decir, los únicos planes disponibles para una consulta son aquellos que ya se han usado para ejecutar la consulta mientras el Almacén de consultas estaba activo.

Nota:

No se admite la aplicación de los planes en el Almacén de consultas en Azure Synapse Analytics.

Plan para forzar la compatibilidad con cursores estáticos y de avance rápido

En SQL Server 2019 (15.x) y versiones posteriores y Azure SQL Database (todos los modelos de implementación), el Almacén de consultas admite la capacidad de forzar planes de ejecución de consultas para cursores de avance rápido y estático Transact-SQL y API. Forzar los planes ahora se admite a través de sp_query_store_force_plan o informes de Almacén de consultas de SQL Server Management Studio.

Quitar el forzado de un plan para una consulta

Para volver a confiar en el optimizador de consultas de SQL Server para calcular el plan de consulta óptimo, use sp_query_store_unforce_plan para dejar de forzar el plan que se seleccionó para la consulta.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Compartir a través de

Ajustar el rendimiento con el almacén de consultas

Ejemplo de consultas de ajuste del rendimiento

Últimas consultas ejecutadas en la base de datos

Recuento de ejecuciones

Tiempo medio de ejecución más largo

Media máxima de lecturas de E/S físicas

Consultas con varios planes

Duraciones de espera más altas

Consultas que se han devuelto recientemente por motivo de rendimiento

Consultas con regresión histórica en el rendimiento

Mantener la estabilidad del rendimiento de las consultas

Forzar un plan para una consulta (aplicar directiva de forzado)

Plan para forzar la compatibilidad con cursores estáticos y de avance rápido

Quitar el forzado de un plan para una consulta

Comentarios

Recursos adicionales

Compartir a través de

Ajustar el rendimiento con el almacén de consultas

Ejemplo de consultas de ajuste del rendimiento

Últimas consultas ejecutadas en la base de datos

Recuento de ejecuciones

Tiempo medio de ejecución más largo

Media máxima de lecturas de E/S físicas

Consultas con varios planes

Duraciones de espera más altas

Consultas que se han devuelto recientemente por motivo de rendimiento

Consultas con regresión histórica en el rendimiento

Mantener la estabilidad del rendimiento de las consultas

Forzar un plan para una consulta (aplicar directiva de forzado)

Plan para forzar la compatibilidad con cursores estáticos y de avance rápido

Quitar el forzado de un plan para una consulta

Contenido relacionado

Comentarios

Recursos adicionales