Estatísticas no SQL do Synapse

Fornecidos neste artigo estão recomendações e exemplos para criar e atualizar estatísticas de otimização de consulta usando os recursos do SQL do Synapse: pool de SQL dedicado e pool de SQL sem servidor.

Estatísticas no pool de SQL dedicado

Por que usar estatísticas

Quanto mais o pool de SQL dedicado souber sobre seus dados, mais rápido poderá executar consultas. Depois de carregar dados em um pool de SQL dedicado, coletar estatísticas sobre seus dados é uma das coisas mais importantes que você pode fazer para otimização de consulta.

O otimizador de consulta do pool de SQL dedicado é um otimizador baseado em custos. Ele compara o custo de vários planos de consulta e, em seguida, escolhe o plano com o menor custo. Na maioria dos casos, ele escolhe o plano que executará o mais rápido.

Por exemplo, se o otimizador estima que a data usada no filtro da consulta retornará uma linha, ele escolherá um plano. Se for estimado que a data selecionada retornará 1 milhão de linhas, será gerado um plano diferente.

Criação automática de estatísticas

O mecanismo do pool de SQL dedicado analisará as consultas dos usuários recebidas em busca de estatísticas ausentes quando a opção AUTO_CREATE_STATISTICS do banco de dados estiver definida como ON. Se as estatísticas estiverem ausentes, o otimizador de consulta criará estatísticas em colunas individuais no predicado de consulta ou na condição de junção.

Essa função é usada para melhorar as estimativas de cardinalidade para o plano de consulta.

Você pode verificar se o seu data warehouse tem AUTO_CREATE_STATISTICS configurado executando o seguinte comando:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Se o data warehouse não tiver AUTO_CREATE_STATISTICS habilitado, recomendamos habilitar essa propriedade executando o seguinte comando:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Essas instruções dispararão a criação automática de estatísticas:

• SELECIONAR
• INSERT-SELECT
• CTAS
• ATUALIZAÇÃO
• EXCLUIR
• EXPLAIN quando for detectada a presença de predicado ou junção

A criação automática de estatísticas é feita de forma síncrona. Portanto, você poderá incorrer em um desempenho de consulta ligeiramente degradado se suas colunas estiverem faltando estatísticas. O tempo para criar estatísticas para uma única coluna depende do tamanho da tabela.

Para evitar a degradação mensurável do desempenho, verifique se as estatísticas foram criadas primeiro executando a carga de trabalho de parâmetros de comparação antes de criar um perfil do sistema.

Quando as estatísticas automáticas forem criadas, elas tomarão a forma: WA_Sys<ID da coluna de 8 dígitos em Hex>_<ID da tabela de 8 dígitos em Hex>. Você pode exibir estatísticas já criadas executando o comando DBCC SHOW_STATISTICS :

DBCC SHOW_STATISTICS (<table_name>, <target>)

O table_name é o nome da tabela que contém as estatísticas a serem exibidas, que não podem ser uma tabela externa. O destino é o nome do índice, as estatísticas ou a coluna de destino cujas informações de estatísticas serão exibidas.

Atualizar estatísticas

Uma prática recomendada é atualizar as estatísticas em colunas de data por dia à medida que novas datas são adicionadas. Sempre que novas linhas são carregadas no data warehouse, novas datas de carregamento ou datas de transação são adicionadas. Essas adições alteram a distribuição de dados e torna as estatísticas desatualizadas.

As estatísticas em uma coluna de país ou região em uma tabela de clientes talvez nunca precisem ser atualizadas porque a distribuição de valores geralmente não é alterada. Supondo que a distribuição seja constante entre os clientes, adicionar novas linhas à variação de tabela não alterará a distribuição dos dados.

No entanto, quando o data warehouse contém apenas um país ou região e você traz dados de um novo país ou região, então você precisa atualizar estatísticas sobre a coluna país ou região.

A seguir, recomendações para a atualização de estatísticas:

Determinar a última atualização de estatísticas

Uma das primeiras perguntas a fazer quando você está solucionando problemas de uma consulta é : "As estatísticas estão atualizadas?"

Essa pergunta não é aquela que pode ser respondida pela idade dos dados. Um objeto de estatísticas atualizado pode ser antigo se não houver nenhuma alteração importante nos dados subjacentes. Quando o número de linhas é alterado substancialmente ou ocorre uma alteração material na distribuição de valores de uma coluna, então é hora de atualizar estatísticas.

Não há uma exibição de gerenciamento dinâmico disponível para determinar se os dados dentro da tabela foram alterados desde a última vez em que as estatísticas foram atualizadas. Saber a idade de suas estatísticas pode fornecer parte do contexto.

Você pode usar a consulta a seguir para determinar a última vez que suas estatísticas foram atualizadas em cada tabela.

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

As colunas de data em um data warehouse, por exemplo, geralmente precisam de atualizações de estatísticas frequentes. Sempre que novas linhas são carregadas no data warehouse, novas datas de carregamento ou datas de transação são adicionadas. Essas adições alteram a distribuição de dados e torna as estatísticas desatualizadas.

As estatísticas em uma coluna de gênero em uma tabela de clientes talvez nunca precisem ser atualizadas. Supondo que a distribuição seja constante entre os clientes, adicionar novas linhas à variação de tabela não alterará a distribuição dos dados.

Mas, se o data warehouse contiver apenas um gênero e um novo requisito resultar em vários gêneros, você precisará atualizar as estatísticas na coluna de gênero.

Para obter mais informações, examine o artigo Estatísticas .

Implementar gerenciamento de estatísticas

Geralmente convém estender o processo de carregamento de dados a fim de garantir que as estatísticas sejam atualizadas no final do carregamento. É no carregamento de dados que as tabelas frequentemente mudam de tamanho, distribuição de valores, ou ambos. Como tal, o processo de carregamento é um momento lógico para implementar alguns processos de gerenciamento.

Os seguintes princípios orientadores são fornecidos para atualizar suas estatísticas durante o processo de carregamento:

• Certifique-se de que cada tabela carregada tenha pelo menos um objeto de estatísticas atualizado. Este processo atualiza as informações do tamanho da tabela (contagem de linhas e contagem de páginas) como parte da atualização de estatísticas.
• Concentre-se em colunas que participam de cláusulas JOIN, GROUP BY, ORDER BY e DISTINCT.
• Considere atualizar com mais frequência as colunas de "chave crescente", como datas de transação, porque esses valores não serão incluídos no histograma de estatísticas.
• Considere atualizar as colunas de distribuição estática com menos frequência.
• Lembre-se, cada objeto estatístico é atualizado em sequência. Simplesmente implementar UPDATE STATISTICS <TABLE_NAME> nem sempre é ideal, especialmente para tabelas amplas com muitos objetos de estatística.

Para obter mais informações, consulte Estimativa de cardinalidade.

Exemplos: Criar estatísticas

Estes exemplos mostram como usar várias opções para a criação de estatísticas. As opções que você usa para cada coluna dependem das características de seus dados e de como a coluna será usada em consultas.

Criar estatísticas de coluna única com opções padrão

Para criar estatísticas em uma coluna, forneça um nome para o objeto de estatísticas e o nome da coluna. Esta sintaxe usa todas as opções padrão. Por padrão, o pool de SQL dedicado amostra 20% da tabela quando cria estatísticas.

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name]);

Por exemplo:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Criar estatísticas de coluna única examinando cada linha

A taxa de amostragem padrão de 20 por cento é suficiente para a maioria das situações. No entanto, você pode ajustar essa taxa de amostragem. Para usar toda a tabela como amostragem, use a seguinte sintaxe:

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name])
    WITH FULLSCAN;

Por exemplo:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH FULLSCAN;

Criar estatísticas de coluna única, especificando o tamanho da amostra

Outra opção que você tem é especificar o tamanho da amostra como uma porcentagem:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH SAMPLE 50 PERCENT;

Criar estatísticas de coluna única em apenas algumas das linhas

Você também pode criar estatísticas em uma parte das linhas em sua tabela, que é chamada de estatística filtrada.

Por exemplo, é possível usar estatísticas filtradas quando você planeja consultar uma partição específica de uma tabela particionada grande. Ao criar estatísticas apenas sobre os valores de partição, a precisão das estatísticas melhorará. Você também experimentará uma melhoria no desempenho das consultas.

Este exemplo cria estatísticas em um intervalo de valores. Os valores podem ser facilmente definidos para corresponder ao intervalo de valores em uma partição.

CREATE STATISTICS stats_col1
    ON table1(col1)
    WHERE col1 > '2000101' AND col1 < '20001231';

Criar estatísticas de coluna única com todas as opções

Também é possível combinar as opções juntas. O exemplo a seguir cria um objeto estatístico filtrado com um tamanho de amostra personalizado:

CREATE STATISTICS stats_col1
    ON table1 (col1)
    WHERE col1 > '2000101' AND col1 < '20001231'
    WITH SAMPLE 50 PERCENT;

Para obter a referência completa, consulte CREATE STATISTICS.

Criar estatísticas de várias colunas

Para criar um objeto estatístico de várias colunas, use os exemplos anteriores, mas especifique mais colunas.

Neste exemplo, o histograma está em product_category. As estatísticas entre colunas são calculadas em product_categorye em product_sub_category:

CREATE STATISTICS stats_2cols
    ON table1 (product_category, product_sub_category)
    WHERE product_category > '2000101' AND product_category < '20001231'
    WITH SAMPLE 50 PERCENT;

Como existe uma correlação entre product_category e product_sub_category, um objeto de estatísticas de várias colunas pode ser útil se essas colunas forem acessadas ao mesmo tempo. Ao consultar essa tabela, as estatísticas de várias colunas melhorarão as estimativas de cardinalidade para junções, agregações GROUP BY, contagens distintas e filtros WHERE (desde que a coluna de estatísticas primárias faça parte do filtro).

Criar estatísticas em todas as coluna em uma tabela

Uma maneira de criar estatísticas é emitir comandos CREATE STATISTICS depois de criar a tabela:

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

Usar um procedimento armazenado para criar estatísticas em todas as colunas em um banco de dados

O pool de SQL não possui um procedimento armazenado do sistema equivalente a sp_create_stats no SQL Server. Esse procedimento armazenado cria um único objeto de estatísticas de coluna em cada coluna do banco de dados que ainda não tem estatísticas.

O exemplo a seguir ajudará você a começar a usar o design do banco de dados. Fique à vontade para adaptá-lo às suas necessidades:

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Para criar estatísticas em todas as colunas da tabela usando os padrões, realize o procedimento armazenado.

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

Para criar estatísticas em todas as colunas da tabela usando um fullscan, chame este procedimento:

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

Para criar estatísticas amostradas em todas as colunas da tabela, insira 3 e a porcentagem de amostra. O procedimento a seguir utiliza uma taxa de amostragem de 20%.

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

Exemplos: Atualizar estatísticas

Para atualizar as estatísticas, você pode:

• Atualizar um objeto de estatísticas. Especifique o nome do objeto de estatísticas que você deseja atualizar.
• Atualizar todos os objetos de estatísticas em uma tabela. Especifique o nome da tabela em vez de um objeto de estatísticas específico.

Atualizar um objeto de estatísticas específico

Use a sintaxe a seguir para atualizar um objeto de estatísticas específico:

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

Por exemplo:

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

Ao atualizar objetos de estatísticas específicos, você pode minimizar o tempo e os recursos necessários para o gerenciamento de estatísticas. Essa ação requer algum pensamento para selecionar os melhores objetos de estatísticas a serem atualizados.

Atualizar todas as estatísticas de uma tabela

Um método simples para atualizar todos os objetos de estatísticas em uma tabela é:

UPDATE STATISTICS [schema_name].[table_name];

Por exemplo:

UPDATE STATISTICS dbo.table1;

A instrução UPDATE STATISTICS é fácil de usar. Basta lembrar que ele atualiza todas as estatísticas na tabela, solicitando mais trabalho do que o necessário.

Se o desempenho não for um problema, esse método é a maneira mais fácil e completa de garantir que as estatísticas estejam atualizadas.

Para obter uma implementação de um UPDATE STATISTICS procedimento, consulte tabelas temporárias. O método de implementação é ligeiramente diferente do procedimento anterior CREATE STATISTICS, mas o resultado é o mesmo. Para obter a sintaxe completa, consulte As estatísticas de atualização.

Metadados de estatísticas

Há várias exibições e funções do sistema que podem ser utilizadas para localizar informações sobre estatísticas. Por exemplo, você pode ver se um objeto de estatísticas pode estar desatualizado usando a função STATS_DATE(). STATS_DATE() permite ver quando as estatísticas foram criadas ou atualizadas pela última vez.

Exibições de catálogo para as estatísticas

Essas exibições do sistema fornecem informações sobre estatísticas:

Funções de sistema para estatísticas

Essas funções de sistema são úteis para trabalhar com estatísticas:

Combinar colunas de estatísticas e funções em uma exibição

Essa exibição reúne colunas relacionadas a estatísticas e resultados da função STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

Exemplos de DBCC SHOW_STATISTICS()

DBCC SHOW_STATISTICS() mostra os dados mantidos em um objeto de estatísticas. Esses dados estão divididos em três partes:

• Cabeçalho
• Vetor de densidade
• Histograma

O cabeçalho são os metadados sobre as estatísticas. O histograma exibe a distribuição de valores na primeira coluna de chave do objeto de estatísticas.

O vetor de densidade mede a correlação entre colunas. O pool de SQL dedicado calcula as estimativas de cardinalidade com os dados do objeto das estatística.

Mostrar cabeçalho, densidade e histograma

Este exemplo simples mostra as três partes de um objeto de estatísticas:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Por exemplo:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

Mostrar uma ou mais partes de DBCC SHOW_STATISTICS()

Se você estiver interessado apenas em visualizar partes específicas, use a cláusula WITH e especifique quais partes deseja ver:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
    WITH stat_header, histogram, density_vector

Por exemplo:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
    WITH histogram, density_vector

Diferenças do DBCC SHOW_STATISTICS()

DBCC SHOW_STATISTICS() é implementado mais estritamente no pool de SQL dedicado em comparação com o SQL Server:

• Recursos não documentados não são suportados.
• Não é possível usar Stats_stream.
• Não é possível unir resultados a subconjuntos específicos de dados estatísticos. Por exemplo, STAT_HEADER JOIN DENSITY_VECTOR.
• NO_INFOMSGS não pode ser definido para supressão de mensagem.
• Colchetes em torno dos nomes das estatísticas não podem ser usados.
• Não é possível usar nomes de colunas para identificar objetos de estatísticas.
• O erro personalizado 2767 não é suportado.

Estatísticas em pool SQL sem servidor

As estatísticas são criadas por coluna específica para um conjunto de dados específico (caminho de armazenamento).

Por que usar estatísticas

Quanto mais o pool de SQL sem servidor souber sobre seus dados, mais rápido ele poderá executar consultas nele. Coletar estatísticas em seus dados é uma das coisas mais importantes que você pode fazer para otimizar suas consultas.

O otimizador de consulta do pool de SQL sem servidor é um otimizador baseado em custo. Ele compara o custo de vários planos de consulta e, em seguida, escolhe o plano com o menor custo. Na maioria dos casos, ele escolhe o plano que executará o mais rápido.

Por exemplo, se o otimizador estimar que a data usada no filtro do consulta retornará uma linha, ele escolherá um plano. Se o otimizador estimar que a data selecionada retornará 1 milhão de linhas, ele escolherá um plano diferente.

Criação automática de estatísticas

O pool de SQL sem servidor analisa as consultas de usuário de entrada para estatísticas ausentes. Se as estatísticas estiverem ausentes, o otimizador de consulta criará estatísticas sobre colunas individuais no predicado da consulta ou na condição de junção para melhorar as estimativas de cardinalidade do plano de consulta.

A instrução SELECT disparará a criação automática de estatísticas.

A criação automática de estatísticas é feita de forma síncrona, portanto, você pode incorrer em um desempenho de consulta ligeiramente degradado se suas colunas estiverem faltando estatísticas. O tempo para criar estatísticas para uma única coluna depende do tamanho do arquivo de destino.

Criação manual de estatísticas

O pool de SQL sem servidor permite que você crie estatísticas manualmente. Caso esteja usando o analisador versão 1.0 com CSV, você provavelmente precisará criar estatísticas manualmente, pois essa versão do analisador não dá suporte à amostragem. A criação automática de estatísticas no caso do analisador versão 1.0 não acontecerá, a menos que a porcentagem de amostragem seja de 100%.

Confira os exemplos a seguir para obter instruções sobre como criar estatísticas manualmente.

Atualizar estatísticas

Alterações nos dados em arquivos, exclusão e adição de arquivos resultam em alterações de distribuição de dados e fazem estatísticas desatualizadas. Nesse caso, as estatísticas precisam ser atualizadas.

O pool de SQL sem servidor recria automaticamente as estatísticas para colunas OPENROWSET se os dados forem alterados significativamente. Toda vez que as estatísticas são criadas automaticamente, o estado atual do conjunto de dados também é salvo: caminhos de arquivo, tamanhos, datas da última modificação.

Quando as estatísticas estiverem obsoletas, novas serão criadas. O algoritmo passa pelos dados e os compara ao estado atual do conjunto de dados. Se o tamanho das alterações for maior que o limite específico, as estatísticas antigas serão excluídas e serão recriadas no novo conjunto de dados.

Estatísticas manuais nunca são declaradas obsoletas.

Uma das primeiras perguntas a fazer quando você está solucionando problemas de uma consulta é : "As estatísticas estão atualizadas?"

Quando o número de linhas mudar substancialmente, ou houver uma alteração material na distribuição de valores para uma coluna, então, significa que é hora de atualizar as estatísticas.

Implementar gerenciamento de estatísticas

Talvez você queira estender o pipeline de dados para garantir que as estatísticas sejam atualizadas quando os dados forem significativamente alterados por meio de adição, exclusão ou alteração de arquivos.

Os seguintes princípios orientadores são fornecidos para atualizar suas estatísticas:

• Verifique se o conjunto de dados tem pelo menos um objeto de estatísticas atualizado. Isso atualiza as informações de tamanho (contagem de linhas e contagem de páginas) como parte da atualização de estatísticas.
• Concentre-se em colunas que participam das cláusulas WHERE, JOIN, GROUP BY, ORDER BY e DISTINCT.
• Atualize colunas de "chave crescente", como datas de transação com mais frequência, porque esses valores não serão incluídos no histograma de estatísticas.
• Atualize as colunas de distribuição estática com menos frequência.

Para obter mais informações, consulte Estimativa de cardinalidade.

Exemplos: crie estatísticas para a coluna no caminho OPENROWSET

Os exemplos a seguir mostram como usar várias opções para criar estatísticas em pools de SQL sem servidor do Azure Synapse. As opções que você usa para cada coluna dependem das características de seus dados e de como a coluna será usada em consultas. Para obter mais informações sobre os procedimentos armazenados usados nesses exemplos, examine sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics, que se aplicam somente a pools de SQL sem servidor.

O procedimento armazenado a seguir é usado para criar estatísticas:

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

Argumentos: [ @stmt = ] N'statement_text' – Especifica uma instrução Transact-SQL que retornará valores de coluna a serem usados para estatísticas. Você pode usar TABLESAMPLE para especificar exemplos de dados a serem usados. Se TABLESAMPLE não for especificado, FULLSCAN será usado.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Criar estatísticas de coluna única examinando cada linha

Para criar estatísticas em uma coluna, forneça uma consulta que retorna a coluna para a qual você precisa de estatísticas.

Por padrão, se você não especificar o contrário ao criar estatísticas manualmente, o pool de SQL sem servidor usará 100% dos dados fornecidos no conjunto de dados quando ele criar estatísticas.

Por exemplo, para criar estatísticas com opções padrão (FULLSCAN) para uma coluna de população do conjunto de dados com base no arquivo us_population.csv:

EXEC sys.sp_create_openrowset_statistics N'SELECT 
    population
FROM OPENROWSET(
    BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE)
AS [r]'

Criar estatísticas de coluna única, especificando o tamanho da amostra

Você pode especificar o tamanho da amostra como uma porcentagem:

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Exemplos: Atualizar estatísticas

Para atualizar as estatísticas, você precisa remover e criar estatísticas. Para obter mais informações, examine sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics.

O procedimento armazenado sys.sp_drop_openrowset_statistics é usado para remover estatísticas:

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

Argumentos: [ @stmt = ] N'statement_text' – Especifica a mesma instrução Transact-SQL usada quando as estatísticas foram criadas.

Para atualizar as estatísticas da coluna "ano" no conjunto de dados, que é baseado no arquivo population.csv, você precisa remover e criar estatísticas:

EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'
GO

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Exemplos: Criar estatísticas para a coluna de tabela externa

Os exemplos a seguir mostram como usar várias opções para criar estatísticas. As opções que você usa para cada coluna dependem das características de seus dados e de como a coluna será usada em consultas.

Para criar estatísticas em uma coluna, forneça um nome para o objeto de estatísticas e o nome da coluna.

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

Argumentos: external_table Especifica a tabela externa para a criação das estatísticas.

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

"SAMPLE number PERCENT" especifica a porcentagem aproximada ou o número de linhas da tabela ou da exibição indexada que o otimizador de consultas deve usar para criar as estatísticas. O número pode ser de 0 a 100.

SAMPLE não pode ser usado com a opção FULLSCAN.

Criar estatísticas de coluna única examinando cada linha

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Criar estatísticas de coluna única, especificando o tamanho da amostra

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

Exemplos: Atualizar estatísticas

Para atualizar as estatísticas, você precisa remover e criar estatísticas. Remova as estatísticas primeiro:

DROP STATISTICS census_external_table.sState

E crie estatísticas:

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Metadados de estatísticas

Há várias exibições e funções do sistema que podem ser utilizadas para localizar informações sobre estatísticas. Por exemplo, você pode ver se um objeto de estatísticas pode estar desatualizado usando a função STATS_DATE(). STATS_DATE() permite ver quando as estatísticas foram criadas ou atualizadas pela última vez.

Exibições de catálogo para as estatísticas

Essas exibições do sistema fornecem informações sobre estatísticas:

Funções de sistema para estatísticas

Essas funções de sistema são úteis para trabalhar com estatísticas:

Combinar colunas de estatísticas e funções em uma exibição

Essa exibição reúne colunas relacionadas a estatísticas e resultados da função STATS_DATE().

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   st.[user_created] = 1
;

Próximas etapas

Para melhorar ainda mais o desempenho da consulta para o pool de SQL dedicado, consulte Monitorar sua carga de trabalho e as práticas recomendadas para o pool de SQL dedicado.

Para melhorar ainda mais o desempenho da consulta para o pool de SQL sem servidor, consulte as práticas recomendadas para o pool de SQL sem servidor.