Partilhar via

Introdução aos índices columnstore para análise operacional em tempo real

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O SQL Server 2016 (13.x) introduz a análise operacional em tempo real, a capacidade de executar cargas de trabalho de análise e OLTP nas mesmas tabelas de banco de dados ao mesmo tempo. Além de executar análises em tempo real, você também pode eliminar a necessidade de ETL e um data warehouse.

Análise operacional em tempo real explicada

Tradicionalmente, as empresas têm sistemas separados para cargas de trabalho operacionais (ou seja, OLTP) e analíticas. Para esses sistemas, os trabalhos de Extrair, Transformar e Carregar (ETL) movem regularmente os dados do repositório operacional para um repositório de análise. Os dados analíticos geralmente são armazenados em um data warehouse ou data mart dedicado à execução de consultas analíticas. Embora esta solução tenha sido o padrão, ela tem estes três desafios principais:

  • Complexity. A implementação do ETL pode exigir codificação considerável, especialmente para carregar apenas as linhas modificadas. Pode ser complexo identificar quais linhas foram modificadas.
  • Cost. A implementação do ETL requer o custo de compra de licenças adicionais de hardware e software.
  • Latência dos dados. A implementação do ETL adiciona um atraso de tempo para a execução da análise. Por exemplo, se o trabalho de ETL for executado no final de cada dia útil, as consultas de análise serão executadas em dados com pelo menos um dia de idade. Para muitas empresas este atraso é inaceitável porque o negócio depende da análise de dados em tempo real. Por exemplo, a deteção de fraudes requer análises em tempo real dos dados operacionais.

Diagrama de um OLTP e interação de carga de trabalho de análise operacional em tempo real.

A análise operacional em tempo real oferece uma solução para esses desafios.

Não há atraso quando as cargas de trabalho de análise e OLTP são executadas na mesma tabela subjacente. Para cenários que podem usar análises em tempo real, os custos e a complexidade são muito reduzidos, eliminando a necessidade de ETL e a necessidade de comprar e manter um data warehouse separado.

Note

A análise operacional em tempo real tem como alvo o cenário de uma única fonte de dados, como um aplicativo de planejamento de recursos empresariais (ERP), no qual você pode executar a carga de trabalho operacional e analítica. Isso não substitui a necessidade de um data warehouse separado quando você precisa integrar dados de várias fontes antes de executar a carga de trabalho de análise ou quando você precisa de um desempenho analítico extremo usando dados pré-agregados, como cubos.

A análise em tempo real usa um índice columnstore não clusterizado atualizável em uma tabela rowstore. O índice columnstore mantém uma cópia dos dados, de modo que as cargas de trabalho OLTP e analytics são executadas em cópias separadas dos dados. Isso minimiza o impacto no desempenho de ambas as cargas de trabalho em execução ao mesmo tempo. O Mecanismo de Banco de Dados mantém automaticamente as alterações de índice para que as alterações OLTP estejam sempre up-todata para análise. Com este design, é possível e prático executar análises em tempo real em dados de up-todata. Isso funciona para tabelas baseadas em disco e com otimização de memória.

Exemplo para começar

Para começar a usar análises em tempo real:

  1. Identifique as tabelas em seu esquema operacional que contêm os dados necessários para análise.

  2. Para cada tabela, elimine todos os índices de B-tree que foram projetados principalmente para acelerar as análises existentes na sua carga de trabalho OLTP. Substitua-os por um único índice columnstore não clusterizado. Isso pode melhorar o desempenho geral da sua carga de trabalho OLTP, uma vez que há menos índices para manter.

    --This example creates a nonclustered columnstore index on an existing OLTP table.
--Create the table
CREATE TABLE t_account (
    accountkey int PRIMARY KEY,
    accountdescription nvarchar (50),
    accounttype nvarchar(50),
    unitsold int
);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
ON t_account (accountkey, accountdescription, unitsold)
;

    O índice columnstore em uma tabela otimizada para memória permite análises operacionais integrando tecnologias OLTP e columnstore na memória para oferecer alto desempenho para cargas de trabalho OLTP e de análise. O índice columnstore em uma tabela com otimização de memória deve ser o índice clusterizado, ou seja, deve incluir todas as colunas.

    -- This example creates a memory-optimized table with a columnstore index.
