Índices columnstore

Artigo
10/31/2013

Um índice columnstore xVelocity de memória otimizada agrupa e armazena dados para cada coluna e une todas as colunas para concluir o índice inteiro. Isso difere de índices tradicionais que agrupam e armazenam dados para cada linha e une todas as linhas para concluir o índice inteiro. Para alguns tipos de consultas, o processador de consulta do SQL Server pode tirar proveito do layout columnstore para melhorar significativamente os tempos de execução de consulta. O crescimento explosivo de aplicativos de data warehouse, apoio à decisão e BI geraram uma urgência para ler e processar conjuntos de dados muito grandes rapidamente e com precisão em informações úteis e conhecimento. Esse crescimento de volumes de dados e expectativas escalonadas são um desafio para manter ou aumentar por meio da melhoria de desempenho incremental. A tecnologia de índice columnstore do SQL Server é especialmente apropriada para conjuntos de dados de data warehouse típicos. Os índices columnstore podem transformar a experiência com data warehouse para usuários proporcionando um desempenho mais rápido para consultas de data warehouse comuns, como filtragem, agregação, agrupamento ou consultas de junção em estrela.

Sumário

Noções básicas

Noções básicas: índices columnstore descritos
Noções básicas: restrições e limitações de índices columnstore
Exemplo de demonstração: índices columnstore com uma tabela particionada
Noções básicas: cenários típicos de índices columnstore
Noções básicas: otimizações de filtro de bitmap

Práticas recomendadas

Práticas recomendadas: atualizando dados em um índice columnstore
Práticas recomendadas: escolhendo as colunas para um índice de repositório de coluna
Práticas recomendadas: tabelas particionadas

Instruções

Como criar um índice de repositório de coluna
Como determinar o tamanho de índice columnstore
Como solucionar problemas de desempenho de um índice de repositório de coluna

Noções básicas: índices columnstore descritos

Os índices columnstore do Mecanismo de Banco de Dados do SQL Server podem ser usados para acelerar significativamente o tempo de processamento de consultas comuns de data warehouse. As cargas de trabalho de data warehouse típicas envolvem o resumo de grandes quantidades de dados. As técnicas que costumam ser usadas no data warehousing e em sistemas de apoio à decisão para melhorar o desempenho são tabelas resumidas pré-computadas, exibições indexadas, cubos OLAP e assim por diante. Embora isso possa acelerar o processamento de consultas, essas técnicas podem ser inflexíveis, difíceis de manter e devem ser criadas especificamente para cada problema de consulta.

Por exemplo, considere uma tabela de fatos F1 com colunas de chave de dimensão dk1 e dk2. Deixe M ser uma função de agregação, como SUM. Em vez de calcular M sobre a coluna dk1 sempre que uma consulta que faz referência a M(dk1) é executada, uma tabela resumida F2(dk1, M) pode ser criada e usada para que o resultado possa ser pré-computado e a consulta possa ser executada rapidamente. No entanto, se uma nova consulta que faz referência a M(dk2) for necessária, uma nova tabela resumida F3(dk2, M) com essas informações deverá ser criada. Quando o número de colunas em uma tabela aumenta e com muitas funções possíveis, essa abordagem se torna difícil de manter e não abrange facilmente todas as consultas necessárias.

Essa sobrecarga pode ser significante para o usuário. Usando os índices columnstore do SQL Server, os usuários podem reduzir a sobrecarga das outras soluções. Os índices columnstore também permitem que consultas computem os resultados rapidamente para que a pré-computação não seja necessária.

Estas são as principais características da tecnologia de repositório de colunas do SQL Server:

