Начало работы с Columnstore для получения операционной аналитики в реальном времениGet started with Columnstore for real-time operational analytics

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL Server 2016 представляет операционную аналитику в реальном времени, дающую возможность одновременно выполнять рабочие нагрузки аналитики и OLTP в одной таблице базы данных.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. Кроме выполнения анализа в реальном времени, можно устранить потребность в процессе извлечения, преобразования и загрузки, а также в хранилище данных.Besides running analytics in real time, you can also eliminate the need for ETL and a data warehouse.

Пояснения к операционной аналитике в реальном времениReal-Time Operational Analytics Explained

Традиционно предприятия использовали отдельные системы для операционных (т. е. OLTP) и аналитических рабочих нагрузок.Traditionally, businesses have had separate systems for operational (that is, OLTP) and analytics workloads. Для таких систем задания извлечения, преобразования и загрузки (ETL) регулярно перемещали данные из операционного хранилища в хранилище аналитики.For such systems, Extract, Transform, and Load (ETL) jobs regularly move the data from the operational store to an analytics store. Аналитические данные обычно хранятся в хранилище или киоске данных, предназначенном для выполнения аналитических запросов.The analytics data is usually stored in a data warehouse or data mart dedicated to running analytics queries. Хотя такое решение являлось стандартным, у него есть три существенных недостатка:While this solution has been the standard, it has these three key challenges:

  • Сложность.Complexity. Реализация извлечения, преобразования и загрузки может потребовать большого объема кода, особенно для загрузки только измененных строк.Implementing ETL can require considerable coding especially to load only the modified rows. Определение того, какие именно строки изменены, может оказаться непростой задачей.It can be complex to identify which rows have been modified.

  • Стоимость.Cost. Для реализации извлечения, преобразования и загрузки нужно приобрести дополнительное оборудование и лицензии на программное обеспечение.Implementing ETL requires the cost of purchasing additional hardware and software licenses.

  • Задержка данных.Data Latency. Реализация извлечения, преобразования и загрузки добавляет задержку для выполнения анализа.Implementing ETL adds a time delay for running the analytics. Например, если задание ETL выполняется в конце каждого рабочего дня, аналитические запросы будут использовать данные, которые устарели по меньшей мере на день.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. Для многих организаций такая задержка недопустима, поскольку их бизнес зависит от анализа данных в реальном времени.For many businesses this delay is unacceptable because the business depends on analyzing data in real time. Например, для выявления мошенничества требуется анализ рабочих данных в реальном времени.For example, fraud-detection requires real-time analytics on operational data.

Обзор операционной аналитики в режиме реального времениreal-time operational analytics overview

Операционная аналитика в реальном времени предлагает решение этих проблем.Real-time operational analytics offers a solution to these challenges.
При выполнении рабочих нагрузок аналитики и OLTP в одной базовой таблице задержка отсутствует.There is no time delay when analytics and OLTP workloads run on the same underlying table. В сценариях, где применима аналитика в реальном времени, значительно снижаются затраты и сложность за счет отказа от извлечения, преобразования и загрузки, а также от приобретения и обслуживания отдельного хранилища данных.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.

Примечание

Сценарий операционной аналитики в реальном времени ориентирован на отдельный источник данных, такой как приложение управления ресурсами предприятия (ERP), где можно выполнять как операционные, так и аналитические рабочие нагрузки.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. Однако отдельное хранилище данных по прежнему необходимо, если требуется интегрировать данные из нескольких источников перед выполнением аналитической рабочей нагрузки либо нужно обеспечить максимальную производительность анализа с помощью данных, прошедших предварительную статистическую обработку, таких как кубы.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.

Аналитика в реальном времени использует индекс columnstore для таблицы rowstore.Real-time analytics uses an updateable columnstore index on a rowstore table. Индекс columnstore хранит копию данных, поэтому рабочие нагрузки аналитики и OLTP выполняются для отдельных копий данных.The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. Это сводит к минимуму влияние одновременно выполняющихся рабочих нагрузок на производительность.This minimizes the performance impact of both workloads running at the same time. SQL Server автоматически сохраняет изменения индекса, поэтому для аналитики всегда доступны актуальные изменения OLTP.SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. Такой подход позволяет удобно анализировать актуальные данные в реальном времени.With this design, it is possible and practical to run analytics in real time on up-to-date data. Он работает как с дисковыми, так и с оптимизированными для памяти таблицы.This works for both disk-based and memory-optimized tables.

