Início / dba / Perguntas / 175682
Accepted
Joe Obbish
Asked: 2017-06-08 07:49:11 +0800 CST

Por que pode levar até 30 segundos para criar um rowgroup CCI simples?

  • 772

Eu estava trabalhando em uma demonstração envolvendo CCIs quando notei que algumas das minhas inserções estavam demorando mais do que o esperado. Definições de tabela para reproduzir:

DROP TABLE IF EXISTS dbo.STG_1048576;
CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_1048576
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

Para os testes estou inserindo todas as 1048576 linhas da tabela de teste. Isso é suficiente para preencher exatamente um rowgroup compactado, desde que ele não seja cortado por algum motivo.

Se eu inserir todos os inteiros mod 17000, leva menos de um segundo:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 17000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 359 ms, tempo decorrido = 364 ms.

No entanto, se eu inserir os mesmos inteiros mod 16000, às vezes leva mais de 30 segundos:

TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

Tempos de execução do SQL Server: tempo de CPU = 32.062 ms, tempo decorrido = 32.511 ms.

Este é um teste repetível que foi feito em várias máquinas. Parece haver um padrão claro no tempo decorrido à medida que o valor do mod muda:

MOD_NUM TIME_IN_MS
1000    2036
2000    3857
3000    5463
4000    6930
5000    8414
6000    10270
7000    12350
8000    13936
9000    17470
10000   19946
11000   21373
12000   24950
13000   28677
14000   31030
15000   34040
16000   37000
17000   563
18000   583
19000   576
20000   584

Se você quiser executar testes, sinta-se à vontade para modificar o código de teste que escrevi aqui .

Não consegui encontrar nada interessante em sys.dm_os_wait_stats para a inserção do mod 16000:

╔════════════════════════════════════╦══════════════╗
║             wait_type              ║ diff_wait_ms ║
╠════════════════════════════════════╬══════════════╣
║ XE_DISPATCHER_WAIT                 ║       164406 ║
║ QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   ║       120002 ║
║ LAZYWRITER_SLEEP                   ║        97718 ║
║ LOGMGR_QUEUE                       ║        97298 ║
║ DIRTY_PAGE_POLL                    ║        97254 ║
║ HADR_FILESTREAM_IOMGR_IOCOMPLETION ║        97111 ║
║ SQLTRACE_INCREMENTAL_FLUSH_SLEEP   ║        96008 ║
║ REQUEST_FOR_DEADLOCK_SEARCH        ║        95001 ║
║ XE_TIMER_EVENT                     ║        94689 ║
║ SLEEP_TASK                         ║        48308 ║
║ BROKER_TO_FLUSH                    ║        48264 ║
║ CHECKPOINT_QUEUE                   ║        35589 ║
║ SOS_SCHEDULER_YIELD                ║           13 ║
╚════════════════════════════════════╩══════════════╝

Por que a inserção de ID % 16000demora muito mais do que a inserção de ID % 17000?

sql-server sql-server-2016

