So optimieren Sie Microsoft SQL Server für die Leistung

Um ihre Benutzer zu halten, muss jede Anwendung oder Website schnell laufen. In unternehmenskritischen Umgebungen kann eine Verzögerung von ein paar Millisekunden beim Abrufen von Informationen große Probleme verursachen. Da die Datenbankgröße von Tag zu Tag wächst, müssen wir Daten so schnell wie möglich abrufen und die Daten so schnell wie möglich in die Datenbank zurückschreiben. Um sicherzustellen, dass alle Operationen reibungslos ausgeführt werden, müssen wir unseren Datenbankserver auf Leistung optimieren.

In diesem Artikel beschreibe ich ein schrittweises Verfahren zur grundlegenden Leistungsoptimierung auf einem der besten Datenbankserver auf dem Markt: Microsoft SQL Server (kurz SQL Server).

#1 Die Schuldigen finden

Wie bei jeder anderen Software müssen wir verstehen, dass SQL Server ein komplexes Computerprogramm ist. Wenn wir ein Problem damit haben, müssen wir herausfinden, warum es nicht so läuft, wie wir es erwarten.

Von SQL Server müssen wir Daten so schnell und so genau wie möglich abrufen und übertragen. Wenn es Probleme gibt, gibt es einige grundlegende Gründe und die ersten beiden Dinge, die Sie überprüfen sollten:

• Die Hardware- und Installationseinstellungen, die möglicherweise korrigiert werden müssen, da die Anforderungen von SQL Server spezifisch sind
• Wenn wir den richtigen T-SQL-Code für die Implementierung von SQL Server bereitgestellt haben

Obwohl SQL Server proprietäre Software ist, hat Microsoft viele Möglichkeiten bereitgestellt, sie zu verstehen und effizient zu nutzen.

Wenn die Hardware in Ordnung ist und die Installation ordnungsgemäß durchgeführt wurde, aber der SQL Server immer noch langsam läuft, müssen wir zuerst herausfinden, ob es irgendwelche softwarebezogenen Fehler gibt. Um zu überprüfen, was passiert, müssen wir beobachten, wie verschiedene Threads funktionieren. Dies wird erreicht, indem Wartestatistiken verschiedener Threads berechnet werden. SQL Server verwendet Threads für jede Benutzeranfrage, und der Thread ist nichts anderes als ein weiteres Programm innerhalb unseres komplexen Programms namens SQL Server. Es ist wichtig zu beachten, dass dieser Thread kein Betriebssystemthread ist, auf dem SQL Server installiert ist; es bezieht sich auf den SQLOS-Thread, der ein Pseudo-Betriebssystem für den SQL Server ist.

Wartestatistiken können mithilfe von sys.dm_os_wait_stats Dynamic Management View (DMV) berechnet werden, die zusätzliche Informationen über den aktuellen Status liefert. Es gibt viele Skripte online, um diese Ansicht abzufragen, aber mein Favorit ist das Skript von Paul Randal, weil es leicht zu verstehen ist und alle wichtigen Parameter enthält, um Wartestatistiken zu beobachten:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Wenn wir dieses Skript ausführen, müssen wir uns auf die oberen Zeilen des Ergebnisses konzentrieren, da sie zuerst gesetzt werden und den maximalen Wartetyp darstellen.

Wir müssen Wartetypen verstehen, damit wir die richtigen Entscheidungen treffen können. Um mehr über die verschiedenen Wartetypen zu erfahren, können wir die hervorragende Microsoft-Dokumentation aufrufen.

