Início / dba / Perguntas / 159790
Accepted
Pரதீப்
Asked: 2017-01-03 20:13:25 +0800 CST

Como o número de etapas do Histograma é decidido em Estatística

  • 772

Como o número de etapas do histograma é decidido em Estatísticas no SQL Server?

Por que é restrito a 200 etapas, embora minha coluna de chave tenha mais de 200 valores distintos? Existe algum fator decisivo?

Demonstração

Definição de esquema

CREATE TABLE histogram_step
  (
     id   INT IDENTITY(1, 1),
     name VARCHAR(50),
     CONSTRAINT pk_histogram_step PRIMARY KEY (id)
  )

Inserindo 100 registros na minha tabela

INSERT INTO histogram_step
            (name)
SELECT TOP 100 name
FROM   sys.syscolumns

Atualizando e verificando as estatísticas

UPDATE STATISTICS histogram_step WITH fullscan

DBCC show_statistics('histogram_step', pk_histogram_step)

Etapas do histograma:

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            1 |          0 |       1 |                   0 |              1 |
|            3 |          1 |       1 |                   1 |              1 |
|            5 |          1 |       1 |                   1 |              1 |
|            7 |          1 |       1 |                   1 |              1 |
|            9 |          1 |       1 |                   1 |              1 |
|           11 |          1 |       1 |                   1 |              1 |
|           13 |          1 |       1 |                   1 |              1 |
|           15 |          1 |       1 |                   1 |              1 |
|           17 |          1 |       1 |                   1 |              1 |
|           19 |          1 |       1 |                   1 |              1 |
|           21 |          1 |       1 |                   1 |              1 |
|           23 |          1 |       1 |                   1 |              1 |
|           25 |          1 |       1 |                   1 |              1 |
|           27 |          1 |       1 |                   1 |              1 |
|           29 |          1 |       1 |                   1 |              1 |
|           31 |          1 |       1 |                   1 |              1 |
|           33 |          1 |       1 |                   1 |              1 |
|           35 |          1 |       1 |                   1 |              1 |
|           37 |          1 |       1 |                   1 |              1 |
|           39 |          1 |       1 |                   1 |              1 |
|           41 |          1 |       1 |                   1 |              1 |
|           43 |          1 |       1 |                   1 |              1 |
|           45 |          1 |       1 |                   1 |              1 |
|           47 |          1 |       1 |                   1 |              1 |
|           49 |          1 |       1 |                   1 |              1 |
|           51 |          1 |       1 |                   1 |              1 |
|           53 |          1 |       1 |                   1 |              1 |
|           55 |          1 |       1 |                   1 |              1 |
|           57 |          1 |       1 |                   1 |              1 |
|           59 |          1 |       1 |                   1 |              1 |
|           61 |          1 |       1 |                   1 |              1 |
|           63 |          1 |       1 |                   1 |              1 |
|           65 |          1 |       1 |                   1 |              1 |
|           67 |          1 |       1 |                   1 |              1 |
|           69 |          1 |       1 |                   1 |              1 |
|           71 |          1 |       1 |                   1 |              1 |
|           73 |          1 |       1 |                   1 |              1 |
|           75 |          1 |       1 |                   1 |              1 |
|           77 |          1 |       1 |                   1 |              1 |
|           79 |          1 |       1 |                   1 |              1 |
|           81 |          1 |       1 |                   1 |              1 |
|           83 |          1 |       1 |                   1 |              1 |
|           85 |          1 |       1 |                   1 |              1 |
|           87 |          1 |       1 |                   1 |              1 |
|           89 |          1 |       1 |                   1 |              1 |
|           91 |          1 |       1 |                   1 |              1 |
|           93 |          1 |       1 |                   1 |              1 |
|           95 |          1 |       1 |                   1 |              1 |
|           97 |          1 |       1 |                   1 |              1 |
|           99 |          1 |       1 |                   1 |              1 |
|          100 |          0 |       1 |                   0 |              1 |
+--------------+------------+---------+---------------------+----------------+

Como podemos ver, existem 53 passos no histograma.

Novamente inserindo alguns milhares de registros

INSERT INTO histogram_step
            (name)
SELECT TOP 10000 b.name
FROM   sys.syscolumns a
       CROSS JOIN sys.syscolumns b

Atualizando e verificando as estatísticas

UPDATE STATISTICS histogram_step WITH fullscan

DBCC show_statistics('histogram_step', pk_histogram_step)

Agora as etapas do histograma são reduzidas para 4 etapas

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            1 |          0 |       1 |                   0 |              1 |
|        10088 |      10086 |       1 |               10086 |              1 |
|        10099 |         10 |       1 |                  10 |              1 |
|        10100 |          0 |       1 |                   0 |              1 |
+--------------+------------+---------+---------------------+----------------+

Novamente inserindo alguns milhares de registros

INSERT INTO histogram_step
            (name)
SELECT TOP 100000 b.name
FROM   sys.syscolumns a
       CROSS JOIN sys.syscolumns b

Atualizando e verificando as estatísticas

UPDATE STATISTICS histogram_step WITH fullscan

DBCC show_statistics('histogram_step', pk_histogram_step)

Agora as etapas do histograma são reduzidas para 3 etapas

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            1 |          0 |       1 |                   0 |              1 |
|       110099 |     110097 |       1 |              110097 |              1 |
|       110100 |          0 |       1 |                   0 |              1 |
+--------------+------------+---------+---------------------+----------------+

Alguém pode me dizer como essas etapas são decididas?

sql-server statistics

1 respostas

  1. Best Answer

    Vou limitar esta postagem a discutir estatísticas de coluna única porque ela já será bastante longa e você está interessado em saber como o SQL Server agrupa os dados em etapas de histograma. Para estatísticas de várias colunas, o histograma é criado apenas na coluna principal.

    Quando o SQL Server determina que uma atualização de estatísticas é necessária, ele inicia uma consulta oculta que lê todos os dados de uma tabela ou uma amostra dos dados da tabela. Você pode visualizar essas consultas com eventos estendidos. Há uma função chamada StatManno SQL Server que está envolvida na criação dos histogramas. Para objetos de estatísticas simples, existem pelo menos dois tipos diferentes de StatManconsultas (existem consultas diferentes para atualizações rápidas de estatísticas e suspeito que o recurso de estatísticas incrementais em tabelas particionadas também usa uma consulta diferente).

    O primeiro apenas pega todos os dados da tabela sem nenhuma filtragem. Você pode ver isso quando a mesa é muito pequena ou coleta estatísticas com a FULLSCANopção:

    CREATE TABLE X_SHOW_ME_STATMAN (N INT);
CREATE STATISTICS X_STAT_X_SHOW_ME_STATMAN ON X_SHOW_ME_STATMAN (N);

-- after gathering stats with 1 row in table
SELECT StatMan([SC0]) FROM
(
    SELECT TOP 100 PERCENT [N] AS [SC0] 
    FROM [dbo].[X_SHOW_ME_STATMAN] WITH (READUNCOMMITTED)
    ORDER BY [SC0] 
) AS _MS_UPDSTATS_TBL 
OPTION (MAXDOP 16);

    O SQL Server escolhe o tamanho da amostra automática com base no tamanho da tabela (acho que é o número de linhas e páginas na tabela). Se uma tabela for muito grande, o tamanho da amostra automática cairá abaixo de 100%. Aqui está o que eu tenho para a mesma tabela com 1 milhão de linhas:

    -- after gathering stats with 1 M rows in table
SELECT StatMan([SC0], [SB0000]) FROM 
(
    SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] 
    FROM 
    (
        SELECT [N] AS [SC0] 
        FROM [dbo].[X_SHOW_ME_STATMAN] TABLESAMPLE SYSTEM (6.666667e+001 PERCENT) WITH (READUNCOMMITTED) 
    ) AS _MS_UPDSTATS_TBL_HELPER 
    ORDER BY [SC0], [SB0000] 
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1);

    TABLESAMPLEestá documentado, mas StatMan e step_direction não estão. aqui o SQL Server amostra cerca de 66,6% dos dados da tabela para criar o histograma. O que isso significa é que você pode obter um número diferente de etapas do histograma ao atualizar as estatísticas (sem FULLSCAN) nos mesmos dados. Nunca observei isso na prática, mas não vejo porque não seria possível.

    Vamos fazer alguns testes em dados simples para ver como as estatísticas mudam ao longo do tempo. Abaixo está um código de teste que escrevi para inserir inteiros sequenciais em uma tabela, reunir estatísticas após cada inserção e salvar informações sobre as estatísticas em uma tabela de resultados. Vamos começar inserindo apenas 1 linha por vez até 10.000. Cama de teste:

    DECLARE
