Erste Schritte mit Columnstore für die operative Echtzeitanalyse

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

SQL Server 2016 (13.x) führt Echtzeit-Betriebsanalysen ein, die Möglichkeit zum gleichzeitigen Ausführen von Analyse- und OLTP-Workloads auf denselben Datenbanktabellen. Damit können Sie nicht nur Analysen in Echtzeit ausführen, sondern benötigen auch keine ETL-Aufträge und kein Data Warehouse mehr.

Grundlagen der operativen Echtzeitanalyse

Bisher verwendeten Unternehmen separate Systeme für operative (d. h. OLTP) und Analysearbeitsauslastungen. Bei derartigen Systemen verschieben ETL-Aufträge (Extrahieren, Transformieren, Laden) die Daten aus dem Betriebs- in den Analysespeicher. Die Analysedaten sind normalerweise in einem Data Warehouse oder Data Mart gespeichert, die dediziert für die Ausführung von Analyseabfragen verwendet werden. Diese Lösung hat sich zwar als Standard etabliert, sie sieht sich jedoch diesen drei Herausforderungen gegenüber:

  • Komplexität. Für das Implementieren von ETL kann Codeerstellung in erheblichem Umfang erforderlich werden, insbesondere, um nur geänderte Zeilen zu laden. Es kann schwierig sein, die geänderten Zeilen zu bestimmen.

  • Kosten: Die Implementierung von ETL verursacht Kosten durch den Erwerb von Hardware und zusätzlicher Softwarelizenzen.

  • Datenlatenz. Die Implementierung von ETL bringt eine zeitliche Verzögerung mit sich, die durch die Ausführung der Analyse bedingt ist. Wenn der ETL-Auftrag beispielsweise am Ende jedes Geschäftstags ausgeführt wird, werden die Analyseabfragen auf Daten ausgeführt, die mindestens einen Tag alt sind. Für viele Unternehmen ist diese Verzögerung nicht akzeptabel, da das Unternehmen von einer Analyse der Daten in Echtzeit abhängig ist. Beispielsweise ist für die Erkennung von Betrugsversuchen eine Echtzeitanalyse der Betriebsdaten erforderlich.

real-time operational analytics overview

Die operative Echtzeitanalyse stellt eine Lösung für diese Herausforderungen bereit.
Wenn Analyse- und OLTP-Arbeitsauslastungen auf der gleichen zugrundeliegenden Tabelle ausgeführt werden, tritt keine Zeitverzögerung ein. In Szenarien, die Echtzeitanalyse verwenden können, lassen sich Kosten und Komplexität stark verringern, da die Notwendigkeit von ETL sowie von Erwerb und Wartung eines separaten Data Warehouses entfallen.

Hinweis

Die operative Echtzeitanalyse zielt auf das Szenario einer einzelnen Datenquelle ab, etwa einer ERP-Anwendung (Enterprise Resource Planning), auf der sowohl die betriebs- als auch die analysebedingte Arbeitsauslastung ausgeführt werden kann. Die Notwendigkeit eines separaten Data Warehouses entfällt dadurch nicht, wenn vor der Ausführung der Analysearbeitsauslastung Daten aus mehreren Quellen integriert werden müssen, oder für die Analyse extreme Leistungsfähigkeit mithilfe zuvor aggregierter Daten, wie etwa Cubes, erforderlich ist.

Echtzeitanalysen verwenden einen aktualisierbaren Columnstore-Index in einer Rowstore-Tabelle. Der Columnstore-Index unterhält eine Kopie der Daten, sodass die OLTP- und Analysearbeitsauslastungen auf separaten Kopien der Daten ausgeführt werden. Dadurch wird die Leistungseinbuße durch die gleichzeitige Ausführung beider Arbeitsauslastungen minimiert. SQL Server verwaltet Indexänderungen automatisch, sodass OLTP-Änderungen jederzeit für die Analyse auf dem aktuellen Stand verfügbar sind. Mit diesem Entwurf ist es möglich und praktikabel, die Analyse in Echtzeit auf aktuellen Daten auszuführen. Dies funktioniert sowohl für datenträgerbasierte als auch für speicheroptimierte Tabellen.