Nehmen wir ein Beispiel, bei dem wir zu viel PAGEIOLATCH_XX haben. Dies bedeutet, dass ein Thread darauf wartet, dass Datenseiten von der Festplatte in den Puffer gelesen werden, der nichts anderes als ein Speicherblock ist. Wir müssen sicher sein, dass wir verstehen, was vor sich geht. Dies bedeutet nicht notwendigerweise ein schlechtes I/O-Subsystem oder nicht genügend Arbeitsspeicher, und das Erhöhen des I/O-Subsystems und des Arbeitsspeichers wird das Problem lösen, aber nur vorübergehend. Um eine dauerhafte Lösung zu finden, müssen wir sehen, warum so viele Daten von der Festplatte gelesen werden: Welche Arten von SQL-Befehlen verursachen dies? Lesen wir zu viele Daten, anstatt weniger Daten zu lesen, indem wir Filter verwenden, z. B. where -Klauseln? Werden aufgrund von Tabellenscans oder Indexscans zu viele Daten gelesen? Können wir sie in Indexsuchen umwandeln, indem wir vorhandene Indizes implementieren oder ändern? Schreiben wir SQL-Abfragen, die von SQL Optimizer (einem anderen Programm innerhalb unseres SQL Server-Programms) missverstanden werden?

Wir müssen aus verschiedenen Blickwinkeln denken und verschiedene Testfälle verwenden, um Lösungen zu finden. Jeder der oben genannten Wartetypen benötigt eine andere Lösung. Ein Datenbankadministrator muss sie gründlich recherchieren, bevor er etwas unternimmt. Meistens werden jedoch 60 bis 70 Prozent der Probleme durch das Auffinden problematischer T-SQL-Abfragen und deren Optimierung gelöst.

#2 Problematische Abfragen finden

Wie oben erwähnt, können wir zunächst nach problematischen Suchanfragen suchen. Der folgende T-SQL-Code findet die 20 Abfragen mit der schlechtesten Leistung:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Wir müssen mit den Ergebnissen vorsichtig sein; Auch wenn eine Abfrage eine maximale durchschnittliche Laufzeit haben kann, ist die Gesamtauswirkung auf den Server gering, wenn sie nur einmal ausgeführt wird, verglichen mit einer Abfrage, die eine mittlere durchschnittliche Laufzeit hat und viele Male an einem Tag ausgeführt wird.

#3 Feinabstimmungsabfragen

Die Feinabstimmung einer T-SQL-Abfrage ist ein wichtiges Konzept. Die grundlegende Sache, die es zu verstehen gilt, ist, wie gut wir T-SQL-Abfragen schreiben und Indizes implementieren können, damit der SQL-Optimierer einen optimierten Plan finden kann, um das zu tun, was wir wollten. Mit jeder neuen Version von SQL Server erhalten wir einen ausgefeilteren Optimierer, der unsere Fehler beim Schreiben nicht optimierter SQL-Abfragen abdeckt und auch alle Fehler im Zusammenhang mit dem vorherigen Optimierer behebt. Aber wie intelligent der Optimierer auch sein mag, wenn wir ihm nicht sagen können, was wir wollen (indem wir eine richtige T-SQL-Abfrage schreiben), wird der SQL-Optimierer seine Aufgabe nicht erfüllen können.

SQL Server verwendet erweiterte Such- und Sortieralgorithmen. Wenn wir gut in Such- und Sortieralgorithmen sind, können wir meistens erraten, warum SQL Server bestimmte Maßnahmen ergreift. Das beste Buch, um mehr zu lernen und solche Algorithmen zu verstehen, ist The Art of Computer Programming von Donald Knuth.

Wenn wir Abfragen untersuchen, die fein abgestimmt werden müssen, müssen wir den Ausführungsplan dieser Abfragen verwenden, damit wir herausfinden können, wie SQL Server sie interpretiert.

Ich kann hier nicht alle Aspekte des Ausführungsplans behandeln, aber auf einer grundlegenden Ebene kann ich die Dinge erklären, die wir berücksichtigen müssen.

• Zuerst müssen wir herausfinden, welche Operatoren den größten Teil der Abfragekosten übernehmen.
• Wenn der Betreiber hohe Kosten übernimmt, müssen wir den Grund dafür erfahren. Meistens kosten Scans mehr als Suchvorgänge. Wir müssen untersuchen, warum ein bestimmter Scan (Tabellenscan oder Indexscan) anstelle einer Indexsuche stattfindet. Wir können dieses Problem lösen, indem wir geeignete Indizes für Tabellenspalten implementieren, aber wie bei jedem komplexen Programm gibt es keine feste Lösung. Wenn die Tabelle beispielsweise klein ist, sind Scans schneller als Suchvorgänge.
• Es gibt ungefähr 78 Operatoren, die die verschiedenen Aktionen und Entscheidungen des SQL Server-Ausführungsplans darstellen. Wir müssen sie gründlich studieren, indem wir die Microsoft-Dokumentation konsultieren, damit wir sie besser verstehen und die richtigen Maßnahmen ergreifen können.