@stats_id INT,
@table_object_id INT,
@rows_per_loop INT = 1,
@num_of_loops INT = 10000,
@loop_num INT;

BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE X_STATS_RESULTS;

    SET @table_object_id = OBJECT_ID ('X_SEQ_NUM');
    SELECT @stats_id = stats_id FROM sys.stats
    WHERE OBJECT_ID = @table_object_id
    AND name = 'X_STATS_SEQ_INT_FULL';

    SET @loop_num = 0;
    WHILE @loop_num < @num_of_loops
    BEGIN
        SET @loop_num = @loop_num + 1;

        INSERT INTO X_SEQ_NUM WITH (TABLOCK)
        SELECT @rows_per_loop * (@loop_num - 1) + N FROM dbo.GetNums(@rows_per_loop);

        UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN; -- can comment out FULLSCAN as needed

        INSERT INTO X_STATS_RESULTS WITH (TABLOCK)
        SELECT 'X_STATS_SEQ_INT_FULL', @rows_per_loop * @loop_num, rows_sampled, steps 
        FROM sys.dm_db_stats_properties(@table_object_id, @stats_id);
        END;
END;

    Para esses dados, o número de etapas do histograma aumenta rapidamente para 200 (primeiro atinge o número máximo de etapas com 397 linhas), permanece em 199 ou 200 até que 1485 linhas estejam na tabela e depois diminui lentamente até que o histograma tenha apenas 3 ou 4 degraus. Aqui está um gráfico de todos os dados:

    primeiro gráfico

    Aqui está o histograma para 10k linhas:

    RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1               0           1       0                   1
9999            9997        1       9997                1
10000           0           1       0                   1

    É um problema que o histograma tenha apenas 3 etapas? Parece que a informação é preservada do nosso ponto de vista. Observe que, como o tipo de dados é um INTEGER, podemos descobrir quantas linhas existem na tabela para cada número inteiro de 1 a 10000. Normalmente, o SQL Server também pode descobrir isso, embora haja alguns casos em que isso não funciona muito bem . Veja este post SE para um exemplo disso.

    O que você acha que acontecerá se excluirmos uma única linha da tabela e atualizarmos as estatísticas? Idealmente, obteríamos outra etapa do histograma para mostrar que o inteiro ausente não está mais na tabela.

    DELETE FROM X_SEQ_NUM
WHERE X_NUM  = 1000;

UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN;

DBCC SHOW_STATISTICS ('X_SEQ_NUM', 'X_STATS_SEQ_INT_FULL'); -- still 3 steps

DELETE FROM X_SEQ_NUM
WHERE X_NUM  IN (2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);

UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN;

DBCC SHOW_STATISTICS ('X_SEQ_NUM', 'X_STATS_SEQ_INT_FULL'); -- still 3 steps

    Isso é um pouco decepcionante. Se estivéssemos construindo um histograma manualmente, adicionaríamos um passo para cada valor ausente. O SQL Server está usando um algoritmo de propósito geral, portanto, para alguns conjuntos de dados, podemos criar um histograma mais adequado do que o código que ele usa. Obviamente, a diferença prática entre obter 0 ou 1 linha de uma tabela é muito pequena. Obtenho os mesmos resultados ao testar com 20.000 linhas, cada número inteiro com 2 linhas na tabela. O histograma não ganha etapas à medida que excluo os dados.

    RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1               0           2       0                   1
9999            19994       2       9997                2
10000           0           2       0                   1

    Se eu testar com 1 milhão de linhas com cada número inteiro tendo 100 linhas na tabela, obtenho resultados um pouco melhores, mas ainda posso construir um histograma melhor manualmente.

    truncate table X_SEQ_NUM;

BEGIN TRANSACTION;
INSERT INTO X_SEQ_NUM WITH (TABLOCK)
SELECT N FROM dbo.GetNums(10000);
GO 100
COMMIT TRANSACTION;

UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN;

DBCC SHOW_STATISTICS ('X_SEQ_NUM', 'X_STATS_SEQ_INT_FULL'); -- 4 steps

DELETE FROM X_SEQ_NUM
WHERE X_NUM  = 1000;

UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN;

DBCC SHOW_STATISTICS ('X_SEQ_NUM', 'X_STATS_SEQ_INT_FULL'); -- now 5 steps with a RANGE_HI_KEY of 998 (?)

DELETE FROM X_SEQ_NUM
WHERE X_NUM  IN (2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);

UPDATE STATISTICS X_SEQ_NUM X_STATS_SEQ_INT_FULL WITH FULLSCAN;

DBCC SHOW_STATISTICS ('X_SEQ_NUM', 'X_STATS_SEQ_INT_FULL'); -- still 5 steps

    Histograma final:

    RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1               0           100     0                   1
998             99600       100     996                 100
3983            298100      100     2981                100
9999            600900      100     6009                100
10000           0           100     0                   1

    Vamos testar mais com inteiros sequenciais, mas com mais linhas na tabela. Observe que, para tabelas muito pequenas, especificar manualmente um tamanho de amostra não terá efeito, portanto adicionarei 100 linhas em cada inserção e reunirei estatísticas a cada vez até 1 milhão de linhas. Vejo um padrão semelhante ao anterior, exceto que quando chego a 637300 linhas na tabela, não faço mais amostra de 100% das linhas na tabela com a taxa de amostragem padrão. À medida que ganho linhas, o número de etapas do histograma aumenta. Talvez seja porque o SQL Server acaba com mais lacunas nos dados conforme o número de linhas sem amostra na tabela aumenta. Não atinjo 200 passos mesmo com 1 milhão de linhas, mas se continuar adicionando linhas, espero chegar lá e, eventualmente, começar a descer.

    gráfico 2

    The X-axis is the number of rows in the table. As the number of rows increases the rows sampled varies a bit and doesn't go over 650k.

    Now let's do some simple tests with VARCHAR data.

    CREATE TABLE X_SEQ_STR (X_STR VARCHAR(5));
CREATE STATISTICS X_SEQ_STR ON X_SEQ_STR(X_STR);

    Here I'm inserting 200 numbers (as strings) along with NULL.

    INSERT INTO X_SEQ_STR
SELECT N FROM dbo.GetNums(200)
UNION ALL
SELECT NULL;

UPDATE STATISTICS X_SEQ_STR X_SEQ_STR ;

DBCC SHOW_STATISTICS ('X_SEQ_STR', 'X_SEQ_STR'); -- 111 steps, RANGE_ROWS is 0 or 1 for all steps

    Note that NULL always gets its own histogram step when it is found in the table. SQL Server could have given me exactly 201 steps to preserve all information but it did not do that. Technically information is lost because '1111' sorts between '1' and '2' for example.

    Now let's try inserting different characters instead of just integers:

    truncate table X_SEQ_STR;

INSERT INTO X_SEQ_STR
SELECT CHAR(10 + N) FROM dbo.GetNums(200)
UNION ALL
SELECT NULL;

UPDATE STATISTICS X_SEQ_STR X_SEQ_STR ;

DBCC SHOW_STATISTICS ('X_SEQ_STR', 'X_SEQ_STR'); -- 95 steps, RANGE_ROWS is 0 or 1 or 2

    No real difference from the last test.

    Now let's try inserting characters but putting different numbers of each character in the table. For example, CHAR(11) has 1 row, CHAR(12) has 2 rows, etc.

    truncate table X_SEQ_STR;

DECLARE
@loop_num INT;

BEGIN
    SET NOCOUNT ON;

    SET @loop_num = 0;
    WHILE @loop_num < 200
    BEGIN
        SET @loop_num = @loop_num + 1;

        INSERT INTO X_SEQ_STR WITH (TABLOCK)
        SELECT CHAR(10 + @loop_num) FROM dbo.GetNums(@loop_num);
    END;
END;

UPDATE STATISTICS X_SEQ_STR X_SEQ_STR ;

