Estatísticas

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureBanco de Dados SQL do Azure Synapse Analyticsno Microsoft Fabric

O Otimizador de Consulta usa estatísticas para criar planos de consulta que melhoram o desempenho da consulta. Para a maioria das consultas, o Otimizador de Consultas já gera as estatísticas necessárias para um plano de consulta de alta qualidade. Em alguns casos, é necessário criar estatísticas extra ou modificar o design da consulta para obter melhores resultados. Este artigo discute conceitos de estatísticas e fornece diretrizes para o uso eficaz de estatísticas de otimização de consultas.

Componentes e conceitos

Estatísticas

As estatísticas para otimização de consultas são BLOBs (objetos binários grandes) que contêm informações estatísticas sobre a distribuição de valores em uma ou mais colunas de uma tabela ou exibição indexada. O Otimizador de Consulta usa essas estatísticas para estimar a cardinalidade, ou o número de linhas, no resultado da consulta. Essas estimativas de cardinalidade permitem que o Otimizador de Consultas crie um plano de consulta de alta qualidade. Por exemplo, dependendo dos seus predicados, o Otimizador de Consultas pode usar estimativas de cardinalidade para escolher o operador de busca de índice em vez do operador de verificação de índice que consome mais recursos, se isso melhorar o desempenho da consulta.

Cada objeto de estatística é criado em uma lista de uma ou mais colunas de tabela e inclui um histograma exibindo a distribuição de valores na primeira coluna. Os objetos de estatísticas em várias colunas também armazenam informações estatísticas sobre a correlação de valores entre as colunas. Estas estatísticas de correlação, ou densidades, são derivadas do número de linhas distintas de valores de coluna.

Histogram

Um histograma mede a frequência de ocorrência para cada valor distinto em um conjunto de dados. O Otimizador de Consultas calcula um histograma dos valores da primeira coluna chave do objeto de estatísticas, selecionando os valores através da amostragem estatística das linhas ou executando uma varredura completa de todas as linhas na tabela ou vista. Se o histograma for criado a partir de um conjunto amostrado de linhas, os totais armazenados para número de linhas e número de valores distintos são estimativas e não precisam ser inteiros.

Note

O SQL Server constrói histogramas apenas para uma única coluna – a primeira coluna do conjunto de colunas-chave do objeto de estatísticas.

Para criar o histograma, o Otimizador de Consulta classifica os valores de coluna, calcula o número de valores que correspondem a cada valor de coluna distinto e, em seguida, agrega os valores de coluna em um máximo de 200 etapas de histograma contíguas. Cada etapa do histograma inclui um intervalo de valores de coluna, seguido por um valor máximo da coluna. O intervalo inclui todos os valores de coluna possíveis entre valores de fronteira, excluindo os próprios valores de limite. O menor dos valores de coluna classificada é o valor de limite superior para a primeira etapa do histograma.

Em mais detalhes, o SQL Server cria o histograma a partir do conjunto classificado de valores de coluna em três etapas:

  • Inicialização do histograma: Na primeira etapa, uma sequência de valores começando no início do conjunto classificado é processada e até 200 valores de range_high_key, equal_rows, range_rows e distinct_range_rows são coletados (range_rows e distinct_range_rows são sempre zero durante esta etapa). O primeiro passo termina quando toda a entrada se esgota, ou quando são encontrados 200 valores.
  • Análise com fusão de buckets: Cada valor adicional da primeira coluna da chave de estatísticas é processado na segunda etapa, pela ordem de ordenação. Cada valor sucessivo é adicionado ao último intervalo ou é criado um novo intervalo no final (esta ordenação é possível porque os valores de entrada estão ordenados). Se for criado um novo intervalo, o processo colapsa um par de intervalos existentes e vizinhos num único intervalo. Este par de intervalos é selecionado para minimizar a perda de informações. Este método usa um algoritmo de diferença máxima para minimizar o número de etapas no histograma enquanto maximiza a diferença entre os valores de limite. O número de passos após o colapso dos intervalos permanecerá em 200 ao longo desta etapa.
  • Consolidação do histograma: na terceira etapa, mais intervalos podem ser fundidos se uma quantidade significativa de informações não for perdida. O número de etapas do histograma pode ser menor do que o número de valores distintos, mesmo para colunas com menos de 200 pontos de limite. Portanto, mesmo que a coluna tenha mais de 200 valores exclusivos, o histograma pode ter menos de 200 etapas. Para uma coluna composta apenas por valores únicos, o histograma consolidado tem um mínimo de três passos.

Note

Se o histograma for construído usando uma amostra em vez de fullscan, os valores de equal_rows, range_rows, distinct_range_rows e average_range_rows são estimativas e, por isso, não precisam de ser inteiros inteiros.

O diagrama a seguir mostra um histograma com seis etapas. A área à esquerda do primeiro valor de limite superior é o primeiro passo.

Diagrama de como um histograma é calculado a partir de valores de coluna amostrados.

Para cada etapa do histograma no exemplo anterior:

  • A linha em negrito representa o valor do limite superior (range_high_key) e o número de ocorrências (equal_rows).

  • A área sólida à esquerda de range_high_key representa o intervalo de valores de coluna e o número médio de vezes que cada valor de coluna ocorre (average_range_rows). O average_range_rows para a primeira etapa do histograma é sempre 0.

  • As linhas pontilhadas representam os valores amostrados usados para estimar o número total de valores distintos no intervalo (distinct_range_rows) e o número total de valores no intervalo (range_rows). O Otimizador de Consulta usa range_rows e distinct_range_rows para calcular average_range_rows e não armazena os valores de amostra.

Vetor de densidade

Densidade é a informação sobre o número de duplicados em uma determinada coluna ou combinação de colunas e é calculada como 1/(número de valores distintos). O Otimizador de Consultas usa densidades para aprimorar as estimativas de cardinalidade para consultas que retornam várias colunas da mesma tabela ou exibição indexada. À medida que a densidade diminui, a seletividade de um valor aumenta. Por exemplo, numa tabela que representa automóveis, muitos automóveis têm o mesmo fabricante, mas cada automóvel tem um número de identificação de veículo único (VIN). Um índice no VIN é mais seletivo do que um índice no fabricante, porque o VIN tem densidade menor do que o fabricante.

