Introducción al almacén de columnas para análisis operativos en tiempo realGet started with Columnstore for real-time operational analytics

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2016 incorpora análisis operativos en tiempo real, esto es, la posibilidad de ejecutar simultáneamente análisis y cargas de trabajo OLTP en las mismas tablas de base de datos.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. Aparte de poder ejecutar análisis en tiempo real, también puede prescindir del uso de ETL y de un almacén de datos.Besides running analytics in real time, you can also eliminate the need for ETL and a data warehouse.

Explicación de los análisis operativos en tiempo realReal-Time Operational Analytics Explained

Tradicionalmente, las empresas siempre han tenido sistemas independientes para las cargas de trabajo operativas (es decir, OLTP) y de análisis.Traditionally, businesses have had separate systems for operational (that is, OLTP) and analytics workloads. En estos sistemas, los trabajos de extracción, transformación y carga de datos (ETL) mueven periódicamente los datos desde el almacén operativo a un almacenamiento de análisis.For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. Los datos de análisis suelen residir en un almacén de datos o data mart dedicado a ejecutar consultas de análisis.The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. Si bien esto ha venido siendo lo habitual, plantea tres retos importantes:While this solution has been the standard, it has these three key challenges:

  • Complejidad.Complexity. La implementación de ETL puede conllevar una tarea de codificación considerable, especialmente para cargar solamente las filas modificadas.Implementing ETL can require considerable coding especially to load only the modified rows. Saber qué filas se han modificado puede ser bastante complicado.It can be complex to identify which rows have been modified.

  • Costo.Cost. La implementación de ETL requiere invertir en más licencias de software y hardware.Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • Latencia de datos.Data Latency. La implementación de ETL conlleva un retraso de tiempo a la hora de ejecutar los análisis.Implementing ETL adds a time delay for running the analytics. Por ejemplo, si el trabajo de ETL tiene lugar al final de cada día laborable, las consultas de análisis se ejecutarán en datos que llevan como mínimo un día de desfase.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 muchas empresas, este retraso es inaceptable porque el negocio depende de poder analizar los datos en tiempo real.For many businesses this delay is unacceptable because the business depends on analyzing data in real time. Por ejemplo, para poder detectar fraudes, es preciso analizar los datos operativos en tiempo real.For example, fraud-detection requires real-time analytics on operational data.

información general del análisis operativo en tiempo realreal-time operational analytics overview

Los análisis operativos en tiempo real ofrecen una solución a estos retos.Real-time operational analytics offers a solution to these challenges.
No comportan ningún retraso cuando las cargas de trabajo OLTP y de análisis se ejecutan en la misma tabla subyacente.There is no time delay when analytics and OLTP workloads run on the same underlying table. En las situaciones en las que se pueden usar análisis en tiempo real, los costos y la complejidad se reducen enormemente, ya que se pone fin a la necesidad de realizar trabajos ETL o de adquirir y mantener un almacén de datos independiente.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.

Nota

Los análisis operativos en tiempo real abordan un escenario con un único origen de datos, como una aplicación de planificación de recursos empresariales (ERP) en la que se pueden ejecutar las cargas de trabajo tanto operativas como de análisis.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. Esto no significa que no pueda necesitarse un almacén de datos independiente cuando haya que integrar datos procedentes de varios orígenes antes de ejecutar la carga de trabajo de análisis, o cuando necesite disponer de un rendimiento de análisis extremo con datos previamente agregados, como los 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.

Los análisis en tiempo real usan un índice de almacén de columnas actualizable en una tabla de almacén de filas.Real-time analytics uses an updateable columnstore index on a rowstore table. El índice de almacén de columnas mantiene una copia de los datos, por lo que las cargas de trabajo OLTP y de análisis ejecutarán copias de los datos distintas.The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. Esto reduce el impacto en el rendimiento que supone ejecutar ambas cargas de trabajo al mismo tiempo.This minimizes the performance impact of both workloads running at the same time. SQL Server mantiene automáticamente los cambios en el índice para que los cambios de OLTP siempre estén actualizados para los análisis.SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. Con este diseño, es posible (y útil) ejecutar análisis en tiempo real en los datos actualizados.With this design, it is possible and practical to run analytics in real time on up-to-date data. Esto es válido tanto para las tablas basadas en disco como para las tablas optimizadas para memoria.This works for both disk-based and memory-optimized tables.

