Estatísticas no Synapse SQL

Artigo
03/25/2023

Neste artigo encontram-se recomendações e exemplos para criar e atualizar estatísticas de otimização de consultas com os recursos sqL do Synapse: conjunto de SQL dedicado e conjunto de SQL sem servidor.

Estatísticas no conjunto de SQL dedicado

Porquê utilizar estatísticas

Quanto mais conjunto de SQL dedicado souber sobre os seus dados, mais rapidamente poderá executar consultas. Depois de carregar dados para um conjunto de SQL dedicado, recolher estatísticas nos seus dados é uma das coisas mais importantes que pode fazer para a otimização de consultas.

O otimizador de consultas do conjunto de SQL dedicado é um otimizador baseado em custos. Compara o custo de vários planos de consulta e, em seguida, escolhe o plano com o custo mais baixo. Na maioria dos casos, escolhe o plano que irá executar o mais rápido.

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

Criação automática de estatísticas

O motor de conjunto de SQL dedicado analisará as consultas de utilizadores recebidas relativamente a estatísticas em falta quando a opção de AUTO_CREATE_STATISTICS da base de dados estiver definida como ON. Se faltarem estatísticas, o otimizador de consultas cria estatísticas sobre as colunas individuais no predicado ou condição de associação da consulta.

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

Importante

A criação automática de estatísticas está ativada por predefinição.

Pode verificar se o armazém de dados AUTO_CREATE_STATISTICS configurado ao executar o seguinte comando:

SELECT name, is_auto_create_stats_on
FROM sys.databases

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

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Estas instruções irão acionar a criação automática de estatísticas:

SELECIONAR
INSERT-SELECT
CTAS
UPDATE
DELETE
EXPLICAR quando contém uma associação ou é detetada a presença de um predicado

Nota

A criação automática de estatísticas não é gerada em tabelas temporárias ou externas.

A criação automática de estatísticas é feita de forma síncrona. Assim, poderá incorrer num desempenho de consulta ligeiramente degradado se as colunas estiverem em falta nas estatísticas. O tempo para criar estatísticas para uma única coluna depende do tamanho da tabela.

Para evitar uma degradação mensurável do desempenho, deve garantir que as estatísticas foram criadas primeiro ao executar a carga de trabalho de referência antes de criar a criação de perfis do sistema.

Nota

A criação de estatísticas é registada no sys.dm_pdw_exec_requests num contexto de utilizador diferente.

Quando as estatísticas automáticas são criadas, assumem o formulário: WA_Sys<ID de coluna de 8 dígitos no ID da tabela hex>_<8 dígitos no Hex>. Pode ver estatísticas já criadas ao executar o comando SHOW_STATISTICS DBCC :

DBCC SHOW_STATISTICS (<table_name>, <target>)

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

Atualização de estatísticas

Uma das melhores práticas é atualizar as estatísticas em colunas de data todos os dias à medida que são adicionadas novas datas. Sempre que forem carregadas novas linhas para o armazém de dados, são adicionadas novas datas de carregamento ou datas de transação. Estas adições alteram a distribuição de dados e tornam as estatísticas desatualizadas.

As estatísticas numa coluna de país ou região numa tabela de clientes podem nunca ter de ser atualizadas porque a distribuição de valores normalmente não é alterada. Partindo do princípio de que a distribuição é constante entre os clientes, a adição de novas linhas à variação da tabela não irá 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 país ou região.

Seguem-se recomendações para atualizar estatísticas:

Tipo	Recomendação
Frequência de atualizações de estatísticas	Conservador: Diariamente após carregar ou transformar os seus dados
Amostragem	Menos de mil milhões de linhas, utilize a amostragem predefinida (20%). Com mais de mil milhões de linhas, utilize a amostragem de 2%.

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

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

Esta pergunta não é uma pergunta que possa ser respondida pela idade dos dados. Um objeto de estatística atualizado pode ser antigo se não tiver havido nenhuma alteração material aos dados subjacentes. Quando o número de linhas tiver sido alterado substancialmente ou ocorrer uma alteração material na distribuição de valores de uma coluna, é altura de atualizar as estatísticas.

