JRB - stock.adobe.com
So vermeiden und beheben Sie SQL Server Performance-Engpässe
Fünf Tipps für Administratoren von SQL Servern – von der Vermeidung eines Hardware-Performance-Engpasses bis zur Optimierung des Datenbankdesigns.
Im Laufe des letzten Jahrzehnts wurden viele komplexe Unternehmensanwendungen mit Microsoft SQL Server entwickelt und eingesetzt. Heute ist der SQL Server ein Eckpfeiler vieler Business-Anwendungen und Geschäftsprozesse.
SQL-Server-Anwendungen reichen von branchenspezifischen Applikationen in der Produktion über internes Customer Relationship Management (CRM) und Decision Support Systeme bis hin zu kundenorientierten E-Commerce- und Web-Self-Service-Anwendungen. Folglich steht die Performance und Skalierbarkeit des SQL Servers ganz oben auf der Prioritätenliste der IT und die Sicherstellung der optimalen SQL Server Performance und Skalierbarkeit ist eine der wichtigsten Aufgaben der SQL Server Admins.
In der Praxis leiden allerdings viele SQL-Server-Systeme an schlechter Performance und Skalierbarkeit. Die Ursachen dafür liegen oft am suboptimalen Datenbank- und Index-Design oder an einem SQL Server-System, das nicht ordnungsgemäß für den vorgesehenen Workload konfiguriert wurde.
Der Grund ist, dass jedes größere SQL-Server-Projekt bei der Entwicklung in erster Linie auf Funktionalität optimiert wird. Performance und Skalierbarkeit werden hingegen häufig erst im Nachhinein behandelt.
Während die Fehlerbehebung von Performance-Problemen bei Datenbanksystemen eine schwierige und zeitaufwendige Aufgabe ist, kann die Verbesserung der Performance mit einem deutlich geringeren Zeitaufwand erreicht werden.
Hardware Performance-Engpässe
Memory
Der Hauptspeicher wirkt sich auf die SQL-Server-Leistung mehr als jede andere Hardware aus. Daher sollte die Speichernutzung regelmäßig über überwacht werden. Damit lässt sich zum Beispiel sicherstellen, dass der verfügbare Speicher größer ist als 20 Prozent.
Wenn Benutzer Leistungsprobleme haben und der Prozentsatz des verfügbaren Speichers unter 20 Prozent fällt, ist das fast immer ein Problem unzureichender Speicherzuweisung. Behalten Sie auch die durchschnittliche Performance des Page Life Expectancy Counters im Auge und vergewissern Sie sich, dass er immer über 300 Sekunden (5 Minuten) liegt.
Alles andere bedeutet entweder ein schlechtes Index-Design und führt zu einer erhöhten Input/Output-Belastung der Festplatte (IOPS) und einer weniger effizienten Nutzung des Hauptspeichers. Oder es liegt ein Speichermangel vor. Überwachen Sie auf dem SQL-Server-System die Paging-Rates, und stellen Sie sicher, dass der Wert etwa 1.000 Pages pro Sekunde beträgt. Überprüfen Sie auch über den MSSQL Puffer-Manager und die Memory Performance Counters.
Ebenso sollte der Zähler Memory Grants Pending im Performance-Monitoring-Objekt SQL Server Memory Manager Counters überwacht werden. Dieser Zähler zeigt die Gesamtzahl der Prozesse pro Sekunde an, die für einen bestimmten Arbeitsbereich auf eine Speicherzuweisung warten. In der Regel erfordern kleine OLTP-Transaktionen keine große Speicherzuweisung. Alles, was für eine OLTP-Transaktion eine Speicherzuweisung von Null übersteigt, deutet auf zu wenig Speicher im SQL-Server-System hin.
Ein Weg, um Speicherengpässe effektiv zu behandeln ist, speicherintensive Prozesse zu orten. Sie können verwendet werden, um mögliche Anwendungsprobleme wie Speicherlecks zu ermitteln. Um die Leistung zu optimieren und weniger Speicher zu verbrauchen, können Sie auch die Queries überprüfen. Ein anderer Ansatz ist es, die SQL-Server-Umgebung mit mehr RAM auszustatten. Ein Scale up ist ein guter Ansatz, um jeden Memory-bezogenen Performance-Engpass zu überwinden.
Disk I/O Verwendung
Im Vergleich zu anderen Hardwareressourcen ist der Storage Input/Output in der Regel die langsamste Systemressource im SQL Server. Um zu erfahren, ob der Storage sich zum Leistungsengpass entwickelt, sollten Sie deshalb das Speichersystem überwachen. Wenn diese Gefahr besteht, müssen Sie im nächste Schritt untersuchen, ob Sie für eine bessere Skalierbarkeit und Leistung das Design und die Konfiguration des Stograge-Systems optimieren können.
Überprüfen Sie dazu die PerfMon Disk Counters for Average Disk Sec/Read and Average Disk Sec/Write. Stellen Sie sicher, dass die Zeit, die ein Lese- oder Schreibvorgang benötigt, für OLTP-Systeme im Idealfall weniger als 12 Millisekunden beträgt und für Decision Support Systeme entsprechend mehr.
Der einfachste Weg zur Lösung von Festplatten-I/O-Performance-Engpässen ist – analog wie beim Memory – das Hochskalieren der SQL-Server-Umgebung. Dazu werden am besten die bestehenden Festplatten durch schnelleren Storage ersetzt, der die I/O-Last besser bewältigen kann und die I/O-Last verteilt. Auch sollten Sie die Festplatte regelmäßig defragmentieren.
CPU
CPU Leistungsengpässe können aus vielen Gründen auftreten. Das kann zum Beispiel ein nicht optimaler Query-Plan sein, ein unzureichendes Design der Anwendung oder der Datenbank, eine schlechte SQL-Server-Konfiguration oder ein Mangel an Hardwareressourcen.
Überprüfen Sie über PerfMon CPU und Processor die Länge der Prozessor-Warteschlange und verifizieren Sie, dass die Anzahl der Threads, die auf CPU-Zyklen warten, acht oder weniger beträgt. Wenn diese Zahl größer als 12 ist, bedeutet dies, dass die CPU das Leistungsproblem verursacht.
Sobald Sie einen CPU-Engpass identifiziert haben, nutzen Sie am besten – wie unten dargestellt – sys.dm_os_wait_stats Dynamische Management View (DMV), um die zehn Queries mit der schlechtesten Leistung für die CPU zu identifizieren.
SELECT TOP 10 (a.total_worker_time / a.execution_count) AS [Avg_CPU_Time]
,Convert(VARCHAR, Last_Execution_Time) AS [Last_Execution_Time]
,Total_Physical_Reads
,SUBSTRING(b.TEXT, a.statement_start_offset / 2, (
CASE
WHEN a.statement_end_offset = - 1
THEN len(convert(NVARCHAR(max), b.TEXT)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset
) / 2) AS [Query_Text]
,dbname = Upper(db_name(b.dbid))
,b.objectid AS 'Object_ID', B.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
ORDER BY [Avg_CPU_Time] DESC
Um den CPU-Engpass zu beheben, können Sie diese Abfragen und die zugrunde liegenden Indizes optimieren. Konfigurieren Sie außerdem Ihren SQL Server so, dass er alle verfügbaren CPU-Maschinen nutzt. Sie können Ihr SQL-Server-System auch skalieren, indem Sie zusätzliche CPUs hinzufügen oder auf einen neuen Server mit mehr und schnelleren CPUs aufrüsten.
Das Datenbankdesign
Stark normalisierte Datenbanken
Ein schlechter Datenbankentwurf führt auch zu einer schlechten Datenbank-Performance. Stark normalisierte Datenbanken sind beispielsweise mit komplexen relationalen Verknüpfungen verbunden. Dies führt zu länger dauernden Abfragen, die Systemressourcen wie CPU, RAM und Festplatten I/O verbrauchen.
Es ist offensichtlich, dass eine stark normalisierte Datenbank die Leistung eines SQL Servers und der Datenbank insgesamt erheblich reduziert. Die allgemeine Regel für das Schreiben effizienter Abfragen ist deshalb, die Datenbank neu zu gestalten, falls eine Operation fünf oder mehr Tabellen-Joins erfordert.
Duplizieren und ungenutzte Indizes
Indizes sind die Lösung für viele Performance-Probleme. Zu viele Indizes können bei häufig aktualisierten Tabellen einen zusätzlichen Overhead erzeugen, da der SQL Server mehr Arbeit verrichten muss, um die Indizes bei insert / update / delete Operationen aktuell zu halten.
Dies bedeutet, dass die SQL-Server-Datenbank-Engine mehr Zeit braucht für das Aktualisieren der Daten in der Tabelle. Auch kann die Indexwartung die CPU- und I/O-Nutzung ansteigen lassen, was sich bei einem schreibintensiven System nachteilig auf die Leistung auswirken kann.
Entfernen Sie deshalb doppelte und redundante Indizes, da sie nur eine überflüssige Belastung für die Systemressourcen darstellen.
Beim SQL Server können sys.dm_db_index_usage_stats DMV verwendet werden, um nicht genutzte Indizes zu identifizieren. Diese DMV liefert Statistiken darüber, wie ein Index für die Bearbeitung von Queries verwendet wurde. Alternativ können Sie auch den Database Engine Tuning Advisor (DTA) laufen lassen, um nicht genutzte Indizes zu suchen.
Weitere Informationen finden Sie im Tutorial: Database Engine Tuning Advisor.
