Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL Server 2016 (13.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (apenas pool SQL dedicado)Base de dados SQL no Microsoft Fabric
O Repositório de Consultas pode ser usado em um amplo conjunto de cenários quando o rastreamento e a garantia de desempenho previsível da carga de trabalho são essenciais. Aqui estão alguns exemplos que você pode considerar:
Identifique e corrija consultas que apresentam regressões na escolha do plano
Identificar e ajustar as principais consultas consumidoras de recursos
Testes A/B
Mantenha a estabilidade de desempenho durante a atualização para o SQL Server mais recente
Identificar e melhorar cargas de trabalho ad hoc
Para obter mais informações sobre como configurar e administrar com o Repositório de Consultas, consulte Monitorando o desempenho usando o Repositório de Consultas.
Para obter informações sobre como descobrir informações acionáveis e ajustar o desempenho com o Repositório de Consultas, consulte Ajustando o desempenho usando o Repositório de Consultas.
Para obter informações sobre como 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.
Identifique e corrija consultas que apresentam regressões na escolha do plano
Durante sua execução regular de consultas, o Otimizador de Consultas pode decidir escolher um plano diferente porque entradas importantes se tornaram diferentes: cardinalidade de dados mudou, índices foram criados, alterados ou descartados, estatísticas foram atualizadas, etc. Normalmente, o novo plano é melhor, ou mais ou menos o mesmo do que o plano usado anteriormente. No entanto, há casos em que o novo plano é significativamente pior - esta situação é referida como regressão de mudança de escolha de plano. Antes do Repositório de Consultas, era um problema difícil de identificar e corrigir, pois o SQL Server não fornecia armazenamento de dados interno para os usuários procurarem planos de execução que foram usados ao longo do tempo.
Com o Query Store, você pode rapidamente:
Identifique todas as consultas cujas métricas de execução foram degradadas, no período de tempo de interesse (última hora, dia, semana, etc.). Use Consultas Retroativas no SQL Server Management Studio para acelerar a sua análise.
Entre as consultas regredidas, é fácil encontrar aquelas que tinham vários planos e que foram prejudicadas por uma escolha de plano inadequada. Use painel Resumo do Plano no Consultas Regressadas para visualizar todos os planos para uma consulta regressada e seu desempenho ao longo do tempo.
Forçar o plano anterior da história, se ele se mostrou melhor. Use o botão Forçar Plano em Consultas Regressadas para forçar o plano selecionado para a consulta.
Para obter uma descrição detalhada do cenário, consulte Query Store: Um gravador de dados de voo para seu banco de dados blog.
Identificar e ajustar as principais consultas consumidoras de recursos
Embora sua carga de trabalho possa gerar milhares de consultas, normalmente apenas algumas delas realmente usam a maioria dos recursos do sistema e, portanto, exigem sua atenção. Entre as principais consultas que consomem recursos, você normalmente encontrará consultas que regrediram ou aquelas que podem ser melhoradas com ajustes adicionais.
A maneira mais fácil de começar a exploração é abrir Principais Consultas que Consomem Recursos no Management Studio. A interface do usuário é separada em três painéis: um histograma que representa as principais consultas consumidoras de recursos (esquerda), um resumo do plano para a consulta selecionada (direita) e um plano de consulta visual para o plano selecionado (inferior). Selecione Configurar para controlar quantas consultas você deseja analisar e o intervalo de tempo de interesse. Além disso, você pode escolher entre diferentes dimensões de consumo de recursos (duração, CPU, memória, E/S, número de execuções) e a linha de base (Média, Mín, Máx, Total, Desvio Padrão).
Veja o resumo do plano à direita para analisar o histórico de execução e aprender sobre os diferentes planos e suas estatísticas de tempo de execução. Use o painel inferior para examinar os diferentes planos ou compará-los visualmente, renderizados lado a lado (use o botão Comparar).
Quando você identifica uma consulta com desempenho abaixo do ideal, sua ação depende da natureza do problema:
Se a consulta foi executada com vários planos e o último plano é significativamente pior do que o plano anterior, você pode usar o mecanismo de imposição de plano para garantir que o SQL Server usará o plano ideal para execuções futuras
Verifique se o otimizador está sugerindo quaisquer índices ausentes no plano XML. Se sim, crie o índice ausente e use o Repositório de Consultas para avaliar o desempenho da consulta após a criação do índice
Certifique-se de que as estatísticas estão up-to-date para as tabelas subjacentes usadas pela consulta.
Certifique-se de que os índices usados pela consulta estão desfragmentados.
Considere reescrever uma consulta cara. Por exemplo, aproveite as vantagens da parametrização de consultas e reduza o uso de SQL dinâmico. Implemente a lógica ideal ao ler os dados (aplique filtragem de dados no lado do banco de dados, não no lado do aplicativo).
Testes A/B
Use o Repositório de Consultas para comparar o desempenho da carga de trabalho antes e depois das alterações do aplicativo.
A lista a seguir contém vários exemplos em que você pode usar o Repositório de Consultas para avaliar o impacto da alteração do ambiente ou do aplicativo no desempenho da carga de trabalho:
Lançamento da nova versão do aplicativo.
Adicionar novo hardware ao servidor.
Criação de índices ausentes em tabelas referenciadas por consultas caras.
Aplicação de política de filtragem para segurança em nível de linha. Para obter mais informações, consulte Otimizando a segurança em nível de linha com o repositório de consultas.
Adicionando controle de versão temporal do sistema a tabelas que são frequentemente modificadas por seus aplicativos OLTP.
Em qualquer um destes cenários, aplique o seguinte fluxo de trabalho:
Execute sua carga de trabalho com o Repositório de Consultas antes da alteração planejada para gerar a linha de base de desempenho.
Aplique a alteração do aplicativo no momento controlado.
Continue executando a carga de trabalho por tempo suficiente para gerar uma imagem de desempenho do sistema após a alteração
Compare os resultados de #1 e #3.
Abra a funcionalidade Consumo Geral do Banco de Dados para determinar o impacto em todo o banco de dados.
Abra Principais Consultas que Consomem Recursos (ou execute sua própria análise usando Transact-SQL) para analisar o impacto da alteração nas consultas mais importantes.
Decida se deseja manter a alteração ou executar a reversão caso o novo desempenho seja inaceitável.
A ilustração a seguir mostra a análise do Repositório de Consultas (etapa 4) em caso de criação de índice ausente. Abra o painel Principais Consultas de Consumo de Recursos / Resumo do plano para visualizar a consulta que deverá ser impactada pela criação do índice:
Além disso, você pode comparar planos antes e depois da criação do índice, renderizando-os lado a lado. ("Compare os planos para a consulta selecionada em uma janela separada" opção da barra de ferramentas, que é marcada com quadrado vermelho na barra de ferramentas.)
Planejar antes que a criação do índice (plan_id = 1, acima) tenha uma dica de índice ausente e você pode inspecionar se a Verificação de Índice Clusterizado foi o operador mais caro na consulta (retângulo vermelho).
O plano após a criação do índice em falta (plan_id = 15, abaixo) agora tem Index Seek (não-clusterizado), que reduz o custo geral da consulta e melhora o seu desempenho (retângulo verde).
Com base na análise, você provavelmente manterá o índice à medida que o desempenho da consulta for melhorado.
Mantenha a estabilidade de desempenho durante a atualização para o SQL Server mais recente
Antes do SQL Server 2014 (12.x), os usuários eram expostos ao risco de regressão de desempenho durante a atualização para a versão mais recente da plataforma. A razão para isso foi o fato de que a versão mais recente do Query Optimizer tornou-se ativa imediatamente assim que novos bits são instalados.
A partir do SQL Server 2014 (12.x), todas as alterações do Otimizador de Consulta estão vinculadas ao nível de compatibilidade de banco de dados mais recente, portanto, os planos não são alterados no momento da atualização, mas sim quando um usuário altera o COMPATIBILITY_LEVEL para o mais recente. Esse recurso, em combinação com o Repositório de Consultas, oferece um ótimo nível de controle sobre o desempenho da consulta no processo de atualização. O fluxo de trabalho de atualização recomendado é mostrado na imagem a seguir:
Atualize o SQL Server sem alterar o nível de compatibilidade do banco de dados. Ele não expõe as alterações mais recentes do Otimizador de Consulta, mas ainda fornece recursos mais recentes do SQL Server, incluindo o Repositório de Consultas.
Habilite o Repositório de Consultas. Para obter mais informações, consulte Manter o Repositório de Consultas ajustado à sua carga de trabalho.
Permita que o Repositório de Consultas capture consultas e planos, e estabeleça uma linha de base de desempenho com o nível de compatibilidade do banco de dados de origem ou anterior. Permaneça nesta etapa por tempo suficiente para capturar todos os planos e obter uma linha de base estável. Esta pode ser a duração de um ciclo de negócios normal para uma carga de trabalho de produção.
Passar para o nível de compatibilidade de base de dados mais recente: exponha a sua carga de trabalho ao Otimizador de Consultas mais recente, a fim de potencialmente criar novos planos.
Use o Repositório de Consultas para correções de análise e regressão: normalmente, as novas melhorias do Otimizador de Consultas devem produzir planos melhores. No entanto, o Repositório de Consultas fornecerá uma maneira fácil de identificar regressões na escolha de planos e corrigi-las usando um mecanismo de forçagem de planos. A partir do SQL Server 2017 (14.x), ao usar o recurso de Correção Automática de Plano, essa etapa torna-se automática.
a. Para os casos em que há regressões, force o plano anteriormente conhecido como bom no Repositório de Consultas.
b. Se houver planos de consulta que não forçam ou se o desempenho ainda for insuficiente, considere reverter o nível de compatibilidade do banco de dados para a configuração anterior e, em seguida, contratar o Suporte ao Cliente da Microsoft.
Tip
Use a tarefa SQL Server Management Studio Atualizar Banco de Dados para atualizar o nível de compatibilidade do banco de dados do banco de dados. Consulte Atualizando bancos de dados usando o Assistente de Ajuste de Consulta para obter detalhes.
Identificar e melhorar cargas de trabalho ad hoc
Algumas cargas de trabalho não têm consultas dominantes que você possa ajustar para melhorar o desempenho geral do aplicativo. Essas cargas de trabalho são normalmente caracterizadas com um número relativamente grande de consultas diferentes, cada uma delas consumindo parte dos recursos do sistema. Sendo únicas, essas consultas são executadas muito raramente (geralmente apenas uma vez, portanto, nome ad hoc), portanto, seu consumo de tempo de execução não é crítico. Por outro lado, dado que o aplicativo está gerando novas consultas líquidas o tempo todo, uma parte significativa dos recursos do sistema é gasta na compilação de consultas, o que não é o ideal. Essa também não é a situação ideal para o Repositório de Consultas, dado que um grande número de consultas e planos inunda o espaço reservado, o que significa que o Repositório de Consultas provavelmente acabará no modo somente leitura muito rapidamente. Se ativaste a Política de Limpeza Baseada em Tamanho (altamente recomendada para manter o Repositório de Consultas sempre operacional), o processo em segundo plano estará a limpar as estruturas do Repositório de Consultas na maioria das vezes, o que também consome recursos significativos do sistema.
A vista Principais Consultas que Consomem Recursos fornece uma primeira indicação da natureza ad hoc da sua carga de trabalho.
Use métrica de de Contagem de Execução para analisar se suas principais consultas são ad hoc (isso requer que você execute o Repositório de Consultas com QUERY_CAPTURE_MODE = ALL). No diagrama acima, pode ver que 90% das suas Consultas que Mais Consomem Recursos são executadas apenas uma vez.
Alternativamente, pode executar o script Transact-SQL para obter o número total de textos de consulta, consultas e planos no sistema e determinar o grau de diferença entre eles comparando query_hash e query_plan_hash:
--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query;
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan;
Este é um resultado potencial que você pode obter em caso de carga de trabalho com consultas ad hoc:
O resultado da consulta mostra que, apesar do grande número de consultas e planos no Repositório de Consultas, seus query_hash e query_plan_hash não são realmente diferentes. Uma proporção entre textos de consulta exclusivos e hashes de consulta exclusivos, que é muito maior do que 1, é uma indicação de que a carga de trabalho é um bom candidato para parametrização, pois a única diferença entre as consultas é a constante literal (parâmetro) fornecida como parte do texto da consulta.
Normalmente, essa situação acontece se seu aplicativo gerar consultas (em vez de invocar procedimentos armazenados ou consultas parametrizadas) ou se depender de estruturas de mapeamento objeto-relacional que geram consultas por padrão.
Se você estiver no controle do código do aplicativo, poderá considerar a reescrita da camada de acesso a dados para utilizar procedimentos armazenados ou consultas parametrizadas. No entanto, essa situação também pode ser significativamente melhorada sem alterações no aplicativo, forçando a parametrização de consulta para todo o banco de dados (todas as consultas) ou para os modelos de consulta individuais com o mesmo query_hash.
A abordagem com modelos de consulta individuais requer a criação de guias de plano:
--Apply plan guide for the selected query template
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'<your query text goes here>',
@stmt OUTPUT,
@params OUTPUT;
EXEC sp_create_plan_guide
N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION (PARAMETERIZATION FORCED)';
Solução com guias de plano é mais precisa, mas requer mais trabalho.
Se todas as suas consultas (ou a maioria delas) forem candidatas à parametrização automática, considere configurar PARAMETERIZATION = FORCED para todo o banco de dados. Para obter mais informações, consulte Diretrizes para o uso de parametrização forçada.
--Apply forced parameterization for entire database
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;
Depois de aplicar qualquer uma dessas etapas, Principais Consultas que Consomem Recursos mostrará uma imagem diferente da sua carga de trabalho.
Em alguns casos, seu aplicativo pode gerar muitas consultas diferentes que não são bons candidatos para parametrização automática. Nesse caso, você verá um grande número de consultas no sistema, mas a proporção entre consultas exclusivas e query_hash exclusivas provavelmente está próxima de 1.
Nesse caso, convém habilitar a opção Otimizar para cargas de trabalho ad hoc servidor para evitar o desperdício de memória cache em consultas que provavelmente não serão executadas novamente. Para impedir a captura dessas consultas no Repositório de Consultas, defina QUERY_CAPTURE_MODE como AUTO.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);