Início / dba / Perguntas / 268328
Accepted
crokusek
Asked: 2020-06-02 14:36:34 +0800 CST

Como controlar a segmentação min/max data_id em um índice ColumnStore não clusterizado

  • 772

Dada uma tabela simples baseada em linhas sem um PK, mas com um índice clusterizado baseado em linhas da seguinte forma:

create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value])

Em seguida, desejo adicionar um índice de armazenamento de colunas segmentado na mesma ordem que o índice clusterizado acima:

create nonclustered columnstore index CS_IX_PropertyValue on dbo.PropertyValue( 
    PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property

A dica do MaxDop para preservar a ordem veio de: aqui

Em seguida, a consulta a seguir foi usada para relatar o min/max data_id para a coluna PropertyId e o intervalo completo foi relatado em cada um dos 7 segmentos:

create view [Common].[ColumnStoreSegmentationView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose: List ColumnStore table segment min/max of columns.

     Source: https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
             https://dba.stackexchange.com/a/268329/9415

    Modified    By            Description
    ----------  ----------    -----------------------------------------------------------------------------------------
    2020.06.02  crokusek/inet Initial Version 
  ---------------------------------------------------------------------------------------------------------------------*/
select --top 20000000000
       s.Name as SchemaName, 
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p  
    on p.object_id = t.object_id   
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id   
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id 
 --order by s.Name, t.Name, i.Name, c.Name, cs.Segment_Id
GO

Tentei tornar o índice clusterizado único, o que afetou ligeiramente os intervalos relatados, mas ainda não estava aumentando monotonicamente.

Alguma ideia?

Aqui está um link que realizou a segmentação dessa maneira, mas não vejo diferença.

Versão: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

sql-server columnstore

1 respostas

  1. Best Answer

    Isso não tem suporte direto para índices columnstore não clusterizados .

    Funciona para columnstore clusterizado .

    O Azure Synapse Analytics tem suporte ao idioma para fazer isso em uma etapa, por exemplo:

    CREATE CLUSTERED COLUMNSTORE INDEX <index_name>
ON dbo.PropertyValue
ORDER (PropertyId, Value);

    Essa sintaxe ainda não chegou ao produto de caixa do SQL Server, embora esteja disponível em um sinalizador de recurso não documentado, portanto, talvez não esteja longe. No entanto, ainda não funcionará em um índice columnstore não clusterizado.

    Solução Geral

    O melhor que você pode fazer é criar o índice rowstore nãoMAXDOP = 1 clusterizado com , e substituí-lo por um índice columnstore não clusterizado com MAXDOP = 1e DROP_EXISTING = ON.

    Isso não garante a preservação da ordem desejada, mas é altamente provável:

    CREATE NONCLUSTERED INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (DROP_EXISTING = ON, MAXDOP = 1);

    Isso lhe dará a melhor chance de conseguir a eliminação de rowgroup ao filtrar em PropertyId.

    Caso especial

    Quando a ordenação desejada corresponde ao índice clusterizado rowstore (como parece ser o caso na pergunta), não há necessidade de criar primeiro um índice não clusterizado rowstore. A documentação diz :

    Observe que, para o índice columnstore não clusterizado (NCCI), se a tabela rowstore base tiver um índice clusterizado, as linhas já estarão ordenadas. Nesse caso, o índice columnstore não clusterizado resultante será ordenado automaticamente.

    Então, no seu caso, deve ser suficiente executar apenas:

    CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

    Veja esta demonstração do db<>fiddle .

    resultados de violino

    Metadados

    Você pode ver os valores mínimo e máximo para cada grupo de linhas e coluna usando:

    SELECT
    CSS.column_id,
    column_name = C.[name],
    rowgroup_id = CSS.segment_id,
    CSS.min_data_id,
    CSS.max_data_id,
    CSS.row_count
FROM sys.partitions AS P
JOIN sys.column_store_segments AS CSS
    ON CSS.hobt_id = P.hobt_id
JOIN sys.indexes AS I
    ON I.[object_id] = P.[object_id]
    AND I.index_id = P.index_id
JOIN sys.index_columns AS IC
    ON IC.[object_id] = I.[object_id]
    AND IC.index_id = I.index_id
    AND IC.index_column_id = CSS.column_id
JOIN sys.columns AS C
    ON C.[object_id] = P.[object_id]
    AND C.column_id = IC.column_id
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.PropertyValue', N'U')
ORDER BY
    C.column_id,
    CSS.segment_id;
    • 7

relate perguntas