Beheben von Performanceproblemen bei SQL Servern

Einleitung

Ein wichtiger Teil unserer täglichen Arbeit mit den unterschiedlichsten Kunden ist zuerst eine umfangreiche Überprüfung der bestehenden Umgebung, dem sogenannten Assessment oder Health Check. Hierfür verwenden unsere Spezialist:innen unser selbst geschriebenes Assessment-Tool.

Dieses Tool besteht aus einer Sammlung von T-SQL Skripten, mit dem viele Aspekte im SQL Server Umfeld überprüft werden. Das Ergebnis für den Kunden ist - nach Auswertung der Ergebnisse - letztendlich eine kommentierte Excel-Datei, die Best Practice Abweichungen der MSSQL Konfiguration aufdeckt. Ein weiteres Ergebnis des Assessments - und dies ist für viele Kunden der Auslöser ihres Auftrags - sind Hinweise auf Ursachen und Lösungen für Performance-Probleme.

Entsprechend der beauftragten und dringend benötigten Performance-Analysen werden in diesem Artikel einige kritische Punkte betrachtet, die aus Sicht eines bekannten Microsoft Architekten und Autors zur Vermeidung von Ressource-Engpässen beachtet werden müssen. Nach dessen Aussage gibt es zwei kritische Situationen die eine Datenbank ausbremsen:

• Entweder ist die CPU-Last sehr hoch

oder

• Die CPU Last ist sehr niedrig, es existieren aber sehr viele Wartezustände (Waits)

Die folgenden Kapitel beleuchten diese beiden Punkte näher, wobei sich entsprechende Beispiele auf die von Microsoft bereitgestellte AdventureWorks2019 Datenbank beziehen, die unter dem angegebenen Link heruntergeladen werden kann.

CPU Last

Ursachen für eine hohe Auslastung der CPU sind in der Regel:

1. Fehlende Indizes
2. Implizite Typkonvertierungen von Abfragen die dazu führen, dass existierende Indizes nicht genutzt werden können
3. Fehlende Ressourcen

Fehlende Indizes

Viele Abfragen haben nur einen Bruchteil der in der Datenbank enthaltenen Tabelleninhalte als Ergebnis. Ohne die Existenz passender Indizes muss die Datenbank alle Zeilen einer Tabelle lesen, um zu entscheiden, ob die jeweiligen Einträge in die Ergebnismenge aufgenommen werden müssen oder nicht (“Full Table Scan”). Das kann bei extrem großen Tabellen zu sehr vielen Leseanforderungen und ebenso vielen Auswertungen der Zeileninhalte führen. Man stelle sich einfach ein Buch mit tausend Seiten vor, in dem alle Aussagen der Hauptperson ermittelt werden sollen. Ohne entsprechende Hilfsmittel bleibt uns hier keine andere Möglichkeit, als das ganze Buch zu lesen. Wird unsere Suche durch einen Index unterstützt, so können wir die gewünschten Stellen extrem schnell ermitteln und zusammentragen.

Aktuelle Datenbanken wissen oft, wo ihnen der Schuh drückt, und sie machen, wie auch unser Assessment-Tool, Vorschläge, wie ein Index eingerichtet werden muss, um existierende Abfragen zu unterstützen. Hat man Vermutungen zu seinen problematischen Abfragen, diese lassen sich auch aus der Datenbank auslesen, so lässt man sich den Ausführungsplan des Optimizers für entsprechende Kandidaten anzeigen. Teil der Anzeige eines solchen Ausführungsplans sind auch Verbesserungsvorschläge bezüglich der Indizierung zur Unterstützung der jeweiligen Abfrage.

Beispiel

Die Tabelle SalesOrderDetail enthält 121317 Zeilen, eine Spalte zur Speicherung eines Discount-Preises und eine weitere Spalte mit einem Preis. Genau einer der in dieser Tabelle abgelegten Einträge hat den Preis 264,2244. Dieser Wert soll für eine Abfrage des Discount-Preises verwendet werden. Die Abfrage wird hier noch nicht durch einen Index unterstützt.

USE [AdventureWorks2019]
GO
select UnitPriceDiscount from [AdventureWorks2019].[Sales].[SalesOrderDetail]
   where UnitPrice = 264.2244
GO

Nach Ausführung und Anzeige des Ausführungsplans erhalten wir die folgende Information:

Entsprechend dieser Ausgabe bewirkt der vorgeschlagene Index auf der Spalte UnitPrice eine geschätzte Performanceverbesserung um über 99%. Das ist doch erfreulich, obwohl die in diesem Beispiel verwendeten 121317 Zeilen eine Datenbank noch langweilen.

Weitere Details und die Statistiken zu der Abfrage ergeben einen Clustered Index Scan, also einen Durchlauf der kompletten Tabelle.