3 respostas

  1. Best Answer

    In many respects, this is expected behaviour. Any set of compression routines will have widely ranging performance depending on input data distribution. We expect to trade data loading speed for storage size and runtime querying performance.

    There is a definite limit to how detailed an answer you're going to get here, since VertiPaq is a proprietary implementation, and the details are a closely-guarded secret. Even so, we do know that VertiPaq contains routines for:

    • Value encoding (scaling and/or translating values to fit in a small number of bits)
    • Dictionary encoding (integer references to unique values)
    • Run Length Encoding (storing runs of repeated values as [value, count] pairs)
    • Bit-packing (storing the stream in as few bits as possible)

    Typically, data will be value or dictionary encoded, then RLE or bit-packing will be applied (or a hybrid of RLE and bit-packing used on different subsections of the segment data). The process of deciding which techniques to apply can involve generating a histogram to help determine how maximum bit savings can be achieved.

    Capturing the slow case with Windows Performance Recorder and analyzing the result with Windows Performance Analyzer, we can see that the vast majority of the execution time is consumed in looking at the clustering of the data, building histograms, and deciding how to partition it for best savings:

    Análise WPA

    The most expensive processing occurs for values that appear at least 64 times in the segment. This is a heuristic to determine when pure RLE is likely to be beneficial. The faster cases result in impure storage e.g. a bit-packed representation, with a larger final storage size. In the hybrid cases, values with 64 or more repetitions are RLE encoded, and the remainder are bit-packed.

    The longest duration occurs when the maximum number of distinct values with 64 repetitions appear in the largest possible segment i.e. 1,048,576 rows with 16,384 sets of values with 64 entries each. Inspection of the code reveals a hard-coded time limit for the expensive processing. This can be configured in other VertiPaq implementations e.g. SSAS, but not in SQL Server as far as I can tell.

    Some insight into the final storage arrangement can be acquired using the undocumented DBCC CSINDEX command. This shows the RLE header and array entries, any bookmarks into the RLE data, and a brief summary of the bit-pack data (if any).

    For more information, see:

    • 12

  2. Não posso dizer exatamente por que esse comportamento está ocorrendo, mas acredito que desenvolvi um bom modelo do comportamento por meio de testes de força bruta. As conclusões a seguir se aplicam apenas ao carregar dados em uma única coluna e com números inteiros muito bem distribuídos.

    Primeiro tentei variar o número de linhas inseridas no CCI usando TOP. Usei ID % 16000para todos os testes. Abaixo está um gráfico comparando as linhas inseridas no tamanho do segmento do grupo de linhas compactado:

    gráfico de topo vs tamanho

    Abaixo está um gráfico de linhas inseridas no tempo de CPU em ms. Observe que o eixo X tem um ponto de partida diferente:

    top vs CPU

    Podemos ver que o tamanho do segmento rowgroup cresce a uma taxa linear e usa uma pequena quantidade de CPU até cerca de 1 M de linhas. Nesse ponto, o tamanho do rowgroup diminui drasticamente e o uso da CPU aumenta drasticamente. Parece que pagamos um preço alto em CPU por essa compactação.

    Ao inserir menos de 1024000 linhas, acabei com um rowgroup aberto no CCI. No entanto, forçar a compressão usando REORGANIZEou REBUILDnão teve efeito no tamanho. Como um aparte, achei interessante que quando usei uma variável para TOPacabei com um rowgroup aberto, mas com RECOMPILEacabei com um rowgroup fechado.

    Em seguida, testei variando o valor do módulo, mantendo o mesmo número de linhas. Aqui está uma amostra dos dados ao inserir 102.400 linhas:

    ╔═══════════╦═════════╦═══════════════╦═════════════╗
║ TOP_VALUE ║ MOD_NUM ║ SIZE_IN_BYTES ║ CPU_TIME_MS ║
╠═══════════╬═════════╬═══════════════╬═════════════╣
║    102400 ║    1580 ║         13504 ║         352 ║
║    102400 ║    1590 ║         13584 ║         316 ║
║    102400 ║    1600 ║         13664 ║         317 ║
║    102400 ║    1601 ║         19624 ║         270 ║
║    102400 ║    1602 ║         25568 ║         283 ║
║    102400 ║    1603 ║         31520 ║         286 ║
║    102400 ║    1604 ║         37464 ║         288 ║
║    102400 ║    1605 ║         43408 ║         273 ║
║    102400 ║    1606 ║         49360 ║         269 ║
║    102400 ║    1607 ║         55304 ║         265 ║
║    102400 ║    1608 ║         61256 ║         262 ║
║    102400 ║    1609 ║         67200 ║         255 ║
║    102400 ║    1610 ║         73144 ║         265 ║
║    102400 ║    1620 ║        132616 ║         132 ║
║    102400 ║    1621 ║        138568 ║         100 ║
║    102400 ║    1622 ║        144512 ║          91 ║
║    102400 ║    1623 ║        150464 ║          75 ║
║    102400 ║    1624 ║        156408 ║          60 ║
║    102400 ║    1625 ║        162352 ║          47 ║
║    102400 ║    1626 ║        164712 ║          41 ║
╚═══════════╩═════════╩═══════════════╩═════════════╝

    Até um valor mod de 1600, o tamanho do segmento rowgroup aumenta linearmente em 80 bytes para cada 10 valores exclusivos adicionais. É uma coincidência interessante que BIGINTtradicionalmente ocupa 8 bytes e o tamanho do segmento aumenta em 8 bytes para cada valor exclusivo adicional. Após um valor de mod de 1600, o tamanho do segmento aumenta rapidamente até estabilizar.

    Também é útil observar os dados ao deixar o valor do módulo igual e alterar o número de linhas inseridas:

    ╔═══════════╦═════════╦═══════════════╦═════════════╗