Пример для начала работыGet Started Example

Чтобы начать работу с операционной аналитикой в реальном времени, выполните следующее.To get started with real-time analytics:

  1. Определите те таблицы в рабочей схеме, которые содержат данные для анализа.Identify the tables in your operational schema that contain data required for analytics.

  2. Для каждой таблицы удалите все индексы сбалансированного дерева, предназначенные для ускорения аналитики по рабочей нагрузке OLTP.For each table, drop all btree indexes that are primarily designed to speed up existing analytics on your OLTP workload. Замените их одним индексом columnstore.Replace them with a single columnstore index. Это позволяет повысить общую производительность рабочей нагрузки OLTP, так как потребуется обрабатывать меньше индексов.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)   
    ;  
    
    

    Индекс columnstore в таблице в памяти позволяет осуществлять операционную аналитику путем интеграции технологий OLTP в памяти и columnstore в памяти для получения высокой производительности рабочих нагрузок OLTP и аналитики.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. Индекс columnstore для таблицы в памяти должен включать все столбцы.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. Вот и все, что нужно сделать!This is all you need to do!

Теперь все готово для запуска операционной аналитики в реальном времени без внесения изменений в приложение.You are now ready to run real-time operational analytics without making any changes to your application. Аналитические запросы будут использовать индекс columnstore, а операции OLTP продолжат использовать индексы сбалансированного дерева OLTP.Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP btree indexes. Рабочие нагрузки OLTP продолжат выполняться, однако появятся некоторые дополнительные издержки на обработку индекса columnstore.The OLTP workloads will continue to perform, but will incur some additional overhead to maintain the columnstore index. В следующем разделе описаны процессы оптимизации производительности.See the performance optimizations in the next section.

Записи блогаBlog Posts

Ознакомьтесь с записями блога Сунила Агарвала (Sunil Agarwal), содержащими дополнительные сведения об операционной аналитике в реальном времени.Read Sunil Agarwal's blog posts to learn more about real-time operational analytics. Это поможет вам лучше понять приведенные ниже советы по производительности.It might be easier to understand the performance tips sections if you look at the blog posts first.

Совет для повышения производительности № 1. Для улучшения производительности запросов пользуйтесь отфильтрованными индексамиPerformance tip #1: Use filtered indexes to improve query performance

Выполнение операционной аналитики в реальном времени может повлиять на производительность рабочей нагрузки OLTP.Running real-time operational analytics can impact the performance of the OLTP workload. Это влияние должно быть минимальным.This impact should be minimal. В приведенном ниже примере показано, как использовать отфильтрованные индексы для минимизации влияния некластеризованного индекса columnstore на транзакционную рабочую нагрузку при одновременном предоставлении аналитики в реальном времени.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.

Чтобы свести к минимуму дополнительные издержки на ведение некластеризованного индекса columnstore для операционной рабочей нагрузки, можно использовать отфильтрованное условие для создания некластеризованного индекса columnstore только для теплых или медленно изменяющихся данных.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. Например, в приложении управления заказом можно создать некластеризованный индекс columnstore для уже доставленных заказов.For example, in an order management application, you can create a nonclustered columnstore index on the orders that have already been shipped. Заказы редко изменяются после доставки, поэтому эти данные можно считать теплыми.Once the order has shipped, it rarely changes and therefore can be considered warm data. При наличии отфильтрованного индекса данным в некластеризованном индексе columnstore требуется меньше обновлений, что снижает влияние на транзакционную рабочую нагрузку.With Filtered index, the data in nonclustered columnstore index requires fewer updates thereby lowering the impact on transactional workload.