Formato de dados em coluna – Diferente da tradicional organização de dados baseada em linhas (chamado formato rowstore), em sistemas de bancos de dados em coluna, como o SQL Server com índices columnstore, os dados são agrupados e armazenados em uma coluna por vez. O processamento de consulta do SQL Server pode tirar proveito do novo layout de dados e melhorar significativamente o tempo de execução de consultas.
Resultados de consulta mais rápidos – Os índices columnstore podem gerar resultados mais rápidos pelos seguintes motivos:
- Somente as colunas necessárias devem ser lidas. Portanto, menos dados são lidos do disco para a memória e posteriormente movidos da memória para o cache do processador.
- As colunas são altamente compactadas. Isso reduz o número de bytes que devem ser lidos e movidos.
- A maioria das consultas não toca todas as colunas da tabela. Portanto, muitas colunas nunca serão levadas à memória. Isso, combinado com a excelente compactação, melhora o uso de pool de buffers que reduz a E/S total.
- A tecnologia avançada de execução de consultas processa partes de colunas chamadas lotes de forma simplificada, reduzindo o uso da CPU.
Colunas de chave – Não há um conceito de colunas de chave em um índice columnstore para que a limitação do número de colunas de chave em um índice (16) não se aplique a índices columnstore.
Chave de índice clusterizado – Se uma tabela base for um índice clusterizado, todas as colunas da chave de clustering deverão estar presentes no índice columnstore não clusterizado. Se uma coluna na chave de clustering não for listada na instrução de criação de índice, ela será adicionada automaticamente ao índice de columnstore.
Particionamento – Os índices columnstore funcionam com o particionamento de tabela. Nenhuma alteração à sintaxe de particionamento de tabela é necessária. Um índice columnstore em uma tabela particionada deve ser alinhado por partição com a tabela base. Portanto, um índice columnstore não clusterizado pode ser criado somente em uma tabela particionada se a coluna de particionamento for uma das colunas no índice columnstore.
Tamanho do registro – A limitação do tamanho do registro de chaves de índice de 900 bytes também não se aplica aos índices columnstore.
Processamento de consulta – Juntamente com o índice columnstore, o SQL Server introduz o processamento em lotes para tirar proveito da orientação em coluna dos dados. A estrutura columnstore e o processamento em lotes contribuem para o aumento do desempenho, mas investigar problemas de desempenho pode ser mais complexo do que se apenas um fator estiver envolvido.
A tabela não pode ser atualizada – No SQL Server 2012, uma tabela com índice columnstore não pode ser atualizada. Para saber quais são as soluções, consulte Práticas recomendadas: atualizando dados em um índice columnstore

Para a sintaxe sobre como criar um índice columnstore, consulte CREATE COLUMNSTORE INDEX (Transact-SQL).

Tipos de Dados

Os tipos de dados corporativos comuns podem ser incluídos em um índice columnstore. Os tipos de dados a seguir podem ser incluídos em um índice columnstore.

char e varchar
nchar e nvarchar (exceto varchar(max) e nvarchar(max))
decimal (e numeric) (exceto com precisão maior que 18 dígitos.)
int, bigint, smallint e tinyint
float (e real)
bit
money e smallmoney
Todos os tipos de dados de data e hora (exceto datetimeoffset com escala maior que 2)

Os tipos de dados a seguir não podem ser incluídos em um índice columnstore:

binary e varbinary
ntext, text e image
varchar(max) e nvarchar(max)
uniqueidentifier
rowversion (e timestamp)
sql_variant
decimal (e numeric) com precisão maior que 18 dígitos
datetimeoffset com escala maior que 2
Tipos CLR (hierarchyid e tipos espaciais)
xml

O potencial para baixo desempenho

O desempenho da consulta de apoio à decisão é geralmente melhorado quando índices columnstore são usados em tabelas grandes, mas algumas consultas e até mesmo cargas de trabalho inteiras podem ter um desempenho pior. Usando uma abordagem baseada no custo, o otimizador de consulta decide usar um índice de columnstore normalmente apenas quando ele melhora o desempenho geral da consulta. Porém, os modelos de custo que o otimizador usa são aproximados e, por vezes, o otimizador opta por usar o índice columnstore para uma tabela quando seria melhor usar um repositório de linha (árvore B ou heap) para acessar a tabela. Se isso ocorrer, use a dica de consulta IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX, ou use uma dica de índice para direcionar o otimizador para um índice de repositório de linha. O otimizador ainda pode incluir algumas informações do índice columnstore. Portanto, em casos raros, essa opção pode não resolver o problema de desempenho. Se o desempenho da carga de trabalho não for melhorado por um índice columnstore, e você não puder usar dicas de índice para corrigir o problema, remova o índice columnstore para reverter para o processamento de repositório de linha.

Áreas de problema