Ejemplo introductorioGet Started Example

Para empezar a usar análisis en tiempo real:To get started with real-time analytics:

  1. Identifique las tablas del esquema operativo que contienen los datos necesarios para el análisis.Identify the tables in your operational schema that contain data required for analytics.

  2. En cada tabla, quite todos los índices de árbol b que están diseñados principalmente para acelerar el análisis existente en la carga de trabajo OLTP.For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. Sustitúyalos por un índice de almacén de columnas único.Replace them with a single columnstore index. Esto mejorará el rendimiento general de la carga de trabajo OLTP, ya que habrá menos índices que mantener.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)   
    ;  
    
    

    El índice de almacén de columnas de una tabla en memoria permite los análisis operativos al integrar las tecnologías de OLTP en memoria y almacén de columnas en memoria, con lo que se logra un alto rendimiento en las cargas de trabajo OLTP y análisis.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. El índice de almacén de columnas de una tabla en memoria debe incluir todas las columnas.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. Esto es todo lo que hay que hacer.This is all you need to do!

Le escuchamos: Si encuentra algo obsoleto o incorrecto en este artículo, como un paso o un ejemplo de código, indíquenoslo.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. Puede hacer clic en el botón Esta página situado en la sección Comentarios en la parte inferior de esta página.You can click the This page button in the Feedback section at the bottom of this page. Leemos todos los elementos de comentarios sobre SQL, normalmente el día siguiente.We read every item of feedback about SQL, typically the next day. Gracias.Thanks.

Ya está listo para ejecutar análisis operativos en tiempo real, sin haber realizado ningún cambio en la aplicación.You are now ready to run real-time operational analytics without making any changes to your application. Las consultas de análisis se ejecutarán en el índice de almacén de columnas y las operaciones OLTP seguirán ejecutándose en los índices de árbol b de OLTP.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. Las cargas de trabajo OLTP seguirán produciéndose, si bien con una ligera sobrecarga adicional para mantener el índice de almacén de columnas.The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. Vea las optimizaciones de rendimiento en la siguiente sección.See the performance optimizations in the next section.

Entradas de blogBlog Posts

Lea las entradas de blog de Sunil Agarwal para obtener más información sobre los análisis operativos en tiempo real.Read Sunil Agarwal's blog posts to learn more about real-time operational analytics. Si lee estas entradas de blog primero, probablemente le será más fácil entender las secciones de consejos de rendimiento.It might be easier to understand the performance tips sections if you look at the blog posts first.

Sugerencia de rendimiento 1: usar índices filtrados para mejorar el rendimiento de las consultasPerformance tip #1: Use filtered indexes to improve query performance

Los análisis operativos en tiempo real pueden tener un impacto negativo en el rendimiento de la carga de trabajo OLTP.Running real-time operational analytics can impact the performance of the OLTP workload. Este impacto debería ser mínimo.This impact should be minimal. En el siguiente ejemplo se muestra cómo usar índices filtrados para minimizar el impacto del índice de almacén de columnas no agrupado en la carga de trabajo transaccional, mientras se siguen realizando análisis en tiempo 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 reducir la sobrecarga derivada de mantener un índice de almacén de columnas no agrupado en una carga de trabajo operativa, puede usar una condición de filtrado para crear un índice de almacén de columnas no agrupado únicamente de los datos semiactivos o de variación lenta.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 ejemplo, en una aplicación de administración de pedidos, puede crear un índice de almacén de columnas no agrupado de los pedidos que ya se hayan enviado.For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. Una vez que un pedido se envía, este apenas si cambia y, por tanto, se puede considerar como un dato semiactivo.Once the order has shipped, it rarely changes and therefore can be considered warm data. Con el índice filtrado, los datos del índice de almacén de columnas no agrupado requieren menos actualizaciones, lo que reduce el impacto en la carga de trabajo transaccional.With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