Аналитические запросы прозрачно обращаются как к теплым, так и к горячим данным для обеспечения аналитики в реальном времени.Analytics queries transparently access both warm and hot data as needed to provide real-time analytics. Если значительная часть операционной рабочей нагрузки связана с "горячими" данными, этим операциям не потребуется дополнительная обработка индекса 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. Для столбцов, используемых в определении отфильтрованного индекса, рекомендуется иметь кластеризованный индекс rowstore.A best practice is to have a rowstore clustered index on the column(s) used in the filtered index definition. SQL Server использует его, чтобы быстро сканировать строки, не соответствующие отфильтрованному условию.SQL Server uses the clustered index to quickly scan the rows that did not meet the filtered condition. Без такого кластеризованного индекса для сканирования этих строк потребуется полное сканирование таблицы rowstore, что может отрицательно повлиять на производительность аналитического запроса.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. В случае отсутствия кластеризованного индекса можно создать дополняющий отфильтрованный некластеризованный индекс сбалансированного дерева для определения таких строк, однако так поступать не рекомендуется, поскольку доступ к большому диапазону строк через некластеризованный индекс сбалансированного дерева потребляет много ресурсов.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.

Примечание

Отфильтрованный некластеризованный индекс columnstore поддерживается только для дисковых таблиц.A filtered nonclustered columnstore index is only supported on disk-based tables. Оптимизированные для памяти таблицы он не поддерживает.It is not supported on memory-optimized tables

Пример А. Доступ к горячим данным из индекса сбалансированного дерева и теплым данным из индекса columnstoreExample A: Access hot data from btree index, warm data from columnstore index

В этом примере отфильтрованное условие (accountkey > 0) определяет, какие строки будут находиться в индексе columnstore.This example uses a filtered condition (accountkey > 0) to establish which rows will be in the columnstore index. Требуется так спроектировать отфильтрованное условие и последующие запросы, чтобы доступ к часто изменяющимся "горячим" данным осуществлялся из индекса сбалансированного дерева, а к более стабильным "теплым" данным — из индекса 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.

Комбинированные индексы для теплых и горячих данныхCombined indexes for warm and hot data

Примечание

Оптимизатор запросов учитывает возможность использования индекса columnstore для плана запроса, но не всегда пользуется ей.The query optimizer will consider, but not always choose, the columnstore index for the query plan. Когда оптимизатор запросов выбирает отфильтрованный индекс columnstore, он прозрачно объединяет строки из индекса columnstore и строки, не соответствующие отфильтрованному условию, обеспечивая аналитику в режиме реального времени.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. Это отличается от обычного отфильтрованного некластеризованного индекса, который можно использовать только в запросах, ограниченных представленными в индексе строками.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  

Аналитический запрос будет выполняться с приведенным ниже планом запроса.The analytics query will execute with the following query plan. Видно, что доступ к не соответствующим отфильтрованному условию строкам осуществляется через кластеризованный индекс сбалансированного дерева.You can see that the rows not meeting the filtered condition are accessed through clustered btree index.

План запросаQuery plan

Дополнительные сведения см. в блоге об отфильтрованном некластеризованном индексе columnstore.Please refer to the blog for details on filtered nonclustered columnstore index.

Совет для повышения производительности № 2. Разгрузка аналитики во вторичную реплику для чтения AlwaysOnPerformance tip #2: Offload analytics to Always On readable secondary

Хотя обработку индекса columnstore можно минимизировать с помощью отфильтрованного индекса columnstore, аналитические запросы могут по-прежнему потреблять значительные вычислительные ресурсы (ЦП, ввода-вывода, памяти), что негативно влияет на производительность рабочей нагрузки.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. Для самых критически важных рабочих нагрузок рекомендуется использовать конфигурацию AlwaysOn.For most mission critical workloads, our recommendation is to use the Always On configuration. В такой конфигурации можно исключить влияние аналитики, выгрузив ее во вторичную реплику для чтения.In this configuration, you can eliminate the impact of running analytics by offloading it to a readable secondary.

Совет для повышения производительности № 3. Снижение фрагментации индексов за счет хранения горячих данных в разностных группах строкPerformance Tip #3: Reducing Index fragmentation by keeping hot data in delta rowgroups

