Introdução ao Columnstore para análise operacional em tempo realGet started with Columnstore for real-time operational analytics

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

O SQL Server 2016 apresenta análise operacional em tempo real, a capacidade de executar cargas de trabalho OLTP e analíticas nas mesmas tabelas de banco de dados ao mesmo tempo.SQL Server 2016 introduces real-time operational analytics, the ability to run both analytics and OLTP workloads on the same database tables at the same time. Além de executar análise em tempo real, você também pode eliminar a necessidade de ETL e de um data warehouse.Besides running analytics in real time, you can also eliminate the need for ETL and a data warehouse.

Análise operacional em tempo real explicadoReal-Time Operational Analytics Explained

Tradicionalmente, as empresas tinham sistemas separados para cargas de trabalho operacionais (isto é, OLTP) e analíticas.Traditionally, businesses have had separate systems for operational (that is, OLTP) and analytics workloads. Para tais sistemas, os trabalhos ETL (Extrair, Transformar e Carregar) movem os dados regularmente do repositório operacional para um repositório analítico.For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. Os dados analíticos geralmente são armazenados em um data warehouse ou data mart dedicado para execução de consultas analíticas.The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. Embora essa solução tenha sido o padrão, ela apresenta estes três desafios principais:While this solution has been the standard, it has these three key challenges:

  • Complexidade.Complexity. Implementar o ETL pode exigir codificação considerável especialmente para carregar apenas as linhas modificadas.Implementing ETL can require considerable coding especially to load only the modified rows. Pode haver complexidade na identificação de quais linhas foram modificadas.It can be complex to identify which rows have been modified.

  • CustoCost. Implementar o ETL exige o custo da compra de licenças adicionais de hardware e software.Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • Latência de dados.Data Latency. Implementar o ETL adiciona um atraso para execução da análise.Implementing ETL adds a time delay for running the analytics. Por exemplo, se o trabalho ETL for executado no final de cada dia útil, as consultas analíticas serão executadas nos dados com pelo menos um dia.For example, if the ETL job runs at the end of each business day, the analytics queries will run on data that is at least a day old. Para muitas empresas, esse atraso é inaceitável porque a empresa depende da análise de dados em tempo real.For many businesses this delay is unacceptable because the business depends on analyzing data in real time. Por exemplo, a detecção de fraudes requer análise em tempo real em dados operacionais.For example, fraud-detection requires real-time analytics on operational data.

visão geral da análise operacional em tempo realreal-time operational analytics overview

A análise operacional em tempo real oferece uma solução para esses desafios.Real-time operational analytics offers a solution to these challenges.
Não há atraso algum quando as cargas de trabalho OLTP e analíticas são executadas na mesma tabela subjacente.There is no time delay when analytics and OLTP workloads run on the same underlying table. Em cenários que podem usar análise em tempo real, os custos e a complexidade são enormemente reduzidos com a eliminação da necessidade de ETL e da necessidade de compra e manutenção de um data warehouse separado.For scenarios that can use real-time analytics, the costs and complexity are greatly reduced by eliminating the need for ETL and the need to purchase and maintain a separate data warehouse.

Observação

A análise operacional em tempo real visa o cenário de uma fonte de dados individual, como um aplicativo ERP (planejamento de recursos empresariais) no qual é possível executar a carga de trabalho operacional e analítica.Real-time operational analytics targets the scenario of a single data source such as an enterprise resource planning (ERP) application on which you can run both the operational and the analytics workload. Isso não substitui a necessidade de um data warehouse separado quando você precisar integrar dados de várias fontes antes de executar a carga de trabalho analítica ou quando você exigir desempenho extremo de análise usando dados pré-agregados, como cubos.This does not replace the need for a separate data warehouse when you need to integrate data from multiple sources before running the analytics workload or when you require extreme analytics performance using pre-aggregated data such as cubes.

A análise em tempo real usa um índice columnstore atualizável em uma tabela rowstore.Real-time analytics uses an updateable columnstore index on a rowstore table. O índice columnstore mantém uma cópia dos dados para que as cargas de trabalho OLTP e analítica sejam executadas em cópias separadas dos dados.The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. Isso minimiza o impacto no desempenho de ambas as cargas de trabalho em execução ao mesmo tempo.This minimizes the performance impact of both workloads running at the same time. O SQL Server mantém automaticamente as mudanças no índice para que as alterações no OLTP estejam sempre atualizadas para análise.SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. Com esse design, é possível e prático executar análise em tempo real em dados atualizados.With this design, it is possible and practical to run analytics in real time on up-to-date data. Isso funciona para tabelas com otimização de memória e baseadas em disco.This works for both disk-based and memory-optimized tables.

