Analysieren Ihrer Datenbank-Workload auf einer Amazon RDS for SQL Server DB-Instance mit Database Engine Tuning Advisor

Database Engine Tuning Advisor ist eine von Microsoft bereitgestellte Client-Anwendung, die den Workload der Datenbank analysiert und einen optimalen Satz von Indizes für Ihre Microsoft SQL Server-Datenbanken basierend auf den Arten von Abfragen empfiehlt, die Sie ausführen. Wie auch SQL Server Management Studio kann der Optimierungshelfer von einem Client-Computer ausgeführt werden, der eine Verbindung zu Ihrer Amazon RDS-DB-Instance aufbaut, auf der SQL Server ausgeführt wird. Beim Clientcomputer kann es sich um einen lokalen hauseigenen Computer innerhalb Ihres Netzwerks oder um eine Amazon EC2-Windows-Instance handeln, die in der gleichen Region wie Ihre Amazon RDS-DB-Instance ausgeführt wird.

In diesem Abschnitt wird gezeigt, wie ein Workload für eine Analyse im Optimierungshelfer erfasst wird. Das ist die bevorzugte Vorgehensweise für die Erfassung einer Workload, da der Host-Zugriff auf die SQL Server-Instance durch Amazon RDS beschränkt wird. Weitere Informationen finden Sie unter Database Engine Tuning Advisor in der Microsoft-Dokumentation.

Um den Optimierungshelfer zu verwenden, müssen Sie dem Helfer eine sogenannte Workload zur Verfügung stellen. Eine Workload besteht aus einer Reihe von Transact-SQL-Statements, die in einer oder in mehreren Datenbanken ausgeführt werden, die Sie optimieren möchten. Der Datenbank-Engine-Optimierungshelfer verwendet bei der Optimierung von Datenbanken Trace-Dateien, Trace-Tabellen, Transact-SQL-Skripts oder XML-Dateien als Workload-Input. Bei der Arbeit mit Amazon RDS kann eine Workload eine Datei auf einem Clientcomputer oder eine Datenbanktabelle in einer Amazon RDS for SQL Server-DB sein, die für Ihren Clientcomputer zugänglich ist. Die Datei oder Tabelle muss Abfragen für die zu optimierenden Datenbanken enthalten, die in einem Format vorliegen, das für eine erneute Wiedergabe geeignet ist.

Damit der Optimierungshelfer optimal arbeiten kann, muss eine Workload so realistisch sein wie möglich. Sie können eine Workload-Datei oder -Tabelle erzeugen, indem Sie ein Trace für Ihre DB-Instance durchführen. Sie können während der Ausführung eines Trace entweder eine Belastung Ihrer DB-Instance simulieren oder Ihre Anwendungen mit normaler Belastung ausführen.

Es gibt zwei Arten von Traces: clientseitig und serverseitig. Die Einrichtung eines clientseitigen Trace ist unkomplizierter und es können im SQL Server Profiler Trace-Ereignisse in Echtzeit erfasst werden. Die Einrichtung eines serverseitigen Trace ist komplizierter und erfordert etwas Transact-SQL-Scripting. Darüber hinaus wird durch den Trace Speicherplatz verbraucht, da der Trace in der Amazon RDS-DB-Instance in eine Datei geschrieben wird. Es ist wichtig, zu beachten, wie viel Speicherplatz die Ausführung eines serverseitigen Trace benötigt, da die DB-Instance in den Storage-Full-Status wechseln könnte und dann nicht mehr verfügbar ist, sobald der Speicherplatz ausgeht.

Sobald in SQL Server Profiler bei einer Client-seitigen Nachverfolgung eine ausreichende Menge an Daten erfasst wurde, können Sie die Workload-Datei erzeugen, indem Sie die Nachverfolgung entweder als Datei auf dem lokalen Computer oder in einer Datenbank-Tabelle auf einer DB-Instance speichern, die für den Client-Computer verfügbar ist. Der Hauptnachteil bei der Verwendung eines clientseitigen Trace ist der, dass unter großer Auslastung eventuell nicht alle Abfragen vom Trace erfasst werden. Das könnte die Effektivität der vom Datenbank-Engine-Optimierungshelfer durchgeführten Analyse negativ beeinträchtigen. Muss ein Trace unter großer Auslastung ausgeführt werden und Sie möchten sicherstellen, dass während einer Trace-Sitzung alle Abfragen erfasst werden, sollte ein serverseitiger Trace zum Einsatz kommen.

Für einen serverseitigen Trace müssen die Trace-Dateien in der DB-Instance in einer geeigneten Workload-Datei gespeichert oder der Trace in einer Tabelle in der DB-Instance gespeichert werden, nachdem der Trace abgeschlossen ist. Sie können den SQL Server Profiler verwenden, um den Trace in einer Datei auf Ihrem lokalen Computer zu speichern oder lassen den Optimierungshelfer aus der Trace-Tabelle in der DB-Instance lesen.

Ausführung eines clientseitigen Trace in einer SQL Server-DB-Instance

Ausführen einer clientseitigen Nachverfolgung in einer SQL Server-DB-Instance

1. Starten Sie SQL Server Profiler. Sie finden das Tool im Ordner Leistungstools Ihres SQL Server-Instance-Ordners. Um einen clientseitigen Trace zu starten, muss eine Trace-Definitionsvorlage geladen oder definiert werden.

2. Wählen Sie im Menü SQL Server Profiler File die Option New Trace (Neue Nachverfolgung). Im Dialogfeld Connect to Server (Mit Server verbinden) geben Sie DB-Instance-Endpunkt, Masterbenutzernamen und das Passwort für die Datenbank ein, für die eine Nachverfolgung ausgeführt werden soll.

