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:

Fehlende Indizes
Implizite Typkonvertierungen von Abfragen die dazu führen, dass existierende Indizes nicht genutzt werden können
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.

Waits

Waits sind Zustände in denen Sessions darauf warten, dass ihnen Ressourcen bereitgestellt werden. Die Art dieser Ressourcen ist sehr vielfältig, es kann sich beispielsweise um

eine evtl. noch mechanische Festplatte
niedrige Bandbreite einer Netzwerkverbindung zum Client
Sperren auf Datenbankobjekten (s.u.) die zur Unterstützung des Transaktionshandling dienen
Bereitstellung von Hauptspeicher

handeln.

Ein erster Hinweis auf Waits ist in der Regel, dass die Datenbank sehr langsam ist und die Auslastung der CPUs sehr gering. Zur Analyse solcher Situationen stellt Microsoft die sogenannten Dynamic Management Views (DMVs) bereit. Hierbei handelt es sich um Views, die nicht wie die üblichen Views auf Abfragen existierender Tabellen basieren, sondern auf Inhalte des Hauptspeichers der von der SQL Server Instanz für ihr Datenbank Management verwendet wird.

Inhalte der DMVs bestehen beispielsweise aus Informationen über

Sessions
Requests
Tasks
Hauptspeicher
Ausführungspläne

Einige konkrete Beispiele:

Ein interessantes Hilfsmittel, um Wait-Zustände und ihre Abhängigkeiten zu identifizieren stellt Microsoft mit dem folgenden Statement bereit, das auf den DMVs der o.a. Tabelle basiert:

with cteHead as (
	select 
		sess.session_id
		,req.request_id
		,left(isnull(req.wait_type, ''), 50) as 'wait_type'
		,left(isnull(req.wait_resource, ''), 40) as 'wait_resource'
		,left(req.last_wait_type, 50) as 'last_wait_type'
		,sess.is_user_process
		,req.cpu_time as 'request_cpu_time'
		,req.logical_reads as 'request_logical_reads'
		,req.reads as 'request_reads'
		,req.writes as 'request_writes'
		,req.wait_time
		,req.blocking_session_id
		,sess.memory_usage
		,sess.cpu_time as 'session_cpu_time'
		,sess.reads as 'session_reads'
		,sess.writes as 'session_writes'
		,sess.logical_reads as 'session_logical_reads'
		,convert(decimal(5, 2), req.percent_complete) as 'percent_complete'
		,req.estimated_completion_time AS 'est_completion_time'
		,req.start_time as 'request_start_time'
		,left(req.[status], 15) as 'request_status'
		,sess.open_transaction_count as 'open_tran_count'
		,req.command
		,req.plan_handle
		,req.[sql_handle]
		,req.statement_start_offset
		,req.statement_end_offset
		,conn.most_recent_sql_handle
		,left(sess.[status], 15) AS 'session_status'
		,sess.group_id
		,req.query_hash
		,req.query_plan_hash
    from 
		sys.dm_exec_sessions as sess
		left outer join sys.dm_exec_requests as req 
			on (sess.session_id = req.session_id)
		left outer join sys.dm_exec_connections as conn 
			on (conn.session_id = sess.session_id )
)
,cteBlockingHierarchy as (
	select 
		head.session_id as head_blocker_session_id
		,head.session_id as session_id
		,head.blocking_session_id
		,head.request_status
		,head.open_tran_count
		,head.wait_type
		,head.wait_time
		,head.wait_resource
		,head.[sql_handle]
		,head.most_recent_sql_handle
		,0 as [level]
    from 
		cteHead AS head
    where 
		(head.blocking_session_id is null 
		or head.blocking_session_id = 0
		)
		and head.session_id in (select distinct 
									blocking_session_id 
								from 
									cteHead 
								where 
									blocking_session_id != 0
								)
    union all
    select 
		h.head_blocker_session_id
		,blocked.session_id
		,blocked.blocking_session_id
		,blocked.request_status
		,blocked.open_tran_count
		,blocked.wait_type
		,blocked.wait_time
		,blocked.wait_resource
		,h.[sql_handle]
		,h.most_recent_sql_handle
		,[level] + 1
    from 
		cteHead as blocked
		inner join cteBlockingHierarchy as h 
			on (h.session_id = blocked.blocking_session_id 
			and h.session_id != blocked.session_id) --avoid infinite recursion for latch type of blocking
    where 
		h.wait_type collate Latin1_General_BIN not in ('EXCHANGE'
														,'CXPACKET'
													) 
		or h.wait_type is null
)
select 
	bh.*
	,txt.[text] as blocker_query_or_most_recent_query 
from 
	cteBlockingHierarchy as bh 
	outer apply sys.dm_exec_sql_text (isnull([sql_handle], most_recent_sql_handle)) as txt;

Beispiel zur Ermittlung der Wait-Abhängigkeiten

Ein sehr einfach gehaltenes Beispiel verdeutlicht, welche wesentlichen Informationen die zuvor wiedergegebene komplexe Abfrage liefert:

Hierzu werden zwei einfache Tabellen erstellt und drei Sessions aufgebaut, die auf diese Tabellen zugreifen wollen.

Session 1 aktualisiert den Datensatz in Tabelle_1 in einer Transaktion die nicht beendet wird.
Session 2 führt die gleiche Operation auf Tabelle_2 aus, und versucht zusätzlich, aus Tabelle_1 zu lesen. Die Transaktion wird noch nicht abgeschlossen.
Session 3 versucht in Tabelle_2 zu lesen.

create table Tabelle_1( Spalte_1 integer);
create table Tabelle_2( Spalte_1 integer);

insert into Tabelle_1 values(1);
insert into Tabelle_2 values(1);

-- Session 1
begin transaction
update Tabelle_1 set Spalte_1 = Spalte_1 +1;
-- die Transaktion bleibt hier offen, um zu signalisieren, dass
-- es noch weitere Aktivitäten innerhalb dieser Transaktion gibt.

-- Session 2
begin transaction
update Tabelle_2 set Spalte_1 = Spalte_1 +1;
select * from Tabelle_1;
-- die Transaktion bleibt hier offen, um zu signalisieren, dass
-- es noch weitere Aktivitäten innerhalb dieser Transaktion gibt.

-- Session 3
select * from Tabelle_2

-- In einer vierten Session wird nun das o.a. SQL Statement zur
-- Analyse der Sperren ausgeführt.

In der folgenden Abbildung werden die durch die drei Sessions verursachten Sperren und Abhängigkeiten dargestellt:

Die Session in der ersten Zeile ist der Verursacher der Blockade. Sie befindet sich auf Level 0 der Abhängigkeiten und hat einen Null-Wert in der Spalte blocking_session_id, wird also durch keine andere Session blockiert. Wie in der Spalte blocker_query_or_most_recent_query zu sehen, ist die Ursache der Sperre die Transaktion zum Update von Tabelle_1.
Session 63 in Zeile 2 ist angehalten, weil sie auf Session 61 wartet.
Session 65 in Zeile 3 ist angehalten, weil sie auf Session 63 wartet.
In der Liste der Abhängigkeiten der blockierenden Sessions ist in allen drei Zeilen Session 61 als Verursacher der Blockadekette head_blocker_session_id aufgeführt.

Sperren

Sperren dienen zur Unterstützung des Transaktionshandlings und sind essentieller Bestandteil einer jeden Datenbank. Um die Anzahl der Sperren gering zu halten, sollten folgende Punkte berücksichtigt werden:

Transaktionen kurz halten
Berücksichtigung impliziter Transaktionen (oft von Java Applikationen verwendet)
Unterstützung von Abfragen und Änderungen durch Verwendung passender Indizes
Sicherstellen, dass Indizes, die zur Unterstützung von Abfragen erstellt wurden, auch tatsächlich benutzt werden (Überprüfung im Ausführungsplan!)
Abfragen tunen
Verwendung von Read Commited Snapshot Isolation reduziert Sperren. Hierbei ist zu berücksichtigen, dass diese Einstellung das Transaktionsverhalten ändert und zusätzlichen Platz in der tempdb benötigt, der i.d.R. gering ist, aber abhängig vom Transaktionsaufkommen und der Größe der Transaktionen sehr umfangreich sein kann.

Deadlocks