Exemplo de introduçãoGet Started Example

Para começar com a análise em tempo real:To get started with real-time analytics:

  1. Identifique as tabelas em seu esquema operacional que contenham dados obrigatórios para análise.Identify the tables in your operational schema that contain data required for analytics.

  2. Em cada tabela, descarte todos os índices btree desenvolvidos basicamente para acelerar a análise existente em sua carga de trabalho OLTP.For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. Substitua-os por um único índice columnstore.Replace them with a single columnstore index. Isso pode melhorar o desempenho geral da carga de trabalho OLTP, já que haverá menos índices para manter.This can improve the overall performance of your OLTP workload since there will be fewer indexes to maintain.

    --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 na memória permite análise operacional integrando tecnologias OLTP in-memory e columnstore in-memory para oferecer alto desempenho a cargas de trabalho OLTP e analíticas.The columnstore index on an in-memory table allows operational analytics by integrating in-memory OLTP and in-memory columnstore technologies to deliver high performance for both OLTP and analytics workloads. O índice columnstore em uma tabela na memória deve incluir todas as colunas.The columnstore index on an in-memory table must include all the columns.

    -- 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 );  
    GO  
    
    
  3. Isso é tudo o que você precisa fazer!This is all you need to do!

Somos todos ouvidos: Se você encontrar algo desatualizado ou incorreto neste artigo, como uma etapa ou um exemplo de código, informe-nos.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. Você pode clicar no botão Esta página na seção Comentários na parte inferior desta página.You can click the This page button in the Feedback section at the bottom of this page. Lemos todos os itens de comentários sobre o SQL, normalmente no dia seguinte.We read every item of feedback about SQL, typically the next day. Obrigado.Thanks.

Agora você está pronto para executar análise operacional em tempo real sem fazer alterações no seu aplicativo.You are now ready to run real-time operational analytics without making any changes to your application. As consultas analíticas serão executadas no índice columnstore e as operações OLTP continuarão sendo executadas nos índices btree OLTP.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. As cargas de trabalho OLTP continuarão sendo executadas, mas incorrerão em alguma sobrecarga adicional para manter o índice columnstore.The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. Veja as otimizações de desempenho na próxima seção.See the performance optimizations in the next section.

Postagens de blogBlog Posts

Leia as postagens no blog de Sunil Agarwal para saber mais sobre análise operacional em tempo real.Read Sunil Agarwal's blog posts to learn more about real-time operational analytics. Talvez seja mais fácil entender as seções de dicas de desempenho se você ler essas postagens primeiro.It might be easier to understand the performance tips sections if you look at the blog posts first.

Dica de desempenho nº 1: use índices filtrados para melhorar o desempenho da consultaPerformance tip #1: Use filtered indexes to improve query performance

Executar a análise operacional em tempo real pode afetar o desempenho da carga de trabalho OLTP.Running real-time operational analytics can impact the performance of the OLTP workload. Esse impacto deve ser mínimo.This impact should be minimal. O exemplo abaixo mostra como usar índices filtrados para minimizar o impacto do índice columnstore não clusterizado na carga de trabalho transacional enquanto ainda estiver entregando análise em tempo real.The example below shows how to use filtered indexes to minimize impact of nonclustered columnstore index on transactional workload while still delivering analytics in real time.

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 apenas de dados passivos ou que mudam de modo constante e lento.To minimize the overhead of maintaining a nonclustered columnstore index on an operational workload, you can use a filtered condition to create a nonclustered columnstore index only on the warm or slowly changing data. Por exemplo, em um aplicativo de gerenciamento de pedidos, você pode criar um índice columnstore não clusterizado dos pedidos que já foram despachados.For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. Depois que o pedido tiver sido enviado, ele raramente muda e, portanto, pode ser considerado como dados passivos.Once the order has shipped, it rarely changes and therefore can be considered warm data. Com o índice filtrado, os dados no índice columnstore não clusterizado exigem menos atualizações, reduzindo, assim, o impacto na carga de trabalho transacional.With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