Las consultas de análisis tienen un acceso transparente a los datos tanto activos como semiactivos, según sea necesario para proporcionar análisis en tiempo real.Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. Si una parte considerable de la carga de trabajo operativa usa los datos 'activos', esas operaciones no requerirán un mantenimiento adicional del índice del almacén de columnas.If a significant part of the operational workload is touching the 'hot' data, those operations will not require additional maintenance of the columnstore index. Un procedimiento recomendado es tener un índice agrupado de almacén de filas en las columnas usadas en la definición del índice filtrado.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL Server usa el índice agrupado para examinar rápidamente las filas que no cumplen la condición de filtrado.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. Sin este índice agrupado, sería necesario realizar un recorrido de tabla completo de la tabla de almacén de filas para encontrar dichas filas, lo que puede repercutir negativamente y en gran medida en el rendimiento de las consultas de análisis.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. Si no hay un índice agrupado, podría crear un índice complementario de árbol b no agrupado filtrado para identificar esas filas, pero esto no es recomendable porque el acceso a un amplio rango de filas a través de índices de árbol b no agrupados es muy costoso.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.

Nota

Un índice de almacén de columnas no agrupado filtrado solo se puede usar en tablas basadas en disco.A filtered nonclustered columnstore index is only supported on disk-based tables. No se admite en tablas optimizadas para memoria.It is not supported on memory-optimized tables

Ejemplo A: acceso a datos activos del índice de árbol b y a datos semiactivos del índice de almacén de columnasExample A: Access hot data from btree index, warm data from columnstore index

En este ejemplo se usa una condición filtrada (accountkey > 0) para establecer qué filas se van a incluir en el índice de almacén de columnas.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. El objetivo es diseñar la condición de filtrado y las consultas posteriores para acceder a los datos "activos" del índice de árbol b que cambian con frecuencia, así como para acceder a los datos "semiactivos" del índice de almacén de columnas, que son más estables.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 datos activos y semiactivosCombined indexes for warm and hot data

Nota

El optimizador de consultas tendrá en cuenta (pero no siempre elegirá) el índice de almacén de columnas para el plan de consulta.The query optimizer will consider, but not always choose, the columnstore index for the query plan. Cuando el optimizador de consultas elige el índice de almacén de columnas filtrado, combina de forma transparente tanto las filas del índice de almacén de columnas como las filas que no cumplen con la condición de filtrado para permitir los análisis en tiempo 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. Esto difiere de un índice filtrado no agrupado regular, que solo se puede usar en las consultas limitadas a las filas existentes en el í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  

La consulta de análisis se ejecutará con el siguiente plan de consulta.The analytics query will execute with the following query plan. Aquí se aprecia que el acceso a las filas que no cumplen con la condición de filtro se efectúa a través del índice de árbol b agrupado.You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

Plan de consultaQuery plan

Consulte el blog para obtener información detallada sobre los índices de almacén de columnas no agrupado filtrado.Please refer to the blog for details on filtered nonclustered columnstore index.

Sugerencia de rendimiento 2: descargar el análisis en una secundaria legible de AlwaysOnPerformance tip #2: Offload analytics to Always On readable secondary

Aunque puede usar un índice de almacén de columnas filtrado para minimizar el mantenimiento de los índices de almacén de columnas, las consultas de análisis seguirán necesitando importantes cantidades de recursos informáticos (CPU, E/S, memoria) que afectan al rendimiento de las cargas de trabajo operativas.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. Nuestra recomendación para la mayor parte de las cargas de trabajo críticas es usar la configuración de AlwaysOn.For most mission critical workloads, our recommendation is to use the Always On configuration. En esta configuración, puede eliminar el impacto de los análisis descargándolos en una secundaria legible.In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

Sugerencia de rendimiento 3: reducir la fragmentación de índice conservando los datos activos en grupos de filas deltaPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