Eine besondere Situation, in der Sperren eine Rolle spielen sind die gefürchteten Deadlocks, in einigen deutschen Dokumenten auch als “Verklemmung” bezeichnet. Hierbei handelt es sich um einen Zustand, bei dem eine zyklische Wartesituation zwischen mehreren Prozessen auftritt, wobei jeder beteiligte Prozess auf die Freigabe von Betriebsmitteln wartet, die ein anderer beteiligter Prozess bereits exklusiv belegt hat. Vergleichbar ist eine solche Situation mit einer Kreuzung mit rechts-vor-links Regelung an der von allen vier Seiten zur gleichen Zeit ein Fahrzeug eintrifft. Im Straßenverkehr wird diese Situation dadurch aufgelöst, dass einer der Fahrer auf sein Vorfahrtsrecht gegenüber dem von links kommenden Fahrer verzichtet. In Datenbanken wird eine solche Situation durch regelmäßige Checks erkannt und durch Rücknahme (Rollback) einer der beteiligten Transaktionen beendet. Es dauert jedoch eine Weile, bis es zur Erkennung und Auflösung kommt. Während dieser Zeit hängen die beteiligten Sessions und das Rollback führt zu einer entsprechenden Fehlermeldung bei der Applikation. Die Deadlock-Prüfung erfolgt bei SQL Server alle fünf Sekunden, das heißt im worst case eine entsprechend lange Wartezeit bis eine Transaktion zurückgerollt wird und die andere(n) weiterlaufen können.

Treten Deadlocks häufig auf, so kann dies zu enormen Performanceproblemen führen, weil die Applikation entsprechende Exceptions abfängt und darauf mit einer erneuten Ausführung der Transaktion reagieren muss. Dem Benutzer bleibt diese Situation i.d.R. verborgen, er muss “lediglich” für jedes Auftreten einer Exception bis zu fünf Sekunden warten. Danach erfolgt ein neuer Versuch die Transaktion durchzubringen. Dies kann auch mehrfach geschehen.

Es existieren viele Tools, mit denen man das Auftreten von Deadlocks und die beteiligten Transaktionen erkennen kann. Eine der einfachsten Formen ist wohl der SQL Profiler als Bestandteil des SQL Server Management Studios. Hier kann eine Ablaufverfolgung speziell für Deadlocks durchgeführt werden. Die Ergebnisse lassen sich in Form einer Datei oder in einer Datenbanktabelle speichern. In der folgenden Abbildung wird eine neue Ablaufverfolgung mit dem Namen ProfilerDemo in Tabelle ProfilerProtokollTabelle der Datenbank DeadlockDemo abgelegt. Um eine Überlastung zu verhindern, kann je nach Zielort des Protokolls - Datei oder Tabelle - eine maximale Dateigröße bzw. eine maximale Zeilenzahl spezifiziert werden. In diesem Beispiel geht es speziell um Deadlocks, so dass keine der vorgefertigten Vorlagen verwendet wird, d.h. der Wert in dem Feld “Vorlage verwenden” ist “Leer”.

Im nächsten Schritt erfolgt die Ereignisauswahl in dem gleichnamigen Reiter:

Nachdem alle oben dargestellten Befehle zur Erzeugung eines einfachen Deadlock-Beispiels in der angegebenen Reihenfolge durchgeführt wurden, kam es nach einer kleinen Verzögerung zu einer entsprechenden Fehlermeldung. Die Verzögerung ergibt sich aus den aufgebauten Sperren und dem fünf Sekunden Rhythmus in dem die Datenbank die Deadlock-Prüfung durchführt. Als Ergebnis der positiven Prüfung wurde die linke Session für das Rollback ausgewählt.

In dem Profiler ergeben sich nun die folgenden Bilder:

Der folgende vergrößerte Ausschnitt gibt die Sperrsituation wieder:

Die Transaktion auf der linken Seite ist Eigentümer der Sperre auf Tabelle “Links”
Die Transaktion auf der rechten Seite ist Eigentümer der Sperre auf Tabelle “Rechts”
Die Transaktion auf der linken Seite benötigt zusätzlich eine Sperre der Tabelle “Rechts”
Die Transaktion auf der rechten Seite benötigt zusätzlich eine Sperre der Tabelle “Links”

Einige Hinweise zum Thema Sperren und Deadlocks