Beispiel für den Einstieg

So steigen Sie in die Echtzeitanalyse ein:

  1. Bestimmen Sie die Tabellen in Ihrem operationalen Schema, die Daten enthalten, die für die Analyse benötigt werden.

  2. Legen Sie für jede Tabelle alle B-Strukturindizes ab, die in erster Linie darauf ausgelegt sind, vorhandene Analysen auf Ihrer OLTP-Workload zu beschleunigen. Ersetzen Sie sie durch einen einzelnen Columnstore-Index. Dadurch wird möglicherweise die Gesamtleistung Ihrer OLTP-Arbeitsauslastung verbessert, da weniger Indizes gewartet werden müssen.

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

    Der Columnstore-Index für eine Tabelle im Arbeitsspeicher ermöglicht die operative Echtzeitanalyse durch die Integration von In-Memory-OLTP- und In-Memory-Columnstore-Technologien, durch die hohe Leistung sowohl für die OLTP- als auch für die analysebedingten Workloads möglich werden. Der Columnstore-Index für eine Tabelle im Arbeitsspeicher muss alle Spalten beinhalten.

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

Sie können jetzt operative Echtzeitanalyse ausführen, ohne Änderungen an Ihrer Anwendung vornehmen zu müssen. Analyseabfragen werden für den Columnstore-Index ausgeführt, und OLTP-Vorgänge werden weiterhin für Ihre OLTP-B-Strukturindizes ausgeführt. Die OLTP-Arbeitsauslastungen werden auch weiterhin mit hoher Leistung ausgeführt, bringen jedoch einen gewissen Mehraufwand für die Wartung des Columnstore-Index mit sich. Informationen über Leistungsoptimierungen finden Sie im nächsten Abschnitt.

Hinweis

In der SQL Server-Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert SQL Server eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Blogbeiträge

In den folgenden Blogbeiträgen erfahren Sie mehr über die operative Echtzeitanalyse. Möglicherweise sind die Abschnitte zu Leistungstipps leichter zu verstehen, wenn Sie sich zuerst die Blogbeiträge ansehen.

Videos

In einer Minireihe zu Data Exposed werden einige Funktionen und Überlegungen ausführlicher erläutert. Das erste Video finden Sie unten, weitere Informationen finden Sie auch noch in Teil 2: Optimieren vorhandener Datenbanken und Anwendungen mit operativen Analysen und Teil 3: Erstellen operativer Analysen mit Windows Functions.

Leistungstipp Nr. 1: Verwenden von gefilterten Indizes zum Verbessern der Abfrageleistung

Das Ausführen der operativen Echtzeitanalyse kann die Leistung der OLTP-Arbeitsauslastung beeinträchtigen. Dieser Einfluss sollte so klein wie möglich sein. Dieses Beispiel zeigt, wie gefilterte Indizes verwendet werden können, um den Einfluss eines nicht gruppierten Columnstore-Indexes auf die transaktionale Arbeitsauslastung zu minimieren, während zugleich eine Echtzeitanalyse bereitgestellt wird.

Um den Mehraufwand für die Wartung eines nicht gruppierten Columnstore-Index für eine Betriebsarbeitsauslastung zu minimieren, können Sie eine gefilterte Bedingung verwenden, um einen nicht gruppierten Columnstore-Index nur für die warmen oder sich langsam ändernden Daten zu erstellen. Beispielsweise können Sie in einer Anwendung zur Bestellungsverwaltung einen nicht gruppierten Columnstore-Index für die bereits versendeten Bestellungen erstellen. Nach dem Versand ändert sich eine Bestellung in der Regel nicht mehr, daher können diese Daten als „warm“ angesehen werden. Bei einem gefilterten Index erfordern die Daten im nicht gruppierten Columnstore-Index weniger Aktualisierungen, wodurch sich der Einfluss auf die Transaktionsarbeitsauslastung verringert.