Note

Frequência é uma informação sobre a ocorrência de cada valor distinto na primeira coluna chave do objeto statistics e é calculada como row count * density. Uma frequência máxima de 1 pode ser encontrada em colunas com valores exclusivos.

O vetor de densidade contém uma densidade para cada prefixo de colunas no objeto de estatísticas. Por exemplo, se um objeto de estatísticas tem as colunas-chave CustomerId, ItemId, e Price, a densidade é calculada em cada um dos prefixos das colunas seguintes.

Prefixo da coluna Densidade calculada com base em
(CustomerId) Linhas com valores correspondentes para CustomerId
(CustomerId, ItemId) Linhas com valores correspondentes para CustomerId e ItemId
(CustomerId, ItemId, Price) Linhas com valores correspondentes para CustomerId, ItemIde Price

Estatísticas filtradas

As estatísticas filtradas podem melhorar o desempenho da consulta para consultas selecionadas a partir de subconjuntos de dados bem definidos. As estatísticas filtradas usam um predicado de filtro para selecionar o subconjunto de dados incluído nas estatísticas. Estatísticas filtradas bem projetadas podem melhorar o plano de execução da consulta em comparação com estatísticas de tabela completa. Para mais informações sobre o predicado do filtro, veja CREATE STATISTICS. Para obter mais informações sobre quando criar estatísticas filtradas, consulte a seção Quando criar estatísticas neste artigo.

Opções de estatísticas

Pode configurar opções que afetam quando e como o sistema cria e atualiza estatísticas. Pode definir estas opções apenas ao nível da base de dados.

AUTO_CREATE_STATISTICS opção

Quando ativa a opção de criação automática de estatísticas, AUTO_CREATE_STATISTICS, o Otimizador de Consultas cria estatísticas em colunas individuais no predicado da consulta, conforme necessário, para melhorar as estimativas de cardinalidade do plano de consulta. Essas estatísticas de coluna única são criadas em colunas que ainda não têm um histograma em um objeto de estatística existente. A AUTO_CREATE_STATISTICS opção não determina se a base de dados cria estatísticas para índices. Esta opção também não gera estatísticas filtradas. Aplica-se estritamente às estatísticas de coluna única para o quadro completo.

Quando o Otimizador de Consulta cria estatísticas como resultado do uso da AUTO_CREATE_STATISTICS opção, o nome das estatísticas começa com _WA. Pode usar a seguinte consulta para determinar se o Otimizador de Consultas criou estatísticas para uma coluna de predicado de consulta.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc
        ON s.stats_id = sc.stats_id
        AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;

opção AUTO_UPDATE_STATISTICS

Quando ativa a opção de atualização automática de estatísticas, AUTO_UPDATE_STATISTICS, o Otimizador de Consultas determina quando as estatísticas podem estar desatualizadas e atualiza-as quando uma consulta as utiliza. Esta ação também é conhecida como recompilação de estatísticas. As estatísticas ficam desatualizadas depois que modificações de operações de inserção, atualização, exclusão ou mesclagem alteram a distribuição de dados na tabela ou exibição indexada. O Otimizador de Consultas conta o número de modificações por linha desde a última atualização estatística e compara esse número com um limiar para determinar se as estatísticas podem estar desatualizadas. O limiar baseia-se na cardinalidade da tabela, que é o número de linhas da tabela ou da vista indexada.

