CREATE STATISTICS (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Ponto de extremidade de análises SQL no Microsoft Fabric Warehouse 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 extras ou CREATE STATISTICS modificar o design da consulta para melhorar o desempenho da consulta.

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

Observação

Para obter mais informações sobre estatísticas no Microsoft Fabric, consulte Estatísticas no armazenamento de dados do Fabric.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

-- 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 ]

Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System).

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 | = | <> | != | > | >= | !> | < | <= | !<

Sintaxe para 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 a serem criadas.

table_or_indexed_view_name

O nome da tabela, exibição indexada ou 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:

  • As colunas xml, full-text e FILESTREAM não podem ser especificadas.

  • As colunas computadas só poderão ser especificadas se as configurações do banco de dados e QUOTED_IDENTIFIER forem ARITHABORT 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 lógica de comparação simples e não pode fazer referência a uma coluna computada, uma coluna UDT, uma coluna de tipo de dados espaciais ou uma coluna de tipo de dados hierarchyID . Comparações usando NULL literais não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL, nesse caso.

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 de filtro, consulte Criar índices filtrados.

FULLSCAN

Aplica-se a: SQL Server 2016 (13.x) SP 1 4, SQL Server 2017 (14.x) 1 e versões posteriores

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 SAMPLE opção.

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 coluna FULLSCAN única e coluna única SAMPLE. Quando nenhuma opção é incluída, SAMPLE as estatísticas são criadas.

SAMPLE number { PERCENT | ROWS }

Especifica a porcentagem aproximada, ou o número de linhas, na tabela ou exibição indexada para o otimizador de consulta usar ao criar estatísticas. Para PERCENT, o número pode ser de 0 a 100 e para ROWS, o número pode ser de 0 até o 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, com base 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 determina o tamanho da amostra estatisticamente significativo por padrão, conforme necessário para criar planos de consulta de alta qualidade.

SAMPLE não pode ser usado com a opção FULLSCAN. Quando SAMPLE ou FULLSCAN não são especificados, o otimizador de consulta usa dados de amostra e calcula o tamanho da amostra por padrão.

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

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

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Quando ON, as estatísticas retêm a porcentagem de amostragem de criação para atualizações subsequentes que não especificam explicitamente uma porcentagem de amostragem. Quando OFF, a porcentagem de amostragem de estatísticas é redefinida para a amostragem padrão em atualizações subsequentes que não especificam explicitamente uma porcentagem de amostragem. O padrão é OFF.

Observação

Se a tabela estiver truncada, todas as estatísticas criadas no HoBT truncado serão revertidas para usar a porcentagem de amostragem padrão.

STATS_STREAM = stats_stream

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

NORECOMPUTE

Desative a opção de atualização automática de estatísticas, AUTO_STATISTICS_UPDATE, por 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 as atualizações de estatísticas, remova as estatísticas com DROP STATISTICS e execute CREATE STATISTICS sem a NORECOMPUTE opção.

Aviso

Se você desabilitar a atualização automática de estatísticas, isso poderá impedir que o Otimizador de Consulta escolha planos de execução ideais para consultas que envolvem a tabela. Você deve usar essa opção com moderação e somente por um administrador de banco de dados qualificado.

Para obter mais informações sobre a AUTO_STATISTICS_UPDATE opção, consulte Opções de ALTER DATABASE SET. 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 versões posteriores

Quando ON, as estatísticas criadas são estatísticas por partição. Quando OFF, as estatísticas são combinadas para todas as partições. O padrão é OFF.

Se não houver suporte para estatísticas por partição, 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 a: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) 3 e versões posteriores

Substitui a opção de configuração de grau máximo de paralelismo durante a operação estatística. Para obter mais informações, consulte Configurar o grau máximo de paralelismo (opção de configuração do 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 planos paralelos.
  • >1: Restringe o número máximo de processadores usados em uma operação de índice paralelo ao número especificado.
  • 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: SQL Server 2022 (16.x) e versões posteriores e Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure

Antes do 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 que o cliente pode desejar.

A partir do SQL Server 2022 (16.x), a AUTO_DROP opção é 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 de modo que uma alteração de esquema subsequente não seja bloqueada pelo objeto de estatística, mas, em vez disso, as estatísticas sejam descartadas conforme necessário. Dessa forma, as estatísticas criadas manualmente com AUTO_DROP habilitado se comportam como estatísticas criadas automaticamente.

Observação

Tentar definir ou desconfigurar a propriedade Auto_Drop em estatísticas criadas automaticamente pode gerar erros. As estatísticas criadas automaticamente sempre usam a remoção automática. Alguns backups, quando restaurados, podem ter essa propriedade configurada incorretamente até a próxima vez que o objeto de estatísticas for atualizado (manual ou automaticamente). No entanto, as estatísticas criadas automaticamente se comportam como estatísticas de remoção 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 estatísticas AUTO_DROP .

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ê tiver 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, confira 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. Considere as operações que você executa nas colunas da tabela antes de criar estatísticas filtradas. Você não pode descartar, renomear ou alterar a definição de uma coluna de tabela definida em um predicado de estatísticas filtradas.

Limitações

  • Não há suporte para 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 MAXDOP opção não é compatível com STATS_STREAM, ROWCOUNT, e options PAGECOUNT .
  • A opção MAXDOP é limitada pela configuração MAX_DOP de grupo de carga de trabalho de Resource Governor, se usada.
  • CREATE e DROP STATISTICS em tabelas externas não têm suporte no Banco de Dados SQL do Azure.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. 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. CREATE e DROP STATISTICS em tabelas externas não têm suporte 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 as NamePurchase estatísticas para todas as BusinessEntityID linhas nas colunas e EmailPromotion da tabela e define uma porcentagem de amostragem de 100% para todas as atualizações subsequentes que não especificam explicitamente uma porcentagem de Person 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;

Conteúdo relacionado