Os índices columnstore e o processamento de consulta baseado em coluna do SQL Server são otimizados para consultas típicas de data warehouse que possuem uma tabela de fatos grande e moderados para tabelas de dimensões unidades em uma configuração de esquema estrela, e depois agrupadas e agregadas. Embora o número de linhas na tabela de fatos seja grande, essas consultas normalmente retornam um conjunto de resultados comparativamente pequeno porque os dados são agregados. O desempenho das consultas que usam o índice columnstore pode ser lento diante de uma ou mais das condições a seguir.

O conjunto de resultados é grande porque os dados não estão agregados. (Retornar um conjunto de dados grande é inerentemente mais lento do que retornar um conjunto de resultados pequeno.)
Não há junção, filtragem ou agregação. Nesse caso, não há processamento em lotes. Portanto, o benefício do índice columnstore é limitado aos benefícios de compactação e leitura de menos colunas.
Duas tabelas grandes devem ser unidades de forma a criar tabelas hash grandes que não caibam na memória e devem ser derramadas em disco.
Muitas colunas são retornadas, o que causa a recuperação de mais dados do índice columnstore.
Uma condição de junção para uma tabela indexada por columnstore inclui mais de uma coluna.

Você poderá usar os métodos descritos anteriormente nesta seção para solucionar o processamento lento do columnstore se isso ocorrer por um dos motivos a seguir

Início

Noções básicas: restrições e limitações de índices columnstore

Restrições básicas

Um índice columnstore:

Não pode ter mais de 1024 colunas.
Não pode ser clusterizado. Apenas índices columnstore não clusterizados estão disponíveis.
Não pode ser um índice exclusivo.
Não pode ser criado em uma exibição ou exibição indexada.
Não pode incluir uma coluna esparsa.
Não pode atuar como uma chave primária ou estrangeira.
Não pode ser alterado por meio da instrução ALTER INDEX. Em vez disso, remova e recrie o índice columnstore. (Você pode usar ALTER INDEX para desabilitar e recriar um índice columnstore.)
Não pode ser criado com a palavra-chave INCLUDE.
Não pode incluir as palavras-chave ASC ou DESC para classificar o índice. Os índices columnstore são ordenados de acordo com os algoritmos de compactação. Não é permitido classificar no índice. Valores selecionados de um índice de columnstore poderiam ser classificados pelo algoritmo de pesquisa, mas você deve usar a cláusula ORDER BY para garantir a classificação de um conjunto de resultados.
Não use ou mantenha estatísticas como um índice tradicional.
Não pode conter uma coluna com um atributo FILESTREAM. Outras colunas na tabela que não são usadas no índice podem conter o atributo FILESTREAM.

Uma tabela com um índice columnstore não pode ser atualizada

Para solucionar esse problema, consulte Práticas recomendadas: atualizando dados em um índice columnstore.

Efeitos de memória limitada

O processamento de repositório de coluna é otimizado para processamento de memória. O SQL Server implementa mecanismos que permitem que dados e a maioria de estruturas de dados sejam derramados em disco em caso de memória insuficiente disponível. Se restrições de memória severas estiverem presentes, o processamento usará o repositório de linha. Pode haver casos em que o índice columnstore é escolhido como método de acesso, mas a memória é insuficiente para criar as estruturas de dados necessárias. Começando como uma operação columnstore e depois seguir para um padrão de caminho de código mais lento, pode haver certa redução de desempenho em casos de consultas que encontram uma restrição de memória grave. O requisito de memória efetiva para qualquer consulta depende da consulta específica. Compilar um índice columnstore requer aproximadamente 8 megabytes vezes o número de colunas do índice vezes o DOP (grau de paralelismo). Geralmente, os requisitos de memória aumentam conforme aumenta a proporção de colunas que são cadeias de caracteres. Portanto, a diminuição do DOP pode reduzir os requisitos de memória para criar o índice columnstore.

A avaliação de algumas expressões será acelerada mais que outras

Algumas expressões comuns são avaliadas usando um modo de lote em vez do modo de uma linha por vez quando o índice de columnstore é usado. O modo em lote fornecerá aceleração de consulta adicional, além das vantagens de usar um índice columnstore. Nem todo operador de execução de consulta é habilitado para processamento em modo de lote.