CREATE TABLE t_account (
    accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
    Accountdescription nvarchar (50),
    accounttype nvarchar(50),
    unitsold int,
    INDEX t_account_cci CLUSTERED COLUMNSTORE
    )
    WITH (MEMORY_OPTIMIZED = ON );

Agora você está pronto para executar análises operacionais em tempo real sem fazer alterações no seu aplicativo. As consultas de análises serão executadas contra o índice columnstore e as operações OLTP continuarão a ser executadas contra os índices B-tree OLTP. As cargas de trabalho OLTP continuam a funcionar, mas incorrem em alguma sobrecarga adicional para manter o índice columnstore. Consulte as otimizações de desempenho na próxima seção.

Mensagens no blogue

Leia as seguintes postagens no blog para saber mais sobre análise operacional em tempo real. Pode ser mais fácil entender as seções de dicas de desempenho se você olhar para as postagens do blog primeiro.

Videos

A série de vídeos Data Exposed contém mais detalhes sobre alguns dos recursos e considerações.

Dica de desempenho #1: Use índices filtrados para melhorar o desempenho da consulta

A execução de análises operacionais em tempo real pode afetar o desempenho da carga de trabalho OLTP. Este impacto deve ser mínimo. O Exemplo A mostra como usar índices filtrados para minimizar o impacto do índice columnstore não clusterizado na carga de trabalho transacional e, ao mesmo tempo, fornecer análises em tempo real.

Para minimizar a sobrecarga de manter um índice columnstore não clusterizado em uma carga de trabalho operacional, você pode usar uma condição filtrada para criar um índice columnstore não clusterizado somente nos dados quentes ou que mudam lentamente. Por exemplo, em um aplicativo de gerenciamento de pedidos, você pode criar um índice columnstore não clusterizado nos pedidos que já foram enviados. Uma vez que o pedido tenha sido enviado, ele raramente muda e, portanto, pode ser considerado um dado quente. Com um índice filtrado, os dados no índice columnstore não clusterizado exigem menos atualizações, reduzindo assim o impacto na carga de trabalho transacional.

As consultas de análises acessam de forma transparente dados frios e quentes, conforme necessário, para fornecer análises em tempo real. Se uma parte significativa da carga de trabalho operacional estiver tocando os dados 'quentes', essas operações não exigirão manutenção adicional do índice columnstore. Uma prática recomendada é ter um índice clusterizado de linha na(s) coluna(s) usada(s) na definição do índice filtrado. O Mecanismo de Banco de Dados usa o índice clusterizado para verificar rapidamente as linhas que não atenderam à condição filtrada. Sem esse índice clusterizado, uma verificação completa da tabela rowstore é necessária para localizar essas linhas, o que pode afetar negativamente o desempenho das consultas analíticas. Na ausência de índice clusterizado, você poderia criar um índice de árvore B não clusterizado filtrado complementar para identificar essas linhas, mas isso não é recomendado porque o acesso a uma grande variedade de linhas por meio de índices de árvore B não agrupados é caro.

Note

Um índice columnstore não clusterizado filtrado só é suportado em tabelas baseadas em disco. Não é suportado em tabelas com otimização de memória.

Exemplo A: Aceder a dados quentes do índice B-tree, dados mornos do índice columnstore.

Este exemplo usa uma condição filtrada (accountkey > 0) para estabelecer quais linhas são incluídas no índice columnstore. O objetivo é projetar a condição filtrada e as consultas subsequentes para acessar dados "quentes" que mudam com frequência do índice de árvore B+ e acessar os dados "quentes" mais estáveis do índice columnstore.

Diagrama mostrando índices combinados para dados mornos e quentes.

Note

O Otimizador de Consultas considera, mas nem sempre escolhe, o índice columnstore para o plano de consulta. Quando o otimizador de consulta escolhe o índice columnstore filtrado, ele combina de forma transparente as linhas do índice columnstore e as linhas que não atendem à condição filtrada para permitir análises em tempo real. Isso é diferente de um índice filtrado regular não clusterizado que pode ser usado apenas em consultas que se restringem às linhas presentes no índice.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

