Compartilhar via

Novidades em índices columnstore

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Banco 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 recursos para versões do produto

Esta tabela resume os principais recursos para índices columnstore, e os produtos nos quais eles estão disponíveis.

Recurso do índice 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 de SQL do AzureAUTD Pool de SQL dedicado do Azure Synapse Analytics
Execução do modo de lote para consultas com vários threads3 sim sim sim sim sim sim sim sim
Execução em modo de lote para consultas com thread único sim sim sim sim sim sim
Opção de compactação de arquivamento sim sim sim sim sim sim sim
Isolamento de instantâneo e isolamento de instantâneo de 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 oferece suporte a índices columnstore sim sim sim sim sim sim sim sim
A réplica secundária para leitura do Always On oferece suporte ao índice columnstore não clusterizado somente leitura sim sim sim sim sim sim sim sim
A réplica secundária para leitura do Always On oferece suporte a índices columnstore atualizáveis sim sim sim sim
Índice columnstore não clusterizado somente leitura no heap ou árvore B sim sim sim 4 sim 4 sim 4 sim 4 sim 4 sim 4
Índice columnstore não clusterizado atualizável no heap ou árvore B sim sim sim sim sim sim
Índices adicionais de árvore B permitidos em um heap ou árvore B com um índice columnstore não clusterizado sim sim sim sim sim sim sim sim
Índice columnstore clusterizado e atualizável sim sim sim sim sim sim sim
Índice de árvore B em um í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
Suporte ao uso de uma condição filtrada pela definição de índice columnstore não clusterizado sim sim sim sim sim sim
Opção de atraso de compactaçã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 não 5
O índice columnstore pode ter uma coluna computada não persistente sim sim sim
Suporte de mesclagem em segundo plano do motor de tupla sim sim sim sim
Índices columnstore clusterizados ordenados sim sim sim
Índices columnstore não clusterizados ordenados sim
Criar e recriar índice columnstore online sim sim
Criar e recriar í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 de SP1) e versões anteriores, os índices columnstore só estão disponíveis no Enterprise Edition.
2 Para o Banco de Dados SQL do Azure, os índices columnstore estão disponíveis nas camadas DTU Premium, camadas DTU Standard – S3 e superior, e todas as camadas de vCore. 3 O grau de paralelismo (DOP) para as operações no 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 de 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 tem suporte em pools de SQL dedicados, mas tem suporte no pool de SQL sem servidor.
AUTD Aplica-se à Instância Gerenciada de SQL do Azure configurada com a Política de atualização Sempre atualizado.

SQL Server 2022 (16.x)

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

  • Índices columnstore clusterizados ordenados melhoram o desempenho de consultas com base em predicados de coluna ordenados. Índices columnstore ordenados podem melhorar o desempenho ignorando completamente segmentos de dados. Isso pode reduzir drasticamente a E/S necessária para concluir consultas em dados columnstore. Para obter mais informações, consulte eliminação de segmento. Para obter mais informações, consulte CRIAR ÍNDICE COLUMNSTORE e Ajuste de desempenho com índices columnstore ordenados.
  • O pushdown de predicado com a eliminação do rowgroup columnstore clusterizado de cadeias de caracteres usa valores de limite para otimizar pesquisas de cadeias de caracteres. Todos os índices columnstore se beneficiam da eliminação aprimorada do segmento por tipo de dados. A partir do SQL Server 2022 (16.x), esses recursos de eliminação de segmento se estendem a tipos de dados de texto, binários e GUID, e ao tipo de dados datetimeoffset para escala maior que dois. Anteriormente, a eliminação do segmento columnstore aplicava-se somente aos tipos de dados numéricos, de data e hora e ao tipo de dados datetimeoffset com escala menor ou igual a dois. Depois de atualizar para uma versão do SQL Server que dá suporte à eliminação de segmento min/max da cadeia de caracteres (SQL Server 2022 (16.x) e versões posteriores), o índice columnstore não se beneficia desse recurso até que ele seja recriado usando ALTER INDEX REBUILD ou CREATE INDEX WITH (DROP_EXISTING = ON).
  • Eliminação de rowgroup columnstore para o prefixo de LIKE predica, por exemplo column LIKE 'string%'. Não há suporte para eliminação de segmento com o uso de LIKE sem prefixo, 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 mover de tupla é ajudado por uma tarefa de mesclagem em segundo plano que compacta automaticamente rowgroups delta OPEN menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla rowgroups COMPACTADOS de onde um grande número de linhas foi excluído. Anteriormente, era necessária uma operação de reorganização de índice para mesclar rowgroups 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) é compatível com colunas computadas não persistentes em índices columnstore clusterizados. Não há suporte para colunas computadas em índices columnstore clusterizados. Você não pode 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. Esses aprimoramentos melhoram os cenários de data warehouse e habilitam a análise operacional em tempo real.

Funcional

  • Uma tabela rowstore pode ter um índice columnstore não clusterizado que seja atualizável. Anteriormente, o índice de armazenagem em colunas não clusterizado era somente de leitura.

  • A definição do índice columnstore não clusterizado dá 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 apenas nos dados inativos da sua carga de trabalho operacional.

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

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

  • Dê suporte a chaves primárias e chaves estrangeiras usando um índice de árvore B para impor essas restrições em um índice columnstore clusterizado.

  • Os índices columnstore têm uma opção de atraso de compactação que minimiza o impacto da carga de trabalho transacional na análise operacional em tempo real. Essa opção permite que as linhas alteradas frequentemente estabilizem 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 do nível de compatibilidade do banco de dados 120 ou 130

  • Os índices columnstore oferecem suporte ao nível RCSI (isolamento do instantâneo confirmado) e ao SI (isolamento de instantâneo). Isso permite consultas de análise consistente transacionais sem qualquer bloqueio.

  • O columnstore oferece suporte à desfragmentação de índice por meio da remoção de linhas excluídas, sem a necessidade de recriar explicitamente o índice. A instrução ALTER INDEX ... REORGANIZE remove do columnstore as linhas excluídas, com base em uma política definida internamente, como uma operação online

  • Os índices columnstore podem ser acessados em uma réplica secundária para leitura do AlwaysOn. Você pode melhorar o desempenho da análise operacional descarregando as consultas de análise para uma réplica secundária Always On.

  • A aplicação agregada calcula as funções de agregação MIN, MAX, SUM, COUNT e AVG durante as verificações de tabela, quando o tipo de dados usa, no máximo, 8 bytes e não é do tipo de dados String. A aplicação agregada é compatível com ou sem a cláusula GROUP BY tanto para índices columnstore clusterizados quanto para índices columnstore não clusterizados. No SQL Server, essa melhoria é reservada para a Edição Enterprise.

  • A aplicação de predicado de cadeia de caracteres acelera as consultas que comparam cadeias de caracteres 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 todos os agrupamentos compatíveis. No SQL Server, essa melhoria é reservada para a Edição Enterprise.

  • Aprimoramentos para operações do modo de lote aproveitando os recursos de hardware baseados em vetor. O mecanismo de banco de dados verifica o nível de suporte de CPU para as extensões de hardware AVX 2 (Extensões de Vetor Avançadas) e SSE 4 (Extensões de Streaming SIMD 4) e as utiliza, se suportadas. No SQL Server, essa melhoria é reservada para a Edição Enterprise.

Desempenho do nível de compatibilidade do banco de dados 130

  • Novo suporte à execução em modo de lote para consultas que usam qualquer uma dessas operações:

    • SORT
    • Realiza agregações com várias funções diferentes. Alguns exemplos: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG e STDEV/STDEVP
    • Funções de agregação de janela: COUNT, COUNT_BIG, SUM, AVG, MIN, MAX e CLR
    • Agregações de janela definidas pelo usuário: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP e GROUPING
    • Funções analíticas de agregação de janela: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST e PERCENT_RANK

  • Consultas single-threaded em execução em MAXDOP 1 ou com um plano de consulta serial são executadas no modo de lote. Anteriormente, somente consultas com vários threads eram executadas com execução em lote.

  • Consultas de tabela com otimização de memória podem ter planos paralelos no modo de interoperabilidade de SQL ao acessar dados em rowstore ou no índice columnstore.

Capacidade de suporte

Esses modos de exibição do sistema são novos no 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 o 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. Há suporte para a execução paralela.

Problemas conhecidos

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

  • Atualmente, as colunas LOB (varbinary(max), varchar(max) e nvarchar(max)) nos segmentos columnstore compactados 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 principal. Isso permitiu cargas regulares, bem como operações de atualização, exclusão e inserção.

  • A tabela pode usar um índice columnstore clusterizado como o armazenamento de tabela primária. Nenhum outro índice tem permissão na tabela, mas o índice columnstore clusterizado é atualizável, de modo que você possa executar cargas regulares e fazer alterações em linhas individuais.
  • O índice columnstore não clusterizado continua com a mesma funcionalidade que tinha no SQL Server 2012 (11.x), exceto no caso de operadores adicionais que, agora, podem ser executados em modo de lote. Ainda não é atualizável, exceto por meio de recompilação e com a troca de partição. O índice columnstore não clusterizado tem suporte 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 compactação de arquivamento 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 também reduz o desempenho da consulta. Ela funciona bem para dados acessados com pouca frequência.
  • O índice columnstore clusterizado e o índice columnstore não clusterizado funcionam de modo muito semelhante; eles usam o mesmo formato de armazenamento colunar, o mesmo mecanismo de processamento de consulta e o mesmo conjunto de exibições de gerenciamento dinâmico. A diferença é de tipos de índice primário e secundário, e o índice columnstore não clusterizado é somente leitura.
  • Estes operadores são executados em modo de lote para consultas multithread: verificação, filtro, projeto, união, agrupar por e união de todos.

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 de columnstore.

  • Uma tabela rowstore pode ter um índice columnstore não clusterizado.
  • O índice columnstore é somente leitura. Depois de criar o índice columnstore, não é possível atualizar a tabela com operações INSERT, DELETE e UPDATE; para executar essas operações, é necessário remover o índice, atualizar a tabela e recompilar o índice columnstore. Você pode carregar dados adicionais na tabela usando a alternância de partição. A vantagem da alternância de partição é que você pode carregar dados sem descartar e recompilar o índice columnstore.
  • O índice columnstore sempre exige armazenamento extra, normalmente 10% a mais no rowstore, pois ele armazena uma cópia dos dados.
  • O processamento de lote fornece um desempenho de consulta duas ou mais vezes melhor, mas está disponível apenas para execução de consulta paralela.

Conteúdo relacionado