Analyseabfragen greifen bei Bedarf transparent sowohl auf „warme“ als auch auf „heiße“ Daten zu, um Echtzeitanalyse bereitzustellen. Wenn ein erheblicher Anteil der Betriebsworkload die „heißen“ Daten betrifft, ist für diese Vorgänge keine zusätzliche Wartung des Columnstore-Index erforderlich. Eine bewährte Methode ist die Erstellung eines gruppierten Rowstore-Index für die in der gefilterten Indexdefinition verwendeten Spalten. SQL Server verwendet den gruppierten Index, um schnell die Zeilen zu durchsuchen, die der Filterbedingung nicht entsprochen haben. Ohne diesen gruppierten Index ist ein vollständiger Scan der Rowstore-Tabelle erforderlich, um diese Zeilen zu finden, was sich deutlich negativ auf die Leistung der Analyseabfrage auswirken kann. Wenn kein gruppierter Index vorhanden ist, können Sie einen ergänzenden gefilterten nicht gruppierten B-Strukturindex erstellen, um solche Zeilen zu identifizieren, es wird jedoch nicht empfohlen, da der Zugriff auf große Zeilen über nicht gruppierte B-Strukturindizes teuer ist.

Hinweis

Ein gefilterter nicht gruppierter Columnstore-Index wird nur für datenträgerbasierte Tabellen unterstützt. Für speicheroptimierte Tabellen wird er nicht unterstützt

Beispiel A: Zugreifen auf hot data from B-tree index, warm data from columnstore index

In diesem Beispiel wird eine gefilterte Bedingung (Accountkey > 0) verwendet, um festzulegen, welche Zeilen sich im Spaltenspeicherindex befinden. Ziel ist es, die gefilterte Bedingung und nachfolgende Abfragen zu entwerfen, um auf häufig geänderte "heiße" Daten aus dem B+-Strukturindex zuzugreifen und auf stabilere "warme" Daten aus dem Spaltenspeicherindex zuzugreifen.

Combined indexes for warm and hot data

Hinweis

Der Abfrageoptimierer zieht den Columnstore-Index für den Abfrageplan in Betracht, wählt ihn aber nicht in jedem Fall aus. Wenn der Abfrageoptimierer den gefilterten Columnstore-Index wählt, kombiniert er transparent die Zeilen aus dem Columnstore-Index mit den Zeilen, die der Filterbedingung nicht entsprechen, um Echtzeitanalyse zu ermöglichen. Dies unterscheidet sich von einem gewöhnlichen nicht gruppierten gefilterten Index, der nur in Abfragen verwendet werden kann, die sich auf die im Index vorhandenen Zeilen beschränken.

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

Die Analyseabfrage wird mit dem folgenden Abfrageplan ausgeführt. Sie können sehen, dass auf die Zeilen, die die gefilterte Bedingung nicht erfüllen, über den gruppierten B-Strukturindex zugegriffen wird.

Query plan

Weitere Informationen zu gefilterten, nicht gruppierten Columnstore-Indizesfinden Sie im Blog.

Leistungstipp Nr. 2: Auslagern der Analyse auf eine schreibgeschützte sekundäre Always On-Datenbank

Zwar kann der Wartungsaufwand für den Columnstore-Index durch Verwendung eines gefilterten Columnstore-Index minimiert werden, die Analyseabfragen können jedoch trotzdem erhebliche Computerressoucen (CPU, E/A, Arbeitsspeicher) in Anspruch nehmen, was sich negativ auf die für die Betriebsworkload verfügbare Leistung auswirkt. Für die meisten unternehmenswichtigen Arbeitsauslastungen ergibt sich als unsere Empfehlung die Always On-Konfiguration. In dieser Konfiguration kann der Einfluss der Ausführung der Analyse beseitigt werden, indem sie in eine schreibgeschützte sekundäre Datenbank ausgelagert wird.

Leistungstipp Nr. 3: Reduzierung der Indexfragmentierung durch Speicherung der „heißen“ Daten in Deltazeilengruppen

