CREATE STATISTICS (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPonto de extremidade de análises SQL no Microsoft FabricWarehouse no Microsoft Fabric

Cria estatísticas de otimização de consulta em uma ou mais colunas de uma tabela, uma exibição indexada ou uma tabela externa. Para a maioria das consultas, o otimizador de consulta já gera as estatísticas necessárias para um plano de consulta de alta qualidade; em alguns casos, você precisa criar estatísticas adicionais com CREATE STATISTICS ou modificar o design de consulta para melhorar o desempenho da consulta.

Para obter mais informações, veja Estatísticas.

Convenções de sintaxe de Transact-SQL

Sintaxe

-- Syntax for SQL Server and Azure SQL Database
-- Create statistics on an external table

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WITH FULLSCAN ] ;
  
-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ ,...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
    
<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

-- Syntax for Microsoft Fabric
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]

Argumentos

statistics_name

É o nome das estatísticas que devem ser criadas.

table_or_indexed_view_name

É o nome da tabela, da exibição indexada ou da tabela externa na qual criar as estatísticas. Para criar estatísticas em outro banco de dados, especifique um nome de tabela qualificado.

column [ ,...n]

Uma ou mais colunas a serem incluídas nas estatísticas. As colunas devem estar em ordem de prioridade da esquerda para a direita. Apenas a primeira coluna é usada para criar o histograma. Todas as colunas são usadas para estatísticas de correlação entre colunas chamadas de densidades.

É possível especificar qualquer coluna que possa ser especificada como uma coluna de chave de índice, com as seguintes exceções:

• Colunas XML, de texto completo e FILESTREAM não podem ser especificadas.

• As colunas computadas poderão ser especificadas somente se as configurações de banco de dados ARITHABORT e QUOTED_IDENTIFIER forem ON.

• As colunas do tipo CLR definidas pelo usuário poderão ser especificadas se o tipo der suporte à ordenação binária. As colunas computadas definidas como invocações de método de uma coluna de tipo definida pelo usuário poderão ser especificadas se os métodos forem marcados como determinísticos.

WHERE <filter_predicate>

Especifica uma expressão para selecionar um subconjunto de linhas a serem incluídas durante a criação do objeto de estatísticas. As estatísticas criadas com um predicado de filtro são chamadas de estatísticas filtradas. O predicado de filtro usa a lógica de comparação simples e não pode fazer referência a uma coluna computada, a uma coluna UDT, a uma coluna de tipo de dados espacial ou a uma coluna de tipo de dados hierarchyID. Comparações que usam literais NULL não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL em seu lugar.

Estes são alguns exemplos de predicados de filtro da tabela Production.BillOfMaterials:

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Para obter mais informações sobre predicados filtrados, veja Criar índices filtrados.

FULLSCAN

Aplica-se a: SQL Server 2016 (13.x) (começando com SQL Server 2016 (13.x) SP1 CU4) e posterior (começando com SQL Server 2017 (14.x) CU1)

Calcule as estatísticas verificando todas as linhas. FULLSCAN e SAMPLE 100 PERCENT têm os mesmos resultados. FULLSCAN não pode ser usado com a opção SAMPLE.

Quando omitido, o SQL Server usa amostragem para criar as estatísticas e determina o tamanho da amostra necessário para criar um plano de consulta de alta qualidade.

No Warehouse no Microsoft Fabric, há suporte apenas para estatísticas baseadas em AMOSTRA de coluna única e FULLSCAN de coluna única. Quando nenhuma opção é incluída, as estatísticas FULLSCAN são criadas.

SAMPLE number { PERCENT | ROWS }

Especifica a porcentagem aproximada ou o número de linhas da tabela ou da exibição indexada para uso do otimizador de consulta ao criar as estatísticas. Para PERCENT, number pode ser de 0 a 100 e, para ROWS, number pode ser de 0 ao número total de linhas. A porcentagem real ou o número de linhas que o otimizador de consulta usa como exemplo talvez não corresponda à porcentagem ou ao número especificado. Por exemplo, o otimizador de consulta verifica todas as linhas de uma página de dados.

SAMPLE é útil para casos especiais em que o plano de consulta, baseado na amostragem padrão, não é ideal. Na maioria das situações, não é necessário especificar SAMPLE porque o otimizador de consulta já usa amostragem e, por padrão, determina o tamanho da amostra estatisticamente significativa, conforme necessário para criar planos de consulta de alta qualidade.

SAMPLE não pode ser usado com a opção FULLSCAN. Quando nem SAMPLE nem FULLSCAN estão especificados, o otimizador de consulta usa dados de exemplo e computa o tamanho do exemplo por padrão.

