Índices columnstore no data warehousing

2025-04-21

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure PDW (Analytics Platform System)Banco de Dados SQL no Microsoft Fabric

Os índices columnstore, junto com o particionamento, são essenciais para criar um data warehouse do SQL Server. Este artigo se concentra nos principais casos de uso e exemplos de designs de data warehouse com o Mecanismo de Banco de Dados SQL.

Principais recursos para armazém de dados

O SQL Server 2016 (13.x) introduziu esses recursos para aprimoramentos de desempenho do columnstore:

Os grupos de disponibilidade Always On dão suporte a consulta de um índice columnstore em uma réplica secundária para leitura.
O MARS (Multiple Active Result Sets) oferece suporte aos índices columnstore.
Uma nova exibição de gerenciamento dinâmico sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) fornece, em nível de grupo de linhas, informações para a solução de problemas de desempenho.
Todas as consultas em índices columnstore podem ser executadas em modo de lote. Anteriormente, somente consultas paralelas podiam ser executadas no modo de lote.
Os operadores Sort, Distinct Sort e Distinct são executados no modo de lote.
As agregações de Janela agora são executadas em modo de lote no nível de compatibilidade do banco de dados 130 e superior.
Pushdown de agregação para processamento eficiente de agregações. Isso tem suporte em todos os níveis de compatibilidade de banco de dados.
Aplicação de predicado de cadeia de caracteres para o processamento eficiente dos predicados de cadeia de caracteres. Isso tem suporte em todos os níveis de compatibilidade de banco de dados.
Isolamento de instantâneo no nível de compatibilidade do banco de dados 130 e superior.
Os índices columnstore clusterizados ordenados foram introduzidos com o SQL Server 2022 (16.x). Para obter mais informações, consulte CRIAR ÍNDICE COLUMNSTORE e Diretrizes de design dos índices columnstore. Para obter a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice de coluna ordenado.

Para mais informações sobre novos recursos nas versões e plataformas do SQL Server e SQL do Azure, confira Novidades nos índices columnstore.

Melhora o desempenho combinando índices não clusterizado e columnstore

A partir do SQL Server 2016 (13.x), você pode criar índices rowstore não clusterizados em um índice columnstore clusterizado.

Exemplo: melhorar a eficiência de buscas de tabelas com um índice não clusterizado

Para melhorar a eficiência das buscas de tabelas em um data warehouse, você pode criar um índice não clusterizado projetado para executar consultas que executam melhor com as buscas de tabelas. Por exemplo, as consultas que procuram valores correspondentes ou retornam um pequeno intervalo de valores têm um desempenho melhor em um índice B-tree em vez de um índice em colunas. Eles não exigem uma varredura completa do índice columnstore e retornam o resultado correto mais rapidamente ao realizar uma busca binária em um índice B-tree.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Exemplo: use um índice não clusterizado para impor uma restrição de chave primária em uma tabela columnstore

Já que uma tabela pode ter no máximo um índice clusterizado, uma tabela com um índice columnstore clusterizado não pode ter uma restrição de chave primária clusterizada. Para criar uma restrição de chave primária em uma tabela columnstore, você deve declará-la como não clusterizada.

O exemplo a seguir cria uma tabela com uma restrição de chave primária não clusterizada e, em seguida, cria um índice columnstore clusterizado na tabela. Como qualquer inserção ou atualização na tabela columnstore também modifica o índice não clusterizado, todas as operações que violam a restrição de chave primária fazem com que toda a operação falhe.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Melhore o desempenho habilitando o bloqueio no nível de linha e no nível de grupo de linhas.

Para complementar o recurso de índice não clusterizado em um índice columnstore, o SQL Server 2016 (13.x) oferece capacidade de bloqueio granular para operações de SELECT, UPDATE e DELETE. É possível executar consultas com o bloqueio no nível de linha em buscas de índice com base em um índice não clusterizado, e um bloqueio no nível do grupo de linhas em verificações de tabela completa com base no índice columnstore. Use isto para alcançar maior simultaneidade de leitura/gravação usando adequadamente o bloqueio no nível de linha e no nível do grupo de linhas.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

Isolamento de instantâneo e isolamento de instantâneo de leitura confirmada

Use o SI (isolamento de instantâneo) para garantir a consistência transacional e o isolamento de instantâneo confirmado por leitura (RCSI) para garantir a consistência no nível da instrução para consultas em índices columnstore. Isso permite que as consultas sejam executadas sem bloquear os gravadores de dados. Esse comportamento de não bloqueio também reduz consideravelmente a probabilidade de deadlocks para transações complexas. Para obter mais informações, consulte Níveis de isolamento com base em controle de versão de linha no Mecanismo de Banco de Dados.

Compartilhar via