Tabellen mit Columnstore-Index können stark fragmentiert werden (in Form von gelöschten Zeilen), wenn durch die Arbeitsauslastung Zeilen aktualisiert/gelöscht werden, die komprimiert wurden. Ein fragmentierter Columnstore-Index führt zu einer ineffizienten Auslastung von Arbeitsspeicher/Speicherplatz. Neben dem ineffizienten Ressourceneinsatz wirkt er sich auch negativ auf die Analyseabfrageleistung aus, da zusätzliche E/A-Vorgänge anfallen und es erforderlich ist, die gelöschten Zeilen aus dem Resultset zu filtern.

Die gelöschten Zeilen werden physisch erst beim Ausführen der Indexdefragmentierung mit dem Befehl REORGANIZE oder durch Neuerstellung des Columnstore-Index für die gesamte Tabelle oder die betroffene(n) Partition(en) entfernt. Sowohl REORGANIZE als auch REBUILD sind aufwändige Indexvorgänge, die Ressourcen beanspruchen, die andernfalls für die Workload zur Verfügung stünden. Ferner kann ein zu frühes Komprimieren von Zeilen dazu führen, dass sie aufgrund von Aktualisierungen mehrfach erneut komprimiert werden müssen, was einen unnützen Mehraufwand für die Komprimierung verursacht.
Die Indexfragmentierung kann mithilfe der Option COMPRESSION_DELAY minimiert werden.

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

Im Blog finden Sie Details zur verzögerten Komprimierung.

Im Folgenden werden die empfohlenen bewährten Methoden aufgeführt:

  • Einfüge-/Abfragearbeitsauslastung: Wenn Ihre Arbeitsauslastung in erster Linie Daten einfügt und sie abfragt, ist die standardmäßige COMPRESSION_DELAY von 0 die empfohlene Option. Die neu eingefügten Zeilen werden komprimiert, sobald eine Million Zeilen in eine einzelne Deltazeilengruppe eingefügt wurden.
    Beispiele für eine solche Arbeitsauslastung sind (a) eine traditionelle DW-Arbeitsauslastung und (b) eine Klickdatenstrom-Analyse, bei der das Klickmuster in einer Webanwendung analysiert werden soll.

  • OLTP-Workload: Wenn die Workload DML schwer ist (d. h. eine schwere Mischung aus Update, Löschen und Einfügen), wird möglicherweise die Fragmentierung des Spaltenspeicherindex angezeigt, indem Sie die DMV-Sys untersuchen. dm_db_column_store_row_group_physical_stats“ bestimmen. Wenn Sie sehen, dass > 10 % Zeilen in kürzlich komprimierten Zeilengruppen gelöscht werden, können Sie COMPRESSION_DELAY Option verwenden, um Zeitverzögerung hinzuzufügen, wenn Zeilen zur Komprimierung berechtigt sind. Wenn bei Ihrer Arbeitsauslastung neu eingefügte Datensätze normalerweise für etwa 60 Minuten „heiß“ bleiben (d. h. in dieser Zeit mehrfach aktualisiert werden), sollten Sie COMPRESSION_DELAY auf 60 festlegen.

Wir gehen davon aus, dass die meisten Kunden keine Anpassungen vornehmen müssen. Der Standardwert der Option COMPRESSION_DELAY sollte für ihren Fall funktionieren.
Fortgeschrittenen Benutzern empfehlen wir, die Abfrage unten auszuführen und den Prozentsatz der gelöschten Zeilen im Lauf der letzten 7 Tage zu bestimmen.

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;  

Wenn die Anzahl der gelöschten Zeilen in komprimierten Zeilengruppen > 20 % beträgt, wird das Plateauing in älteren Zeilengruppen mit < einer Variation von 5 % (als kalte Zeilengruppen bezeichnet) COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time) festgelegt. Beachten Sie, dass dieser Ansatz sich besonders für eine stabile und relativ homogene Arbeitslast eignet.

Weitere Informationen

Beschreibung von Columnstore-Indizes
Laden von Daten für Columnstore-Indizes
Abfrageleistung für Columnstore-Indizes
Columnstore-Indizes für Data Warehousing
Neuorganisieren und Neuerstellen von Indizes