As consultas analíticas acessam de modo transparente os dados passivos e ativos, conforme a necessidade de fornecer análise em tempo real.Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. Se uma parte significativa da carga de trabalho operacional estiver tocando os dados "ativos", essas operações não exigirão manutenção adicional do índice columnstore.If a significant part of the operational workload is touching the 'hot' data, those operations will not require additional maintenance of the columnstore index. Uma prática recomendada é ter um índice clusterizado rowstore em colunas usadas na definição do índice filtrado.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. O SQL Server usa o índice clusterizado para verificar rapidamente as linhas que não atenderam à condição filtrada.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. Sem esse índice clusterizado, uma verificação de tabela completa da tabela rowstore será exigida para encontrar essas linhas, que podem afetar negativamente o desempenho da consulta analítica de modo significativo.Without this clustered index, a full table scan of the rowstore table will be required to find these rows, which can negatively impact the performance of analytics query significantly. Na ausência do índice clusterizado, você pode criar um índice btree não clusterizado filtrado complementar para identificar tais linhas, mas não é recomendado, pois acessar grandes intervalos de linhas por meio de índices btree não clusterizados custa caro.In the absence of clustered index, you could create a complementary filtered nonclustered btree index to identify such rows but it is not recommended because accessing large range of rows through nonclustered btree indexes is expensive.

Observação

Um índice columnstore não clusterizado filtrado é permitido apenas em tabelas baseadas em disco.A filtered nonclustered columnstore index is only supported on disk-based tables. Ele não é permitido em tabelas com otimização de memóriaIt is not supported on memory-optimized tables

Exemplo A: acessar dados ativos do índice btree, dados passivos do índice columnstoreExample A: Access hot data from btree index, warm data from columnstore index

Este exemplo usa uma condição filtrada (accountkey > 0) para estabelecer quais linhas estarão no índice columnstore.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. A meta é criar a condição filtrada e as consultas subsequentes para acessar dados "frequentes" que mudam frequentemente no índice btree e acessar os dados "passivos" mais estáveis no índice columnstore.The goal is to design the filtered condition and subsequent queries to access frequently changing "hot" data from the btree index, and to access the more stable "warm" data from the columnstore index.

Índices combinados para dados ativos e passivosCombined indexes for warm and hot data

Observação

O otimizador de consulta vai considerar, mas nem sempre escolherá, o índice columnstore para o plano de consulta.The query optimizer will consider, but not always choose, the columnstore index for the query plan. Quando o otimizador de consulta escolher o índice columnstore filtrado, ele combinará de modo transparente as linhas do índice columnstore, bem como as linhas que não atendem à condição filtrada para permitir análise em tempo real.When the query optimizer chooses the filtered columnstore index, it transparently combines the rows both from columnstore index as well as the rows that do not meet the filtered condition to allow real-time analytics. Isso é diferente de um índice filtrado não clusterizado regular, que pode ser usado apenas em consultas que restringem a si mesmas a linhas presentes no índice.This is different from a regular nonclustered filtered index which can be used only in queries that restrict themselves to the rows present in the index.

--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 Fullfillment 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 analítica será executada com o plano de consulta a seguir.The analytics query will execute with the following query plan. Você pode ver que as linhas que não atendem à condição filtrada são acessadas por meio do índice btree clusterizado.You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

Plano de consultaQuery plan

Veja o blog para obter detalhes sobre o índice columnstore não clusterizado filtrado.Please refer to the blog for details on filtered nonclustered columnstore index.

Dica de desempenho nº 2: transferir a análise para o secundário legível Always OnPerformance tip #2: Offload analytics to Always On readable secondary

Mesmo que você possa minimizar a manutenção do índice columnstore usando um índice columnstore filtrado, as consultas analíticas ainda podem exigir recursos consideráveis de computação (CPU, E/S, memória), que afetam o desempenho da carga de trabalho operacional.Even though you can minimize the columnstore index maintenance by using a filtered columnstore index, the analytics queries can still require significant computing resources (CPU, IO, memory) which impact the operational workload performance. Para a maioria das cargas de trabalho de missão crítica, nossa recomendação é usar a configuração Always On.For most mission critical workloads, our recommendation is to use the Always On configuration. Nessa configuração, você pode eliminar o impacto de executar a análise transferindo-a para um secundário legível.In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

Dica de desempenho nº 3: reduzir a fragmentação de índice mantendo dados ativos em rowgroups deltaPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

Tabelas com índice columnstore poderão ser significativamente fragmentadas (isto é, linhas excluídas) se a carga de trabalho atualizar/excluir linhas que foram compactadas.Tables with columnstore index may get significantly fragmented (that is, deleted rows) if the workload updates/deletes rows that have been compressed. Um índice columnstore fragmentado leva à utilização ineficaz de memória/armazenamento.A fragmented columnstore index leads to inefficient utilization of memory/storage. Além do uso ineficaz de recursos, ele também afeta negativamente o desempenho da consulta de análise devido à E/S extra e à necessidade de filtrar as linhas excluídas no conjunto de resultados.Besides inefficient use of resources, it also negatively impacts the analytics query performance because of extra IO and the need to filter the deleted rows from the result set.