Um das Auftreten von Deadlocks zu minimieren, sollten Transaktionen möglichst klein gehalten werden.
Müssen die gleichen Objekte innerhalb verschiedener Transaktionen gesperrt werden, so muss hierbei zur Reduzierung der Deadlock-Gefahr immer die gleiche Reihenfolge gewählt werden. Dies ist aber leider noch keine Garantie dafür, dass keine Deadlocks auftreten.
Nicht nur aus Performance-Gründen, sondern auch zur Reduzierung der Deadlock-Gefahr sollten möglichst Covering Indizes eingesetzt werden.
Foreign Key Spalten sollten grundsätzlich indiziert werden. Dies reduziert nicht nur Full Table bzw. Full Index Scans sondern im Fall von Löschoperationen den Umfang benötigter Sperren.
SQL Server verwendet im Fall von Cascading Deletes andere / weitreichendere Sperrmechanismen als bei “normalen” Löschoperationen. Häufige Cascading Deletes können eine Datenbank ausbremsen und im Worst Case zu Deadlocks führen. Die Alternative ist hier - leider - ausprogrammieren der Löschoperationen auf Applikations- oder Stored Procedure-Ebene.
An dieser Stelle erneut der Hinweis: Es sollte überprüft werden, ob das Transaktionsverhalten der Datenbank auf READ COMMITTED SNAPSHOT ISOLATION eingestellt ist bzw. umgestellt werden kann. Hierbei werden Sperren weniger restriktiv eingesetzt, so dass während eines Updates durch eine Transaktion andere Transaktionen in der betreffenden Tabelle lesen können. Hierbei sind folgende Punkte zu berücksichtigen:
Die lesende Transaktion erhält ihre Informationen aus dem Before-Image der schreibenden Transaktion.
Dieser Mechanismus benötigt zusätzlichen Speicherplatz in der tempdb. Ähnlich wie Oracle ein Before-Image der Transaktionen im Undo Tablespace anlegt, schreibt SQL Server diese Daten in die tempdb.
Hinweis für Applikationsentwickler: Deadlocks werden durch die Exception “Msg 1205” angezeigt. Diese Exception sollte für die Benutzer transparent bleiben und die Applikation sollte die Transaktion erneut ausführen. Der Administrator sollte aber in einer geeigneten Form über die auftretenden Deadlocks informiert werden, damit Ursachenanalysen und Gegenmaßnahmen erfolgen können.

Memory Grants und Sortieroperationen

“Memory Grants” treten im Zusammenhang mit Sortieroperationen auf. Sollen Daten sortiert werden, so muss hierfür angemessener Hauptspeicherbereich reserviert werden. Erst wenn diese Reservierung abgeschlossen wurde, kann der Speicher verwendet werden. Reicht der reservierte Bereich für die zu sortierenden Daten nicht aus, weil der Umfang zu groß ist, mehrere Sessions sortierte Daten benötigen oder der reservierte Bereich auf Grund falscher Statistiken zu klein gewählt wurde, so müssen zur Unterstützung der Sortierung Zwischenergebnisse in der tempdb abgelegt werden. Diese ist sehr viel langsamer als der Hauptspeicher. Die tempdb Zugriffe lassen sich über das Auftreten von IO_COMPLETION- und IO_QUEUE_LIMIT-Wait Events identifizieren.

Erkennt man viele Memory Grants und vielleicht auch IO_Completion- und IO_QUEUE_LIMIT-Waits ist das ein Hinweis auf aufwändige Sortieroperationen. Hierbei sollte auch die Komplexität von Sortieroperationen berücksichtigt werden, die in der O-Notation mit O( n * log n ) angegeben wird und damit ein exponentielles Wachstum zeigt und damit bei großen Datenmengen verheerend wirken kann.

Gelingt es also, sortierte Daten zu erhalten, ohne die Daten explizit sortieren zu müssen, so haben wir hierdurch einen weiteren Tuning-Schritt erreicht.

Das Zaubermittel das hierzu verwendet wird, ist wieder einmal ein Index: Erstellen wir einen Index, der den zu sortierenden Spalten entspricht, so braucht die Datenbank zum Erhalt sortierter Daten lediglich einen Index Seek oder einen Index Scan durchzuführen, die Daten im Index sind ja bereist sortiert. Die Komplexität für den Durchlauf durch einen Index ist O( n ), zeigt also lineares Verhalten. Ein weiterer Optimierungsschritt kann sich dadurch ergeben, dass man einen Covering Index verwendet, das heißt, dass man eventuell zusätzlich zu ermittelnde Spaltenwerte per Include in den Index aufnimmt. Die hier beschriebene Vorgehensweise entspannt die Sperrsituation des Hauptspeichers erheblich und reduziert die Erfordernis der Auslagerung von Daten auf die tempdb.

Fazit

In diesem Artikel wurden wesentliche Ursachen für Performance-Probleme dargestellt, ihre Erkennung und Behebung aufgezeigt.

Wir hoffen, Sie beim Tuning Ihrer Datenbank unterstützt zu haben. Einen viel tieferen und umfassenderen Einblick in Möglichkeiten zur Verbesserung der Datenbankperformance erreichen wir mit unserem Assessment-Tool.

Bei offenen Fragen oder Interesse für eine Auswertung Ihrer speziellen Situation stehen wir Ihnen gerne für ein Beratungsgespräch zur Verfügung. Nutzen Sie dafür unverbindlich unser Kontaktformular.