DBCC SHOW_STATISTICS ('X_SEQ_STR', 'X_SEQ_STR'); -- 148 steps, most with RANGE_ROWS of 0

    As before I still don't get exactly 200 histogram steps. However, many of the steps have RANGE_ROWS of 0.

    For the final test, I'm going to insert a random string of 5 characters in each loop and gather stats each time. Here's the code the random string:

    char((rand()*25 + 65))+char((rand()*25 + 65))+char((rand()*25 + 65))+char((rand()*25 + 65))+char((rand()*25 + 65))

    Here is the graph of rows in table vs histogram steps: gráfico 3

    Note that the number of steps doesn't dip below 100 once it starts going up and down. I've heard from somewhere (but can't source it right now) that the SQL Server histogram building algorithm combines histogram steps as it runs out of room for them. So you can end up with drastic changes in the number of steps just by adding a little data. Here's one sample of the data that I found interesting:

    ROWS_IN_TABLE   ROWS_SAMPLED    STEPS
36661           36661           133
36662           36662           143
36663           36663           143
36664           36664           141
36665           36665           138

    Even when sampling with FULLSCAN, adding a single row can increase the number of steps by 10, keep it constant, then decrease it by 2, then decrease it by 3.

    What can we summarize from all of this? I can't prove any of this, but these observations appear to hold true:

    • SQL Server uses a general use algorithm to create the histograms. For some data distributions it may be possible to create a more complete representation of the data by hand.
    • If there is NULL data in the table and the stats query finds it then that NULL data always gets its own histogram step.
    • The minimum value found in the table gets its own histogram step with RANGE_ROWS = 0.
    • The maximum value found in the table will be the final RANGE_HI_KEY in the table.
    • As SQL Server samples more data it may need to combine existing steps to make room for the new data that it finds. If you look at enough histograms you may see common values repeat for DISTINCT_RANGE_ROWS or RANGE_ROWS. For example, 255 shows up a bunch of times for RANGE_ROWS and DISTINCT_RANGE_ROWS for the final test case here.
    • For simple data distributions you may see SQL Server combine sequential data into one histogram step that causes no loss of information. However when adding gaps to the data the histogram may not adjust in the way you would hope.

    When is all of this a problem? It's a problem when a query performs poorly due to a histogram that is unable to represent the data distribution in a way for the query optimizer to make good decisions. I think there's a tendency to think that having more histogram steps is always better and for there to be consternation when SQL Server generates a histogram on millions of rows or more but doesn't use exactly 200 or 201 histogram steps. However, I have seen plenty of stats problems even when the histogram has 200 or 201 steps. We don't have any control over how many histogram steps that SQL Server generates for a statistics object so I wouldn't worry about it. However, there are some steps that you can take when you experience poor performing queries caused by stats issues. I will give an extremely brief overview.

    Gathering statistics in full can help in some cases. For very large tables the auto sample size may be less than 1% of the rows in the table. Sometimes that can lead to bad plans depending on the data disruption in the column. Microsofts's documentation for CREATE STATISTICS and UPDATE STATISTICS says as much:

    SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. In most situations, it is not necessary to specify SAMPLE because the query optimizer already uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

    For most workloads, a full scan is not required, and default sampling is adequate. However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan.

    In some cases creating filtered statistics can help. You may have a column with skewed data and many different distinct values. If there are certain values in the data that are commonly filtered on you can create a statistics histogram for just those common values. The query optimizer can use the statistics defined on a smaller range of data instead of the statistics defined on all column values. You still are not guaranteed to get 200 steps in the histogram, but if you create the filtered stats on just one value you will a histogram step that value.

    Usar uma exibição particionada é uma maneira de obter efetivamente mais de 200 etapas para uma tabela. Suponha que você possa facilmente dividir uma tabela grande em uma tabela por ano. Você cria uma UNION ALLexibição que combina todas as tabelas anuais. Cada tabela terá seu próprio histograma. Observe que as novas estatísticas incrementais introduzidas no SQL Server 2014 permitem apenas que as atualizações de estatísticas sejam mais eficientes. O otimizador de consulta não usará as estatísticas criadas por partição.

    Existem muitos outros testes que podem ser executados aqui, então eu o encorajo a experimentar. Eu fiz este teste no SQL Server 2014 express, então realmente não há nada que o impeça.

    • 17

relate perguntas