Início / dba / Perguntas / 282893
Accepted
gotqn
Asked: 2021-01-10 03:30:24 +0800 CST

Índices de repositório de colunas do SQL Server 2019 - manutenção

  • 772

Eu tenho um índice armazenado em colunas clusterizado na tabela usada para log - apenas inserções (mas não inserções em massa). As estatísticas atuais da tabela são:

  • 3541 milhões de linhas
  • 6,6 GB de espaço reservado

Eu vejo esta manhã a seguinte operação via sp_whoisactive:

ALTER INDEX [...] ON [...].[...] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Eu uso a seguinte consulta para verificar quantas linhas por row_group_idnós temos:

SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    partitions.partition_number,
    dm_db_column_store_row_group_physical_stats.row_group_id,
    dm_db_column_store_row_group_physical_stats.total_rows,
    dm_db_column_store_row_group_physical_stats.deleted_rows,
    dm_db_column_store_row_group_physical_stats.state_desc,
    dm_db_column_store_row_group_physical_stats.trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
INNER JOIN sys.indexes
ON indexes.index_id = 
    dm_db_column_store_row_group_physical_stats.index_id
AND indexes.object_id = 
    dm_db_column_store_row_group_physical_stats.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.partitions
ON partitions.partition_number = 
    dm_db_column_store_row_group_physical_stats.partition_number
AND partitions.index_id = indexes.index_id
AND partitions.object_id = tables.object_id

e 3383remamos grupos com 1048576linhas e poucos no final assim:

insira a descrição da imagem aqui

O problema é que estamos usando a edição padrão (no local) e a operação de reconstrução não é realizada online e causa muitos bloqueios.

Eu nunca vi tal questão antes. Algumas semanas atrás, atualizamos de SQL Server 2016 SP1para SQL Server 2019.

Minhas perguntas são:

  • se apenas inserções forem aplicadas, deve ser a operação reorganizee ser mais rápida
  • caso contrário, se aplicarmos o particionamento, por exemplo, com base no ano, pois a tabela é usada para registro, o processo de automação reconstruirá apenas os dados da última partição

insira a descrição da imagem aqui

sql-server t-sql

2 respostas

  1. Best Answer

    Eu tenho um índice armazenado em colunas clusterizado na tabela usada para log - apenas inserções

    se apenas inserções forem aplicadas, a operação deve ser reorganizada e mais rápida

    Você não deve nem se incomodar. Reorganize para um columnstore faz:

    Remove fisicamente as linhas de um rowgroup quando 10% ou mais das linhas foram excluídas logicamente. Os bytes excluídos são recuperados na mídia física. Por exemplo, se um grupo de linhas compactado de 1 milhão de linhas tiver 100 mil linhas excluídas, o SQL Server removerá as linhas excluídas e recomprimirá o grupo de linhas com 900 mil linhas. Ele economiza no armazenamento removendo linhas excluídas.

    Combina um ou mais rowgroups compactados para aumentar as linhas por rowgroup até o máximo de 1.048.576 linhas. Por exemplo, se você importar em massa 5 lotes de 102.400 linhas, obterá 5 rowgroups compactados. Se você executar REORGANIZE, esses rowgroups serão mesclados em 1 rowgroup compactado de tamanho 512.000 linhas. Isso pressupõe que não havia limitações de tamanho ou memória de dicionário.

    Para rowgroups em que 10% ou mais das linhas foram excluídas logicamente, o Mecanismo de Banco de Dados tenta combinar esse rowgroup com um ou mais rowgroups. Por exemplo, o rowgroup 1 é compactado com 500.000 linhas e o rowgroup 21 é compactado com o máximo de 1.048.576 linhas. Rowgroup 21 tem 60% das linhas excluídas, o que deixa 409.830 linhas. O Mecanismo de Banco de Dados favorece a combinação desses dois grupos de linhas para compactar um novo grupo de linhas com 909.830 linhas.

    Considerações específicas para reorganizar um índice columnstore

    Portanto, tudo o que ele fará é combinar os grupos de linhas abertos em um novo grupo de linhas compactado. Então, da próxima vez que você inserir qualquer coisa, você obterá novos rowgroups abertos. Portanto, não há nenhum benefício real em REORGANIZAR em seu cenário.

    Como o JD aconselha, você pode particionar essa tabela se quiser aplicar a compactação de arquivamento apenas a partições mais antigas. Mas sua compressão já está muito boa.

    • 5

  2. A reorganização de seus índices sempre será mais rápida do que uma reconstrução completa e, normalmente, a diferença nos ganhos de desempenho de um em relação ao outro é mais rentável com a reorganização .

    Se você usar Partitioning , poderá especificar quais partições deseja reconstruir e/ou reorganizar . O particionamento é uma boa solução para dividir grandes tabelas/índices para melhorar o desempenho com tarefas de manutenção em seus dados. Então, sim, você pode optar por reconstruir / reorganizar apenas a última partição ou agendar as partições que deseja manter em qualquer intervalo.

    Não tenho certeza se quando você disse que tem "3541 milhões de linhas" você quis dizer 3,5 bilhões de linhas, porque 6,6 GB de espaço reservado é surpreendentemente pequeno para tantas linhas, mas na minha opinião quando você começa a ultrapassar cerca de meio bilhão de linhas em uma única tabela é quando o Particionamento pode ser uma boa opção de implementação.

    • 3

relate perguntas