Não existe uma vista de gestão dinâmica 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 da imagem.

Pode utilizar a seguinte consulta para determinar a última vez que as estatísticas foram atualizadas em cada tabela.

Nota

Se existir uma alteração material na distribuição de valores de uma coluna, deve atualizar as estatísticas independentemente da última vez que foram atualizados.

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 num armazém de dados, por exemplo, normalmente precisam de atualizações de estatísticas frequentes. Sempre que forem carregadas novas linhas para o armazém de dados, são adicionadas novas datas de carregamento ou datas de transação. Estas adições alteram a distribuição de dados e tornam as estatísticas desatualizadas.

As estatísticas numa coluna de género numa tabela de clientes poderão nunca ter de ser atualizadas. Partindo do princípio de que a distribuição é constante entre os clientes, a adição de novas linhas à variação da tabela não irá alterar a distribuição de dados.

No entanto, se o seu armazém de dados contiver apenas um género e um novo requisito resultar em múltiplos sexos, terá de atualizar as estatísticas na coluna de género.

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

Implementar a gestão de estatísticas

Muitas vezes, é boa ideia expandir o seu processo de carregamento de dados para garantir que as estatísticas são atualizadas no final da carga. O carregamento de dados é quando as tabelas alteram mais frequentemente o respetivo tamanho, distribuição de valores ou ambos. Como tal, o processo de carregamento é um local lógico para implementar alguns processos de gestão.

São fornecidos os seguintes princípios de orientação para atualizar as estatísticas durante o processo de carregamento:

Certifique-se de que cada tabela carregada tem, pelo menos, um objeto de estatística atualizado. Este processo atualiza as informações de tamanho da tabela (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 JOIN, GROUP BY, ORDER BY e DISTINCT.
Considere atualizar colunas de "chave ascendente", como datas de transação com mais frequência, uma vez que estes valores não serão incluídos no histograma de estatísticas.
Considere atualizar as colunas de distribuição estáticas com menos frequência.
Lembre-se de que cada objeto de estatística é atualizado em sequência. A simples implementação UPDATE STATISTICS <TABLE_NAME> nem sempre é ideal, especialmente para tabelas largas com muitos objetos de estatística.

Para obter mais informações, veja Estimativa da Cardinalidade.

Exemplos: Criar estatísticas

Estes exemplos mostram como utilizar várias opções para criar estatísticas. As opções que utiliza para cada coluna dependem das características dos seus dados e da forma como a coluna será utilizada nas consultas.

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

Para criar estatísticas numa coluna, forneça um nome para o objeto de estatística e o nome da coluna. Esta sintaxe utiliza todas as opções predefinidas. Por predefinição, o conjunto de SQL dedicado mostra 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 ao examinar cada linha

A taxa de amostragem predefinida de 20 por cento é suficiente para a maioria das situações. No entanto, pode ajustar a taxa de amostragem. Para amostrar a tabela completa, utilize 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;

Criar estatísticas de coluna única ao especificar o tamanho da amostra

Outra opção que tem é especificar o tamanho da amostra como uma percentagem:

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

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

Também pode criar estatísticas numa parte das linhas da tabela, que é denominada estatística filtrada.

Por exemplo, pode utilizar estatísticas filtradas quando planeia 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á. Também irá deparar-se com uma melhoria no desempenho das consultas.

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

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

Nota

Para que o otimizador de consultas considere a utilização de estatísticas filtradas quando escolher o plano de consulta distribuída, a consulta tem de caber dentro da definição do objeto de estatísticas. Com o exemplo anterior, a cláusula WHERE da consulta tem de especificar valores col1 entre 2000101 e 20001231.

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

Também pode combinar as opções em conjunto. O exemplo seguinte cria um objeto de estatística filtrado com um tamanho de exemplo personalizado:

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

Para obter a referência completa, veja CREATE STATISTICS (CRIAR ESTATÍSTICAS).

Criar estatísticas de várias colunas

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

Nota

O histograma, que é utilizado para estimar o número de linhas no resultado da consulta, só está disponível para a primeira coluna listada na definição do objeto de estatística.

Neste exemplo, o histograma está no product_category. As estatísticas entre colunas 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;

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

Criar estatísticas em todas as colunas numa tabela

Uma forma 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);