Таблицы с индексом columnstore могут становиться сильно фрагментированными (т. е. иметь удаленные строки), когда рабочая нагрузка обновляет или удаляет сжатые строки.Tables with columnstore index may get significantly fragmented (that is, deleted rows) if the workload updates/deletes rows that have been compressed. Фрагментированный индекс columnstore вызывает неэффективное использование памяти и хранилища.A fragmented columnstore index leads to inefficient utilization of memory/storage. Кроме того, это негативно влияет на производительность аналитических запросов из-за дополнительных операций ввода-вывода и необходимости фильтрации удаленных строк из результирующего набора.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.

До запуска дефрагментации индекса с помощью команды РЕОРГАНИЗОВАТЬ или перестроения индекса columnstore для всей таблицы или затронутых секций фактической очистки удаленных строк не происходит.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). Команды реорганизации и перестроения индекса потребляют значительную часть ресурсов, которые в противном случае могли бы использоваться для рабочей нагрузки.Both REORGANIZE and Index REBUILD are expensive operations taking resources away which otherwise could be used for the workload. Кроме того, если строки сжаты слишком рано, из-за обновлений может потребоваться их многократное повторное сжатие, что ведет к бесполезной трате ресурсов.Additionally, if rows compressed too early, it may need to be re-compressed multiple times due to updates leading to wasted compression overhead.
Для минимизации фрагментации индекса можно использовать параметр 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);  
  
;  

Дополнительные сведения см. в блоге о задержке сжатия.Please refer to the blog for details on compression delay.

Ниже приведены рекомендуемые методики.Here are the recommended best practices

  • Рабочая нагрузка со вставками и запросами. Если основная часть рабочей нагрузки связана со вставкой и запросом данных, для параметра COMPRESSION_DELAY рекомендуется использовать значение по умолчанию — 0.Insert/Query workload: If your workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option. Новые строки будут сжиматься после вставки 1 миллиона строк в отдельную разностную группу строк.The newly inserted rows will get compressed once 1 million rows have been inserted into a single delta rowgroup.
    Примерами такой рабочей нагрузки являются: (а) обычные рабочие нагрузки хранилища данных, (б) анализ навигации, когда требуется проанализировать данные о щелчках мышью в веб-приложении.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.

  • Рабочая нагрузка OLTP. При интенсивной рабочей нагрузке DML (т. е. ресурсоемком сочетании операций Update, Delete и Insert) можно определить фрагментацию индекса columnstore, проверив DMV sys.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. Если вы видите, что более 10 % строк будут в недавно сжатых группах строк помечены как удаленные, можно использовать параметр COMPRESSION_DELAY, чтобы отсрочить сжатие этих строк.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. Например, если вновь вставленные для рабочей нагрузки данные остаются "горячими" (т. е. многократно обновляются), скажем, в течение 60 минут, необходимо выбрать COMPRESSION_DELAY равным 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.

Предполагается, что большинству пользователей никаких действий выполнять не потребуется.We expect most customers do not need to do anything. Им подойдет и значение COMPRESSION_DELAY по умолчанию.The default value of COMPRESSION_DELAY option should work for them.
Опытным пользователям рекомендуется выполнить приведенный ниже запрос и собирать данные о проценте удаленных строк за последние 7 дней.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  

Если число удаленных строк в сжатых группах строк превышает 20 %, выполнив выравнивание в старых группах строк с вариативностью менее 5 % (называются холодными), задайте 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). Помните, что такой подход лучше всего работает для стабильной и сравнительно однородной рабочей нагрузки.Note that this approach works best with a stable and relatively homogeneous workload.

См. также:See Also

Руководство по индексам columnstore Columnstore Indexes Guide
Загрузка данных индексов ColumnStore Columnstore Indexes Data Loading
Производительность запросов индексов columnstore Columnstore Indexes Query Performance
Индексы сolumnstore для хранилищ данных Columnstore Indexes for Data Warehousing
Реорганизация и перестроение индексовReorganize and Rebuild Indexes