O índice columnstore não oferece suporte a SEEK

Se for esperado que a consulta retorne uma pequena fração de linhas, o otimizador provavelmente não selecionará o índice columnstore (por exemplo: consultas do tipo needle-in-the-haystack). Se a dica de tabela FORCESEEK for usada, o otimizador não considerará o índice columnstore.

Índices columnstore não podem ser combinados com os recursos a seguir:

Compactação de página e de linha, e formato de armazenamento vardecimal (um índice columnstore já foi compactado em um formato diferente).
Replicação
Controle de alterações
Change Data Capture
Fluxo de arquivos

Início

Exemplo de demonstração: índices columnstore com uma tabela particionada

Os exemplos deste tópico usam uma tabela particionada chamada FactResellerSalesPtnd criada no banco de dados de exemplo AdventureWorksDW2012 . Para testar um índice columnstore em uma tabela particionada, conecte-se ao banco de dados AdventureWorksDW2012 e execute o seguinte código para criar uma versão particionada da tabela de fatos.

Observação
Para obter mais informações sobre os bancos de dados de exemplo e instruções sobre como baixar o banco de dados, consulte Bancos de dados de exemplo AdventureWorks.

Criar a tabela FactResellerSalesPtnd

Execute o código a seguir para criar uma versão particionada da tabela FactResellerSales chamada FactResellerSalesPtnd.

USE AdventureWorksDW2012;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [CustomerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL,
    OrderDate [datetime] NULL,
    DueDate [datetime] NULL,
    ShipDate [datetime] NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERT…SELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT * FROM dbo.FactResellerSales;
GO

-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

Agora execute uma consulta que possa se beneficiar do índice columnstore e confirme se o índice columnstore é usado.

Testar o índice columnstore

Pressione Ctrl+M ou, no menu Consulta, selecione Incluir Plano de Execução Real. Isso ativa uma representação gráfica do plano de execução real usada pelo SQL Server Management Studio.
Na janela do Editor de Consultas, execute a consulta a seguir.
```
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;
```
Na janela Resultados, na guia Plano de execução, confirme se o plano de consulta selecionou uma verificação de índice do índice csindx_FactResellerSalesPtnd não clusterizado.

Observação

Para obter mais informações sobre ícones de planos de execução gráficos, consulte Referência de operadores físicos e lógicos de plano de execução.

Início

Observação
Para obter mais informações sobre ícones de planos de execução gráficos, consulte Referência de operadores físicos e lógicos de plano de execução.

Noções básicas: cenários típicos de índices columnstore

Esquemas de bancos de dados de estrela ou floco de neve são normalmente encontrados em data warehouses e data marts dimensionais, onde a velocidade da recuperação de dados é mais importante que a eficiência de manipulações de dados. A tecnologia de repositório de coluna SQL Server 2012 pode detectar e acelerar consultas de esquemas de estrela e floco de neve.

Exemplos:

Observação
O processamento em lotes pode não ser usado nos exemplos a seguir porque as tabelas não são grandes o suficiente. O modo de execução em lotes, como o processamento paralelo, é usado somente para consultas mais caras.

A: uma consulta de agregação que une duas tabelas

Considere uma consulta de junção em estrela que calcula quanto de produto 215 vendemos a cada trimestre. A tabela de fatos chamada FactResellerSalesPtnd é particionada na coluna OrderDateKey. Uma das tabelas de dimensões chamada DimDate é vinculada à tabela de fatos por uma relação chave primária – chave estrangeira na chave de dados.
```
SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;
```

Além disso, haverá consultas que podem se destinar somente a uma única tabela. Nesses casos, o SQL Server tenta tirar vantagem do poder da tecnologia de execução em lotes e repositório de colunas para também acelerar a execução de consultas.

B: uma consulta de agregação simples em uma única tabela

A tabela de fatos é FactResellerSalesPtnd e é particionada na coluna OrderDateKey. A consulta a seguir retorna o número de linhas e os números de pedidos.
```
SELECT COUNT(*) AS NumberOfRows, 
    COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders
FROM dbo.FactResellerSalesPtnd AS f;
```
Para consultas típicas do cenário de data warehouse, um aumento de velocidade entre 1,5 e 10 vezes é normal quando índices columnstore e o modo de execução em lotes são usados durante a execução de consultas. Para algumas consultas de junção em estrela, o aumento de velocidade é muito maior.

Noções básicas: otimizações de filtro de bitmap

Além do layout dos dados em formato de coluna, o SQL Server usa filtros de bitmap que são transmitidos para o mecanismo de armazenamento para melhorar o desempenho durante a execução de consultas. Os filtros de bitmap aumentam a velocidade de execução de consulta reduzindo o número de linhas incluídas, antes da implementação de junções, reduzindo o número de linhas processadas pelo operador de junção. O bitmap é criado na fase da construção de um junção de hash, mas as verificações de bitmap reais são executadas na fase de investigação da junção de hash. Você pode observar o uso de filtros de bitmap usando o plano de execução gráfico ou xml.

Práticas recomendadas: atualizando dados em um índice columnstore

As tabelas com um índice columnstore não podem ser atualizadas. Há três formas de solucionar esse problema.

Para atualizar uma tabela com um índice columnstore, remova o índice columnstore, execute qualquer operação INSERT, DELETE, UPDATEou MERGE necessária e reconstrua o índice columnstore.
Particione a tabela e alterne as partições. Para uma inserção em massa, insira dados em uma tabela de preparação, crie um índice columnstore na tabela de preparação e transforme a tabela de preparação em uma partição vazia. Para outras atualizações, alterne uma partição fora da tabela principal em uma tabela de preparação, desabilite ou remova o índice columnstore da tabela de preparação, execute as operações de atualização, reconstrua ou recrie o índice columnstore na tabela de preparação e retorne a tabela de preparação para a tabela principal.
Coloque dados estáticos em uma tabela principal com um índice columnstore e coloque novos dados e dados recentes suscetíveis a alterações, em uma tabela separada com o mesmo esquema que não tem um índice columnstore. Aplique atualizações à tabela com os dados mais recentes. Para consultar os dados, reescreva a consulta como duas consultas, uma em cada tabela, e combine os dois conjuntos de resultados com UNION ALL. A subconsulta na tabela principal grande se beneficiará do índice columnstore. Se a tabela atualizável for muito menor, a falta do índice columnstore terá menos efeito no desempenho. Embora também seja possível consultar uma exibição que é a UNION ALL das duas tabelas, talvez você não veja uma melhoria clara no desempenho. O desempenho dependerá do plano de consulta, que dependerá da consulta, dos dados e das estimativas de cardinalidade. A vantagem de usar uma exibição é que um gatilho INSTEAD OF na exibição pode desviar as atualizações para a tabela que não tem um índice columnstore e o mecanismo de exibição ficaria transparente para o usuário e para os aplicativos. Se você usar qualquer um dessas abordagens com UNION ALL, teste o desempenho em consultas típicas e decida se a conveniência de usar essa abordagem compensa a perda do benefício de desempenho.

Observação
Não crie um índice columnstore como um mecanismo para transformar a tabela em somente leitura. A restrição da atualização de tabelas que têm um índice columnstore não é garantida em versões futuras. Quando o comportamento somente leitura é necessário, ele deve ser imposto criando um grupo de arquivos somente leitura e movendo a tabela para esse grupo de arquivos.

Práticas recomendadas: escolhendo as colunas para um índice de repositório de coluna

Parte do benefício de desempenho de um índice columnstore é derivado das técnicas de compactação que reduzem o número de páginas de dados que devem ser lidas e manipuladas para processar a consulta. A compactação funciona melhor em colunas de caractere ou numéricas com grandes quantidades de valores duplicados. Por exemplo, tabelas de dimensões podem ter colunas para códigos postais, cidades e regiões de vendas. Se forem localizados muitos códigos postais em cada cidade, e se forem localizadas muitas cidades em cada região de vendas, a coluna de região de vendas será a mais compactada, a coluna de cidade seria menos compactada e o código postal teria a menor compactação. Embora todas as colunas sejam boas candidatas a um índice columnstore, adicionar a coluna do código da região de vendas ao índice columnstore terá o maior benefício da compactação columnstore e o código postal teria o menor.

Início

Práticas recomendadas: tabelas particionadas

Índices columnstore são criados para suportar consultas em cenários de data warehouse muito grandes, onde o particionamento é comum. O particionamento é recomendado quando os dados de uma tabela com um índice columnstore devem ser atualizados periodicamente. Para obter mais informações sobre como atualizar partições de um índice columnstore, consulte a seção anterior Práticas recomendadas: atualizando dados em um índice columnstore.

Início

Como criar um índice columnstore

Criar um índice columnstore é como criar qualquer outro índice. Você pode criar um índice columnstore usando Transact-SQL ou usando ferramentas gráficas do SQL Server Management Studio.

Criando um índice columnstore usando Transact-SQL

Na janela do Editor de Consultas, execute a instrução CREATE COLUMNSTORE INDEX. Para obter um exemplo, consulte Criar a tabela FactResellerSalesPtnd, anteriormente. Para obter mais informações, consulte CREATE COLUMNSTORE INDEX (Transact-SQL).

Criando um índice columnstore usando SQL Server Management Studio

No Management Studio, use o Pesquisador de Objetos para se conectar a uma instância do Mecanismo de Banco de Dados do SQL Server.
No Pesquisador de Objetos, expanda a instância do SQL Server, expanda Bancos de Dados, expanda um banco de dados, expanda uma tabela, clique com o botão direito do mouse em uma tabela, aponte para Novo Índice e clique em Índice Columnstore Não Clusterizado.
Na caixa de diálogo Nome do índice, na guia Geral, digite um nome para o novo índice e clique em Adicionar.
Na caixa de diálogo Selecionar Colunas, selecione as colunas para participar do índice columnstore e clique em OK duas vezes para criar o índice.

Como determinar o tamanho de índice columnstore

Um índice columnstore consiste em segmentos e dicionários. O exemplo a seguir demonstra como determinar o tamanho total de um índice columnstore (na tabela FactResellerSalesPtnd) combinando as colunas on_disk_size de sys.column_store_segments e sys.column_store_dictionaries.

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

Como solucionar problemas de desempenho de um índice de repositório de coluna

Para determinar se um índice columnstore está sendo usado, examine o plano de execução de consulta. Três elementos estão presentes quando o benefício máximo é obtido.

O índice columnstore está presente no plano de execução de consulta.

ícone do operador de análise de índice columnstore

Se o índice columnstore não estiver sendo usado e você achar que o columnstore pode se beneficiar da consulta, avalie o desempenho da consulta enquanto força o uso do índice columnstore usando a dica WITH (INDEX(<indexname>)). O exemplo a seguir demonstra uma consulta com uma dica de índice.
```
SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd))
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;
```
Quando você move o ponteiro sobre o ícone do índice columnstore no plano de consulta gráfico, o modo de execução real é listado como lote em vez de linha.
Um ícone de operador físico de bitmap está presente no plano de execução gráfico que indica que o filtro de bitmap está reduzindo o número de linhas antes de uma operação de junção.

ícone do operador de bitmap

Início

Tarefas relacionadas

CREATE COLUMNSTORE INDEX (Transact-SQL)

Conteúdo relacionado

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_segments (Transact-SQL)

Índices columnstore

Sumário

Noções básicas: índices columnstore descritos

Tipos de Dados

O potencial para baixo desempenho

Noções básicas: restrições e limitações de índices columnstore

Exemplo de demonstração: índices columnstore com uma tabela particionada

Criar a tabela FactResellerSalesPtnd

Testar o índice columnstore

Noções básicas: cenários típicos de índices columnstore

Exemplos:

A: uma consulta de agregação que une duas tabelas

B: uma consulta de agregação simples em uma única tabela

Noções básicas: otimizações de filtro de bitmap

Práticas recomendadas: atualizando dados em um índice columnstore

Práticas recomendadas: escolhendo as colunas para um índice de repositório de coluna

Práticas recomendadas: tabelas particionadas

Como criar um índice columnstore

Criando um índice columnstore usando Transact-SQL

Criando um índice columnstore usando SQL Server Management Studio

Como determinar o tamanho de índice columnstore

Como solucionar problemas de desempenho de um índice de repositório de coluna

Tarefas relacionadas

Conteúdo relacionado

Recursos adicionais