Recomendamos especificar 0 PERCENT ou 0 ROWS. Quando 0 PERCENT ou 0 ROWS é especificado, o objeto de estatísticas é criado, mas ele não contém dados estatísticos.

No Warehouse no Microsoft Fabric, há suporte apenas para estatísticas baseadas em AMOSTRA de coluna única e FULLSCAN de coluna única. Quando nenhuma opção é incluída, as estatísticas FULLSCAN são criadas.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Quando for ON, as estatísticas reterão o percentual de amostragem de criação para as atualizações seguintes que não especificam explicitamente um percentual de amostragem. Quando for OFF, o percentual de amostragem de estatísticas será redefinido com a amostragem padrão nas atualizações seguintes que não especificam explicitamente um percentual de amostragem. O padrão é OFF.

STATS_STREAM = stats_stream

Identificado apenas para fins informativos. Não há suporte. A compatibilidade futura não está garantida.

NORECOMPUTE

Desabilite a opção de atualização das estatísticas automáticas, AUTO_STATISTICS_UPDATE, para statistics_name. Se essa opção for especificada, o otimizador de consulta concluirá todas as atualizações de estatísticas em andamento para statistics_name e desabilitará atualizações futuras.

Para reabilitar atualizações de estatísticas, remova as estatísticas com DROP STATISTICS e execute CREATE STATISTICS sem a opção NORECOMPUTE.

Para obter mais informações sobre a opção AUTO_STATISTICS_UPDATE, confira Opções de ALTER DATABASE SET (Transact-SQL). Para obter mais informações sobre como desabilitar e reabilitar atualizações de estatísticas, veja Estatísticas.

INCREMENTAL = { ON | OFF }

Aplica-se a: SQL Server 2014 (12.x) e posterior.

Quando estiver ON, as estatísticas serão criadas conforme as estatísticas de partição. Quando OFF, as estatísticas serão combinadas para todas as partições. O padrão é OFF.

Se as estatísticas por partição não tiverem suporte, um erro será gerado. As estatísticas incrementais não têm suporte para os seguintes tipos de estatísticas:

• Estatísticas criadas com os índices que não estejam alinhados por partição com a tabela base.
• Estatísticas criadas em bancos de dados secundários legíveis AlwaysOn.
• Estatísticas criadas em bancos de dados somente leitura.
• Estatísticas criadas em índices filtrados.
• Estatísticas criadas em exibições.
• Estatísticas criadas em tabelas internas.
• Estatísticas criadas com índices espaciais ou índices XML.

MAXDOP = max_degree_of_parallelism

Aplica-se ao: SQL Server (Começando pelo SQL Server 2016 (13.x) SP2 e SQL Server 2017 (14.x) CU3).

Substitui a opção de configuração max degree of parallelism enquanto durar a operação estatística. Para obter mais informações, veja Configurar a opção max degree of parallelism de configuração de servidor. Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.

max_degree_of_parallelism pode ser:

1
Suprime a geração de plano paralelo.

>1
Restringe o número máximo de processadores usados em uma operação estatística paralela ao número especificado ou menos, com base na carga de trabalho atual do sistema.

0 (padrão)
Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.

update_stats_stream_option

Identificado apenas para fins informativos. Não há suporte. A compatibilidade futura não está garantida.

AUTO_DROP = { ON | OFF }

Aplica-se a: Banco de dados SQL do Azure, Instância Gerenciada de SQL do Azure e a partir do SQL Server 2022 (16.x)

Antes do SQL Server 2022 (16.x), se as estatísticas eram criadas manualmente por um usuário ou por uma ferramenta de terceiros em um banco de dados de usuário, esses objetos de estatísticas podiam bloquear as alterações de esquema desejadas pelo cliente ou interferir nelas.

A partir do SQL Server 2022 (16.x), a opção AUTO_DROP está habilitada por padrão em todos os bancos de dados novos e migrados. A propriedade AUTO_DROP permite a criação de objetos de estatísticas em um modo que uma alteração de esquema posterior não seja bloqueada pelo objeto de estatística, mas, em vez disso, as estatísticas sejam removidas conforme necessário. Dessa forma, as estatísticas criadas manualmente com o AUTO_DROP habilitado se comportam como as estatísticas criadas automaticamente.

Para obter mais informações, confira Opção AUTO_DROP.

Permissões

Requer uma destas permissões:

• ALTER TABLE
• Usuário é o proprietário da tabela
• Associação na função de banco de dados fixa db_ddladmin

Comentários

O SQL Server pode usar tempdb para classificar as linhas de amostragem antes de criar as estatísticas.

Estatísticas para tabelas externas

Ao criar estatísticas de tabela externa, o SQL Server importa a tabela externa para uma tabela temporária do SQL Server e, depois, cria as estatísticas. Para estatísticas de amostra, apenas as linhas de amostra são importadas. Se você tem uma tabela externa grande, é mais rápido usar a amostragem padrão, em vez da opção de verificação completa.

