Übung: Optimieren der Anwendungsleistung

  • 10 Minuten

In dieser Übung beobachten Sie ein neues Leistungsszenario, in dem Sie die Anwendung und Abfragen optimieren.

Optimieren der Anwendungsleistung mit Azure SQL

In einigen Fällen kann bei der Migration einer vorhandenen Anwendung und einer SQL-Abfrageauslastung zu Azure Optimierungspotenzial für Abfragen aufgedeckt werden.

Sie müssen eine neue Tabelle für eine große Anzahl von gleichzeitigen INSERT-Aktivitäten hinzufügen, um eine neue Erweiterung einer Website für AdventureWorks-Bestellungen zu unterstützen und ein Bewertungssystems für Kunden bereitzustellen. Sie haben die SQL-Abfragearbeitsauslastung auf einem Entwicklungscomputer mit SQL Server 2022 getestet, der über ein lokales SSD-Laufwerk für die Datenbank und das Transaktionsprotokoll verfügt.

Wenn Sie den Test mithilfe der Dienstebene „Universell“ (acht virtuelle Kerne) zu Azure SQL-Datenbank verschieben, verlangsamt sich die INSERT-Arbeitsauslastung. Sollten Sie das Dienstziel oder die Ebene ändern, um die neue Arbeitsauslastung zu unterstützen, oder sollten Sie sich auf die Anwendung konzentrieren?

Sie finden alle Skripts für diese Übung im Ordner 04-Performance\tuning_applications im GitHub-Repository, das Sie geklont haben, oder in der heruntergeladenen ZIP-Datei.

Erstellen einer neuen Tabelle für die Anwendung

Klicken Sie im Objekt-Explorer auf die AdventureWorks-Datenbank. Verwenden Sie Datei>Öffnen>Datei, um das Skript order_rating_ddl.sql zu öffnen, um eine Tabelle in der AdventureWorks-Datenbank zu erstellen. Das Fenster des Abfrage-Editors sollte in etwa den folgenden Text beinhalten:

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

Wählen Sie Ausführen aus, um das Skript auszuführen.

Laden von Abfragen zum Überwachen der Abfrageausführung

Laden Sie nun einige T-SQL-Abfragen für dynamische Verwaltungssichten (DMVs), um die Abfrageleistung von aktiven Abfrage-, Warte- und E/A-Vorgängen zu beobachten. Laden Sie alle Abfragen in den Kontext der AdventureWorks-Datenbank.

  1. Klicken Sie im Objekt-Explorer auf die AdventureWorks-Datenbank. Verwenden Sie Datei>Öffnen>Datei, um das Skript sqlrequests.sql zu öffnen, um aktive SQL-Abfragen anzuzeigen. Das Fenster des Abfrage-Editors sollte in etwa den folgenden Text beinhalten:

    SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

  2. Klicken Sie im Objekt-Explorer auf die AdventureWorks-Datenbank. Verwenden Sie Datei>Öffnen>Datei, um das Skript top_waits.sql zu öffnen, um die wichtigsten Wartetypen nach Anzahl anzuzeigen. Das Fenster des Abfrage-Editors sollte in etwa den folgenden Text beinhalten:

    SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;

  3. Klicken Sie im Objekt-Explorer auf die AdventureWorks-Datenbank. Verwenden Sie Datei>Öffnen>Datei, um das Skript tlog_io.sql zu öffnen, um die Latenz für Transaktionsprotokollschreibvorgänge zu beobachten. Das Fenster des Abfrage-Editors sollte in etwa den folgenden Text beinhalten:

    SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
FROM sys.dm_io_virtual_file_stats
(db_id('AdventureWorks'), 2);

Vorbereiten des Arbeitsauslastungsskripts für die Ausführung

Öffnen und bearbeiten Sie das Workload-Skript order_rating_insert_single.cmd.

  • Ersetzen Sie Ihre eindeutige ID (unique_id), die Sie in der ersten Übung für den Servernamen erhalten haben, durch den -S parameter.
  • Ersetzen Sie das Kennwort, das Sie bei der Datenbankbereitstellung in der ersten Übung angegeben haben, durch den -P parameter.
  • Speichern Sie die Änderungen in der Datei.