#4 Wiederverwendung des Ausführungsplans

Selbst wenn wir ordnungsgemäße Indizes für Tabellen implementieren und guten T-SQL-Code schreiben, treten Leistungsprobleme auf, wenn der Ausführungsplan nicht wiederverwendet wird. Nach der Feinabstimmung der Abfragen müssen wir sicherstellen, dass der Ausführungsplan bei Bedarf wiederverwendet werden kann. Die meiste CPU-Zeit wird für die Berechnung des Ausführungsplans aufgewendet, der eliminiert werden kann, wenn wir den Plan wiederverwenden.

Wir können die folgende Abfrage verwenden, um herauszufinden, wie oft der Ausführungsplan wiederverwendet wird, wobei usecounts , wie oft der Plan wiederverwendet wird:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Die beste Möglichkeit, den Ausführungsplan wiederzuverwenden, besteht darin, parametrisierte gespeicherte Prozeduren zu implementieren. Wenn wir keine gespeicherten Prozeduren implementieren können, können wir sp_executesql verwenden, das stattdessen zum Ausführen von T-SQL-Anweisungen verwendet werden kann, wenn die einzige Änderung an den SQL-Anweisungen Parameterwerte sind. SQL Server wird höchstwahrscheinlich den Ausführungsplan wiederverwenden, der bei der ersten Ausführung generiert wurde.

Auch hier gibt es, wie bei jedem komplexen Computerprogramm, keine feste Lösung. Manchmal ist es besser, den Plan neu zu erstellen.

Sehen wir uns die folgenden zwei Beispielabfragen an:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

Nehmen wir an, wir haben einen nicht gruppierten Index in der name und die Hälfte der Tabelle hat den Wert sri und einige Zeilen haben pal in der name . Für die erste Abfrage verwendet SQL Server den Tabellenscan, da die Hälfte der Tabelle dieselben Werte enthält. Aber für die zweite Abfrage ist es besser, den Index-Scan zu verwenden, da nur wenige Zeilen einen pal -Wert haben.

Auch wenn Abfragen ähnlich sind, ist der gleiche Ausführungsplan möglicherweise keine gute Lösung. Meistens wird es ein anderer Fall sein, also müssen wir alles sorgfältig analysieren, bevor wir uns entscheiden. Wenn wir den Ausführungsplan nicht wiederverwenden möchten, können wir immer die Option „Neu kompilieren“ in gespeicherten Prozeduren verwenden.

Denken Sie daran, dass es auch nach der Verwendung von gespeicherten Prozeduren oder sp_executesql Zeiten gibt, in denen der Ausführungsplan nicht wiederverwendet wird. Sie sind:

• Wenn sich die von der Abfrage verwendeten Indizes ändern oder gelöscht werden
• Wenn sich die Statistiken, die Struktur oder das Schema einer von der Abfrage verwendeten Tabelle ändert
• Wenn wir die Option „Neu kompilieren“ verwenden
• Bei einer großen Anzahl von Einfügungen, Aktualisierungen oder Löschungen
• Wenn wir DDL und DML in einer einzigen Abfrage mischen

#5 Unnötige Indizes entfernen

Nach der Feinabstimmung der Abfragen müssen wir überprüfen, wie die Indizes verwendet werden. Die Indexverwaltung erfordert viel CPU und E/A. Jedes Mal, wenn wir Daten in eine Datenbank einfügen, muss SQL Server auch die Indizes aktualisieren, daher ist es besser, sie zu entfernen, wenn sie nicht verwendet werden.

