Ajustar el rendimiento con el almacén de consultas
Se aplica a:
SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure 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 siguientes consultas de ejemplo pueden ser útiles en la línea base de rendimiento y en 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;
Lecturas de E/S físicas más altas
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 consulta siguiente 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 devuelve las 10 consultas principales con las duraciones de espera más altas para 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 ejemplo Almacén de consultas de esta sección se admiten con la excepción de las estadísticas de espera, que no están disponibles en las DMV de Azure Synapse Analytics Almacén de consultas.
Consultas que se han devuelto recientemente por motivo de rendimiento
En el ejemplo de consulta siguiente se devuelven todas las consultas para las que el tiempo de ejecución se duplica en las últimas 48 horas debido a un cambio de elección de 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 desea ver todas las regresiones de rendimiento (no solo regresiones relacionadas con el cambio de elección del plan), quite la condición AND p1.plan_id <> p2.plan_id de la consulta anterior.
Consultas con regresión histórica en el rendimiento
Si desea comparar la ejecución reciente con la ejecución histórica, la consulta siguiente compara la ejecución de consultas en función del período de ejecución. 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);
Mantenimiento de 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 da lugar a un uso y una duración diferentes de los recursos. 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 (autoparámetros o parametrizados manualmente). Entre diferentes planes, puede identificar el plan lo suficientemente rápido como ó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 forzar el plan, se desencadena un evento extendido y se indica al optimizador que optimice de la manera normal.
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
Cuando se usa sp_query_store_force_plan, solo puede forzar los planes registrados por Almacén de consultas como plan para esa consulta. En otras palabras, los únicos planes disponibles para una consulta son planes que ya se usaron para ejecutar esa consulta mientras Almacén de consultas estaba activo.
Nota:
No se admite forzar planes en 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), Almacén de consultas admite la capacidad de forzar los planes de ejecución de consultas para cursores estáticos y estáticos de 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;
Contenido relacionado
- Supervisión del rendimiento mediante el Almacén de consultas
- Procedimiento recomendado con el Almacén de consultas
- Uso del almacén de consultas con OLTP en memoria
- Query Store Usage Scenarios (Escenarios de uso del Almacén de consultas)
- Introducción a la recopilación de datos del Almacén de consultas
- Procedimientos almacenados del almacén de consultas (Transact-SQL)
- Vistas del catálogo del almacén de consultas (Transact-SQL)
- Abrir el Monitor de actividad (SQL Server Management Studio)
- Estadísticas de consultas activas
- Monitor de actividad
- sys.database_query_store_options (Transact-SQL)
- Supervisión y optimización del rendimiento
- Herramientas de optimización y supervisión del rendimiento
- Sugerencias del Almacén de consultas
- Optimización de la base de datos mediante la carga de trabajo del almacén de consultas con el Asistente para la optimización de motor de base de datos
Comentarios
Proximamente: Ao longo de 2024, retiraremos gradualmente GitHub Issues como mecanismo de comentarios sobre o contido e substituirémolo por un novo sistema de comentarios. Para obter máis información, consulte: https://aka.ms/ContentUserFeedback.
Enviar e ver os comentarios