Quando a tabela externa está usando DELIMITEDTEXT, CSV, PARQUET ou DELTA como tipos de dados, as tabelas externas oferecem suporte apenas a estatísticas para uma coluna por comando CREATE STATISTICS.

Estatísticas com uma condição filtrada

As estatísticas filtradas podem melhorar o desempenho de consultas selecionadas em subconjuntos bem definidos de dados. Estatísticas filtradas usam um predicado de filtro na cláusula WHERE para selecionar o subconjunto de dados incluído nas estatísticas.

Quando usar CREATE STATISTICS

Para obter mais informações sobre quando usar CREATE STATISTICS, veja Estatísticas.

Dependências de referência para as estatísticas filtradas

A exibição do catálogo sys.sql_expression_dependencies controla cada coluna no predicado de estatísticas filtradas como uma dependência de referência. Avalie as operações realizadas nas colunas da tabela antes de criar estatísticas filtradas, pois não será possível remover, renomear ou alterar a definição de uma coluna da tabela que esteja definida em um predicado de estatísticas filtradas.

Limitações e Restrições

• Não há compatibilidade com a atualização de estatística em tabelas externas. Para atualizar as estatísticas em uma tabela externa, remova e recrie as estatísticas.
• Você pode listar até 64 colunas por objeto de estatísticas.
• A opção MAXDOP não é compatível com as opções STATS_STREAM, ROWCOUNT e PAGECOUNT.
• A opção MAXDOP é limitada pela configuração MAX_DOP de grupo de carga de trabalho de Resource Governor, se usada.
• Não há suporte para CREATE e DROP STATISTICS em tabelas externas no Banco de Dados SQL do Azure.

Exemplos

Os exemplos usam o banco de dados AdventureWorks.

a. Usar CREATE STATISTICS com SAMPLE número PERCENT

O exemplo a seguir cria as estatísticas ContactMail1 utilizando uma amostra aleatória de 5% das colunas BusinessEntityID e EmailPromotion da tabela Person do banco de dados AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Usar CREATE STATISTICS com FULLSCAN e NORECOMPUTE

O exemplo a seguir cria a estatística NamePurchase de todas as linhas das colunas BusinessEntityID e EmailPromotion da tabela Person e desabilita o recálculo de estatísticas.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Usar CREATE STATISTICS para criar estatísticas filtradas

O exemplo a seguir cria as estatísticas filtradas ContactPromotion1. O Mecanismo de Banco de Dados faz a amostragem de 50% dos dados e seleciona as linhas com EmailPromotion igual a 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Criar estatísticas em uma tabela externa

A única decisão que você precisa tomar ao criar estatísticas em uma tabela externa, além de fornecer a lista de colunas, é criar as estatísticas por amostragem de linhas ou verificando todas as linhas. Não há suporte para CREATE e DROP STATISTICS em tabelas externas no Banco de Dados SQL do Azure.

Uma vez que o SQL Server importa dados da tabela externa para uma tabela temporária para criar estatísticas, a opção de verificação completa demora muito mais. Para uma tabela grande, o método de amostragem padrão normalmente é suficiente.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
  
--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Usar CREATE STATISTICS com FULLSCAN e PERSIST_SAMPLE_PERCENT

O exemplo a seguir cria estatísticas NamePurchase para todas as linhas nas colunas BusinessEntityID e EmailPromotion da tabela Person e define um percentual de amostragem de 100 por cento de todas as atualizações subsequentes que não especificam explicitamente um percentual de amostragem.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Exemplos do uso de banco de dados AdventureWorksDW

F. Criar estatísticas em duas colunas

O exemplo a seguir cria as estatísticas CustomerStats1 com base nas colunas CustomerKey e EmailAddress da tabela DimCustomer. As estatísticas são criadas com base em uma amostragem estatisticamente significativa das linhas na tabela Customer.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Criar estatísticas usando uma verificação completa

O exemplo a seguir cria as estatísticas de CustomerStatsFullScan com base na verificação de todas as linhas da tabela DimCustomer.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Criar estatísticas especificando o percentual de amostra

O exemplo a seguir cria as estatísticas de CustomerStatsSampleScan com base na verificação de 50 por cento de todas as linhas da tabela DimCustomer.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Usar CREATE STATISTICS com AUTO_DROP

Para usar as estatísticas de remoção automática, basta adicionar o código abaixo à cláusula "WITH" de criação ou atualização de estatísticas.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

Para avaliar a configuração de remoção automática nas estatísticas existentes, use a coluna auto_drop em sys.stats:

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

Próximas etapas

• Estatísticas
• Estatísticas no Microsoft Fabric
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)