Clever Geek Handbook

Berechnete Spaltenleistung in SQL Server

Die Übersetzung des Artikels wurde speziell für Studenten des Kurses "MS SQL Server Developer" erstellt .









Berechnete Spalten können zu schwer zu diagnostizierenden Leistungsproblemen führen. Dieser Artikel beschreibt eine Reihe von Problemen und einige Lösungen.



Berechnete Spalten sind eine bequeme Möglichkeit, Berechnungen in Tabellendefinitionen einzubetten. Sie können jedoch Leistungsprobleme verursachen, insbesondere wenn Ausdrücke komplexer werden, Anwendungen anspruchsvoller werden und das Datenvolumen weiter wächst.



Eine berechnete Spalte ist eine virtuelle Spalte, deren Wert basierend auf den Werten in anderen Spalten in der Tabelle berechnet wird. Standardmäßig wird der berechnete Wert nicht physisch gespeichert, sondern von SQL Server bei jeder Spaltenanforderung berechnet. Dies erhöht die Belastung des Prozessors, verringert jedoch die Datenmenge, die beibehalten werden muss, wenn sich die Tabelle ändert.



Nicht persistente berechnete Spalten sind häufig CPU-intensiv, verlangsamen Abfragen und frieren Anwendungen ein. Glücklicherweise bietet SQL Server verschiedene Möglichkeiten, um die Leistung berechneter Spalten zu verbessern. Sie können persistierte berechnete Spalten erstellen, indizieren oder beides tun.



Für die Demonstration habe ich vier ähnliche Tabellen erstellt und sie mit identischen Daten aus der WideWorldImporters-Demo-Datenbank gefüllt. Jede Tabelle hat dieselbe berechnete Spalte, aber zwei Tabellen haben sie dauerhaft und zwei haben einen Index. Das Ergebnis sind die folgenden Optionen:



  • Eine Tabelle Orders1ist eine nicht persistente berechnete Spalte.
  • Tabelle Orders2ist eine dauerhaft berechnete Spalte.
  • Die Tabelle Orders3ist eine nicht persistente berechnete Spalte mit einem Index.
  • Tabelle Orders4ist eine persistente berechnete Spalte mit einem Index.


Der berechnete Ausdruck ist ziemlich einfach und der Datensatz ist sehr klein. Es sollte jedoch ausreichen, die Prinzipien persistenter und indizierter berechneter Spalten zu demonstrieren und wie dies bei der Lösung von Leistungsproblemen hilft.



Nicht gespeicherte berechnete Spalte



Möglicherweise möchten Sie in Ihrer Situation nicht persistente berechnete Spalten, um das Speichern von Daten, das Erstellen von Indizes oder die Verwendung mit einer nicht deterministischen Spalte zu vermeiden. Beispielsweise behandelt SQL Server eine skalare UDF als nicht deterministisch, wenn WITH SCHEMABINDING in der Funktionsdefinition fehlt. Wenn Sie versuchen, mit dieser Funktion eine persistente berechnete Spalte zu erstellen, wird die Fehlermeldung angezeigt, dass die persistente Spalte nicht erstellt werden kann.



Es sollte jedoch beachtet werden, dass benutzerdefinierte Funktionen ihre eigenen Leistungsprobleme verursachen können. Wenn die Tabelle eine berechnete Spalte mit einer Funktion enthält, verwendet die Abfrage-Engine keine Parallelität (es sei denn, Sie verwenden SQL Server 2019). Auch in einer Situation, in der die berechnete Spalte in der Abfrage nicht angegeben ist. Bei einem großen Datensatz kann dies große Auswirkungen auf die Leistung haben. Funktionen können auch die Ausführung von UPDATEs verlangsamen und sich darauf auswirken, wie der Optimierer die Kosten einer Abfrage für eine berechnete Spalte berechnet. Dies bedeutet nicht, dass Sie niemals Funktionen für eine berechnete Spalte verwenden sollten, aber es sollte auf jeden Fall mit Vorsicht behandelt werden.



Unabhängig davon, ob Sie Funktionen verwenden oder nicht, ist das Erstellen einer nicht persistenten berechneten Spalte ziemlich einfach. Nächste AnweisungCREATE TABLEdefiniert eine Tabelle Orders1, die eine berechnete Spalte enthält Cost. 



USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit));

INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Um eine berechnete Spalte zu definieren, geben Sie ihren Namen gefolgt von dem AS-Schlüsselwort und dem Ausdruck an. In unserem Beispiel multiplizieren wir Quantitymit Priceund subtrahieren Profit. Nach dem Erstellen der Tabelle füllen wir sie mit INSERT unter Verwendung von Daten aus Sales.InvoiceLinesder WideWorldImporters-Datenbanktabelle. Als nächstes führen wir SELECT aus. 



SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;


Diese Abfrage sollte 22.973 Zeilen oder alle Zeilen zurückgeben, die Sie in der WideWorldImporters-Datenbank haben. Der Ausführungsplan für diese Abfrage ist in Abbildung 1 dargestellt.





Abbildung 1. Der Ausführungsplan für die Abfrage für die Tabelle Orders1.



Als Erstes ist der Clustered Index Scan zu beachten, der keine effiziente Methode zum Abrufen der Daten darstellt. Dies ist jedoch nicht das einzige Problem. Schauen wir uns die Anzahl der logischen Lesevorgänge (tatsächliche logische Lesevorgänge) in den Eigenschaften des Clustered Index Scan an (siehe Abbildung 2).





Abbildung 2. Logische Lesevorgänge zum Abfragen der Tabelle Orders1



Die Anzahl der logischen Lesevorgänge (in diesem Fall 1108) ist die Anzahl der Seiten, die aus dem Datencache gelesen wurden. Ziel ist es, diese Zahl so weit wie möglich zu reduzieren. Daher ist es nützlich, sich daran zu erinnern und es mit anderen Optionen zu vergleichen.



Die Anzahl der logischen Lesevorgänge kann auch durch Ausführen der Anweisung SET STATISTICS IO ONvor dem Ausführen von SELECT ermittelt werden. Anzeigen der CPU und der Gesamtzeit - SET STATISTICS TIME ONoder Anzeigen der Eigenschaften der SELECT-Anweisung im Abfrageausführungsplan.



Ein weiterer erwähnenswerter Punkt ist, dass der Ausführungsplan zwei Compute Scalar-Anweisungen enthält. Der erste (der rechts) ist die Berechnung des berechneten Spaltenwerts für jede zurückgegebene Zeile. Da Spaltenwerte im laufenden Betrieb berechnet werden, können Sie diesen Schritt mit nicht persistent berechneten Spalten nur vermeiden, wenn Sie einen Index für diese Spalte erstellen.



In einigen Fällen bietet eine nicht persistente berechnete Spalte die erforderliche Leistung, ohne sie zu speichern oder einen Index zu verwenden. Dies spart nicht nur Speicherplatz, sondern vermeidet auch den Aufwand für die Aktualisierung berechneter Werte in einer Tabelle oder einem Index. Meistens führt eine nicht persistente berechnete Spalte jedoch zu Leistungsproblemen, und dann sollten Sie nach einer Alternative suchen.



Persistent berechnete Spalte



Eine häufig zur Lösung von Leistungsproblemen verwendete Technik besteht darin, eine berechnete Spalte als dauerhaft zu definieren. Bei diesem Ansatz wird der Ausdruck im Voraus berechnet und das Ergebnis zusammen mit den restlichen Tabellendaten gespeichert.



Damit eine Spalte persistent ist, muss sie deterministisch sein, dh der Ausdruck muss immer dasselbe Ergebnis für dieselbe Eingabe zurückgeben. Beispielsweise können Sie die Funktion GETDATE nicht in einem Spaltenausdruck verwenden, da sich der Rückgabewert ständig ändert.



Um eine dauerhaft berechnete Spalte zu erstellen, müssen Sie der Spaltendefinition ein Schlüsselwort hinzufügen PERSISTED, wie im folgenden Beispiel gezeigt. 



DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED);

INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Die Tabelle ist Orders2fast identisch mit der Tabelle Orders1, außer dass die Spalte Costdas Schlüsselwort enthält PERSISTED. SQL Server füllt diese Spalte automatisch, wenn Zeilen hinzugefügt oder geändert werden. Dies bedeutet natürlich, dass der Tisch Orders2mehr Platz beansprucht als der Tisch Orders1. Dies kann mit einer gespeicherten Prozedur überprüft werden sp_spaceused. 



sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO


Abbildung 3 zeigt die Ausgabe dieser gespeicherten Prozedur. Die Datengröße in der Tabelle Orders1beträgt 8.824 KB und in der Tabelle Orders212.936 KB. 4 112 KB mehr zum Speichern der berechneten Werte.





Abbildung 3. Vergleich der Größe der Tabellen Orders1 und Orders2



Obwohl diese Beispiele auf einem relativ kleinen Datensatz basieren, können Sie sehen, wie schnell die Menge der gespeicherten Daten zunehmen kann. Dies kann jedoch ein Kompromiss sein, wenn sich die Leistung verbessert.



Führen Sie die folgenden Schritte aus, um den Leistungsunterschied festzustellen. 



SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;


Dies ist das gleiche SELECT, das ich für die Tabelle Orders1 verwendet habe (mit Ausnahme der Namensänderung). Abbildung 4 zeigt den Ausführungsplan.





Abbildung 4. Ausführungsplan für eine Abfrage an die Tabelle Orders2.



Dies beginnt auch mit dem Clustered Index Scan. Diesmal gibt es jedoch nur eine Anweisung zum Berechnen des Skalars, da die berechneten Spalten zur Laufzeit nicht mehr berechnet werden müssen. Im Allgemeinen ist es umso besser, je weniger Schritte ausgeführt werden. Dies ist zwar nicht immer der Fall.



Die zweite Abfrage erzeugte 1593 logische Lesevorgänge, was 485 mehr als 1108 Lesevorgängen für die erste Tabelle entspricht. Trotzdem läuft es schneller als das erste. Obwohl nur etwa 100 ms und manchmal viel weniger. Die Prozessorzeit hat sich ebenfalls verringert, aber auch nicht wesentlich. Höchstwahrscheinlich wäre der Unterschied bei größeren Volumina und komplexeren Berechnungen viel größer.



Index für nicht persistente berechnete Spalte



Eine andere Technik, die üblicherweise verwendet wird, um die Leistung einer berechneten Spalte zu verbessern, ist die Indizierung. Um einen Index erstellen zu können, muss die Spalte deterministisch und präzise sein. Dies bedeutet, dass der Ausdruck die Typen float und real nicht verwenden kann (wenn die Spalte nicht persistent ist). Es gibt auch Einschränkungen für andere Datentypen sowie für SET-Parameter. Eine vollständige Liste der Einschränkungen finden Sie in der SQL Server-Dokumentation, Indizes für berechnete Spalten .



Sie können überprüfen, ob eine nicht persistente berechnete Spalte für die Indizierung über ihre Eigenschaften geeignet ist. Verwenden Sie die Funktion, um die Eigenschaften anzuzeigen COLUMNPROPERTY. Die Eigenschaften IsDeterministic, IsIndexable und IsPrecise sind uns wichtig. 



DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
  COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
  COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
  COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';


Die SELECT-Anweisung muss für jede Eigenschaft 1 zurückgeben, damit die berechnete Spalte indiziert werden kann (siehe Abbildung 5).





Abbildung 5. Überprüfen, ob der Index



erstellt werden kann Nach der Überprüfung können Sie einen nicht gruppierten Index erstellen. Anstatt die Tabelle zu ändern, habe Orders1ich eine dritte Tabelle ( Orders3) erstellt und den Index in die Tabellendefinition aufgenommen. 



DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit),
  INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Ich habe einen nicht gruppierten Abdeckungsindex erstellt, der sowohl Spalten aus ItemIDals auch Costaus einer SELECT-Abfrage enthält. Nach dem Erstellen und Auffüllen der Tabelle und des Index können Sie die folgende SELECT-Anweisung ausführen, die den vorherigen Beispielen ähnelt. 



SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;


Abbildung 6 zeigt den Ausführungsplan für diese Abfrage, die jetzt den nicht gruppierten Index ix_cost3 (Index Seek) verwendet, anstatt einen Clustered-Index-Scan durchzuführen.





Abbildung 6. Ausführungsplan für eine Abfrage in der Tabelle Orders3



Wenn Sie sich die Eigenschaften der Index Seek-Anweisung ansehen, werden Sie feststellen, dass die Abfrage nur noch 92 logische Lesevorgänge ausführt. In den Eigenschaften der SELECT-Anweisung sehen Sie, dass sich die CPU und die Gesamtzeit verringert haben. Der Unterschied ist nicht signifikant, aber auch dies ist ein kleiner Datensatz.



Es sollte auch beachtet werden, dass der Ausführungsplan nur eine Compute Scalar-Anweisung enthält, nicht zwei wie in der ersten Abfrage. Da die berechnete Spalte indiziert ist, wurden die Werte bereits berechnet. Dadurch entfällt die Notwendigkeit, zur Laufzeit Werte zu berechnen, selbst wenn die Spalte nicht als persistent definiert wurde.



Index für die gespeicherte Spalte



Sie können auch einen Index für die berechnete Spalte erstellen, die Sie speichern. Dies führt zwar dazu, dass zusätzliche Daten und Indexdaten gespeichert werden, kann jedoch in einigen Fällen hilfreich sein. Sie können beispielsweise einen Index für eine persistente berechnete Spalte erstellen, selbst wenn der Datentyp float oder real verwendet wird. Dieser Ansatz kann auch nützlich sein, wenn Sie mit CLR-Funktionen arbeiten und nicht überprüfen können, ob die Funktionen deterministisch sind.



Die folgende Anweisung CREATE TABLEerstellt eine Tabelle Orders4. Die Tabellendefinition enthält sowohl eine persistente Spalte Costals auch einen nicht gruppierten Abdeckungsindex ix_cost4. 



DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED,
  INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Führen Sie SELECT aus, nachdem die Tabelle und der Index erstellt und gefüllt wurden. 



SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;


Abbildung 7 zeigt den Ausführungsplan. Wie im vorherigen Beispiel beginnt die Abfrage mit einer nicht gruppierten Indexsuche (Indexsuche).





Abbildung 7. Ausführungsplan für eine Abfrage in der Tabelle Orders4



Diese Abfrage führt auch nur 92 logische Lesevorgänge wie die vorherige aus, was zu ungefähr derselben Leistung führt. Der Hauptunterschied zwischen den beiden berechneten Spalten sowie zwischen indizierten und nicht indizierten Spalten besteht in der Menge des verwendeten Speicherplatzes. Lassen Sie uns dies überprüfen, indem Sie die gespeicherte Prozedur ausführen sp_spaceused. 



sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GO


Die Ergebnisse sind in Abbildung 8 dargestellt. Wie erwartet enthalten die gespeicherten berechneten Spalten mehr Daten und die indizierten Spalten mehr Indizes.





Abbildung 8. Vergleich der Speicherplatznutzung für alle vier Tabellen



Höchstwahrscheinlich müssen Sie die gespeicherten berechneten Spalten nicht ohne Grund indizieren. Wie bei anderen datenbankbezogenen Fragen sollte Ihre Auswahl auf Ihrer spezifischen Situation basieren: Ihren Abfragen und der Art Ihrer Daten.



Arbeiten mit berechneten Spalten in SQL Server



Die berechnete Spalte ist keine reguläre Tabellenspalte und sollte mit Vorsicht behandelt werden, um Leistungseinbußen zu vermeiden. Die meisten Leistungsprobleme können durch Speichern oder Indizieren der Spalte behoben werden. Bei beiden Ansätzen muss jedoch der zusätzliche Speicherplatz und die Änderung der Daten berücksichtigt werden. Wenn sich die Daten ändern, müssen die berechneten Spaltenwerte in der Tabelle oder im Index oder in beiden aktualisiert werden, wenn Sie die beibehaltene berechnete Spalte indiziert haben. Sie können nur entscheiden, welche der Optionen für Ihren speziellen Fall am besten geeignet ist. Und höchstwahrscheinlich müssen Sie alle Optionen verwenden.





Weiterlesen





More articles:


All Articles