Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric
Mit SQL Server 2016 (13.x) wird die operative Echtzeitanalyse eingeführt, die Möglichkeit, Analyse- und OLTP-Arbeitsauslastungen zugleich auf den gleichen Datenbanktabellen auszuführen. Damit können Sie nicht nur Analysen in Echtzeit ausführen, sondern benötigen auch keine ETL-Aufträge und kein Data Warehouse mehr.
Erläuterung der Betriebsanalysen in Echtzeit
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, das speziell für die Ausführung von Analyseabfragen verwendet wird. Diese Lösung hat sich zwar als Standard etabliert, sie sieht sich jedoch diesen drei Herausforderungen gegenüber:
- Komplexität. Das Implementieren von ETL kann einen erheblichen Programmieraufwand erfordern, insbesondere, um nur die geänderten 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.
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 nicht gruppierten 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. Die Datenbank-Engine 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:
Bestimmen Sie die Tabellen in Ihrem operationalen Schema, die Daten enthalten, die für die Analyse benötigt werden.
Löschen Sie für jede Tabelle alle B-Strukturindizes, deren Hauptzweck im Beschleunigen der vorhandenen Analyse für Ihre OLTP-Workload besteht. Ersetzen Sie sie durch einen einzelnen nicht gruppierten Spaltenspeicherindex. Dies kann die Gesamtleistung Ihrer OLTP-Workload verbessern, da weniger Indizes zu verwalten sind.
--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 speicheroptimierte Tabelle ermöglicht die Betriebsanalyse, indem In-Memory-OLTP- und Columnstore-Technologien integriert werden, um hohe Leistung sowohl für OLTP- als auch für Analyseworkloads zu bieten. Der Spaltenspeicherindex einer speicheroptimierten Tabelle muss der gruppierte Index sein, d. h. er muss alle Spalten enthalten.
-- 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 );
Sie können jetzt operative Echtzeitanalyse ausführen, ohne Änderungen an Ihrer Anwendung vornehmen zu müssen. Die Analyseabfragen werden auf dem Columnstore-Index ausgeführt, während die OLTP-Operationen auch weiterhin auf den OLTP-B-Strukturindizes ausgeführt werden. Die OLTP-Workloads werden weiterhin ausgeführt, verursachen jedoch zusätzlichen Aufwand, um den Spaltenspeicherindex beizubehalten. Informationen über Leistungsoptimierungen finden Sie im nächsten Abschnitt.
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
Die Videoreihe "Data Exposed" enthält ausführlichere Informationen zu einigen der Funktionen und Überlegungen.
- Teil 1: So ermöglicht Azure SQL Die Betriebsanalyse in Echtzeit (HTAP)
- Teil 2: Optimieren vorhandener Datenbanken und Anwendungen mit Betriebsanalysen
- Teil 3: Erstellen von Betriebsanalysen mit Fensterfunktionen.
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. Beispiel A zeigt, wie gefilterte Indizes verwendet werden, um die Auswirkungen des nicht gruppierten Columnstore-Index auf transaktionsrelevante Arbeitsauslastungen zu minimieren und gleichzeitig Analysen in Echtzeit zu liefern.
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 benötigen die Daten im nicht gruppierten Columnstore-Index weniger Aktualisierungen, wodurch die Auswirkungen auf die Transaktionsauslastung verringert werden.
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. Das Datenbankmodul verwendet den gruppierten Index, um die Zeilen, die nicht der gefilterten Bedingung entsprechen, schnell zu scannen. Ohne diesen gruppierten Index ist eine vollständige Tabellenüberprüfung der Rowstore-Tabelle erforderlich, um diese Zeilen zu finden, was sich negativ auf die Leistung analytischer Abfragen auswirken kann. Ohne Einsatz eines gruppierten Index lässt sich auch ein komplementär gefilterter, nicht gruppierter B-Strukturindex zur Bestimmung dieser Zeilen verwenden, jedoch ist das nicht zu empfehlen, da der Zugriff auf einen großen Zeilenbereich über nicht gruppierte B-Strukturindizes aufwändig ist.
Hinweis
Ein gefilterter nicht gruppierter Columnstore-Index wird nur für datenträgerbasierte Tabellen unterstützt. Sie wird für speicheroptimierte Tabellen nicht unterstützt.
Beispiel A: Zugriff auf „heiße“ Daten über einen B-Strukturindex, auf „warme“ Daten über einen Columnstore-Index
In diesem Beispiel wird eine gefilterte Bedingung (accountkey > 0) verwendet, um festzulegen, welche Zeilen im Columnstore-Index enthalten sind. Dies hat den Zweck, die Filterbedingung und die nachfolgenden Abfragen so zu gestalten, dass der Zugriff auf sich häufig ändernde „heiße“ Daten über den B-Strukturindex erfolgt, während der Zugriff auf die stabileren „warmen“ Daten über den Columnstore-Index erfolgt.
Hinweis
Der Abfrageoptimierer berücksichtigt den Spaltenspeicherindex, wählt ihn aber nicht immer für den Abfrageplan. 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 Fulfillment Wait'
-- 4 => 'Order Shipped'
-- 5 => 'Order Received'
CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);
--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;
-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;
Die Analyseabfrage wird mit dem folgenden Abfrageplan ausgeführt. Man sieht, dass der Zugriff über den gruppierten B-Tree-Index auf die Zeilen erfolgt, die der Filterbedingung nicht entsprechen.
Weitere Informationen finden Sie im Blog: Gefilterter nicht gruppierter Spaltenspeicherindex.
Leistungstipp 2: Auslagern der Analyse auf ein lesbares sekundäres Always On-Replikat
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 Workloads empfehlen wir die Always On-Konfiguration. In dieser Konfiguration kann der Einfluss der Ausführung der Analyse beseitigt werden, indem sie in ein lesbares sekundäres Replikat ausgelagert wird.
Leistungstipp Nr. 3: Reduzieren der Indexfragmentierung, indem heiße Daten in Delta-Zeilengruppen beibehalten werden
Tabellen mit Spaltenspeicherindex werden möglicherweise erheblich fragmentiert (d. h. gelöschte Zeilen), wenn die Workload Zeilen aktualisiert/löscht, 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. Wenn Zeilen zu früh komprimiert werden, müssen sie möglicherweise aufgrund von Updates, was zu überflüssigem Komprimierungsaufwand führt, mehrmals erneut komprimiert werden.
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);
Weitere Informationen finden Sie im Blog: Komprimierungsverzögerung.
Im Folgenden werden die empfohlenen bewährten Methoden aufgeführt:
Arbeitsauslastung für Einfüge-/Abfrage: Wenn Ihre Workload in erster Linie Daten einfügt und sie abfragt, ist die Standardeinstellung
COMPRESSION_DELAY0 die empfohlene Option. Die neu eingefügten Zeilen werden komprimiert, sobald eine Million Zeilen in eine einzelne Deltazeilengruppe eingefügt wurden. Einige Beispiele für solche Workloads sind eine herkömmliche DW-Workload oder eine Stream-Analyse, wenn Sie das Auswahlmuster in einer Webanwendung analysieren müssen.OLTP-Workload: Wenn die Workload DML-intensiv ist (d. h. eine stark gemischte Belastung von Aktualisieren, Löschen und Einfügen), können Sie möglicherweise die Fragmentierung des Spaltenspeicherindex feststellen, indem Sie die DMV
sys.dm_db_column_store_row_group_physical_statsuntersuchen. Wenn Sie sehen, dass > 10% Zeilen in kürzlich komprimierten Zeilengruppen als gelöscht markiert sind, können Sie die OptionCOMPRESSION_DELAYverwenden, um eine Zeitverzögerung hinzuzufügen, wenn Zeilen zur Komprimierung berechtigt sind. Wenn bei Ihrer Workload neu eingefügte Datensätze normalerweise für etwa 60 Minuten „heiß“ bleiben (d. h. in dieser Zeit mehrfach aktualisiert werden), sollten SieCOMPRESSION_DELAYauf 60 festlegen.
Der Standardwert der COMPRESSION_DELAY Option sollte für die meisten Kunden funktionieren.
Für fortgeschrittene Nutzer empfehlen wir, die folgende Abfrage auszuführen und % gelöschter Zeilen über die letzten sieben Tage hinweg zu sammeln.
SELECT row_group_id,
CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
AND state_desc = 'COMPRESSED'
AND deleted_rows > 0
AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;
Wenn die Anzahl der gelöschten Zeilen in komprimierten Zeilengruppen > 20 % beträgt, mit einer Häufung in älteren Zeilengruppen mit einer Variation von < 5 % (als „kalte“ Zeilengruppen bezeichnet), legen Sie COMPRESSION_DELAY = (Erstellungszeit_der_jüngsten_Zeilengruppe – aktuelle_Zeit) fest. Dieser Ansatz funktioniert am besten mit einer stabilen und relativ homogenen Arbeitsauslastung.