Ajustar o desempenho com o Repositório de Consultas
Aplica-se a:
SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics
O recurso Repositório de Consultas do SQL Server oferece a capacidade de descobrir e ajustar consultas na carga de trabalho, tanto por meio da interface visual do SQL Server Management Studio quanto por meio de consultas T-SQL. Este artigo detalha como você pode obter informações acionáveis para melhorar o desempenho da consulta no seu banco de dados, incluindo como identificar consultas com base em suas estatísticas de uso e planos forçados. Você também pode usar o recurso de dicas do Repositório de Consultas para identificar consultas e formular os planos de consulta sem alterar o código do aplicativo.
- Para obter mais informações sobre como esses dados são coletados, consulte Como o Repositório de Consultas coleta dados.
- Para obter mais informações configuração e administração com o Repositório de Consultas, confira Monitoramento do desempenho usando o Repositório de Consultas.
- Para obter informações sobre operar o Repositório de consultas no Banco de dados SQL do Azure, consulte Operando o Repositório de Consultas no Banco de dados SQL do Azure.
Consultas de exemplo de ajuste de desempenho
O Repositório de Consultas mantém um histórico das métricas de compilação e de runtime durante as execuções de consulta, permitindo que você faça perguntas sobre sua carga de trabalho.
As consultas de exemplo a seguir podem ser úteis na investigação de desempenho da linha de base de desempenho e da consulta:
Últimas consultas executadas no banco de dados
As últimas n consultas executadas no banco de dados na ú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;
Contagens de execução
Número de execuções para cada consulta na ú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;
Tempo médio de execução mais longo
O número de consultas com a maior duração média na ú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;
Leituras de E/S físicas médias mais altas
O número de consultas que tiveram a maior média de leituras físicas de E/S nas últimas 24 horas, com a média de contagem de linhas e execuções correspondente:
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 com vários planos
Consultas com mais de um plano são especialmente interessantes, pois podem ser candidatas a uma regressão no desempenho devido a uma mudança na escolha do plano.
A consulta a seguir identifica as consultas com o maior número de planos na ú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;
A consulta a seguir identifica essas consultas junto com todos os planos na ú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;
Durações de espera mais altas
Essa consulta retorna as 10 principais consultas com as durações de espera mais altas para a ú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;
Observação
No Azure Synapse Analytics, as consultas de exemplo do Repositório de Consultas nesta seção têm suporte, com exceção das estatísticas de espera, que não estão disponíveis nos DMVs do Repositório de Consultas do Azure Synapse Analytics.
Consultas que recentemente regrediram em desempenho
O exemplo de consulta a seguir retorna todas as consultas cujo tempo de execução dobrou nas últimas 48 horas devido a uma alteração na escolha do plano. Essa consulta compara todos os intervalos de estatísticas de runtime lado a lado:
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;
Se você quiser ver todas as regressões de desempenho (não apenas as regressões relacionadas à alteração de escolha de plano), remova a condição AND p1.plan_id <> p2.plan_id da consulta anterior.
Consultas com regressão histórica no desempenho
Quando você deseja comparar a execução recente com a execução histórica, a consulta a seguir compara a execução da consulta com base no período de execução. Nesse exemplo específico, a consulta compara a execução no período recente (uma hora) com o período do histórico (último dia) e identifica as que apresentaram o additional_duration_workload. Essa métrica é calculada como uma diferença entre a média de execução recente e a média de execução do histórico, multiplicado pelo número de execuções recentes. Ele representa quanta duração extra essas execuções recentes introduziram, em comparação com a história:
--- "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);
Manter a estabilidade do desempenho da consulta
Para consultas executadas várias vezes, você pode notar que o SQL Server usa planos diferentes, resultando em utilização e duração de recursos diferentes. Com o Repositório de Consultas, você pode detectar quando o desempenho da consulta regrediu e determinar o plano ideal dentro de um período de interesse. Em seguida, você pode impor esse plano ideal para execução futura da consulta.
Você também pode identificar desempenho de consulta inconsistente para uma consulta com parâmetros (parametrizados automaticamente ou parametrizados manualmente). Entre diferentes planos, você pode identificar o plano que é rápido e ideal o suficiente para todos ou a maioria dos valores de parâmetro e forçar esse plano. Isso mantém o desempenho previsível para o conjunto mais amplo de cenários de usuário.
Impor um plano para uma consulta (aplicar política de imposição)
Quando um plano é imposto em determinada consulta, o SQL Server tenta impor o plano no otimizador. Se a força do plano falhar, um Evento Estendido será acionado e o otimizador será instruído a otimizar da maneira normal.
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
Ao usar sp_query_store_force_plano , você só pode forçar planos registrados pelo Repositório de Consultas como um plano para essa consulta. Em outras palavras, os únicos planos disponíveis para uma consulta são planos que já foram usados para executar essa consulta enquanto o Repositório de Consultas estava ativo.
Observação
Não há suporte para forçar planos no Repositório de Consultas no Azure Synapse Analytics.
Planejar forçar suporte para cursores fast forward e static
No SQL Server 2019 (15.x) e versões posteriores e no Banco de Dados SQL do Azure (todos os modelos de implantação), o Repositório de Consultas oferece suporte à capacidade de forçar planos de execução de consulta para cursores Transact-SQL e API estáticos e de avanço rápido. Há suporte para a imposição por meio de sp_query_store_force_plan ou por meio de relatórios do Repositório de Consultas do SQL Server Management Studio.
Remover a imposição de plano de uma consulta
Para confiar novamente no otimizador de consulta do SQL Server para calcular o plano de consulta ideal, use sp_query_store_unforce_plan para liberar a imposição do plano que foi selecionado para a consulta.
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;
Conteúdo relacionado
- Monitorar o desempenho usando o Repositório de Consultas
- Melhor prática com o Repositório de Consultas
- Usar o Repositório de Consultas com OLTP na memória
- Cenários de uso do Repositório de Consultas
- Como o Repositório de Consultas coleta dados
- Procedimentos armazenados do Repositório de Consultas (Transact-SQL)
- Exibições de catálogo do Repositório de Consultas (Transact-SQL)
- Abrir o Monitor de Atividade (SQL Server Management Studio)
- Estatísticas de consulta dinâmica
- Monitor de Atividade
- sys.database_query_store_options (Transact-SQL)
- Monitorar e ajustar o desempenho
- Ferramentas para monitoramento e ajuste de desempenho
- Dicas do Repositório de Consultas
- Ajuste do banco de dados usando a carga de trabalho do Repositório de Consultas com o Orientador de Otimização do Mecanismo de Banco de Dados
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários