Estatísticas em Synapse SQL

Este artigo fornece recomendações e exemplos para criar e atualizar estatísticas de otimização de consultas usando os recursos SQL Synapse: pool SQL dedicado e pool SQL sem servidor.

As estatísticas no pool SQL dedicado

Porquê utilizar estatísticas

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

O otimizador de consulta de pool SQL dedicado é 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, escolhe o plano que será executado mais rapidamente.

Por exemplo, se o otimizador estimar que a data em que sua consulta está filtrando retornará uma linha, ele escolherá um plano. Se ele estimar que a data selecionada retornará 1 milhão de linhas, ele retornará um plano diferente.

Criação automática de estatísticas

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

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

Pode verificar se o seu armazém de dados tem o AUTO_CREATE_STATISTICS configurado executando o seguinte comando:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Se o seu armazém de dados não tiver AUTO_CREATE_STATISTICS ativado, recomendamos que ative esta propriedade executando o seguinte comando:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Estas declarações desencadearão a criação automática de estatísticas:

• SELECIONAR
• INSERT-SELECT
• CTAS
• ATUALIZAÇÃO
• SUPRIMIR
• EXPLICAR quando a contenção de uma junção ou a presença de um predicado é detetada

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 da tabela.

Para evitar a degradação mensurável do desempenho, você deve garantir que as estatísticas tenham sido criadas primeiro executando a carga de trabalho de referência antes de criar o perfil do sistema.

Quando as estatísticas automáticas são criadas, elas assumem a forma: WA_Sys<id de coluna de 8 dígitos em Hex>_<id de tabela de 8 dígitos em Hex>. Você pode visualizar 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 pode ser uma tabela externa. O alvo é o nome do índice, das estatísticas ou da coluna para os quais se deseja exibir informações estatísticas.

Atualizar estatísticas

Uma prática recomendada é atualizar as estatísticas nas colunas de data todos os dias à medida que novas datas são adicionadas. Cada vez que novas linhas são carregadas no data warehouse, novas datas de carregamento ou datas de transação são adicionadas. Estes aditamentos alteram a distribuição dos dados e tornam as estatísticas desatualizadas.

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

No entanto, quando o seu armazém de dados contém apenas um país ou região e traz dados de um novo país ou região, tem de atualizar as estatísticas na coluna de país ou região.

Seguem-se recomendações para a atualização das estatísticas:

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

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

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

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

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 estatísticas frequentes. Cada vez que novas linhas são carregadas no data warehouse, novas datas de carregamento ou datas de transação são adicionadas. Estes aditamentos alteram a distribuição dos dados e tornam as estatísticas desatualizadas.

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

Mas, se o seu armazém de dados contiver apenas um género e um novo requisito resultar em vários géneros, então terá de atualizar as estatísticas na coluna género.

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

Implementar a gestão de estatísticas

Muitas vezes, é útil alargar o processo de carregamento de dados para garantir que as estatísticas são atualizadas no final da carga. Carga de dados é quando as tabelas mais frequentemente mudam o seu tamanho, a distribuição de valores, ou ambos. Como tal, o processo de carga é um local lógico para implementar alguns processos de gestão.

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

• Certifique-se de que cada tabela carregada tem, pelo menos, um objeto de estatísticas atualizado. Este processo atualiza as informações de tamanho da tabela (contagem de linhas e contagem de páginas) como parte da atualização das estatísticas.
• Concentre-se nas colunas que participam das cláusulas JOIN, GROUP BY, ORDER BY e DISTINCT.
• Considere atualizar as colunas de "chave crescente", como datas de transações, com mais frequência, pois 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 de cada objeto estatístico é atualizado em sequência. A simples implementação UPDATE STATISTICS <TABLE_NAME> nem sempre é ideal, especialmente para tabelas amplas com muitos objetos estatísticos.

Para obter mais informações, consulte Cardinality Estimation.

Exemplos: Criar estatísticas

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

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

Para criar estatísticas numa coluna, forneça um nome para o objeto de estatísticas e o nome da coluna. Essa sintaxe usa todas as opções padrão. Por padrão, o pool SQL dedicado amostra 20 por cento da tabela ao criar estatísticas.

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

Por exemplo:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Crie estatísticas de coluna única examinando cada linha

A taxa de amostragem padrão de 20% é suficiente para a maioria das situações. No entanto, é possível ajustar a taxa de amostragem. Para obter um exemplo da tabela completa, use esta 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;

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

Outra opção é 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 da tabela, que é chamada de estatística filtrada.

Por exemplo, você pode usar estatísticas filtradas quando 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 irá melhorar. Você também notará uma melhoria no desempenho da consulta.

Este exemplo cria estatísticas sobre 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';

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

Você também pode combinar as opções. O exemplo a seguir cria um objeto de estatísticas 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 com várias colunas

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

Neste exemplo, o histograma está em product_category. As estatísticas intercolunas são calculadas em product_category e 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 com várias colunas pode ser útil se essas colunas forem acessadas ao mesmo tempo. Ao consultar esta 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 colunas de 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 de um banco de dados

Pool SQL não tem um procedimento armazenado de sistema equivalente ao sp_create_stats no SQL Server. Este 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 irá ajudá-lo a começar com seu design de banco de dados. Sinta-se à 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, execute o procedimento armazenado.

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