A consulta de análise é executada com o seguinte plano de consulta. Você pode ver que as linhas que não atendem à condição filtrada são acessadas por meio do índice de árvore B clusterizado.

Captura de ecrã do SQL Server Management Studio de um plano de consulta usando uma verificação de índice columnstore.

Para obter mais informações, consulte Blog: Índice columnstore não clusterizado filtrado.

Dica de desempenho #2: Transferir a análise para o secundário de leitura legível "Always On"

Embora você possa minimizar a manutenção do índice columnstore usando um índice columnstore filtrado, as consultas de análise ainda podem exigir recursos de computação significativos (CPU, I/O, memória) que afetam o desempenho da carga de trabalho operacional. Para a maioria das cargas de trabalho de missão crítica, nossa recomendação é usar a configuração Always On. Nessa configuração, você pode eliminar o impacto da execução de análises descarregando-as para um secundário legível.

Dica de desempenho #3: Reduzindo a fragmentação do índice mantendo dados ativos em grupos de linhas delta

As tabelas com índice de armazenamento em coluna podem ficar significativamente fragmentadas (ou seja, linhas eliminadas) se as operações de trabalho atualizarem/eliminarem linhas que foram compactadas. Um índice columnstore fragmentado leva a uma utilização ineficiente da memória/armazenamento. Além do uso ineficiente de recursos, também afeta negativamente o desempenho da consulta de análise devido à E/S extra e à necessidade de filtrar as linhas excluídas do conjunto de resultados.

As linhas excluídas não são fisicamente removidas até que você execute a desfragmentação de índice com REORGANIZE o comando ou reconstrua o índice columnstore em toda a tabela ou na(s) partição(ões) afetada(s). Tanto REORGANIZE quanto REBUILD são operações dispendiosas que retiram recursos que, de outro modo, poderiam ser utilizados para a carga de trabalho. Além disso, se as linhas forem comprimidas demasiado cedo, poderá ser necessário voltar a comprimir várias vezes devido a atualizações, resultando em sobrecarga de compressão desnecessária.

Você pode minimizar a fragmentação do índice usando COMPRESSION_DELAY a opção.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Para obter mais informações, consulte Blog: Atraso de compactação.

Aqui estão as práticas recomendadas:

  • Inserir / Consultar carga de trabalho: Se sua carga de trabalho estiver principalmente inserindo dados e consultando-os, o padrão COMPRESSION_DELAY 0 é a opção recomendada. As linhas recém-inseridas serão compactadas assim que 1 milhão de linhas forem inseridas em um único grupo de linhas delta. Alguns exemplos dessas cargas de trabalho são uma carga de trabalho DW tradicional ou uma análise de fluxo seletivo quando você precisa analisar o padrão de seleção em um aplicativo Web.

  • Carga de trabalho OLTP: Se a carga de trabalho for pesada em DML (ou seja, uma combinação pesada de Atualizar, Excluir e Inserir), poderá ver a fragmentação do índice columnstore examinando o DMV sys.dm_db_column_store_row_group_physical_stats. Se vires que > 10 linhas% estão marcadas como excluídas em grupos de linhas compactados recentemente, podes usar COMPRESSION_DELAY a opção para adicionar um atraso de tempo quando as linhas se tornarem elegíveis para compactação. Por exemplo, se para a sua carga de trabalho, os dados recém-inseridos permanecem 'quentes' (ou seja, são atualizados várias vezes) por 60 minutos, deve definir COMPRESSION_DELAY para 60.

O valor padrão da COMPRESSION_DELAY opção deve funcionar para a maioria dos clientes.

Para usuários avançados, recomendamos executar a seguinte consulta e coletar % de linhas excluídas nos últimos sete dias.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Se o número de linhas excluídas em grupos de linhas compactados > 20%, atingindo um platô em grupos de linhas mais antigos com < variação de 5% (referidos como grupos de linhas frios), então defina COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Essa abordagem funciona melhor com uma carga de trabalho estável e relativamente homogênea.

Conteúdo relacionado

  • Last updated on