As linhas excluídas não são fisicamente removidas até que você execute a desfragmentação do índice com o comando REORGANIZE ou recrie o índice columnstore na tabela inteira ou nas partições afetadas.The deleted rows are not physically removed until you run index defragmentation with REORGANIZE command or rebuild the columnstore index on the entire table or the affected partition(s). REORGANIZE e INDEX REBUILD são operações caras que eliminam recursos que, de outra forma, poderiam ser usados para a carga de trabalho.Both REORGANIZE and Index REBUILD are expensive operations taking resources away which otherwise could be used for the workload. Além disso, no caso de linhas compactadas com muita antecedência, talvez elas precisem ser recompactadas várias vezes devido às atualizações, gerando sobrecarga desnecessária de compactação.Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
Você pode minimizar a fragmentação de índice usando a opção COMPRESSION_DELAY.You can minimize index fragmentation using COMPRESSION_DELAY option.

  
-- 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);  
  
;  

Veja o blog para obter detalhes sobre atraso na compactação.Please refer to the blog for details on compression delay.

Veja as práticas recomendadasHere are the recommended best practices

  • Inserir/consultar a carga de trabalho: se a carga de trabalho for basicamente inserir dados e consultá-los, o padrão COMPRESSION_DELAY de 0 será a opção recomendada.Insert/Query workload: If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. As linhas recentemente inseridas serão compactadas uma vez que 1 milhão de linhas foram inseridas em um único rowgroup delta.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    Alguns exemplos de tal carga de trabalho são (a) carga de trabalho DW tradicional (b) análise de fluxo de clique quando você precisa analisar o padrão de clique em um aplicativo Web.Some examples of such workload are (a) traditional DW workload (b) click-stream analysis when you need to analyze the click pattern in a web application.

  • Carga de trabalho OLTP: se a carga de trabalho for DML pesada (isto é, combinação pesada de Atualizar, Excluir e Inserir), você poderá ver a fragmentação do índice columnstore examinando o sys DMV.OLTP workload: If the workload is DML heavy (that is, heavy mix of Update, Delete and Insert), you may see columnstore index fragmentation by examining the DMV sys. dm_db_column_store_row_group_physical_stats.dm_db_column_store_row_group_physical_stats. Caso veja que mais de 10% das linhas são marcadas como excluídas em rowgroups recentemente compactados, você poderá usar a opção COMPRESSION_DELAY para adicionar atraso quando as linhas se tornarem qualificadas para compactação.If you see that > 10% rows are marked deleted in recently compressed rowgroups, you can use COMPRESSION_DELAY option to add time delay when rows become eligible for compression. Por exemplo, se para sua carga de trabalho, os dados recentemente inseridos permanecerem 'frequentes' (isso é, forem atualizados várias vezes) digamos que por 60 minutos, você deverá escolher COMPRESSION_DELAY para ser 60.For example, if for your workload, the newly inserted stays 'hot' (that is, gets updated multiple times) for say 60 minutes, you should choose COMPRESSION_DELAY to be 60.

Esperamos que a maioria dos clientes não precise fazer nada.We expect most customers do not need to do anything. O valor padrão da opção COMPRESSION_DELAY deve funcionar para eles.The default value of COMPRESSION_DELAY option should work for them.
Para usuários avançados, é recomendável executar a consulta abaixo e coletar a % de linhas excluídas nos últimos 7 dias.For advance users, we recommend running the query below and collect % of deleted rows over the last 7 days.

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 > GETDATE() - 7  
ORDER BY created_time DESC  

Se o número de linhas excluídas em rowgroups compactados for superior a 20%, lidando com rowgroups antigos com variação inferior a 5% (referido como rowgroups frios) defina COMPRESSION_DELAY = (youngest_rowgroup_created_time – current_time).If the number of deleted rows in compressed rowgroups > 20%, plateauing in older rowgroups with < 5% variation (referred to as cold rowgroups) set COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Observe que essa abordagem funciona melhor com uma carga de trabalho relativamente homogênea e estável.Note that this approach works best with a stable and relatively homogeneous workload.

Consulte TambémSee Also

Guia de Índices Columnstore Columnstore Indexes Guide
Carregamento de dados dos índices columnstore Columnstore Indexes Data Loading
Desempenho de consultas de índices ColumnStore Columnstore Indexes Query Performance
Índices columnstore para Data Warehouse Columnstore Indexes for Data Warehousing
Reorganizar e recompilar índicesReorganize and Rebuild Indexes