Para criar estatísticas em todas as colunas da tabela usando uma análise completa, chame este procedimento:

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

Para criar estatísticas de amostra em todas as colunas da tabela, insira 3 e a porcentagem da amostra. O procedimento abaixo usa 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:

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

Atualizar um objeto de estatística específico

Use a sintaxe a seguir para atualizar um objeto de estatística 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 gerenciar estatísticas. Esta ação requer alguma reflexão para selecionar os melhores objetos estatísticos 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. Apenas lembre-se 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, este método é a forma mais fácil e completa de garantir que as estatísticas estão atualizadas.

Para 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 Atualizar estatísticas.

Metadados estatísticos

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

Visualizações de catálogo para estatísticas

Estas visualizações do sistema fornecem informações sobre estatísticas:

Funções do sistema para estatísticas

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

Combine estatísticas, colunas e funções em uma única exibição

Esta vista reúne colunas relacionadas com 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 dentro de um objeto de estatística. Estes dados dividem-se 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ística.

O vetor de densidade mede a correlação entre colunas. O pool SQL dedicado calcula estimativas de cardinalidade com qualquer um dos dados no objeto de estatística.

Mostrar cabeçalho, densidade e histograma

Este exemplo simples mostra todas 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 do DBCC SHOW_STATISTICS()

Se você estiver interessado apenas em visualizar partes específicas, use a WITH cláusula 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

DBCC SHOW_STATISTICS() diferenças

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

• Funcionalidades não documentadas não são suportadas.
• Não é possível usá Stats_stream.
• Não é possível juntar resultados para 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 mensagens.
• Os colchetes em torno dos nomes das estatísticas não podem ser usados.
• Não é possível usar nomes de coluna para identificar objetos de estatística.
• O erro personalizado 2767 não é suportado.

Estatísticas no pool SQL sem servidor

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

Porquê utilizar estatísticas

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

O otimizador de consulta de pool 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, escolhe o plano que será executado mais rapidamente.

Por exemplo, se o otimizador estimar que a data em que sua consulta está filtrando retornará uma linha, ele escolherá um plano. Se for estimado que a data selecionada retornará 1 milhão de linhas, será escolhido um plano diferente.

Criação automática de estatísticas

O pool SQL sem servidor analisa consultas de usuários de entrada em busca de estatísticas ausentes. 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 para melhorar as estimativas de cardinalidade para o plano de consulta.

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

A criação automática de estatísticas é feita de forma síncrona, de modo que 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 dos arquivos de destino.

Criação manual de estatísticas

O pool SQL sem servidor permite criar estatísticas manualmente. No caso de você estar usando o analisador versão 1.0 com CSV, você provavelmente terá que criar estatísticas manualmente, porque esta versão do analisador não suporta amostragem. A criação automática de estatísticas no caso da versão 1.0 do analisador não acontecerá, a menos que a porcentagem de amostragem seja de 100%.

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

Atualizar estatísticas

Alterações em dados em arquivos, exclusão e adição de arquivos resultam em alterações na distribuição de dados e tornam as estatísticas desatualizadas. Nesse caso, é necessário atualizar as estatísticas.

O pool 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 analisa os dados e os compara com o estado atual do conjunto de dados. Se o tamanho das alterações for maior do que o limite específico, as estatísticas antigas serão excluídas e serão recriadas sobre o novo conjunto de dados.

As estatísticas manuais nunca são declaradas obsoletas.

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

Quando o número de linhas mudou substancialmente, ou há uma mudança material na distribuição de valores para uma coluna, então é hora de atualizar as estatísticas.

Implementar a gestão de estatísticas

Talvez você queira estender seu 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ística 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 nas colunas que participam nas 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 colunas de distribuição estática com menos frequência.

Para obter mais informações, consulte Cardinality Estimation.

Exemplos: Criar estatísticas para coluna no percurso do OPENROWSET

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

O seguinte procedimento armazenado é 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 )

Crie estatísticas de coluna única examinando cada linha

Para criar estatísticas em uma coluna, forneça uma consulta que retorne 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 SQL sem servidor usará 100% dos dados fornecidos no conjunto de dados quando 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]'

Crie 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 estatísticas, é necessário eliminar e criar estatísticas. Para obter mais informações, consulte sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics.

O sys.sp_drop_openrowset_statistics procedimento armazenado é usado para eliminar 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 do ano no conjunto de dados, que se baseia no arquivo population.csv, necessita eliminar 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 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 dos seus dados e de como a coluna será usada nas consultas.

Para criar estatísticas numa 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 em que as estatísticas devem ser criadas.

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

SAMPLE number PERCENT Especifica a percentagem ou número aproximado de linhas de uma tabela ou vista indexada que o otimizador de consultas deve usar ao criar estatísticas. O número pode ser de 0 a 100.

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

Crie estatísticas de coluna única examinando cada linha

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

Crie 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 estatísticas, é necessário eliminar 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 estatísticos

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

Visualizações de catálogo para estatísticas

Estas visualizações do sistema fornecem informações sobre estatísticas:

Funções do sistema para estatísticas

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

Combine estatísticas, colunas e funções em uma única exibição

Esta vista reúne colunas relacionadas com 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óximos passos

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

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