Partilhar via

Quais são as novidades nos índices columnstore

  • Artigo

Aplica-se a:SQL ServerBase de Dados SQL do AzureInstância Gerida do Azure SQLAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de Dados SQL no Microsoft Fabric

Saiba quais recursos columnstore estão disponíveis para cada versão do SQL Server e as versões mais recentes do Banco de Dados SQL, do Azure Synapse Analytics e do Analytics Platform System (PDW).

Resumo de funcionalidades para lançamentos de produtos

Esta tabela resume as principais características dos índices columnstore e os produtos em que estão disponíveis.

Recurso de índices Columnstore SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) Banco de Dados SQL do Azure2 e Instância Gerenciada SQL do AzureAUTD Pool SQL dedicado do Azure Synapse Analytics
Execução em modo batch para consultas multi-threaded3 Sim Sim Sim Sim Sim Sim Sim Sim
Execução em modo batch para consultas de thread único Sim Sim Sim Sim Sim Sim
Opção de compressão de arquivo Sim Sim Sim Sim Sim Sim Sim
Isolamento de snapshot e isolamento de snapshot com leitura confirmada Sim Sim Sim Sim Sim Sim
Especifique o índice columnstore ao criar uma tabela Sim Sim Sim Sim Sim Sim
O Always On suporta índices de armazenamento em colunas Sim Sim Sim Sim Sim Sim Sim Sim
O secundário legível Always On suporta o índice columnstore não clusterizado somente leitura Sim Sim Sim Sim Sim Sim Sim Sim
O secundário legível Always On suporta índices columnstore atualizáveis Sim Sim Sim Sim
Índice columnstore não clusterizado só de leitura em heap ou árvore B Sim Sim Sim, 4 Sim, 4 Sim, 4 Sim, 4 Sim, 4 Sim, 4
Índice de armazenamento por coluna não agrupado e atualizável em heap ou árvore B Sim Sim Sim Sim Sim Sim
Índices adicionais de árvore B permitidos em uma pilha ou árvore B que tenha um índice columnstore não clusterizado Sim Sim Sim Sim Sim Sim Sim Sim
Índice clusterizado e atualizável de columnstore Sim Sim Sim Sim Sim Sim Sim
Índice de árvore B num índice columnstore clusterizado Sim Sim Sim Sim Sim Sim
Índice Columnstore em uma tabela com otimização de memória Sim Sim Sim Sim Sim Sim
A definição do índice columnstore não clusterizado suporta a utilização de uma condição filtrada Sim Sim Sim Sim Sim Sim
Opção de atraso de compressão para índices columnstore em CREATE TABLE e ALTER TABLE Sim Sim Sim Sim Sim Sim
Suporte para o tipo nvarchar(max) Sim Sim Sim Sim sem 5
O índice Columnstore pode ter uma coluna computada não persistente Sim Sim Sim
Suporte para mesclagem em segundo plano do movimentador de tuplas Sim Sim Sim Sim
Índices columnstore agrupados ordenados Sim Sim Sim
Índices columnstore não clusterizados ordenados Sim
Criação e reconstrução do índice columnstore online Sim Sim
Criar e reconstruir o índice columnstore ordenado online Sim

1 Para o SQL Server 2016 (13.x) SP1 e versões posteriores, os índices columnstore estão disponíveis em todas as edições. Para o SQL Server 2016 (13.x) (antes do SP1) e versões anteriores, os índices columnstore só estão disponíveis na Enterprise Edition.
2 Para o Banco de Dados SQL do Azure, os índices columnstore estão disponíveis nas camadas DTU Premium, DTU Standard - S3 e superior e todas as camadas vCore. 3 O grau de paralelismo (DOP) para operações de em modo de lote é limitado a 2 para SQL Server Standard Edition e 1 para SQL Server Web e Express Editions. Essa limitação refere-se a índices columnstore criados em tabelas baseadas em disco e tabelas com otimização de memória.
4 Para criar um índice columnstore não clusterizado somente leitura, armazene o índice em um grupo de arquivos somente leitura.
5 Não há suporte em pools SQL dedicados, mas é suportado em pool SQL sem servidor.
AUTD Aplica-se à Instância Gerida do Azure SQL configurada com a política de atualização Sempre Atualizada.

SQL Server 2022 (16.x)

O SQL Server 2022 (16.x) adicionou estes recursos:

  • Os índices columnstore agrupados ordenados melhoram o desempenho de consultas com base em predicados de coluna ordenados. Os índices columnstore ordenados podem melhorar o desempenho ignorando segmentos de dados por completo. Isso pode reduzir drasticamente a E/S necessária para concluir consultas em dados de columnstore. Para mais informações, consulte a eliminação do segmento. Para obter mais informações, consulte CREATE COLUMNSTORE INDEX e Ajuste de desempenho com índices columnstore ordenados.
  • A eliminação de grupos de linhas de um columnstore clusterizado para strings usando pushdown de predicado utiliza valores limítrofes para otimizar pesquisas de cadeias de caracteres. Todos os índices columnstore se beneficiam da eliminação aprimorada de segmentos por tipo de dados. A partir do SQL Server 2022 (16.x), estas capacidades de eliminação de segmento se estendem aos tipos de dados string, binário e GUID, e ao tipo de dados datetimeoffset para escalas superiores a dois. Anteriormente, a eliminação do segmento columnstore aplicava-se apenas aos tipos de dados numéricos, de data e hora, e o datetimeoffset tipo de dados com escala menor ou igual a dois. Depois de atualizar para uma versão do SQL Server que oferece suporte à eliminação de segmento min/max (SQL Server 2022 (16.x) e versões posteriores), o índice columnstore não se beneficia desse recurso até que seja reconstruído usando ALTER INDEX REBUILD ou CREATE INDEX WITH (DROP_EXISTING = ON).
  • Eliminação de grupos de linhas Columnstore para o prefixo de predicados LIKE, como, por exemplo, o column LIKE 'string%'. A eliminação de segmentos não é suportada para a utilização de LIKE sem prefixo, tal como column LIKE '%string'.
  • Para obter mais informações sobre recursos adicionados, consulte Novidades no SQL Server 2022.

SQL Server 2019 (15.x)

O SQL Server 2019 (15.x) adiciona estes novos recursos:

Funcional

A partir do SQL Server 2019 (15.x), o movimentador de tuplas é auxiliado por uma tarefa de mesclagem em segundo plano que comprime automaticamente pequenos grupos de linhas delta abertos que existem há algum tempo, conforme um limite interno determina, ou mescla grupos de linhas comprimidos das quais foram excluídas um grande número de linhas. Anteriormente, uma operação de reorganização de índice era necessária para mesclar grupos de linhas com dados parcialmente excluídos. Isso melhora a qualidade do índice columnstore ao longo do tempo.

SQL Server 2017 (14.x)

O SQL Server 2017 (14.x) adiciona esses novos recursos.

Funcional

  • O SQL Server 2017 (14.x) oferece suporte a colunas computadas não persistentes em índices columnstore clusterizados. Não há suporte para colunas computadas persistentes em índices columnstore clusterizados. Não é possível criar um índice columnstore não clusterizado em uma coluna computada.

SQL Server 2016 (13.x)

O SQL Server 2016 (13.x) adiciona aprimoramentos importantes para melhorar o desempenho e a flexibilidade dos índices columnstore. Essas melhorias aprimoram os cenários de armazenamento de dados e permitem análises operacionais em tempo real.

Funcional

  • Uma tabela rowstore pode ter um índice columnstore não clusterizado atualizável. Anteriormente, o índice columnstore não clusterizado era de apenas leitura.

  • A definição do índice de armazenamento em coluna não clusterizado oferece suporte ao uso de uma condição filtrada. Para minimizar o impacto no desempenho da adição de um índice columnstore em uma tabela OLTP, use uma condição filtrada para criar um índice columnstore não clusterizado somente nos dados frios de sua carga de trabalho operacional.

  • Uma tabela na memória pode ter um índice de columnstore. Você pode criá-la quando a tabela for criada ou adicioná-la mais tarde com ALTER TABLE (Transact-SQL). Anteriormente, apenas uma tabela baseada em disco podia ter um índice colunar.

  • Um índice columnstore clusterizado pode ter um ou mais índices rowstore não clusterizados. Anteriormente, o índice columnstore não suportava índices não clusterizados. O SQL Server mantém automaticamente os índices não clusterizados para operações DML.

  • Suporte para chaves primárias e chaves estrangeiras utilizando um índice B-tree para aplicar estas restrições num índice columnstore clusterizado.

  • Os índices Columnstore têm uma opção de adiamento da compressão que minimiza o impacto da carga de trabalho transacional na análise operacional em tempo real. Essa opção permite alterar frequentemente as linhas para estabilizar antes de compactá-las no columnstore. Para obter detalhes, consulte CREATE COLUMNSTORE INDEX (Transact-SQL) e Introdução ao Columnstore para análise operacional em tempo real.

Desempenho para o nível de compatibilidade de banco de dados 120 ou 130

  • Os índices Columnstore suportam o nível de isolamento de instantâneo confirmado (Read Committed Snapshot Isolation Level, RCSI) e o isolamento de instantâneo (Snapshot Isolation, SI). Isso permite consultas de análise transacionais consistentes sem bloqueios.

  • Columnstore suporta desfragmentação de índice removendo linhas excluídas sem a necessidade de reconstruir explicitamente o índice. A instrução ALTER INDEX ... REORGANIZE remove linhas eliminadas, com base numa política definida internamente, do columnstore numa operação online

  • Os índices Columnstore podem ser acessíveis numa réplica secundária legível de Always On. Você pode melhorar o desempenho da análise operacional transferindo consultas de análise para uma réplica secundária Always On.

  • Aggregate Pushdown calcula as funções agregadas MIN, MAX, SUM, COUNTe AVG durante escanes de tabela quando o tipo de dados não usa mais de 8 bytes e não é um tipo de dados string. O suporte a "pushdown" agregado é fornecido com ou sem a cláusula GROUP BY tanto para índices columnstore agrupados como para índices columnstore não agrupados. No SQL Server, esse aprimoramento é reservado para a edição Enterprise.

  • String Predicate pushdown acelera consultas que comparam strings do tipo VARCHAR/CHAR ou NVARCHAR/NCHAR. Isso se aplica aos operadores de comparação comuns e inclui operadores como LIKE que usam filtros de bitmap. Isso funciona com todas as classificações suportadas. No SQL Server, esse aprimoramento é reservado para a edição Enterprise.

  • Aprimoramentos para operações em modo de lote aproveitando os recursos de hardware baseados em vetor. O Mecanismo de Banco de Dados deteta o nível de suporte da CPU para as extensões de hardware AVX 2 (Advanced Vetor Extensions) e SSE 4 (Streaming SIMD Extensions 4) e as usa, se suportadas. No SQL Server, esse aprimoramento é reservado para a edição Enterprise.

Desempenho para o nível de compatibilidade de banco de dados 130

  • Novo suporte de execução em modo batch para consultas usando qualquer uma destas operações:

    • SORT
    • Agregados com múltiplas funções distintas. Alguns exemplos: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG, STDEV/STDEVP
    • Funções de agregação de janelas: COUNT, COUNT_BIG, SUM, AVG, MIN, MAXe CLR
    • Agregações definidas pelo usuário da janela: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARPe GROUPING
    • Funções analíticas de agregado de janelas: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST, e PERCENT_RANK

  • Consultas de thread único executadas sob MAXDOP 1 ou com um plano de consulta serial são executadas em modo de lote. Anteriormente, apenas consultas multiencadeadas eram executadas com execução em lote.

  • As consultas em tabelas otimizadas para memória podem ter planos paralelos no modo SQL InterOp, tanto ao aceder a dados em rowstore quanto num índice columnstore.

Capacidade de suporte

Estas visualizações do sistema são novas para columnstore:

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

Esses DMVs baseados em OLTP na memória contêm atualizações para columnstore:

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

Limitações

  • Para tabelas na memória, um índice columnstore deve incluir todas as colunas; O índice columnstore não pode ter uma condição filtrada.
  • Para tabelas na memória, as consultas em índices columnstore são executadas somente no modo de interoperabilidade e não no modo de compilação nativo. A execução paralela é suportada.

Problemas conhecidos

se aplica a: SQL Server, Instância Gerenciada SQL do Azure

  • Atualmente, as colunas LOB (varbinary(max), varchar(max) e nvarchar(max)) nos segmentos de columnstore comprimidos não são afetadas por DBCC SHRINKDATABASE e DBCC SHRINKFILE.

SQL Server 2014 (12.x)

O SQL Server 2014 (12.x) introduziu o índice columnstore clusterizado como o formato de armazenamento primário. Isso permitia carregamentos regulares, bem como operações de atualização, exclusão e inserção.

  • A tabela pode usar um índice columnstore clusterizado como armazenamento principal da tabela. Nenhum outro índice é permitido na tabela, mas o índice columnstore clusterizado é atualizável para que você possa executar carregamentos regulares e fazer alterações em linhas individuais.
  • O índice columnstore não clusterizado continua a ter a mesma funcionalidade do SQL Server 2012 (11.x), exceto para operadores adicionais que agora podem ser executados no modo de lote. Isso ainda não é atualizável, exceto pela reconstrução e pelo uso da troca de partições. O índice columnstore não clusterizado é suportado apenas em tabelas baseadas em disco e não em tabelas na memória.
  • O índice columnstore clusterizado e não clusterizado tem uma opção de compressão arquivística que compacta ainda mais os dados. A opção de arquivamento é útil para reduzir o tamanho dos dados na memória e no disco, mas diminui o desempenho da consulta. Ele funciona bem para dados que são acessados com pouca frequência.
  • O índice columnstore clusterizado e o índice columnstore não clusterizado funcionam de forma muito semelhante, ambos os índices usam o mesmo formato de armazenamento colunar, o mesmo mecanismo de processamento de consultas e o mesmo conjunto de vistas de gerenciamento dinâmico. A diferença está nos tipos de índices primário e secundário, e o índice columnstore não clusterizado é de leitura apenas.
  • Esses operadores são executados em modo de lote para consultas multithreaded: análise, filtro, projeção, junção, agrupamento e união total.

SQL Server 2012 (11.x)

O SQL Server 2012 (11.x) introduziu o índice columnstore não clusterizado como outro tipo de índice em tabelas rowstore e processamento em lote para consultas em dados columnstore.

  • Uma tabela rowstore pode ter um índice columnstore não clusterizado.
  • O índice columnstore é de apenas leitura. Depois de criar o índice columnstore, não é possível atualizar a tabela pelas operações INSERT, DELETEe UPDATE; para executar essas operações, deve remover o índice, atualizar a tabela e reconstruir o índice columnstore. Você pode carregar dados adicionais na tabela utilizando a técnica de troca de partições. A vantagem da comutação de partições é que é possível carregar dados sem necessitar de eliminar e reconstruir o índice columnstore.
  • O índice columnstore sempre requer armazenamento extra, normalmente 10% adicionais sobre rowstore, porque armazena uma cópia dos dados.
  • O processamento em lote fornece 2x ou melhor desempenho de consulta, mas só está disponível para execução de consulta paralela.

Conteúdo relacionado