Marcar estatísticas como desatualizadas com base em modificações de linha acontece mesmo quando a AUTO_UPDATE_STATISTICS opção é OFF. Quando a AUTO_UPDATE_STATISTICS opção é OFF, o sistema não atualiza as estatísticas, mesmo quando as marca como desatualizadas. Os planos continuam a utilizar os objetos de estatísticas obsoletos. Definir AUTO_UPDATE_STATISTICS para OFF pode causar planos de consulta subótimos e degradação do desempenho da consulta. Defina a AUTO_UPDATE STATISTICS opção para ON.

  • Até o SQL Server 2014 (12.x), o Mecanismo de Banco de Dados usa um limite de recompilação com base no número de linhas na tabela ou no modo de exibição indexado no momento em que as estatísticas foram avaliadas. O limiar varia dependendo se uma tabela é temporária ou permanente.

    Tipo de tabela Cardinalidade da tabela (n) Limiar de recompilação (# modificações)
    Temporary n< 6 6
    Temporary <6 = n<= 500 500
    Permanent n<= 500 500
    Temporário ou permanente n> 500 500 + (0,20 * n)

    Por exemplo, se a sua tabela contiver 20.000 linhas, o cálculo é 500 + (0.2 * 20,000) = 4,500 e as estatísticas são atualizados a cada 4.500 modificações.

  • A partir do SQL Server 2016 (13.x) e do nível de compatibilidade da base de dados 130, o Database Engine utiliza um limiar decrescente e dinâmico de recompilação estatística que se ajusta de acordo com a cardinalidade da tabela no momento em que as estatísticas foram avaliadas. Com esta alteração, as estatísticas em grandes tabelas são atualizadas com maior frequência. No entanto, se uma base de dados tiver um nível de compatibilidade inferior a 130, aplicam-se os limiares do SQL Server 2014 (12.x).

    Tipo de tabela Cardinalidade da tabela (n) Limiar de recompilação (# modificações)
    Temporary n < 6 6
    Temporary 6 <= n <= 500 500
    Permanent n <= 500 500
    Temporário ou permanente n > 500 MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

    Por exemplo, se a sua tabela contém 2 milhões de linhas, o cálculo é o mínimo de 500 + (0.20 * 2,000,000) = 400,500 e SQRT(1,000 * 2,000,000) = 44,721. Isso significa que as estatísticas são atualizadas a cada 44.721 modificações.

Important

No SQL Server 2008 R2 (10.50.x) até o SQL Server 2014 (12.x) ou no SQL Server 2016 (13.x) e versões posteriores com nível de compatibilidade de banco de dados 120 e versões inferiores, habilite o sinalizador de rastreamento 2371 para que o SQL Server use um limite de atualização de estatísticas dinâmicas decrescente.

Embora recomendado para todos os cenários, habilitar o sinalizador de rastreamento 2371 é opcional. No entanto, você pode usar as seguintes diretrizes para habilitar o sinalizador de rastreamento 2371 em seu ambiente anterior ao SQL Server 2016 (13.x):

  • Se você estiver em um sistema SAP, habilite esse rastreamento. Para obter mais informações, consulte este blog sobre o sinalizador de rastreamento 2371.
  • Se tiver de depender de uma tarefa noturna para atualizar as estatísticas porque a atualização automática atual não é acionada com frequência suficiente, considere ativar a trace flag 2371 para ajustar o limiar em função da cardinalidade da tabela.

O Otimizador de Consultas verifica se há estatísticas desatualizadas antes de compilar uma consulta e antes de executar um plano de consulta em cache. Antes de compilar uma consulta, o Otimizador de Consulta usa as colunas, tabelas e exibições indexadas no predicado de consulta para determinar quais estatísticas podem estar desatualizadas. Antes de executar um plano de consulta em cache, o Mecanismo de Banco de Dados verifica se o plano de consulta faz referência às estatísticas de data up-to.

A AUTO_UPDATE_STATISTICS opção aplica-se a objetos estatísticos criados para índices, colunas únicas em predicados de consulta e estatísticas criadas com a CREATE STATISTICS instrução. Esta opção também se aplica a estatísticas filtradas.

Podes usar o sys.dm_db_stats_properties para acompanhar com precisão o número de linhas alteradas numa tabela e decidir se queres atualizar as estatísticas manualmente.

AUTO_UPDATE_STATISTICS é sempre OFF para tabelas otimizadas para memória.

AUTO_UPDATE_STATISTICS_ASYNC

A opção de atualização de estatísticas assíncronas, AUTO_UPDATE_STATISTICS_ASYNC, determina se o Otimizador de Consultas usa atualizações de estatísticas síncronas ou assíncronas. Por defeito, a opção de atualização de estatísticas assíncronas é OFF, e o Otimizador de Consultas atualiza as estatísticas de forma síncrona. A AUTO_UPDATE_STATISTICS_ASYNC opção aplica-se a objetos estatísticos criados para índices, colunas individuais em predicados de consulta e estatísticas criadas com a CREATE STATISTICS instrução.

Note

Para definir a opção de atualização de estatísticas assíncronas no SQL Server Management Studio, na página de Opções da janela de Propriedades da Base de Dados, defina tanto as Estatísticas de Atualização Automática como as Estatísticas de Atualização Automática para Assíncronascomo Verdadeiras.

As atualizações de estatísticas podem ser síncronas (padrão) ou assíncronas.

  • Com atualizações síncronas de estatísticas, as consultas sempre compilam e executam com estatísticas com a data de referência up-to. Quando as estatísticas estão desatualizadas, o Otimizador de Consultas aguarda as estatísticas atualizadas antes de compilar e executar a consulta.

  • Com atualizações assíncronas de estatísticas, as consultas são compiladas com estatísticas existentes, mesmo que as estatísticas existentes estejam desatualizadas. O Otimizador de Consulta pode escolher um plano de consulta subótimo se as estatísticas estiverem desatualizadas quando a consulta for compilada. Normalmente, as estatísticas são atualizadas pouco tempo depois. As consultas que são compiladas após a conclusão das atualizações de estatísticas se beneficiam do uso das estatísticas atualizadas.

Considere o uso de estatísticas síncronas ao executar operações que alteram a distribuição de dados, como truncar uma tabela ou executar uma atualização em massa de uma grande porcentagem das linhas. Se não atualizar manualmente as estatísticas depois de concluir a operação, a utilização de estatísticas síncronas garante que as estatísticas estão atualizadas antes de as consultas precisarem delas.

Considere o uso de estatísticas assíncronas para obter tempos de resposta de consulta mais previsíveis para os seguintes cenários:

  • Seu aplicativo frequentemente executa a mesma consulta, consultas semelhantes ou planos de consulta em cache semelhantes. Os tempos de resposta da consulta podem ser mais previsíveis com atualizações de estatísticas assíncronas do que com atualizações de estatísticas síncronas, porque o Otimizador de Consultas pode executar consultas de entrada sem esperar por estatísticas de data up-to. Isso evita atrasar algumas consultas e não outras.

  • A sua aplicação sofreu timeouts de pedidos de clientes causados por uma ou mais consultas à espera de estatísticas atualizadas. Em alguns casos, aguardar por estatísticas síncronas pode levar aplicações com timeouts agressivos a falhar.

Note

As estatísticas nas tabelas temporárias locais são sempre atualizadas de forma síncrona, independentemente da AUTO_UPDATE_STATISTICS_ASYNC opção. As estatísticas em tabelas temporárias globais são atualizadas de forma síncrona ou assíncrona de acordo com o AUTO_UPDATE_STATISTICS_ASYNC conjunto de opções para a base de dados do utilizador.

A atualização assíncrona de estatísticas é realizada por uma solicitação em segundo plano. Quando a solicitação está pronta para gravar estatísticas atualizadas no banco de dados, ela tenta adquirir um bloqueio de modificação de esquema no objeto de metadados de estatísticas. Se uma sessão diferente já estiver mantendo um bloqueio no mesmo objeto, a atualização assíncrona de estatísticas será bloqueada até que o bloqueio de modificação de esquema possa ser adquirido. Da mesma forma, as sessões que precisam adquirir um bloqueio de estabilidade de esquema (Sch-S) no objeto de metadados de estatísticas para compilar uma consulta podem ser bloqueadas pela sessão em segundo plano de atualização de estatísticas assíncrona, que já está segurando ou aguardando para adquirir o bloqueio de modificação de esquema. Portanto, para cargas de trabalho com compilações de consulta muito frequentes e frequentes atualizações de estatísticas, o uso de estatísticas assíncronas pode aumentar a probabilidade de problemas de concorrência devido a bloqueios.

No Base de Dados SQL do Azure, Azure SQL Managed Instance, e a partir do SQL Server 2022 (16.x), pode evitar potenciais problemas de concorrência usando atualizações estatísticas assíncronas se ativar a ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYconfiguração com escopo de base de dados. Com essa configuração habilitada, a solicitação em segundo plano aguarda para adquirir o bloqueio de modificação de esquema (Sch-M) e persiste as estatísticas atualizadas em uma fila de baixa prioridade separada, permitindo que outras solicitações continuem compilando consultas com estatísticas existentes. Quando nenhuma outra sessão estiver mantendo um bloqueio no objeto de metadados de estatísticas, a solicitação em segundo plano adquire o seu bloqueio de modificação de esquema e atualiza as estatísticas. No improvável caso de o pedido em segundo plano não conseguir adquirir o bloqueio dentro de um período de timeout de vários minutos, a atualização assíncrona das estatísticas é abortada, e as estatísticas não são atualizadas até que outra atualização automática seja acionada, ou até que as estatísticas sejam atualizadas manualmente.

Note

A ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY opção de configuração com âmbito de base de dados está disponível no Base de Dados SQL do Azure, Azure SQL Managed Instance e no SQL Server a partir do SQL Server 2022 (16.x).

opção AUTO_DROP

Aplica-se a: Base de Dados SQL do Azure, Instância Gerida do SQL do Azure e a começar pelo SQL Server 2022 (16.x)

No SQL Server antes do SQL Server 2022 (16.x), se criar manualmente estatísticas ou usar uma ferramenta de terceiros numa base de dados de utilizador, esses objetos de estatísticas podem bloquear ou interferir com alterações de esquema.

A partir do SQL Server 2022 (16.x), a opção de soltar automaticamente é habilitada por padrão em todos os bancos de dados novos e migrados. Se ativar a AUTO_DROP propriedade, a base de dados cria objetos de estatísticas de forma que uma alteração subsequente no esquema não seja bloqueada pelo objeto de estatística, mas sim que as estatísticas sejam descartadas conforme necessário. Desta forma, as estatísticas criadas manualmente com a queda automática ativada comportam-se como estatísticas criadas automaticamente.

Em versões Base de Dados SQL do Azure, Azure SQL Managed Instance e SQL Server 2022 (16.x) e posteriores, as estatísticas criadas automaticamente comportam-se sempre como se a AUTO_DROP estivesse ativada.

Note

Tentar definir ou desdefinir a propriedade de descarte automático em estatísticas criadas automaticamente pode gerar erros. As estatísticas criadas automaticamente usam sempre a eliminação automática. Alguns backups, quando restaurados, podem ter essa propriedade definida incorretamente até a próxima vez que o objeto statistics for atualizado (manual ou automaticamente). No entanto, as estatísticas criadas automaticamente comportam-se sempre como estatísticas eliminadas automaticamente. Ao restaurar um banco de dados para o SQL Server 2022 (16.x) de uma versão anterior, é recomendável executar sp_updatestats no banco de dados, definindo os metadados adequados para o recurso de descarte automático de estatísticas.

Por exemplo, para criar manualmente um objeto de estatísticas na dbo.DatabaseLog tabela:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Por exemplo, para atualizar a configuração de descarte automático de um objeto de estatísticas na tabela dbo.DatabaseLog:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Para avaliar a configuração de auto drop em estatísticas existentes, use a coluna auto_drop em sys.stats.

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Para obter mais informações, consulte AUTO_DROP.

INCREMENTAL

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores.

Quando defines a opção INCREMENTAL de CREATE STATISTICS como ON, crias estatísticas por partição. Quando defines para OFF, a base de dados elimina a árvore estatística e recalcula as estatísticas. A predefinição é OFF. Esta definição substitui a propriedade INCREMENTAL ao nível da base de dados.

Quando adicionas novas partições a uma tabela grande, deves atualizar as estatísticas para incluir as novas partições. No entanto, o tempo necessário para analisar toda a tabela (opções FULLSCAN ou SAMPLE) pode demorar. Além disso, a verificação de toda a tabela não é necessária, pois apenas as estatísticas sobre as novas partições podem ser necessárias. A opção incremental cria e armazena estatísticas por partição e, quando atualizada, só atualiza estatísticas nas partições que necessitam de novas estatísticas.

Se as estatísticas por partição não forem suportadas, a base de dados ignora a opção e gera um aviso. Estatísticas incrementais não são suportadas para os seguintes tipos de estatísticas:

  • Estatísticas criadas com índices que não estão alinhados por partições com a tabela de base.
  • Estatísticas criadas nas bases de dados secundárias legíveis do Always On.
  • Estatísticas criadas em bases de dados só de leitura.
  • Estatísticas criadas em índices filtrados.
  • Estatísticas criadas em visualizações.
  • Estatísticas criadas em tabelas internas.
  • Estatísticas criadas com índices espaciais ou índices XML.

Quando criar estatísticas

O Otimizador de Consultas já cria estatísticas das seguintes maneiras:

  1. Quando cria um índice em tabelas ou vistas, o Query Optimizer cria estatísticas para índices. Essas estatísticas são criadas nas colunas-chave do índice. Se o índice for um índice filtrado, o Otimizador de Consulta criará estatísticas filtradas no mesmo subconjunto de linhas especificado para o índice filtrado. Para mais informações sobre índices filtrados, veja Criar índices filtrados e CREATE INDEX.

    Note

    No SQL Server 2014 (12.x) e versões posteriores, a base de dados não cria estatísticas ao analisar todas as linhas da tabela quando cria ou reconstrói um índice particionado. Em vez disso, o Otimizador de Consulta usa o algoritmo de amostragem padrão para gerar estatísticas. Depois de atualizar um banco de dados com índices particionados, você pode notar uma diferença nos dados de histograma para esses índices. Essa alteração no comportamento pode não afetar o desempenho da consulta. Para obter estatísticas sobre índices particionados ao analisar todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a FULLSCAN cláusula.

  2. O Otimizador de Consulta cria estatísticas para colunas únicas em predicados de consulta quando AUTO_CREATE_STATISTICS está ativado.

Para a maioria das consultas, estes dois métodos de criação de estatísticas garantem um plano de consulta de alta qualidade. Em alguns casos, pode melhorar os planos de consulta criando estatísticas adicionais ao utilizar a instrução CREATE STATISTICS. Essas estatísticas adicionais podem capturar correlações estatísticas que o Otimizador de Consultas não leva em conta quando cria estatísticas para índices ou colunas únicas. Seu aplicativo pode ter correlações estatísticas adicionais nos dados da tabela que, se calculadas em um objeto de estatística, podem permitir que o Otimizador de Consultas melhore os planos de consulta. Por exemplo, estatísticas filtradas em um subconjunto de linhas de dados ou estatísticas de várias colunas em colunas de predicados de consulta podem melhorar o plano de consulta.

Quando cria estatísticas usando a CREATE STATISTICS instrução, mantenha a AUTO_CREATE_STATISTICS opção ON para que o Otimizador de Consultas continue a criar rotineiramente estatísticas de coluna única para colunas de predicados de consulta. Para obter mais informações sobre predicados de consulta, consulte Condição de pesquisa.

Considere criar estatísticas usando a CREATE STATISTICS afirmação quando se aplicar qualquer uma das seguintes condições:

  • O Orientador de Otimização do Mecanismo de Banco de Dados sugere a criação de estatísticas.
  • O predicado de consulta contém várias colunas correlacionadas que ainda não são chaves no mesmo índice.
  • A consulta seleciona a partir de um subconjunto de dados.
  • A consulta tem estatísticas ausentes.

Note

Para obter informações específicas sobre tabelas e estatísticas relacionadas ao In-Memory OLTP, consulte Estatísticas para tabelas Memory-Optimized.

O predicado de consulta contém múltiplas colunas correlacionadas

Quando um predicado de consulta contém várias colunas com relações e dependências entre colunas, as estatísticas sobre as várias colunas podem melhorar o plano de consulta. As estatísticas em várias colunas contêm estatísticas de correlação entre colunas, chamadas densidades, que não estão disponíveis em estatísticas de coluna única. As densidades podem melhorar as estimativas de cardinalidade quando os resultados da consulta dependem de relações de dados entre várias colunas.

Se as colunas já estiverem no mesmo índice, o objeto de estatísticas multicolunas já existe e não precisas de o criar manualmente. Se as colunas ainda não estiverem no mesmo índice, pode criar estatísticas multicolunas criando um índice nas colunas ou usando a CREATE STATISTICS instrução. Ele requer mais recursos do sistema para manter um índice do que um objeto de estatística. Se o aplicativo não exigir o índice de várias colunas, você poderá economizar recursos do sistema criando o objeto statistics sem criar o índice.

Quando criamos estatísticas multicolunas, a ordem das colunas na definição do objeto de estatísticas afeta a eficácia das densidades na realização de estimativas de cardinalidade. O objeto de estatísticas armazena densidades para cada prefixo das colunas-chave na definição do objeto de estatísticas. Para mais informações sobre densidades, consulte a secção Densidade neste artigo.

Para criar densidades úteis para estimativas de cardinalidade, as colunas no predicado de consulta devem corresponder a um dos prefixos de colunas na definição do objeto de estatística. Por exemplo, o exemplo a seguir cria um objeto de estatísticas com várias colunas nas colunas LastName, MiddleName, e FirstName.

USE AdventureWorks2022;
GO

IF EXISTS (SELECT name
           FROM sys.stats
           WHERE name = 'LastFirst'
                 AND object_ID = OBJECT_ID('Person.Person'))
    DROP STATISTICS Person.Person.LastFirst;
GO

CREATE STATISTICS LastFirst
    ON Person.Person(LastName, MiddleName, FirstName);
GO

Neste exemplo, o objeto de estatísticas LastFirst tem densidades para os seguintes prefixos de coluna: (LastName), (LastName, MiddleName), e (LastName, MiddleName, FirstName). A densidade não está disponível para (LastName, FirstName). Se a consulta usar LastName e FirstName sem usar MiddleName, a densidade não estará disponível para estimativas de cardinalidade.

A consulta seleciona a partir de um subconjunto de dados

Quando o Otimizador de Consulta cria estatísticas para colunas e índices únicos, ele cria as estatísticas para os valores em todas as linhas. Quando as consultas são selecionadas a partir de um subconjunto de linhas e esse subconjunto de linhas tem uma distribuição de dados exclusiva, as estatísticas filtradas podem melhorar os planos de consulta. Pode criar estatísticas filtradas usando a CREATE STATISTICS instrução com a cláusula WHERE para definir a expressão do predicado do filtro.

Por exemplo, usando o AdventureWorks2025, cada produto na Production.Product tabela pertence a uma das quatro categorias da Production.ProductCategory tabela: Bikes, Components, Clothing, e Accessories. Cada uma das categorias tem uma distribuição de dados diferente para o peso: os pesos das bicicletas variam de 13,77 a 30,0, os pesos dos componentes variam de 2,12 a 1050,00 com alguns valores NULL, os pesos das roupas são todos NULL e os pesos dos acessórios são também NULL.

Usando Bikes como exemplo, estatísticas filtradas sobre todos os pesos de bicicletas fornecem dados mais precisos para o Optimizador de Consultas e podem melhorar a qualidade do plano de consulta em comparação com as estatísticas de tabela completa ou estatísticas inexistentes na coluna Peso. A coluna de peso da bicicleta é um bom candidato para estatísticas filtradas, mas não necessariamente um bom candidato para um índice filtrado se o número de pesquisas de peso for relativamente pequeno. O ganho de desempenho para pesquisas que um índice filtrado fornece pode não compensar o custo adicional de manutenção e armazenamento para adicionar um índice filtrado ao banco de dados.

A instrução a seguir cria as BikeWeights estatísticas filtradas em todas as subcategorias do Bikes. A expressão de predicados filtrados define bicicletas enumerando todas as subcategorias de bicicletas com a comparação Production.ProductSubcategoryID IN (1,2,3). O predicado não pode usar o nome da Bikes categoria porque ele é armazenado na Production.ProductCategory tabela e todas as colunas na expressão de filtro devem estar na mesma tabela.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

O Otimizador de Consulta pode usar as BikeWeights estatísticas filtradas para melhorar o plano de consulta para a consulta a seguir que seleciona todas as bicicletas que pesam mais de 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER JOIN Production.ProductSubcategory AS S
         ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
      AND P.Weight > 25
ORDER BY P.Weight;
GO

Consulta identifica estatísticas ausentes

Se um erro ou outro evento impedir que o Otimizador de Consulta crie estatísticas, o Otimizador de Consulta criará o plano de consulta sem usar estatísticas. O Otimizador de Consulta marca as estatísticas como ausentes e tenta regenerá-las na próxima vez que a consulta for executada.

As estatísticas ausentes são indicadas como avisos (nome da tabela em texto vermelho) quando o plano de execução de uma consulta é exibido graficamente usando o SQL Server Management Studio. Além disso, monitorar a classe de evento Estatísticas de Coluna Ausente usando o SQL Server Profiler indica quando as estatísticas estão ausentes. Para obter mais informações, consulte Categoria de evento de erros e avisos (Mecanismo de Banco de Dados).

Se faltarem estatísticas, execute as seguintes etapas:

Estatísticas temporárias

Quando as estatísticas numa base de dados de leitura apenas ou cópia instantânea de leitura apenas estão ausentes ou obsoletas, o Mecanismo de Base de Dados cria e mantém estatísticas temporárias no tempdb. Quando o Mecanismo de Banco de Dados cria estatísticas temporárias, o nome das estatísticas é acrescentado com o sufixo _readonly_database_statistic para diferenciar as estatísticas temporárias das estatísticas permanentes. O sufixo _readonly_database_statistic é reservado para estatísticas geradas pelo Motor de Base de Dados. Scripts para as estatísticas temporárias podem ser criados e executados numa base de dados de leitura-escrita. Quando é gerado um script, o Management Studio modifica o sufixo do nome da estatística de _readonly_database_statistic para _readonly_database_statistic_scripted.

Só o Motor de Base de Dados pode criar e atualizar estatísticas temporárias. No entanto, você pode excluir estatísticas temporárias e monitorar propriedades de estatísticas usando as mesmas ferramentas que você usa para estatísticas permanentes:

  • Apague estatísticas temporárias usando a DROP STATISTICS instrução.
  • Monitore estatísticas usando sys.stats e sys.stats_columns exibições de catálogo. A sys.stats visualização do catálogo do sistema inclui a is_temporary coluna, para indicar quais estatísticas são permanentes e quais são temporárias.

Como as estatísticas temporárias são armazenadas em tempdb, um reinício do Motor de Base de Dados remove todas as estatísticas temporárias.

Tal como em todas as estatísticas, criar e atualizar estatísticas temporárias requer um bloqueio de modificação de esquema (Sch-M) no objeto. Este bloqueio pode bloquear outras consultas e processos, incluindo o processo de reformulação do sistema em réplicas secundárias que aplica transações da réplica primária. Se este bloqueio afetar cargas de trabalho de consulta ou propagação de dados, pode desativar a criação e atualização automática de estatísticas temporárias usando as READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATEREADABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE de dados, respetivamente.

Quando atualizar estatísticas

O Otimizador de Consultas determina quando as estatísticas podem estar desatualizadas e atualiza-as quando necessário para um plano de consulta. Em alguns casos, pode melhorar o plano de consulta e, assim, melhorar o desempenho das consultas atualizando estatísticas com mais frequência do que quando AUTO_UPDATE_STATISTICS é ON. Pode atualizar estatísticas usando a UPDATE STATISTICS instrução ou o procedimento sp_updatestatsarmazenado .

A atualização das estatísticas garante que as consultas sejam compiladas com estatísticas de up-todata. Atualizar estatísticas através de qualquer processo pode fazer com que os planos de consulta sejam recompilados automaticamente. Não atualize manualmente as estatísticas com demasiada frequência, porque existe uma compensação em termos de desempenho entre a melhoria dos planos de consulta e o tempo necessário para recompilar as consultas. As compensações específicas dependem da sua aplicação.

Quando atualiza estatísticas usando UPDATE STATISTICS ou sp_updatestats, mantenha AUTO_UPDATE_STATISTICS definido para ON que o Otimizador de Consultas atualize rotineiramente as estatísticas.

  • Para mais informações sobre como atualizar estatísticas numa coluna, num índice, numa tabela ou numa vista indexada, consulte UPDATE STATISTICS.

  • Para obter informações sobre como atualizar estatísticas para todas as tabelas internas e definidas pelo usuário no banco de dados, consulte o procedimento armazenado sp_updatestats.

  • Para obter mais informações sobre os limites para atualizações automáticas de estatísticas, consulte AUTO_UPDATE_STATISTICS opção.

Quando defines AUTO_UPDATE_STATISTICS para OFF, a recompilação de planos ainda pode ocorrer por várias outras razões, mas não ocorre automaticamente devido a atualizações estatísticas desatualizadas. Quando definir AUTO_UPDATE_STATISTICS para OFF, as atualizações estatísticas só ocorrem através de outros processos agendados manualmente, como planos de manutenção. Definir AUTO_UPDATE_STATISTICS para OFF pode, portanto, causar planos de consulta subótimos e desempenho de consulta degradado.

Detetar estatísticas desatualizadas

Para determinar quando as estatísticas foram atualizadas pela última vez, use as funções sys.dm_db_stats_properties ou STATS_DATE .

Considere atualizar as estatísticas para as seguintes condições:

  • Os tempos de execução da consulta são lentos.
  • As operações de inserção ocorrem em colunas de chave ascendente ou descendente.
  • Após operações de manutenção.

Para exemplos de atualização manual de estatísticas, veja UPDATE STATISTICS.

Os tempos de execução da consulta são lentos

Se os tempos de resposta da consulta forem lentos ou imprevisíveis, certifique-se de que as consultas tenham estatísticas de data up-toantes de executar etapas adicionais de solução de problemas.

As operações de inserção ocorrem em colunas de chave ascendente ou decrescente

As estatísticas sobre colunas de chave ascendentes ou descendentes, como IDENTITY ou colunas de carimbo de data/hora em tempo real, podem exigir atualizações das estatísticas mais frequentes do que as que o Otimizador de Consultas efetua. As operações de inserção adicionam novos valores a colunas organizadas em ordem ascendente ou descendente. O número de linhas adicionadas pode ser muito pequeno para acionar uma atualização estatística. Se as estatísticas não estiverem up-to-date e as consultas selecionarem entre as linhas adicionadas mais recentemente, as estatísticas atuais não terão estimativas de cardinalidade para esses novos valores. Esta condição pode resultar em estimativas de cardinalidade imprecisas e desempenho lento da consulta.

Por exemplo, uma consulta que seleciona entre as datas mais recentes das encomendas de venda tem estimativas de cardinalidade imprecisas se as estatísticas não forem atualizadas para incluir estimativas de cardinalidade para as datas mais recentes das encomendas de venda.

Após operações de manutenção

Considere atualizar as estatísticas depois de executar procedimentos de manutenção que alteram a distribuição de dados, como truncar uma tabela ou executar uma inserção em massa de uma grande porcentagem das linhas. Atualizar proativamente as estatísticas pode evitar futuros atrasos no processamento das consultas enquanto as consultas aguardam atualizações automáticas de estatísticas.

Operações como reconstruir, desfragmentar ou reorganizar um índice não alteram a distribuição dos dados. Portanto, não é necessário atualizar as estatísticas após efetuar as operações ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG ou ALTER INDEX REORGANIZE. O Otimizador de Consultas atualiza as estatísticas quando você reconstrói um índice em uma tabela ou exibição com ALTER INDEX REBUILD ou DBCC DBREINDEX, no entanto, essa atualização de estatísticas é um subproduto da recriação do índice. O otimizador de consultas não atualiza estatísticas após as operações DBCC INDEXDEFRAG ou ALTER INDEX REORGANIZE.

Tip

A partir do SQL Server 2016 (13.x) SP1 CU4, utilize a opção PERSIST_SAMPLE_PERCENT de CREATE STATISTICS ou UPDATE STATISTICS para definir e manter uma percentagem de amostragem específica para atualizações subsequentes das estatísticas que não especifiquem explicitamente uma percentagem de amostragem.

Gestão automática de índices e estatísticas

Use soluções inteligentes, como o Adaptive Index Defrag , para gerenciar automaticamente a desfragmentação de índices e atualizações de estatísticas para um ou mais bancos de dados. Este procedimento escolhe automaticamente se reconstrói ou reorganiza um índice de acordo com o seu nível de fragmentação, entre outros parâmetros, e atualiza as estatísticas com um limiar linear.

Determinar quais as estatísticas que o Otimizador de Consultas utilizou

Pode encontrar os objetos estatísticos que o Otimizador de Consultas utiliza quando compila uma consulta, inspecionando um plano de execução estimado ou real. Quando inspeciona um plano de execução, o OptimizerStatsUage elemento contém StatisticsInfo elementos que contêm informação sobre os objetos de estatísticas que o Query Optimizer carregou durante a compilação. Os StatisticsInfo elementos contêm o nome do objeto de estatísticas, a base de dados, o esquema e a tabela a que pertencem, a contagem de modificações no momento da compilação, a percentagem de amostragem e a data da última atualização.

Utilize qualquer uma das seguintes técnicas para inspecionar o plano de execução:

  • No SQL Server Management Studio, selecione Incluir Plano de Execução Real (Ctrl+M) antes de executar a consulta. No separador Plano de Execução que aparece com os resultados, pode:
    • Clique com o botão direito dentro do plano gráfico e selecione Mostrar Plano de Execução XML. Procure o elemento OptimizerStatsUsage e cada elemento filho StatisticsInfo.
    • Selecione o operador final (mais à esquerda). (No caso de uma consulta SELECT, este operador é um nó SELECT.) Na janela Propriedades, expanda o nó OptimizerStatsUsage e veja as informações sobre os objetos de estatísticas utilizados na consulta.
  • Executa SETSET STATISTICS XML ON antes da consulta. Selecione o hiperlink que aparece com os resultados para visualizar o plano de execução em XML.
  • Consulta sys.dm_exec_query_plan ou sys.dm_exec_query_statistics_xml para consultas recentes.
  • Leia um plano previamente capturado a partir do Query Store com sys.query_store_plan.

Cada StatisticsInfo elemento assemelha-se ao seguinte fragmento XML de uma consulta na base de AdventureWorks2022 dados de exemplo:

<StatisticsInfo 
    Database="[AdventureWorks2022]" 
    Schema="[Sales]" 
    Table="[SalesOrderDetail]" 
    Statistics="[IX_SalesOrderDetail_ProductID]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2025-09-07T15:32:16.89" />
Attribute Meaning
Database, Schema, Table O objeto a que pertence a estatística.
Statistics Nome do objeto de estatísticas na base de dados. Utilize este nome com DBCC SHOW_STATISTICS ou sys.stats para inspecionar o histograma e o vetor de densidade.
ModificationCount Número de modificações de dados desde a última atualização da estatística, na altura em que o plano foi compilado. Um valor elevado em relação ao tamanho da tabela indica que a estatística estava obsoleta durante a compilação.
SamplingPercent Percentagem de linhas amostradas para construir a estatística. Valores mais baixos podem produzir histogramas menos precisos para dados enviesados.
LastUpdate Carimbo temporal da última atualização estatística. Se a opção AUTO_UPDATE_STATISTICS estiver ativada na base de dados, esta atualiza automaticamente as estatísticas quando necessário.

Note

StatisticsInfo reflete estatísticas disponíveis e consideradas durante a elaboração do plano. Se falta uma StatisticsInfo entrada numa coluna onde a sua consulta filtra, o otimizador de consultas não identificou estatísticas relevantes, o que pode ser uma fonte de baixo desempenho.

Para verificar a frescura atual e as contagens de modificações para um objeto estatístico, use sys.dm_db_stats_properties. Por exemplo, a consulta seguinte fornece as métricas atuais para um objeto estatístico nomeado IX_SalesOrderDetail_ProductID na tabela Sales.SalesOrderDetail:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND s.name = N'IX_SalesOrderDetail_ProductID';

Para determinar se as opções automáticas de criação e atualização de estatísticas da base de dados atual estão ativadas, utilize:

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE [name] = DB_NAME();

Consultas que utilizam estatísticas de forma eficaz

Determinadas implementações de consulta, como variáveis locais e expressões complexas no predicado de consulta, podem levar a planos de consulta subótimos. Para evitar estes problemas, siga as diretrizes de desenho de consultas para utilizar estatísticas de forma eficaz. Para obter mais informações sobre predicados de consulta, consulte Condição de pesquisa.

Você pode melhorar os planos de consulta aplicando diretrizes de design de consulta que usam estatísticas de forma eficaz para melhorar as estimativas de cardinalidade para expressões, variáveis e funções usadas em predicados de consulta. Quando o Otimizador de Consulta não sabe o valor de uma expressão, variável ou função, ele não sabe qual valor procurar no histograma e, portanto, não pode recuperar a melhor estimativa de cardinalidade do histograma. Em vez disso, o Otimizador de Consulta baseia a estimativa de cardinalidade no número médio de linhas por valor distinto para todas as linhas amostradas no histograma. Esta situação leva a estimativas de cardinalidade subótimas e pode prejudicar o desempenho das consultas. Para mais informações sobre histogramas, consulte a secção de histogramas neste artigo ou sys.dm_db_stats_histogram.

As diretrizes a seguir descrevem como escrever consultas para melhorar os planos de consulta melhorando as estimativas de cardinalidade.

Melhorar as estimativas de cardinalidade para expressões

Para melhorar as estimativas de cardinalidade para expressões, siga estas diretrizes:

  • Sempre que possível, simplifique expressões que contenham constantes. O Otimizador de Consultas não avalia todas as funções e expressões que contêm constantes antes de determinar estimativas de cardinalidade. Por exemplo, simplifique a expressão ABS(-100) para 100.
  • Se a expressão usar várias variáveis, considere a criação de uma coluna computada para a expressão e, em seguida, crie estatísticas ou um índice na coluna computada. Por exemplo, o predicado WHERE PRICE + Tax > 100 de consulta pode ter uma estimativa de cardinalidade melhor se você criar uma coluna computada para a expressão Price + Tax.

Melhorar as estimativas de cardinalidade para variáveis e funções

Para melhorar as estimativas de cardinalidade para variáveis e funções, siga estas orientações:

  • Se o predicado de consulta usar uma variável local, considere reescrever a consulta para usar um parâmetro em vez de uma variável local. O Otimizador de Consultas não conhece o valor de uma variável local quando cria o plano de execução da consulta. Quando uma consulta utiliza um parâmetro, o Otimizador de Consultas utiliza a estimativa de cardinalidade para o primeiro valor real do parâmetro que o procedimento armazenado recebe.

  • Considere usar uma tabela padrão ou uma tabela temporária para armazenar os resultados de funções com valores de tabela com múltiplas sentenças. O Query Optimizer não cria estatísticas para funções com valores de tabela com múltiplas sentenças. Utilizando esta abordagem, o Otimizador de Consultas pode criar estatísticas nas colunas da tabela e usá-las para criar um melhor plano de consulta.

  • Considere o uso de uma tabela padrão ou temporária como um substituto para variáveis de tabela. O Otimizador de Consultas não cria estatísticas para variáveis de tabela. Utilizando esta abordagem, o Otimizador de Consultas pode criar estatísticas nas colunas da tabela e usá-las para criar um melhor plano de consulta. Há vantagens e desvantagens na decisão entre usar uma tabela temporária ou uma variável de tabela. As variáveis de tabela usadas em procedimentos armazenados causam menos recompilações do procedimento armazenado do que tabelas temporárias. Dependendo do aplicativo, usar uma tabela temporária em vez de uma variável de tabela pode não melhorar o desempenho.

  • Se um procedimento armazenado contiver uma consulta que usa um parâmetro passado, evite alterar o valor do parâmetro dentro do procedimento armazenado antes de usá-lo na consulta. As estimativas de cardinalidade para a consulta são baseadas no valor do parâmetro passado e não no valor atualizado. Para evitar alterar o valor do parâmetro, você pode reescrever a consulta para usar dois procedimentos armazenados.

    Por exemplo, o procedimento Sales.GetRecentSales armazenado a seguir altera o valor do parâmetro @date quando @date é NULL.

    USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

    Se a primeira chamada para o procedimento armazenado Sales.GetRecentSales passar um NULL para o parâmetro @date, o Otimizador de Consulta compilará o procedimento armazenado com base na estimativa de cardinalidade para @date = NULL mesmo que o predicado de consulta não seja chamado com @date = NULL. Esta estimativa de cardinalidade pode ser significativamente diferente do número de linhas no resultado real da consulta. Como resultado, o Otimizador de Consulta pode escolher um plano de consulta subótimo. Para ajudar a evitar este problema, pode reescrever o procedimento armazenado em dois procedimentos, da seguinte forma:

    USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNullRecentSales;
GO

CREATE PROCEDURE Sales.GetNullRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    EXECUTE Sales.GetNonNullRecentSales @date;
END
GO

IF OBJECT_ID('Sales.GetNonNullRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetNonNullRecentSales;
GO

CREATE PROCEDURE Sales.GetNonNullRecentSales
@date DATETIME
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID
        AND h.OrderDate > @date;
END
GO

Melhore as estimativas de cardinalidade com dicas de consulta

Para melhorar as estimativas de cardinalidade para variáveis locais, utilize as sugestões de consulta OPTIMIZE FOR <value> ou OPTIMIZE FOR UNKNOWN com RECOMPILE. Para obter mais informações, consulte Dicas de consulta.

Para alguns aplicativos, recompilar a consulta cada vez que ela é executada pode levar muito tempo. A OPTIMIZE FOR dica de consulta pode ajudar mesmo que você não use a RECOMPILE opção. Por exemplo, você pode adicionar uma OPTIMIZE FOR opção ao procedimento Sales.GetRecentSales armazenado para especificar uma data específica. O exemplo a seguir adiciona a OPTIMIZE FOR opção ao Sales.GetRecentSales procedimento.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Melhore as estimativas de cardinalidade com guias de execução de planos

Para alguns aplicativos, as diretrizes de design de consulta podem não se aplicar porque você não pode alterar a consulta ou a dica RECOMPILE de consulta pode causar muitas recompilações. Utilize guias de plano para especificar outras sugestões, como USE PLAN, para controlar o comportamento da consulta enquanto investiga alterações na aplicação com o fornecedor da aplicação. Para obter mais informações sobre guias de plano, consulte Guias de plano.

No Base de Dados SQL do Azure, considere utilizar sugestões do Query Store para impor planos, em vez de guias de planos. Para obter mais informações, consulte Dicas do Query Store.

Conteúdo relacionado

  • Last updated on