Utilizar um procedimento armazenado para criar estatísticas em todas as colunas numa base de dados

O conjunto de SQL não tem um procedimento armazenado do sistema equivalente a sp_create_stats no SQL Server. Este procedimento armazenado cria um objeto de estatísticas de coluna única em todas as colunas da base de dados que ainda não têm estatísticas.

O exemplo seguinte irá ajudá-lo a começar a utilizar a estrutura da base de dados. Não se sinta à 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 na tabela com as predefinições, execute o procedimento armazenado.

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

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

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

Para criar estatísticas de exemplo em todas as colunas na tabela, introduza 3 e a percentagem de exemplo. O procedimento abaixo utiliza uma taxa de exemplo de 20%.

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

Exemplos: Atualizar estatísticas

Para atualizar as estatísticas, pode:

Atualizar um objeto de estatística. Especifique o nome do objeto de estatística que pretende atualizar.
Atualizar todos os objetos de estatística numa tabela. Especifique o nome da tabela em vez de um objeto de estatística específico.

Atualizar um objeto de estatística específico

Utilize a seguinte sintaxe 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ística específicos, pode minimizar o tempo e os recursos necessários para gerir as estatísticas. Esta ação requer algum pensamento para selecionar os melhores objetos de estatística a atualizar.

Atualizar todas as estatísticas numa tabela

Um método simples para atualizar todos os objetos de estatística numa tabela é:

UPDATE STATISTICS [schema_name].[table_name];

Por exemplo:

UPDATE STATISTICS dbo.table1;

A instrução ESTATÍSTICAS DE ATUALIZAÇÃO é fácil de utilizar. Lembre-se apenas de que atualiza todas as estatísticas na tabela, o que exige 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.

Nota

Ao atualizar todas as estatísticas numa tabela, o conjunto de SQL dedicado faz uma análise para provar a tabela para cada objeto de estatística. Se a tabela for grande e tiver muitas colunas e muitas estatísticas, poderá ser mais eficiente atualizar estatísticas individuais com base nas necessidades.

Para uma implementação de um UPDATE STATISTICS procedimento, veja 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, veja Atualizar estatísticas.

Metadados de estatísticas

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

Vistas de catálogo para estatísticas

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

Vista de catálogo	Descrição
sys.columns	Uma linha para cada coluna.
sys.objects	Uma linha para cada objeto na base de dados.
sys.schemas	Uma linha para cada esquema na base de dados.
sys.stats	Uma linha para cada objeto de estatística.
sys.stats_columns	Uma linha para cada coluna no objeto de estatísticas. Ligações para sys.columns.
sys.tables	Uma linha para cada tabela (inclui tabelas externas).
sys.table_types	Uma linha para cada tipo de dados.

Funções do sistema para estatísticas

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

Função do sistema	Descrição
STATS_DATE	Data em que o objeto de estatística foi atualizado pela última vez.
DBCC SHOW_STATISTICS	Nível de resumo e informações detalhadas sobre a distribuição de valores, conforme compreendido pelo objeto de estatísticas.

Combinar colunas e funções de estatística numa única vista

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
;

DBCC SHOW_STATISTICS() exemplos

DBCC SHOW_STATISTICS() mostra os dados contidos num objeto de estatística. Estes dados são fornecidos em três partes:

Cabeçalho
Vetor de densidade
Histograma

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

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

Mostrar cabeçalho, densidade e histograma

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

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

Por exemplo:

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

Mostrar uma ou mais partes de SHOW_STATISTICS DBCC()

Se só estiver interessado em ver partes específicas, utilize a WITH cláusula e especifique as partes que pretende 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 mais rigorosamente no conjunto de SQL dedicado em comparação com SQL Server:

As funcionalidades não documentadas não são suportadas.
Não é possível utilizar Stats_stream.
Não é possível associar resultados a subconjuntos específicos de dados de estatísticas. Por exemplo, STAT_HEADER associar DENSITY_VECTOR.
NO_INFOMSGS não pode ser definida para supressão de mensagens.
Os parênteses retos à volta dos nomes das estatísticas não podem ser utilizados.
Não é possível utilizar nomes de colunas para identificar objetos de estatística.
O erro 2767 personalizado não é suportado.

Estatísticas no conjunto de SQL sem servidor

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

Nota

Não é possível criar estatísticas para colunas LOB.

Porquê utilizar estatísticas

Quanto mais conjunto de SQL sem servidor souber sobre os seus dados, mais rápido pode executar consultas nos mesmos. Recolher estatísticas nos seus dados é uma das coisas mais importantes que pode fazer para otimizar as suas consultas.

O otimizador de consultas do conjunto de SQL sem servidor é um otimizador baseado em custos. Compara o custo de vários planos de consulta e, em seguida, escolhe o plano com o custo mais baixo. Na maioria dos casos, escolhe o plano que irá executar o mais rápido.

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

Criação automática de estatísticas

O conjunto de SQL sem servidor analisa as consultas de utilizador recebidas para obter estatísticas em falta. Se as estatísticas estiverem em falta, o otimizador de consultas cria estatísticas em colunas individuais na condição de predicado de consulta ou associação para melhorar as estimativas de cardinalidade do plano de consulta.

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

Nota

Para a criação automática de amostragem de estatísticas é utilizada e, na maioria dos casos, a percentagem de amostragem será inferior a 100%. Este fluxo é o mesmo para cada formato de ficheiro. Tenha em atenção que ao ler CSV com amostragem da versão 1.0 do analisador não é suportado e a criação automática de estatísticas não ocorrerá com uma percentagem de amostragem inferior a 100%. Para tabelas pequenas com uma cardinalidade baixa estimada (número de linhas), a criação de estatísticas automáticas será acionada com uma percentagem de amostragem de 100%. Isto significa basicamente que fullscan é acionado e as estatísticas automáticas são criadas mesmo para CSV com a versão 1.0 do analisador.

A criação automática de estatísticas é feita de forma síncrona para que possa incorrer num desempenho de consulta ligeiramente degradado se as colunas estiverem em falta. O tempo para criar estatísticas para uma única coluna depende do tamanho dos ficheiros visados.

Criação manual de estatísticas

O conjunto de SQL sem servidor permite-lhe criar estatísticas manualmente. Caso esteja a utilizar a versão 1.0 do analisador com CSV, provavelmente terá de criar estatísticas manualmente, uma vez que esta versão do analisador não suporta a amostragem. A criação automática de estatísticas no caso da versão 1.0 do analisador não ocorrerá, a menos que a percentagem de amostragem seja de 100%.

Veja os seguintes exemplos para obter instruções sobre como criar estatísticas manualmente.

Atualização de estatísticas

As alterações aos dados em ficheiros, eliminação e adição de ficheiros resultam em alterações na distribuição de dados e tornam as estatísticas desatualizadas. Nesse caso, as estatísticas têm de ser atualizadas.

O conjunto de SQL sem servidor recria automaticamente as estatísticas se os dados forem alterados significativamente. Sempre que as estatísticas são criadas automaticamente, o estado atual do conjunto de dados também é guardado: caminhos de ficheiro, tamanhos e datas de última modificação.

Quando as estatísticas estiverem obsoletas, serão criadas novas. O algoritmo percorre os dados e compara-os com o estado atual do conjunto de dados. Se o tamanho das alterações for superior ao limiar específico, as estatísticas antigas serão eliminadas e serão recriadas através do novo conjunto de dados.

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

Nota