3. Im Dialogfeld Trace Properties (Eigenschaften der Nachverfolgung) geben Sie einen Namen für die Nachverfolgung ein und wählen eine Definitionsvorlage für die Nachverfolgung aus. Die Standardvorlage TSQL_Replay wird mit der Anwendung geliefert. Diese Vorlage kann für das Definieren Ihres Trace bearbeitet werden. Ereignisse und Ereignisinformationen können unter der Registerkarte Events Selection (Auswahl von Ereignissen) im Dialogfeld Trace Properties (Eigenschaften der Nachverfolgung) bearbeitet werden.

   Weitere Informationen zu Trace-Definitionsvorlagen und zur Verwendung von SQL Server Profiler zur Angabe eines clientseitigen Trace finden Sie in der Microsoft-Dokumentation in Database Engine Tuning Advisor.

4. Starten Sie den clientseitigen Trace und beobachten Sie die SQL-Abfragen in Echtzeit, die für Ihre DB-Instance ausgeführt werden.

5. Wählen Sie im Menü File (Datei) die Option Stop Trace (Nachverfolgung beenden) aus, sobald die Nachverfolgung abgeschlossen ist. Speichern Sie die Ergebnisse als Datei oder als Trace-Tabelle in Ihrer DB-Instance.

Ausführung eines serverseitigen Trace in einer SQL Server-DB-Instance

Das Schreiben eines Skripts für das Erstellen eines serverseitigen Trace kann eine komplexe Angelegenheit sein und geht über dieses Dokument hinaus. Dieser Abschnitt enthält Beispiel-Skripts, die Sie als Beispiele verwenden können. Wie auch bei einem clientseitigen Trace liegt das Ziel darin, eine Workload-Datei oder eine Trace-Tabelle zu erstellen, die mit dem Datenbank-Engine-Optimierungshelfer geöffnet werden kann.

Hier ist ein gekürztes Beispiel-Script, das einen serverseitigen Trace startet und die Details in einer Workload-Datei erfasst. Der Trace wird zunächst im Verzeichnis D:\RDSDBDATA\Log in der Datei RDSTrace.trc gespeichert und jede Erhöhung der Dateigröße um jeweils 100 MB führt zu Roll-Over-Dateien mit der Bezeichnung RDSTrace_1.trc, RDSTrace_2.trc usw.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

Das folgende Beispiel ist ein Script, mit dem ein Trace angehalten wird. Beachten Sie, dass ein vom vorherigen Script erstellter Trace solange ausgeführt wird, bis Sie den Trace explizit anhalten oder die Festplattenkapazität für den Vorgang nicht mehr ausreicht.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

Die Ergebnisse des serverseitigen Trace können in einer Datenbank-Tabelle gespeichert, und die Datenbank-Tabelle mithilfe der Funktion fn_trace_gettable als Workload für den Optimierungshelfer verwendet werden. Die folgenden Befehle laden die Ergebnisse aller Dateien des Namens RDSTrace.trc im Verzeichnis D:\rdsdbdata\Log, einschließlich aller Roll-Over-Dateien, z. B. RDSTrace_1.trc, in eine Tabelle mit dem Namen RDSTrace in der aktuellen Datenbank.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

Um eine bestimmte Roll-Over-Datei in einer Tabelle zu speichern, z. B. die Datei RDSTrace_1.trc, geben Sie den Namen der Roll-Over-Datei ein und setzen 1 anstelle des Standards als den letzten Parameter für fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

Ausführung des Optimierungshelfers mit einem Trace

Sobald Sie einen Trace erstellen, entweder als lokale Datei oder als Datenbank-Tabelle, können Sie den Optimierungshelfer für Ihre DB-Instance ausführen. Die Verwendung des Optimierungshelfers für Amazon RDS entspricht dem Vorgang der Arbeit mit einer eigenständigen Remote-SQL Server-Instance. Sie können entweder die Benutzerfläche des Optimierungshelfers auf Ihrem Client-Computer oder das Hilfsprogramm dta.exe von der Befehlszeile aus verwenden. In beiden Fällen müssen bei der Verwendung des Optimierungshelfers mithilfe des Endpunkts der DB-Instance eine Verbindung zur Amazon RDS-DB-Instance hergestellt und Ihr Master User Name und Master User Password eingegeben werden.

Das folgende Code-Beispiel demonstriert die Verwendung des Befehlszeilen-Hilfsprogramms dta.exe für eine Amazon RDS-DB-Instance mit dem Endpunkt dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. Das Beispiel enthält den Master-Benutzernamen admin und das Master-Benutzerkennwort test, die zu tunende Beispieldatenbank heißt „machine“ C:\RDSTrace.trc. Der Befehlszeilenbeispielcode legt außerdem eine Nachverfolgungssitzung mit dem Namen RDSTrace1 fest und legt die Output-Dateien auf dem lokalen Computer mit dem Namen RDSTrace.sql für das SQL-Output-Script, RDSTrace.txt für eine Ergebnisdatei und RDSTrace.xml für eine XML-Datei der Analyse fest. In der Datenbank RDSDTA wird außerdem eine Fehlertabelle mit dem Namen festgeleg RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

Hier sehen Sie den gleichen Befehlszeilenbeispielcode mit der Ausnahme, dass es sich bei der Input-Workload um eine Tabelle in der Amazon RDS-Instance mit dem Namen RDSTrace handelt, die sich in der Datenbank RDSDTA befindet.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

Eine vollständige Liste der Befehlszeilenparameter des dta-Dienstprogramms finden Sie unter dta Utility in der Microsoft-Dokumentation.