║ TOP_VALUE ║ MOD_NUM ║ SIZE_IN_BYTES ║ CPU_TIME_MS ║
╠═══════════╬═════════╬═══════════════╬═════════════╣
║    300000 ║    5000 ║        600656 ║         131 ║
║    305000 ║    5000 ║        610664 ║         124 ║
║    310000 ║    5000 ║        620672 ║         127 ║
║    315000 ║    5000 ║        630680 ║         132 ║
║    320000 ║    5000 ║         40688 ║        2344 ║
║    325000 ║    5000 ║         40696 ║        2577 ║
║    330000 ║    5000 ║         40704 ║        2589 ║
║    335000 ║    5000 ║         40712 ║        2673 ║
║    340000 ║    5000 ║         40728 ║        2715 ║
║    345000 ║    5000 ║         40736 ║        2744 ║
║    350000 ║    5000 ║         40744 ║        2157 ║
╚═══════════╩═════════╩═══════════════╩═════════════╝

    Parece que quando o número de linhas inseridas < ~ 64 * o número de valores exclusivos, vemos compactação relativamente baixa (2 bytes por linha para mod <= 65000) e uso de CPU linear baixo. Quando o número inserido de linhas > ~64 * o número de valores exclusivos, vemos uma compactação muito melhor e um uso de CPU mais alto e ainda linear. Há uma transição entre os dois estados que não é fácil para mim modelar, mas pode ser vista no gráfico. Não parece ser verdade que vemos o uso máximo da CPU ao inserir exatamente 64 linhas para cada valor exclusivo. Em vez disso, só podemos inserir um máximo de 1048576 linhas em um rowgroup e vemos muito mais uso e compactação da CPU quando há mais de 64 linhas por valor exclusivo.

    Abaixo está um gráfico de contorno de como o tempo de CPU muda conforme o número de linhas inseridas e o número de linhas únicas mudam. Podemos ver os padrões descritos acima:

    CPU de contorno

    Abaixo está um gráfico de contorno do espaço usado pelo segmento. Depois de um certo ponto, começamos a ver uma compactação muito melhor, conforme descrito acima:

    tamanho do contorno

    Parece que existem pelo menos dois algoritmos de compressão diferentes em ação aqui. Dado o exposto, faz sentido vermos o uso máximo da CPU ao inserir 1048576 linhas. Também faz sentido vermos o maior uso da CPU nesse ponto ao inserir cerca de 16.000 linhas. 1048576 / 64 = 16384.

    Carreguei todos os meus dados brutos aqui caso alguém queira analisá-los.

    Vale mencionar o que acontece com os planos paralelos. Eu só observei esse comportamento com valores distribuídos uniformemente. Ao fazer uma inserção paralela, geralmente há um elemento de aleatoriedade e os encadeamentos geralmente são desequilibrados.

    Coloque 2097152 linhas na tabela de preparo:

    DROP TABLE IF EXISTS STG_2097152;
CREATE TABLE dbo.STG_2097152 (ID BIGINT NOT NULL);
INSERT INTO dbo.STG_2097152 WITH (TABLOCK)
SELECT TOP (2097152) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

    Esta pastilha termina em menos de um segundo e tem baixa compressão:

    DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16000
FROM dbo.STG_2097152 
OPTION (MAXDOP 2);

    Podemos ver o efeito dos threads desbalanceados:

    ╔════════════╦════════════╦══════════════╦═══════════════╗