Para a recriação automática da amostragem de estatísticas é utilizada e, na maioria dos casos, a percentagem de amostragem será inferior a 100%. Este fluxo é o mesmo para cada formato de ficheiro. Tenha em atenção que ao ler CSV com amostragem de parser versão 1.0 não é suportado e a recriação automática de estatísticas não ocorrerá com uma percentagem de amostragem inferior a 100%. Nesse caso, tem de remover e recriar as estatísticas manualmente. Veja os exemplos abaixo sobre como remover e criar estatísticas. Para tabelas pequenas com cardinalidade baixa estimada (número de linhas), a recriação automática de estatísticas será acionada com uma percentagem de amostragem de 100%. Isto significa basicamente que fullscan é acionado e as estatísticas automáticas são criadas mesmo para CSV com a versão 1.0 do analisador.

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

Quando o número de linhas tiver sido alterado substancialmente ou se existir uma alteração material na distribuição de valores de uma coluna, é altura de atualizar as estatísticas.

Nota

Se existir uma alteração material na distribuição de valores de uma coluna, deve atualizar as estatísticas independentemente da última vez que foram atualizados.

Implementar a gestão de estatísticas

Poderá querer expandir o pipeline de dados para garantir que as estatísticas são atualizadas quando os dados são significativamente alterados através da adição, eliminação ou alteração de ficheiros.

São fornecidos os seguintes princípios de orientação para atualizar as estatísticas:

Certifique-se de que o conjunto de dados tem, pelo menos, um objeto de estatística atualizado. Esta ação 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 ascendente", como datas de transação com mais frequência, porque estes valores não serão incluídos no histograma de estatísticas.
Atualizar colunas de distribuição estáticas com menos frequência.

Para obter mais informações, veja Estimativa da Cardinalidade.

Exemplos: Criar estatísticas para colunas no caminho OPENROWSET

Os exemplos seguintes mostram-lhe como utilizar várias opções para criar estatísticas em conjuntos de SQL sem servidor Azure Synapse. As opções que utilizar para cada coluna dependem das características dos seus dados e da forma como a coluna será utilizada nas consultas. Para obter mais informações sobre os procedimentos armazenados utilizados nestes exemplos, veja sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics, que se aplicam apenas a conjuntos de SQL sem servidor.

Nota

Pode criar estatísticas de coluna única apenas neste momento.

São necessárias as seguintes permissões para executar sp_create_openrowset_statistics e sp_drop_openrowset_statistics: ADMINISTRAR OPERAÇÕES EM MASSA ou ADMINISTRAR OPERAÇÕES EM MASSA DA BASE DE DADOS.

O seguinte procedimento armazenado é utilizado 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 devolverá valores de coluna a serem utilizados para estatísticas. Pode utilizar TABLESAMPLE para especificar exemplos de dados a utilizar. Se TABLESAMPLE não for especificado, será utilizado FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Nota

A amostragem CSV não funciona se estiver a utilizar a versão 1.0 do analisador, apenas o FULLSCAN é suportado para CSV com a versão 1.0 do analisador.

Criar estatísticas de coluna única ao examinar cada linha

Para criar estatísticas numa coluna, forneça uma consulta que devolva a coluna para a qual precisa de estatísticas.

Por predefinição, se não especificar o contrário ao criar manualmente estatísticas, o conjunto de SQL sem servidor utiliza 100% dos dados fornecidos no conjunto de dados quando cria estatísticas.

Por exemplo, para criar estatísticas com opções predefinidas (FULLSCAN) para uma coluna de população do conjunto de dados com base no ficheiro 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 ao especificar o tamanho da amostra

Pode especificar o tamanho da amostra como uma percentagem:

/* 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, tem de remover e criar estatísticas. Para obter mais informações, veja sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics.

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

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

Nota

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

Para atualizar as estatísticas da coluna ano no conjunto de dados, que se baseia no population.csv ficheiro, tem de 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 seguintes mostram-lhe como utilizar várias opções para criar estatísticas. As opções que utilizar para cada coluna dependem das características dos seus dados e da forma como a coluna será utilizada nas consultas.

Nota

Pode criar estatísticas de coluna única apenas neste momento.

Para criar estatísticas numa coluna, forneça um nome para o objeto de estatística 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 que as estatísticas devem ser criadas.

Estatísticas de Computação FULLSCAN ao analisar todas as linhas. FULLSCAN e SAMPLE 100 PERCENT têm os mesmos resultados. FULLSCAN não pode ser utilizado com a opção EXEMPLO.

PERCENTAGEM do número de EXEMPLO Especifica a percentagem aproximada ou o número de linhas na tabela ou vista indexada para o otimizador de consultas a utilizar quando cria estatísticas. O número pode ser de 0 a 100.

O EXEMPLO não pode ser utilizado com a opção FULLSCAN.

Nota

A amostragem CSV não funciona se estiver a utilizar a versão 1.0 do analisador, apenas o FULLSCAN é suportado para CSV com a versão 1.0 do analisador.

Criar estatísticas de coluna única ao examinar cada linha

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

Criar estatísticas de coluna única ao especificar 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, tem de remover e criar estatísticas. Remova primeiro as estatísticas:

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

Nota

Os metadados de estatísticas só estão disponíveis para colunas de tabelas externas. Os metadados de estatísticas não estão disponíveis para colunas OPENROWSET.

Vistas de catálogo para estatísticas

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

Vista de catálogo	Descrição
sys.columns	Uma linha para cada coluna.
sys.objects	Uma linha para cada objeto na base de dados.
sys.schemas	Uma linha para cada esquema na base de dados.
sys.stats	Uma linha para cada objeto de estatística.
sys.stats_columns	Uma linha para cada coluna no objeto de estatísticas. Ligações para sys.columns.
sys.tables	Uma linha para cada tabela (inclui tabelas externas).
sys.table_types	Uma linha para cada tipo de dados.

Funções do sistema para estatísticas

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

Função do sistema	Descrição
STATS_DATE	Data em que o objeto de estatística foi atualizado pela última vez.

Combinar colunas e funções de estatística numa única vista

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
;

Passos seguintes

Para melhorar ainda mais o desempenho das consultas para o conjunto de SQL dedicado, veja Monitorizar a carga de trabalho e As melhores práticas para o conjunto de SQL dedicado.

Para melhorar ainda mais o desempenho das consultas para o conjunto de SQL sem servidor, veja Melhores práticas para o conjunto de SQL sem servidor.

Estatísticas no Synapse SQL

Estatísticas no conjunto de SQL dedicado

Porquê utilizar estatísticas

Criação automática de estatísticas

Atualização de estatísticas

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

Implementar a gestão de estatísticas

Exemplos: Criar estatísticas

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

Criar estatísticas de coluna única ao examinar cada linha

Criar estatísticas de coluna única ao especificar o tamanho da amostra

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

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

Criar estatísticas de várias colunas

Criar estatísticas em todas as colunas numa tabela

Utilizar um procedimento armazenado para criar estatísticas em todas as colunas numa base de dados

Exemplos: Atualizar estatísticas

Atualizar um objeto de estatística específico

Atualizar todas as estatísticas numa tabela

Metadados de estatísticas

Vistas de catálogo para estatísticas

Funções do sistema para estatísticas

Combinar colunas e funções de estatística numa única vista

DBCC SHOW_STATISTICS() exemplos

Mostrar cabeçalho, densidade e histograma

Mostrar uma ou mais partes de SHOW_STATISTICS DBCC()

DBCC SHOW_STATISTICS() diferenças

Estatísticas no conjunto de SQL sem servidor

Porquê utilizar estatísticas

Criação automática de estatísticas

Criação manual de estatísticas

Atualização de estatísticas

Implementar a gestão de estatísticas

Exemplos: Criar estatísticas para colunas no caminho OPENROWSET

Criar estatísticas de coluna única ao examinar cada linha

Criar estatísticas de coluna única ao especificar o tamanho da amostra

Exemplos: Atualizar estatísticas

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

Criar estatísticas de coluna única ao examinar cada linha

Criar estatísticas de coluna única ao especificar o tamanho da amostra

Exemplos: Atualizar estatísticas

Metadados de estatísticas

Vistas de catálogo para estatísticas

Funções do sistema para estatísticas

Combinar colunas e funções de estatística numa única vista

Passos seguintes

Recursos adicionais