Ćwiczenie — Optymalizowanie wydajności aplikacji

10 min

W tym ćwiczeniu zaobserwujesz nowy scenariusz wydajności i rozwiążesz go, optymalizując aplikację i zapytania.

Optymalizowanie wydajności aplikacji za pomocą usługi Azure SQL

W niektórych przypadkach migrowanie istniejącej aplikacji i obciążeń zapytań SQL na platformę Azure pozwala odkryć możliwości optymalizacji i dostrajania zapytań.

Aby obsługiwać nowe rozszerzenie witryny internetowej dla zamówień w firmie AdventureWorks w celu udostępnienia systemu ocen dla klientów, musisz dodać nową tabelę dla dużego zestawu współbieżnych operacji INSERT. Przetestowano obciążenie zapytań SQL na komputerze programistycznym przy użyciu programu SQL Server 2022 z lokalnym dyskiem SSD dla bazy danych i dziennika transakcji.

Po przeniesieniu testu do usługi Azure SQL Database w warstwie Ogólnego przeznaczenia (8 rdzeni wirtualnych) obciążenie operacji INSERT działa wolniej. Czy masz zmienić cel usługi lub warstwę w celu obsługi nowego obciążenia, czy też masz przyjrzeć się aplikacji?

Wszystkie skrypty dla tego ćwiczenia można znaleźć w folderze 04-Performance\tuning_applications w sklonowanym repozytorium GitHub lub pobranym pliku zip.

Tworzenie nowej tabeli dla aplikacji

W Eksploratorze obiektów wybierz bazę danych AdventureWorks. Otwórz plik>,>aby otworzyć skrypt order_rating_ddl.sql, aby utworzyć tabelę w AdventureWorks bazie danych. Tekst w oknie edytora zapytań powinien wyglądać następująco:

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

Wybierz pozycję Wykonaj , aby uruchomić skrypt.

Ładowanie zapytań w celu monitorowania ich wykonywania

Załadujmy teraz zapytania T-SQL dla dynamicznych widoków zarządzania (DMV), aby obserwować wydajność zapytań w przypadku aktywnych zapytań, oczekiwania i operacji wejścia/wyjścia. Załaduj wszystkie te zapytania w kontekście bazy danych AdventureWorks.

W Eksploratorze obiektów wybierz bazę danych AdventureWorks. Otwórz plik>,>aby otworzyć skrypt sqlrequests.sql, aby przyjrzeć się aktywnym zapytaniom SQL. Tekst w oknie edytora zapytań powinien wyglądać następująco:

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;

W Eksploratorze obiektów wybierz bazę danych AdventureWorks. Użyj pliku>Otwórz>plik, aby otworzyć skrypt top_waits.sql, aby przyjrzeć się najwyższym typom oczekiwania według liczby. Tekst w oknie edytora zapytań powinien wyglądać następująco:
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
W Eksploratorze obiektów wybierz bazę danych AdventureWorks. Użyj pliku>Otwórz>plik, aby otworzyć skrypt tlog_io.sql w celu obserwowania opóźnienia zapisów dziennika transakcji. Tekst w oknie edytora zapytań powinien wyglądać następująco:
```
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);
```

Przygotowywanie skryptu obciążenia do wykonywania

Otwórz i zmodyfikuj skrypt obciążenia order_rating_insert_single.cmd.

Zastąp wartość unique_id podaną w pierwszym ćwiczeniu dla nazwy serwera .-S parameter
Zastąp hasło podane we wdrożeniu bazy danych z pierwszego ćwiczenia dla elementu -P parameter.
Zapisz zmiany w pliku.

Uruchamianie obciążenia