║ state_desc ║ total_rows ║ deleted_rows ║ size_in_bytes ║
╠════════════╬════════════╬══════════════╬═══════════════╣
║ OPEN       ║      13540 ║            0 ║        311296 ║
║ COMPRESSED ║    1048576 ║            0 ║       2095872 ║
║ COMPRESSED ║    1035036 ║            0 ║       2070784 ║
╚════════════╩════════════╩══════════════╩═══════════════╝

    Existem vários truques que podemos fazer para forçar os threads a serem balanceados e terem a mesma distribuição de linhas. Aqui está um deles:

    DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT FLOOR(0.5 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  % 15999
FROM dbo.STG_2097152
OPTION (MAXDOP 2)

    Escolher um número ímpar para o módulo é importante aqui. O SQL Server verifica a tabela de preparo em série, calcula o número da linha e usa a distribuição round robin para colocar as linhas em threads paralelos. Isso significa que vamos acabar com threads perfeitamente equilibrados.

    saldo 1

    The insert takes around 40 seconds which is similar to the serial insert. We get nicely compressed rowgroups:

    ╔════════════╦════════════╦══════════════╦═══════════════╗
║ state_desc ║ total_rows ║ deleted_rows ║ size_in_bytes ║
╠════════════╬════════════╬══════════════╬═══════════════╣
║ COMPRESSED ║    1048576 ║            0 ║        128568 ║
║ COMPRESSED ║    1048576 ║            0 ║        128568 ║
╚════════════╩════════════╩══════════════╩═══════════════╝

    We can get the same results by inserting data from the original staging table:

    DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT t.ID % 16000 ID
FROM  (
    SELECT TOP (2) ID 
    FROM (SELECT 1 ID UNION ALL SELECT 2 ) r
) s
CROSS JOIN dbo.STG_1048576 t
OPTION (MAXDOP 2, NO_PERFORMANCE_SPOOL);

    Here round robin distribution is used for the derived table s so one scan of the table is done on each parallel thread:

    equilibrado 2

    In conclusion, when inserting evenly distributed integers you can see very high compression when each unique integer appears more than 64 times. This may be due to a different compression algorithm being used. There can be a high cost in CPU to achieve this compression. Small changes in the data can lead to dramatic differences in the size of the compressed rowgroup segment. I suspect that seeing the worst case (from a CPU perspective) will be uncommon in the wild, at least for this data set. It's even harder to see when doing parallel inserts.

    • 9

  3. Acredito que isso tenha a ver com as otimizações internas da compactação para as tabelas de coluna única e com o número mágico dos 64 KB ocupados pelo dicionário.

    Exemplo: se você executar com o MOD 16600 , o resultado final do tamanho do Row Group será de 1,683 MB , enquanto a execução do MOD 17000 fornecerá um Row Group com o tamanho de 2,001 MB .

    Agora, dê uma olhada nos dicionários criados (você pode usar minha biblioteca CISL para isso, você precisará da função cstore_GetDictionaries, ou alternativamente vá e consulte sys.column_store_dictionaries DMV):

    (MOD 16600) 61 KB

    insira a descrição da imagem aqui

    (MOD 17000) 65 KB

    insira a descrição da imagem aqui

    Engraçado, se você adicionar outra coluna à sua tabela, e vamos chamá-la de REALID :

    DROP TABLE IF EXISTS dbo.CCI_BIGINT;
CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, REALID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);

    Recarregue os dados para o MOD 16600:

    TRUNCATE TABLE dbo.CCI_BIGINT;

INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
SELECT ID % 16600, ID
FROM dbo.STG_1048576
OPTION (MAXDOP 1);

    Desta vez, a execução será rápida, porque o otimizador decidirá não sobrecarregar e compactar demais:

    select column_id, segment_id, cast(sum(seg.on_disk_size) / 1024. / 1024 as Decimal(8,3) ) as SizeInMB
    from sys.column_store_segments seg
        inner join sys.partitions part
            on seg.hobt_id = part.hobt_id 
    where object_id = object_id('dbo.CCI_BIGINT')
    group by column_id, segment_id;

    Mesmo que haja uma pequena diferença entre os tamanhos dos grupos de linhas, ela será insignificante (2,000 (MOD 16600) vs 2,001 (MOD 17000))

    Para este cenário, o dicionário do MOD 16000 será maior que o do primeiro cenário com 1 coluna (0,63 vs 0,61).

    • 8

relate perguntas