Ausführen der Arbeitsauslastung

  1. Wechseln Sie über eine PowerShell-Eingabeaufforderung in das Verzeichnis für diese Modulübung:

    cd c:<base directory>\04-Performance\tuning_applications

  2. Führen Sie die Arbeitsauslastung mit dem folgenden Befehl aus:

    .\order_rating_insert_single.cmd

    Dieses Skript verwendet das Programm „ostress.exe“ für die Ausführung von 25 gleichzeitigen Benutzern, indem die folgende T-SQL-Anweisung ausgeführt wird (im Skript order_rating_insert_single.sql):

    DECLARE @x int;
SET @x = 0;
WHILE (@x < 500)
BEGIN
SET @x = @x + 1;
INSERT INTO SalesLT.OrderRating
(SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
VALUES (@x, getdate(), 5, 'This was a great order');
END

    In diesem Skript sehen Sie, dass es sich nicht um eine echte Darstellung von Daten handelt, die von der Website stammen. Es simuliert jedoch viele Bestellungsbewertungen, die in die Datenbank aufgenommen werden.

Beobachten von dynamischen Verwaltungssichten und der Leistung der Arbeitsauslastung

Führen Sie nun die Abfragen in SQL Server Management Studio (SSMS) aus, die Sie zuvor geladen haben, um die Leistung zu beobachten. Führen Sie die Abfragen für sqlrequests.sql, top_waits.sql und tlog_io.sql aus.

Anhand dieser Abfragen können Sie Folgendes beobachten:

  • Viele Anforderungen weisen dauerhaft den wait_type „WRITELOG“ mit einem Wert > 0 (Null) auf.
  • Der Wartevorgangstyp WRITELOG ist einer der am häufigsten vorkommenden Wartevorgangstypen.
  • Die durchschnittliche Schreibzeit für das Transaktionsprotokoll (die avg_tlog_io_write_ms-Spalte im Resultset tlog_io.sql ) liegt ungefähr bei 2 ms.

Die Dauer dieser Arbeitsauslastung auf einer SQL Server 2022-Instanz mit einem SSD-Laufwerk liegt bei ungefähr zehn bis zwölf Sekunden. Die Gesamtdauer in Azure SQL-Datenbank mit einem Gen5-v8-Kern beträgt ca. 25 Sekunden.

WRITELOG-Wartevorgangstypen mit höheren Wartezeiten sind ein Hinweis auf eine Latenz, die durch das Leeren in das Transaktionsprotokoll entsteht. Eine Wartezeit von zwei Millisekunden pro Schreibvorgang scheint sehr wenig zu sein, auf einem lokalen SSD-Laufwerk können diese Wartevorgänge jedoch auch kürzer als eine Millisekunde sein.

Auswählen einer Lösung

Das Problem besteht nicht in einem hohen Prozentsatz von Protokollschreibaktivität. Das Azure-Portal und sys.dm_db_resource_stats weisen keinen Prozentsatz über 20 bis 25 Prozent auf (diese müssen nicht abgefragt werden). Das Problem besteht ebenso wenig in einer IOPS-Begrenzung. Es besteht darin, dass die Anwendungsarbeitsauslastung anfällig für eine geringe Wartezeit bei Schreibvorgängen in das Transaktionsprotokoll ist und dass die Dienstebene „Universell“ nicht auf diese Art von Latenzanforderungen ausgelegt ist. Die erwartete E/A-Latenz für die Azure SQL-Datenbank beträgt 5-7 ms.

Hinweis

Für die Dienstebene „Universell“ von Azure SQL-Datenbank liegt die ungefähre E/A-Wartezeit laut der Dokumentation bei fünf bis sieben (Schreibvorgänge) bzw. fünf bis zehn Millisekunden (Lesevorgänge). Es sind also eher Wartezeiten wie diese zu erwarten. Wartezeiten bei der Azure SQL Managed Instance-Ebene „Universell“ sind ähnlich. Wenn Ihre Anwendung sehr anfällig für E/A-Wartezeiten ist, sollten Sie unter Umständen zur Dienstebene des Typs „Unternehmenskritisch“ wechseln.

Untersuchen Sie das T-SQL-Workload-Skript order_rating_insert_single.sql. Jede INSERT-Aktivität ist ein einzelner Transaktionscommit, der das Leeren eines Transaktionsprotokolls erfordert.

Ein Commit für jede Einfügung ist nicht effizient. Die Anwendung wurde jedoch nicht beeinträchtigt, da Commits auf einem lokalen SSD-Laufwerk sehr schnell erfolgten. Der Tarif „Unternehmenskritisch“ (Dienstziel oder SKU) bietet lokale SSD-Datenträger mit geringerer Wartezeit. Es ist möglich, dass eine Anwendung optimiert werden kann, sodass die Arbeitsauslastung für das Transaktionsprotokoll in Bezug auf die E/A-Wartezeit nicht so empfindlich ist.

Sie können den T-SQL-Batch für die Workload ändern, um die INSERT Iterationen mit BEGIN TRAN/COMMIT TRAN zu umschließen.

Ausführen einer geänderten, effizienteren Arbeitsauslastung

Nehmen Sie Änderungen an Skripts vor, und führen Sie diese aus, um eine effizientere E/A-Leistung zu erzielen. Sie finden die geänderte Workload im Skript order_rating_insert.sql.

  1. Bereiten Sie das Workload-Skript vor, indem Sie order_rating_insert.cmd bearbeiten und den korrekten Servernamen und das korrekte Kennwort eingeben.

  2. Führen Sie die geänderte Arbeitsauslastung mithilfe des Skripts order_rating_insert.cmd aus, und gehen Sie dabei ähnlich wie bei der Ausführung des vorherigen Workload-Skripts vor.

Untersuchen der neuen Ergebnisse

  1. Sehen Sie sich die Ergebnisse des T-SQL-Skripts für sqlrequests.sql in SSMS an. Beachten Sie, dass insgesamt viel weniger WRITELOG-Wartevorgänge erforderlich sind und diese insgesamt eine geringere Wartezeit beanspruchen.

    Die Arbeitsauslastung wird im Vergleich zu vorher jetzt erheblich schneller ausgeführt. Das ist ein Beispiel für das Optimieren einer Anwendung für SQL-Abfragen, die innerhalb oder außerhalb von Azure ausgeführt werden.

    Hinweis

    Diese Arbeitsauslastung kann für eine Azure SQL-Datenbank-Instanz mit dem Verbindungstyp Umleiten noch schneller ausgeführt werden. Für die Bereitstellung in dieser Übung wird die Standardverbindungsmethode (ein Proxy) ausgewählt, da Sie außerhalb von Azure verbunden sind. Mit einer Umleitung können Sie Arbeitsauslastungen erheblich beschleunigen, sofern die vom Client zum Server erforderlichen Roundtrips vorhanden sind.

  2. Achten Sie auf die Dauer der Arbeitsauslastung. Die Arbeitsauslastung wird so schnell ausgeführt, dass es möglicherweise schwierig ist, die Diagnosedaten der Abfragen zu verfolgen, die zuvor in dieser Übung verwendet wurden.

    Das Konzept der Batchverarbeitung ist für die meisten und somit auch für mit Azure SQL verbundene Anwendungen nützlich.

Tipp

Die Ressourcengovernance in Azure kann sich auf sehr große Transaktionen auswirken, und die Symptome sind LOG_RATE_GOVERNOR. In diesem Beispiel enthält die Spalte „char(500) not null“ Leerzeichen und führt daher zu großen Transaktionsprotokollen. Sie können die Leistung weiter optimieren, indem Sie die variable Länge für diese Spalte aktivieren.

In der nächsten Lerneinheit erfahren Sie mehr über die intelligente Leistung in Azure SQL.