Partilhar via

Índices Columnstore no armazenamento de dados

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric

Os índices Columnstore, em conjunto 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 warehousing com o Mecanismo de Banco de Dados SQL.

Principais características para armazenamento de dados

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

  • Os grupos de disponibilidade "Always On" suportam a consulta de um índice columnstore numa réplica secundária legível.
  • Multiple Active Result Sets (MARS) é compatível com índices columnstore.
  • Uma nova exibição de gerenciamento dinâmico sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) fornece informações de solução de problemas de desempenho no nível do grupo de linhas.
  • Todas as consultas em índices columnstore podem ser executadas no modo de lote. Anteriormente, apenas consultas paralelas podiam ser executadas no modo de lote.
  • Os operadores Sort, Distinct Sort e Distinct são executados no modo de lote.
  • As funções de agregação da janela agora são executadas em modo em lote para compatibilidade de banco de dados de nível 130 e superior.
  • Redução agregada para processamento eficiente de funções agregadas. Isso é suportado em todos os níveis de compatibilidade de banco de dados.
  • Redução de predicados de strings para processamento eficiente de predicados. Isso é suportado em todos os níveis de compatibilidade de banco de dados.
  • Isolamento instantânea para nível de compatibilidade de banco de dados 130 e superior.
  • Os índices columnstore agrupados ordenados foram introduzidos com o SQL Server 2022 (16.x). Para obter mais informações, consulte CREATE COLUMNSTORE INDEX e Ajuste de desempenho com índices columnstore ordenados. Para ver a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice columnstore ordenado.

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

Melhore o desempenho combinando índices não clusterizados e columnstore

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

Exemplo: Melhorar a eficiência das pesquisas de tabela com um índice não agrupado

Para melhorar a eficiência das buscas de tabela em um data warehouse, você pode criar um índice não clusterizado projetado para executar consultas com melhor desempenho com buscas de tabela. Por exemplo, consultas que procuram valores correspondentes ou retornam um pequeno intervalo de valores têm melhor desempenho em relação a um índice de árvore B em vez de um índice columnstore. Eles não exigem uma verificação completa do índice columnstore e retornam o resultado correto mais rapidamente efetuando uma pesquisa binária através de 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: usar um índice não clusterizado para impor uma restrição de chave primária em uma tabela columnstore

Como 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 da linha e no nível do grupo de linhas

Para complementar o índice não-clusterizado numa funcionalidade de índice columnstore, o SQL Server 2016 (13.x) oferece capacidade de bloqueio granular para SELECT, UPDATE e DELETE operações. As consultas podem ser executadas com bloqueio em nível de linha em buscas de índice em um índice não clusterizado e bloqueio em nível de grupo de linhas em verificações de tabela completa em relação ao índice columnstore. Utilize isto adequadamente para alcançar uma maior simultaneidade de leitura/gravação, através do uso de bloqueios a nível de linha e a nível de 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 snapshot e isolamento de snapshot com leitura confirmada

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

Conteúdo relacionado

  • Last updated on