Las tablas con índices de almacén de columnas pueden llegar a fragmentarse (es decir, se eliminan filas) de forma muy acusada si la carga de trabajo actualiza o elimina filas que se han comprimido.Tables with columnstore index may get significantly fragmented (that is, deleted rows) if the workload updates/deletes rows that have been compressed. Un índice de almacén de columnas fragmentado conduce a un uso ineficaz de la memoria y el almacenamiento.A fragmented columnstore index leads to inefficient utilization of memory/storage. Pero, aparte del uso ineficaz de los recursos, también repercute negativamente en el rendimiento de las consultas de análisis, dada la E/S adicional y la necesidad de filtrar las filas eliminadas del 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.

Las filas eliminadas no se quitarán físicamente hasta que se lleve a cabo una desfragmentación del índice con el comando REORGANIZE o hasta que se vuelva a generar el índice de almacén de columnas en toda la tabla o en las particiones afectadas.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). Ambos comandos, REORGANIZE y REBUILD, son operaciones costosas que consumen recursos que, de otro modo, se podrían usar para la carga de trabajo.Both REORGANIZE and Index REBUILD are expensive operations taking resources away which otherwise could be used for the workload. Además, si las filas se comprimen demasiado pronto, puede que haya que volver a comprimirlas varias veces debido a las actualizaciones, lo que daría lugar a una sobrecarga de compresión innecesaria.Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
La fragmentación del índice se puede reducir con la opción 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);  
  
;  

Consulte el blog para obtener información detallada sobre el retraso de compresión.Please refer to the blog for details on compression delay.

A continuación encontrará algunos procedimientos recomendados.Here are the recommended best practices

  • Carga de trabajo de inserción/consulta: si la carga de trabajo consiste principalmente en insertar datos y realizar consultas sobre estos, la opción recomendada para COMPRESSION_DELAY es 0.Insert/Query workload: If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. Las filas recién insertadas se comprimirán cuando se haya insertado 1 millón de filas en un solo grupo de filas delta.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    Algunos ejemplos de este tipo de carga de trabajo son: (a) carga de trabajo de DW tradicional o (b) análisis de secuencia de clics cuando hay que analizar el patrón de clics en una aplicación 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 trabajo OLTP: si la carga de trabajo hace un uso profuso de DML (es decir, un uso combinado intensivo de actualizaciones, eliminaciones e inserciones), puede ver la fragmentación de índices de almacén de columnas examinando el sys de 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. Si ve que más de un 10 % de las filas se marcan como eliminadas en los grupos de filas comprimidos recientemente, puede usar la opción COMPRESSION_DELAY para agregar un retraso cuando las filas sean aptas para la compresión.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 ejemplo, si en la carga de trabajo las filas recién insertadas se mantienen como "activas" (es decir, se actualizan varias veces) durante, digamos, 60 minutos, conviene establecer la opción COMPRESSION_DELAY en 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.

Es de esperar que la mayoría de los clientes no tenga que hacer nada.We expect most customers do not need to do anything. El valor predeterminado de la opción COMPRESSION_DELAY debería valerles.The default value of COMPRESSION_DELAY option should work for them.
En el caso de los usuarios avanzados, se recomienda ejecutar la siguiente consulta y recopilar un porcentaje de las filas eliminadas en los últimos 7 días.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  

Si el número de filas eliminadas en los grupos de filas comprimidas es mayor del 20 %, teniendo un nivel predefinido de los grupos de filas más antiguos con una variación inferior a 5 % (denominados grupos de filas inactivos), establezca 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). Tenga en cuenta que este método funciona mejor en cargas de trabajo estables y relativamente homogéneas.Note that this approach works best with a stable and relatively homogeneous workload.

Consulte tambiénSee Also

Guía de índices de almacén de columnas Columnstore Indexes Guide
Carga de datos de índices de almacén de columnas Columnstore Indexes Data Loading
Rendimiento de las consultas de índices de almacén de columnas Columnstore Indexes Query Performance
Índices de almacén de columnas para el almacenamiento de datos Columnstore Indexes for Data Warehousing
Reorganizar y volver a generar índicesReorganize and Rebuild Indexes