Novidades nos índices columnstore

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

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) Bancos de dados do SQL1 Pool de SQL dedicado do Azure Synapse Analytics
Execução em modo de lote para consultas com multiconversas2 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
Especificação do índice columnstore durante a criação de uma tabela sim sim sim sim sim sim
Suporte a índices columnstore pelo Always On sim sim sim sim sim sim sim sim
Suporte ao índice columnstore não clusterizado somente leitura pelo secundário legível Always On sim sim sim sim sim sim sim sim
Suporte ao índice columnstore atualizável pelo secundário legível Always On sim sim sim sim
Índice columnstore não clusterizado somente leitura no heap ou árvore B sim sim sim 3 sim 3 sim 3 sim 3 sim 3 sim 3
Í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(máx.) sim sim sim sim não 4
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

1 Para o banco de dados SQL, índices columnstore estão disponíveis nas camadas de Banco de Dados SQL DTU Premium e DTU Standard: S3 e superior, e em todas as camadas de vCore. Para o SQL Server 2016 (13.x) SP1 e versões posteriores, índices columnstore estão disponíveis em todas as edições. Para o SQL Server 2016 (13.x) (anterior ao SP1) e versões mais antigas, os índices columnstore estão disponíveis apenas na Enterprise Edition.

2 O grau de paralelismo (DOP) para operações no modo de lote é limitado a 2 para a edição SQL Server Standard e a 1 para as edições SQL Server Web e Express. Essa limitação refere-se a índices de columnstore criados em tabelas baseadas em disco e tabelas com otimização de memória.

3 Para criar um índice columnstore não clusterizado somente leitura, armazene o índice em um grupo de arquivos somente leitura.

4 Não tem suporte em pools SQL dedicados, mas tem suporte no pool SQL sem servidor.

SQL Server 2022 (16.x)

O SQL Server 2022 (16.x) adiciona esses novos recursos. Para obter mais informações, veja Novidades no SQL Server 2022 (16.x).

  • Os índices columnstore clusterizados ordenados melhoram o desempenho de consultas com base em predicados de coluna ordenados. Os índices columnstore ordenados podem melhorar o desempenho ignorando completamente os 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. Os índices columnstore clusterizados ordenados são disponibilizados no SQL Server 2022 (16.x). Para obter mais informações, veja CRIAR ÍNDICE COLUMNSTORE e ajuste de desempenho com índice columnstore clusterizado ordenado.

  • Pushdown de predicado com o rowgroup columnstore clusterizado. A eliminação de cadeias de caracteres usa valores de limite para otimizar as pesquisas de cadeia de caracteres. Todos os índices columnstore se beneficiam da eliminação aprimorada do segmento por tipo de dados. Do SQL Server 2022 (16.x) em diante, esses recursos de eliminação de segmento se estendem a tipos de dados de cadeia de caracteres, binários, GUID e o tipo de dados datetimeoffset para escala maior que dois. Anteriormente, essa eliminação de segmento columnstore se aplicava 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 ofereça suporte à eliminação de segmento mín/máx de cadeia de caracteres (SQL Server 2022 (16.x) e versões posteriores), o índice columnstore não beneficiará esse recurso até que seja recriado usando um RECRIAR ou ELIMINAR/CRIAR.

SQL Server 2019 (15.x)

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

Funcional

Desde o SQL Server 2019 (15.x), o motor de tupla recebe a ajuda de uma tarefa de mesclagem em segundo plano que compacta automaticamente os rowgroups delta ABERTOS menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla os rowgroups COMPACTADOS dos quais foi excluído um número grande de linhas. 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 atualizável. Anteriormente, o índice columnstore não clusterizado era somente 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 baseada 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 detecta o nível de suporte de CPU para extensões de hardware AVX 2 (extensões de vetor avançadas) e SSE 4 (Extensões SIMD de Streaming 4) e as usa, se compatíveis. 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 multiconversas executavam com a execução de 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 nativo na memória. Há suporte para a execução paralela.

Problemas conhecidos

Aplicável a: SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure, pool de SQL dedicado do Azure Synapse Analytics

  • Atualmente, colunas de LOB (varbinary(max), varchar(max) e nvarchar(max)) em segmentos de 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 usando a alternância 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