In dem Indexvorschlag fällt auf, dass eine weitere (Include-) Spalte verwendet wird, die für das where-Kriterium der o.a. Abfrage irrelevant ist. Im Normalfall muss die Abfrage, nachdem sie die Einträge mit dem gewünschten where-Kriterium gefunden hat, mit der aus dem Index gewonnenen Adresse in der Tabelle adressieren. Dieser Schritt wird durch die zusätzliche Aufnahme der Spalte in den Index eingespart. Include-Spalten haben keinen Einfluss auf die Sortierstruktur des Index-Baums, sondern lediglich auf den Inhalt der Indexknoten. Man erkauft sich in diesem Fall einen leichten Performancevorteil durch Speicherung zusätzlicher Informationen (hier des Discount-Preises).

USE [AdventureWorks2019]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_UnitPrice]
ON [Sales].[SalesOrderDetail] ([UnitPrice])
INCLUDE ([UnitPriceDiscount])
GO

Wird dieser Index eingerichtet, so erfährt man, wie aus der oben dargestellten und der folgenden Statistik hervorgeht, eine Reduktion der geschätzten CPU-Kosten von 0,133606 um 99,87% auf 0,0001612. Es wird ein Index Seek verwendet und lediglich eine Zeile gelesen.

Implizite Typkonvertierung

In einigen speziellen Fällen kann es vorkommen, dass ein Index nicht effizient verwendet werden kann, weil die Abfrage eine implizite Typkonvertierung erfordert. Als Beispiel wird hier die Tabelle Product der Datenbank AdventureWorks2019 verwendet, die jedoch für dieses Beispiel leicht modifiziert wurde: Diese Tabelle enthält 504 Datensätze und eine Spalte “Size” vom Typ nvarchar(5) , in der Kleidergrößen gespeichert sind. Neben den klassischen Größen ‘38’, ‘40’, ‘42’ …, ‘70’ enthält diese Spalte auch die Größen ‘L', ‘M’, ‘S' und ‘XL’. Die Modifikation der Tabelle ProductModified besteht darin, dass lediglich die Größen 'L’, … 'XL’ in die klassischen Werte umgewandelt wurden und ein Index “IX_ProductModified_size” auf die Größenspalte gelegt wurde.

Verwendet man nun eine Abfrage nach Produkten einer bestimmten Größe und verwendet hierzu einen numerischen Wert statt einer Zeichenkette. So erkennt man, dass der existierende Index (wegen einer implizit erforderlichen Typkonvertierung) zwar verwendet wurde, aber hierzu ein kompletter Indexdurchlauf (Scan) erforderlich ist, obwohl man lediglich zwei Zeilen als Ergebnis erhält. Im Ausführungsplan wird durch das gelbe Dreieck mit dem Ausrufezeichen auf die implizite Typkonvertierung aufmerksam gemacht.

select size from [AdventureWorks2019].[Production].[ProductModified] where size = 56;

Im Detail zu dem Select-Statement findet man eine Warnung vor der impliziten Typkonvertierung:

Modifiziert man nun das o.a. Statement, indem man für die Abfrage den Datentyp der Tabellenspalte verwendet, so verringert sich die CPU-Last um 77,62 Prozent. Im Vergleich zu dem vorherigen Ausführungsplan fällt unter anderem auf, dass hier ein Index Seek erfolgt, der in vielen Fällen einen schnelleren Zugriff als ein Index Scan signalisiert.

select size from [AdventureWorks2019].[Production].[ProductModified] where size = '56';

Fehlende Ressourcen (CPUs)

Hat man die Aspekte der vorherigen beiden Unterkapitel berücksichtigt und die Auswirkungen behoben und zeigt sich weiterhin, dass

• die CPU-Last des Servers sehr hoch ist und zeitweise oder permanent in Richtung 100% geht
• die Anzahl aktiver Sessions im Vergleich zur Anzahl der CPUs sehr groß ist
• der Query Store eine hohe Zahl von Waits auf die CPU anzeigt
• Statements, die auf die CPU warten, optimiert sind, also einen performanten Ausführungsplan mit niedrigen Kosten besitzen

so liegt hier tatsächlich ein Mangel an CPU Ressourcen vor. In diesem Fall bleibt einem nur die Anzahl der Cores bzw. CPUs zu erhöhen. Im Fall der Verwendung virtueller Maschinen (On-Prem oder in der Cloud) ist es besonders einfach, die CPUs entsprechend der Performance-Anforderungen zu erhöhen.

In Azure Umgebungen kann in solchen Fällen alternativ die Serverless Option verwendet werden. Hier lassen sich minimale und maximale Anzahl virtueller Cores festlegen. Die Azure Umgebung konfiguriert innerhalb dieser Grenzen die Anzahl der bereitgestellten Cores in Abhängigkeit von der Auslastung der Datenbank. Auf diese Art wird mit der automatischen, dynamischen Bereitstellung bzw. Freigabe von CPU-Ressourcen auf unterschiedliche Auslastungszeiten reagiert. Hierdurch kann sich je nach Nutzungsverhalten der Anwender ein interessanter Preisvorteil gegenüber statischen Ressourcen ergeben.