SQL Server stellt uns dm_db_index_usage_stats DMV zur Verfügung, um Indexstatistiken zu finden. Wenn wir den folgenden T-SQL-Code ausführen, erhalten wir Nutzungsstatistiken für verschiedene Indizes. Wenn wir Indizes finden, die überhaupt nicht oder selten verwendet werden, können wir sie löschen, um die Leistung zu steigern.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 SQL Server-Installation und Datenbankeinrichtung

Bei der Einrichtung einer Datenbank müssen wir Daten und Protokolldateien getrennt aufbewahren. Der Hauptgrund dafür ist, dass das Schreiben und Zugreifen auf Datendateien nicht sequenziell erfolgt, während das Schreiben und Zugreifen auf Protokolldateien sequenziell erfolgt. Wenn wir sie auf das gleiche Laufwerk legen, können wir sie nicht optimal nutzen.

Wenn wir ein Storage Area Network (SAN) kaufen, gibt uns ein Anbieter möglicherweise einige Empfehlungen zur Einrichtung, aber diese Informationen sind nicht immer hilfreich. Wir müssen mit unseren Hardware- und Netzwerkleuten eine detaillierte Diskussion darüber führen, wie Daten und Protokolldateien getrennt und auf optimierte Weise aufbewahrt werden können.

#7 Überladen Sie SQL Server nicht

Die Hauptaufgabe eines jeden Datenbankadministrators besteht darin, sicherzustellen, dass der Produktionsserver reibungslos läuft und Kunden so gut wie möglich bedient. Um dies zu erreichen, müssen wir separate Datenbanken (wenn möglich auf separaten Computern) für die folgenden Umgebungen unterhalten:

• Produktion
• Entwicklung
• Testen
• Analytisch

Für eine Produktionsdatenbank benötigen wir eine Datenbank mit vollständigem Wiederherstellungsmodus, und für andere Datenbanken reicht ein einfacher Wiederherstellungsmodus aus.

Das Testen auf einer Produktionsdatenbank wird das Transaktionsprotokoll, die Indizes, die CPU und die E/A stark belasten. Deshalb müssen wir getrennte Datenbanken für Produktion, Entwicklung, Test und Analyse verwenden. Verwenden Sie nach Möglichkeit separate Computer für jede Datenbank, da dies die CPU- und E/A-Last verringert.

#8 Transaktionsprotokoll, tempdb und Speicher

Die Protokolldatei muss über genügend freien Speicherplatz für normale Operationen verfügen, da eine automatische Vergrößerungsoperation für eine Protokolldatei zeitaufwändig ist und andere Operationen dazu zwingen könnte, zu warten, bis sie abgeschlossen ist. Um die Größe der Protokolldatei für jede Datenbank und deren Nutzung herauszufinden, können wir DBCC SQLPERF(logspace) .

Der beste Weg, tempdb einzurichten, besteht darin, es auf einer separaten Festplatte abzulegen. Wir müssen die Anfangsgröße so groß halten, wie wir es uns leisten können, denn wenn sie eine Autogrow-Situation erreicht, nimmt die Leistung ab.

Wie bereits erwähnt, müssen wir sicherstellen, dass der SQL-Server auf einem separaten Computer ausgeführt wird, vorzugsweise auf einem ohne andere Anwendungen darauf. Wir müssen etwas Arbeitsspeicher für das Betriebssystem bereithalten, plus etwas mehr, wenn es Teil eines Clusters ist, also sollten in den meisten Fällen etwa 2 GB ausreichen.

Fazit:

Die hier besprochenen Verfahren und Vorschläge dienen nur der grundlegenden Leistungsoptimierung. Wenn wir diese Schritte befolgen, können wir im Durchschnitt eine Leistungssteigerung von etwa 40 bis 50 Prozent erzielen. Um eine erweiterte SQL Server-Leistungsoptimierung durchzuführen, müssten wir uns viel eingehender mit jedem der hier behandelten Schritte befassen.

Weiterführende Literatur im Toptal Engineering Blog:

• Engpässe mit SQL-Indizes und -Partitionen lösen
• Migrationshandbuch von Oracle zu SQL Server und von SQL Server zu Oracle