Partilhar via

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, você precisa criar estatísticas extras 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

Os histogramas no SQL Server são criados apenas para uma única coluna - a primeira coluna no conjunto de colunas de 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). A primeira etapa termina quando todas as entradas tiverem sido esgotadas ou quando 200 valores tiverem sido encontrados.
  • Analisar com mesclagem de bucket: Cada valor adicional da coluna principal da chave de estatísticas é processado na segunda etapa, em ordem crescente; cada valor sucessivo é adicionado ao último intervalo ou cria-se um novo intervalo no fim (isso é possível porque os valores de entrada são ordenados). Se um novo intervalo for criado, um par de intervalos vizinhos existentes será fundido 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 que consiste apenas em valores exclusivos, o histograma consolidado tem um mínimo de três etapas.

Note

Se o histograma foi construído usando uma amostra em vez de fullscan, então os valores de equal_rows, range_rows e distinct_range_rows e average_range_rows são estimados e, portanto, não precisam 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 limite superior (range_high_key) e o número de vezes que ocorre (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 utilizados 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 densidade contém uma densidade para cada prefixo de colunas no objeto statistics. Por exemplo, se um objeto de estatística tiver as colunas de chave CustomerId, ItemId e Price, a densidade será calculada em cada um dos seguintes prefixos de coluna.

Prefixo da coluna Densidade calculada 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 obter mais informações sobre o predicado de filtro, consulte 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

Existem opções que afetam quando e como as estatísticas são criadas e atualizadas. Essas opções são configuráveis apenas no nível do banco de dados.

AUTO_CREATE_STATISTICS opção

Quando a opção de criação automática de estatísticas, AUTO_CREATE_STATISTICS está ATIVADA, o Otimizador de Consulta cria estatísticas em colunas individuais no predicado de consulta, conforme necessário, para melhorar as estimativas de cardinalidade para o 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 as estatísticas são criadas 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. Você pode usar a consulta a seguir para determinar se o Otimizador de Consulta criou estatísticas para uma coluna de predicados 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;

AUTO_UPDATE_STATISTICS opção

Quando a opção de atualização automática de estatísticas, AUTO_UPDATE_STATISTICS está ATIVADA, o Otimizador de Consultas determina quando as estatísticas podem estar desatualizadas e, em seguida, atualiza-as quando são usadas por uma consulta. 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 determina quando as estatísticas podem estar desatualizadas contando o número de modificações de linha desde a última atualização de estatísticas e comparando o número de modificações de linha com um limite. O limite é baseado na cardinalidade da tabela, que pode ser definida como o número de linhas na tabela ou no modo de exibição indexado.

A marcação de estatísticas como desatualizadas com base em modificações de linha ocorre mesmo quando a AUTO_UPDATE_STATISTICS opção está OFF. Quando a opção está OFF, as AUTO_UPDATE_STATISTICS estatísticas não são atualizadas, mesmo quando estão marcadas como desatualizadas. Os planos continuam a utilizar os objetos de estatísticas obsoletos. A configuração AUTO_UPDATE_STATISTICS como OFF pode causar planos de consulta abaixo do ideal e desempenho de consulta degradado. Recomenda-se definir a AUTO_UPDATE STATISTICS opção como ATIVADA.

  • 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 limite é diferente 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 contém 20 mil linhas, então o cálculo é 500 + (0.2 * 20,000) = 4,500 e as estatísticas são atualizadas a cada 4.500 modificações.

  • A partir do SQL Server 2016 (13.x) e do nível de compatibilidade de banco de dados 130, o Mecanismo de Banco de Dados também usa um limite de recompilação de estatísticas dinâmicas decrescente 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 um banco de dados tiver um nível de compatibilidade abaixo de 130, os limites do SQL Server 2014 (12.x) serão aplicados.

    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 tabela contiver 2 milhões de linhas, o cálculo será 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 você tiver que confiar no trabalho noturno para atualizar as estatísticas porque a atualização automática atual não é acionada com frequência suficiente, considere habilitar o sinalizador de rastreamento 2371 para ajustar o limite à 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 opção AUTO_UPDATE_STATISTICS aplica-se a objetos de estatísticas criados para índices, colunas únicas em predicados de consulta e estatísticas criadas com a instrução CREATE STATISTICS . Esta opção também se aplica a estatísticas filtradas.

Você pode usar o sys.dm_db_stats_properties para controlar com precisão o número de linhas alteradas em uma tabela e decidir se deseja atualizar as estatísticas manualmente.

AUTO_UPDATE_STATISTICS está sempre DESLIGADO para tabelas com otimização de 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 predefinição, a opção de atualização de estatísticas assíncronas é Desativada e o Otimizador de Consultas atualiza as estatísticas de forma síncrona. A opção AUTO_UPDATE_STATISTICS_ASYNC aplica-se a objetos de estatísticas criados para índices, colunas únicas em predicados de consulta e estatísticas criadas com a instrução CREATE STATISTICS .

Note

Para definir a opção de atualização assíncrona de estatísticas no SQL Server Management Studio, na página Opções da janela Propriedades do Banco de Dados , as opções Estatísticas de Atualização Automática e Estatísticas de Atualização Automática Assincronamente precisam ser definidas como True.

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. Caso não atualize manualmente as estatísticas depois de concluir a operação, o uso de estatísticas síncronas garantirá que as estatísticas estejam up-toatualizadas antes que as consultas sejam executadas nos dados alterados.

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.

  • Seu aplicativo experimentou tempos limite de solicitação do cliente causados por uma ou mais consultas aguardando estatísticas atualizadas. Em alguns casos, aguardar por estatísticas sincronizadas pode fazer com que aplicações com limites de tempo agressivos falhem.

Note

As estatísticas em tabelas temporárias locais são sempre atualizadas de forma síncrona, independentemente da opção AUTO_UPDATE_STATISTICS_ASYNC. As estatísticas em tabelas temporárias globais são atualizadas de forma síncrona ou assíncrona de acordo com a opção AUTO_UPDATE_STATISTICS_ASYNC definida para o banco de dados do usuário.

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 Banco de Dados SQL do Azure, na Instância Gerenciada SQL do Azure e a partir do SQL Server 2022 (16.x), você pode evitar possíveis problemas de simultaneidade usando a atualização assíncrona de estatísticas se habilitar a configuração de escopo de banco de dados ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. 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 caso improvável de a solicitação em segundo plano não conseguir adquirir o bloqueio dentro de um período de tempo limite de vários minutos, a atualização assíncrona de estatísticas será abortada e as estatísticas não serão atualizadas até que outra atualização automática de estatísticas seja acionada ou até que as estatísticas sejam atualizadas manualmente.

Note

A opção de configuração com escopo de banco de dados ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY está disponível no Banco de Dados SQL do Azure, na Instância Gerenciada do SQL do Azure e no SQL Server a partir do SQL Server 2022 (16.x).

AUTO_DROP opção

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 anterior ao SQL Server 2022 (16.x), se as estatísticas forem criadas manualmente por um usuário ou ferramenta de terceiros em um banco de dados de usuário, esses objetos de estatísticas poderão bloquear ou interferir nas alterações de esquema desejadas.

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. A AUTO_DROP propriedade permite a criação de objetos de estatísticas em um modo tal que uma alteração de esquema subsequente não seja bloqueada pelo objeto statistic, mas em vez disso, 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.

No Banco de Dados SQL do Azure, na Instância Gerenciada SQL do Azure e no SQL Server 2022 (16.x) e versões posteriores, as estatísticas criadas automaticamente sempre se comportam como se o AUTO_DROP tivesse sido definido.

Note

Tentar definir ou desdefinir a propriedade de descarte automático em estatísticas criadas automaticamente pode gerar erros. As estatísticas criadas automaticamente sempre usam a queda 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 sempre se comportam como estatísticas de queda automática. 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 a opção INCREMENTAL de CREATE STATISTICS está ATIVADA, as estatísticas criadas são estatísticas por partição. Quando OFF, a árvore de estatísticas é descartada e o SQL Server recalcula as estatísticas. O padrão é OFF. Esta configuração anula a propriedade INCREMENTAL ao nível do banco de dados. Para obter mais informações sobre como criar estatísticas incrementais, consulte CREATE STATISTICS. Para obter mais informações sobre como criar estatísticas por partição automaticamente, consulte Propriedades do banco de dados (página Opções) e Opções ALTER DATABASE SET.

Quando novas partições são adicionadas a uma tabela grande, as estatísticas devem ser atualizadas para incluir as novas partições. No entanto, o tempo necessário para verificar toda a tabela (FULLSCANSAMPLE ou opções) pode ser bastante longo. 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, atualiza apenas as estatísticas nas partições que precisam de novas estatísticas

Se as estatísticas por partição não forem suportadas, a opção será ignorada e um aviso será gerado. Não há suporte para estatísticas incrementais para os seguintes tipos de estatísticas:

  • Estatísticas criadas com índices que não estão alinhados com a tabela base.
  • Estatísticas criadas em bases de dados secundárias legíveis 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. O Otimizador de Consultas cria estatísticas para índices em tabelas ou visões quando o índice é criado. 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 obter mais informações sobre índices filtrados, consulte Criar índices filtrados e CRIAR ÍNDICE.

    Note

    No SQL Server 2014 (12.x) e versões posteriores, as estatísticas não são criadas examinando todas as linhas da tabela quando um índice particionado é criado ou reconstruído. 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 examinando todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.

  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, esses dois métodos para criar estatísticas garantem um plano de consulta de alta qualidade; em alguns casos, você pode melhorar os planos de consulta criando estatísticas adicionais com 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.

Ao criar estatísticas com a instrução CREATE STATISTICS, recomendamos manter a opção AUTO_CREATE_STATISTICS 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 a criação de estatísticas com a instrução CREATE STATISTICS quando qualquer uma das seguintes situações se aplicar:

  • 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.

Predicado de consulta contém várias 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 de várias colunas já existe e não é necessário criá-lo manualmente. Se as colunas ainda não estiverem no mesmo índice, você poderá criar estatísticas de várias colunas criando um índice nas colunas ou usando a instrução CREATE STATISTICS . 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 obter mais informações sobre densidades, consulte a seção Densidade nesta página.

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.

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. Você pode criar estatísticas filtradas usando a instrução CREATE STATISTICS com a cláusula WHERE para definir a expressão de predicado de 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:

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 SQL Server. Os scripts para as estatísticas temporárias podem ser criados e reproduzidos em um banco de dados de leitura-gravação. Quando é gerado um script, o Management Studio modifica o sufixo do nome da estatística de _readonly_database_statistic para _readonly_database_statistic_scripted.

Somente o SQL Server 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:

  • Exclua estatísticas temporárias usando a instrução DROP STATISTICS .
  • 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 no tempdb, uma reinicialização do serviço SQL Server faz com que todas as estatísticas temporárias desapareçam.

Quando atualizar estatísticas

O Otimizador de Consultas determina quando as estatísticas podem estar desatualizadas e, em seguida, atualiza-as quando são necessárias para um plano de consulta. Em alguns casos, você pode melhorar o plano de consulta e, portanto, melhorar o desempenho da consulta atualizando as estatísticas com mais frequência do que ocorre quando AUTO_UPDATE_STATISTICS está ativado. Você pode atualizar as estatísticas com a instrução UPDATE STATISTICS ou o procedimento armazenado sp_updatestats.

A atualização das estatísticas garante que as consultas sejam compiladas com estatísticas de up-todata. A atualização de estatísticas através de qualquer processo pode fazer com que os planos de consulta sejam recompilados automaticamente. Recomendamos não atualizar manualmente as estatísticas com muita frequência porque há uma compensação de desempenho entre melhorar os planos de consulta e o tempo necessário para recompilar consultas. As compensações específicas dependem da sua aplicação.

Ao atualizar estatísticas com UPDATE STATISTICS ou sp_updatestats, recomendamos manter AUTO_UPDATE_STATISTICS definido como ATIVADO para que o Otimizador de Consultas atualize estatísticas rotineiramente.

  • Para obter mais informações sobre como atualizar estatísticas em uma coluna, um índice, uma tabela ou um modo de exibição indexado, consulte ATUALIZAR ESTATÍSTICAS.

  • 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 AUTO_UPDATE_STATISTICS está definido como OFF, a recompilação do plano ainda pode ocorrer por vários outros motivos, mas não ocorre automaticamente devido a atualizações de estatísticas desatualizadas. Quando AUTO_UPDATE_STATISTICS definido como DESATIVADO, as atualizações de estatísticas só ocorrem por meio de outros processos agendados manualmente, como planos de manutenção. A configuração AUTO_UPDATE_STATISTICS como 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 obter exemplos de atualização manual de estatísticas, consulte ATUALIZAR ESTATÍSTICAS.

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 em colunas de chave ascendente ou decrescente, como IDENTITY ou colunas de timestamp em tempo real, podem precisar de atualizações de estatísticas mais frequentes do que as executadas pelo Otimizador de Consultas. 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. Isso pode resultar em estimativas de cardinalidade imprecisas e desempenho lento da consulta.

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

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. Isso pode evitar atrasos futuros no processamento de 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, você não precisa atualizar as estatísticas depois de executar ALTER INDEX REBUILDDBCC DBREINDEXDBCC 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, use a opção PERSIST_SAMPLE_PERCENT de CREATE STATISTICS ou UPDATE STATISTICS, para definir e manter uma porcentagem de amostragem específica para atualizações estatísticas subsequentes que não especifiquem explicitamente uma porcentagem 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 deseja reconstruir ou reorganizar um índice de acordo com seu nível de fragmentação, entre outros parâmetros, e atualizar as estatísticas com um limite linear.

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. Seguir as diretrizes de design de consulta para usar estatísticas de forma eficaz pode ajudar a evitar isso. 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. Isso leva a estimativas de cardinalidade abaixo do ideal e pode prejudicar o desempenho da consulta. Para obter mais informações sobre histogramas, consulte a seção de histograma nesta página 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 com constantes nelas. O Otimizador de Consulta 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 diretrizes:

  • 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 valor de uma variável local não é conhecido quando o Otimizador de Consulta cria o plano de execução da consulta. Quando uma consulta usa um parâmetro, o Otimizador de Consulta usa a estimativa de cardinalidade para o primeiro valor de parâmetro real que é passado para o procedimento armazenado.

  • Considere utilizar uma tabela padrão ou uma tabela temporária para armazenar os resultados de funções valoradas por tabela de várias instruções (mstvf). O Otimizador de Consulta não cria estatísticas para funções com valor de tabela de várias instruções. Com essa abordagem, o Otimizador de Consulta pode criar estatísticas nas colunas da tabela e usá-las para criar um plano de consulta melhor.

  • 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. Com essa abordagem, o Otimizador de Consulta pode criar estatísticas nas colunas da tabela e usá-las para criar um plano de consulta melhor. Há compensações para determinar se deve 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 as 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. Essa 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 isso, você pode reescrever o procedimento armazenado em dois procedimentos da seguinte maneira:

    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, você pode usar as dicas de OPTIMIZE FOR <value> consulta 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. Você pode usar guias de plano para especificar outras dicas, como USE PLAN, para controlar o comportamento da consulta enquanto investiga alterações no aplicativo com o fornecedor do aplicativo. Para obter mais informações sobre guias de plano, consulte Guias de plano.

No Azure SQL Database, 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