W wierszu polecenia programu PowerShell przejdź do katalogu dotyczącego czynności w tym module:
```
cd c:<base directory>\04-Performance\tuning_applications
```
Uruchom obciążenie przy użyciu następującego polecenia:
```
.\order_rating_insert_single.cmd
```
Ten skrypt używa programu ostress.exe, aby uruchomić 25 współbieżnych użytkowników, uruchamiając następującą instrukcję języka T-SQL (w skrypcie 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
```
Ten skrypt pokazuje, że nie jest to dokładne rzeczywiste zobrazowanie danych pochodzących z witryny internetowej. Ale symuluje on różne oceny zamówień pobranych do bazy danych.

Obserwowanie dynamicznych widoków zarządzania i wydajności obciążenia

Teraz uruchom w programie SSMS (SQL Server Management Studio) zapytania, które zostały wcześniej załadowane w celu obserwowania wydajności. Uruchom zapytania sqlrequests.sql, top_waits.sql i tlog_io.sql.

Za pomocą tych zapytań możesz zaobserwować następujące fakty:

Wiele żądań stale ma wait_type wartość WRITELOG z wartością > 0.
Typ WRITELOG oczekiwania jest jedną z najwyższych liczb dla typów oczekiwania.
Średni czas zapisu w dzienniku transakcji (kolumna avg_tlog_io_write_ms w zestawie wyników tlog_io.sql ) wynosi około 2 ms.

Czas trwania tego obciążenia w wystąpieniu programu SQL Server 2022 z dyskiem SSD wynosi około 10–12 sekund. Łączny czas trwania w usłudze Azure SQL Database przy użyciu rdzeni Gen5 v8 wynosi około 25 sekund.

WRITELOG typy oczekiwania z wyższymi godzinami oczekiwania wskazują na opróżnianie opóźnienia do dziennika transakcji. Czas oczekiwania równy 2 ms na zapis nie wydaje się długi, ale na lokalnym dysku SSD to oczekiwanie może być krótsze niż 1 ms.

Wybór rozwiązania

Problemem nie jest wysoki odsetek działań zapisu dziennika. Witryna Azure Portal i sys.dm_db_resource_stats nie pokazują żadnych liczb wyższych niż 20–25 procent (nie musisz wykonywać zapytań o te dane). Problemem nie jest też limit liczby operacji wejścia/wyjścia na sekundę. Problem polega na tym, że obciążenie aplikacji jest wrażliwe na małe opóźnienia zapisów dziennika transakcji, a warstwa Ogólnego przeznaczenia nie jest zaprojektowana dla takiego wymagania dotyczącego opóźnienia. Oczekiwane opóźnienie we/wy dla usługi Azure SQL Database wynosi 5–7 ms.

Uwaga

Dokumentacja usługi Azure SQL Database w warstwie Ogólnego przeznaczenia informuje o przybliżonych opóźnieniach operacji wejścia/wyjścia równych 5–7 (zapisy) i 5–10 (odczyty). Mogą wystąpić opóźnienia, takie jak te wartości. Opóźnienia dla wystąpienia zarządzanego usługi Azure SQL w warstwie Ogólnego przeznaczenia są podobne. Jeśli Twoja aplikacja jest bardzo wrażliwa na opóźnienia operacji wejścia/wyjścia, rozważ użycie warstw Krytyczne dla działania firmy.

Sprawdź skrypt języka T-SQL obciążenia order_rating_insert_single.sql. Każde z nich INSERT jest jednym zatwierdzeniem transakcji, które wymaga opróżnienia dziennika transakcji.

Jedno zatwierdzenie dla każdego wstawienia jest niewydajne, ale nie wpływało to na aplikację na lokalnym dysku SSD, ponieważ każde zatwierdzenie było bardzo szybkie. Warstwa cenowa Krytyczne dla działania firmy (cel usługi lub jednostka SKU) udostępnia lokalne dyski SSD z mniejszym opóźnieniem. Istnieje możliwość, że istnieje optymalizacja aplikacji, więc obciążenie nie jest tak wrażliwe na opóźnienia operacji we/wy dla dziennika transakcji.

Możesz zmienić partię języka T-SQL dla obciążenia, aby opakowować BEGIN TRAN/COMMIT TRAN ją wokół INSERT iteracji.

Uruchamianie zmodyfikowanego, wydajniejszego obciążenia

Przeprowadź edycję skryptów i uruchom je, aby zaobserwować większą wydajność operacji wejścia/wyjścia. Zmodyfikowane obciążenie można znaleźć w skryscie order_rating_insert.sql .

Przygotuj skrypt obciążenia, edytując order_rating_insert.cmd , aby użyć poprawnej nazwy serwera i hasła.
Uruchom zmodyfikowane obciążenie przy użyciu skryptu order_rating_insert.cmd , podobnie jak w przypadku uruchomienia poprzedniego skryptu obciążenia.

Obserwowanie nowych wyników

Przyjrzyj się wynikom skryptu języka T-SQL dla pliku sqlrequests.sql w programie SSMS. Zwróć uwagę na znacznie mniejszą liczbę oczekiwań na instrukcję WRITELOG i ogólny krótszy czas oczekiwania na te oczekiwania.

Obciążenie działa teraz znacznie szybciej w porównaniu z poprzednim wykonaniem. Jest to przykład dostrojenia aplikacji pod kątem zapytań SQL, które będą uruchamiane potem na platformie Azure lub poza nią.

Uwaga

To obciążenie może działać jeszcze szybciej w wystąpieniu usługi Azure SQL Database z typem połączenia Przekierowanie . Wdrożenie wykonane w tym ćwiczeniu używa domyślnego typu połączenia, który jest typem serwera proxy, ponieważ masz połączenie poza platformą Azure. Użycie połączenia Przekierowanie może znacznie przyspieszyć obciążenie takie jak to, uwzględniając wymagane rundy od klienta do serwera.
Obserwuj czas trwania obciążenia. Obciążenie jest uruchamiane tak szybko, że obserwowanie danych diagnostycznych z zapytań używanych wcześniej w tym działaniu może być trudne.

Koncepcja „dzielenia na partie” może ułatwić działanie większości aplikacji, w tym tych połączonych z usługą Azure SQL.

Napiwek

Nadzór nad zasobami na platformie Azure może mieć wpływ na bardzo duże transakcje, a objawy to LOG_RATE_GOVERNOR. W tym przykładzie niepusta kolumna typu char(500) dodaje spacje i powoduje duże rekordy dziennika transakcji. Wydajność możesz jeszcze bardziej zoptymalizować, zmieniając tę kolumnę na kolumnę o zmiennej długości.

W następnej lekcji dowiesz się więcej o inteligentnej wydajności w usłudze Azure SQL.