Oefening: toepassingsprestaties optimaliseren

10 minuten

In deze oefening bekijkt u een nieuw prestatiescenario en lost u dit op door de toepassing en query's te optimaliseren.

Toepassingsprestaties optimaliseren met Azure SQL

In sommige gevallen kan het migreren van een bestaande toepassing en een SQL-queryworkload naar Azure mogelijkheden voor het optimaliseren en afstemmen van query's aan het licht brengen.

Ter ondersteuning van een nieuwe uitbreiding van een website om voor AdventureWorks-bestellingen een beoordelingssysteem voor klanten te bieden moet u een nieuwe tabel toevoegen voor een intensieve set gelijktijdige INSERT-activiteiten. U hebt de SQL-queryworkload getest op een ontwikkelcomputer met SQL Server 2022 met een lokaal SSD-station voor de database en het transactielogboek.

Wanneer u de test verplaatst naar Azure SQL Database met behulp van de categorie Algemeen gebruik (8 vCores), is de INSERT-workload trager. Moet u de servicedoelstelling of -categorie wijzigen om de nieuwe workload te ondersteunen of moet u de toepassing bekijken?

U vindt alle scripts voor deze oefening in de map 04 Performance\tuning_applications in de GitHub-opslagplaats die u hebt gekloond of het zip-bestand dat u hebt gedownload.

Een nieuwe tabel voor de toepassing maken

In Objectverkenner selecteert u de AdventureWorks-database. Gebruik Bestand> openen>om het script order_rating_ddl.sql te openen om een tabel in de AdventureWorks database te maken. Het venster van de query-editor moet er als volgt uitzien:

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

Selecteer Uitvoeren om het script uit te voeren.

Query's laden om de uitvoering van query's te bewaken

We laden nu een aantal T-SQL-query's voor dynamische beheerweergaven (DMV's) om de queryprestaties voor actieve query's, wachttijden en I/O te observeren. Laad al deze query's in de context van de AdventureWorks-database.

In Objectverkenner selecteert u de AdventureWorks-database. Gebruik Bestand> openen>om het script sqlrequests.sql te openen om actieve SQL-query's te bekijken. Het venster van de query-editor moet er als volgt uitzien:

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;

In Objectverkenner selecteert u de AdventureWorks-database. Gebruik Bestand> openen>om het top_waits.sql-script te openen om te kijken naar de belangrijkste wachttypen op aantal. Het venster van de query-editor moet er als volgt uitzien:
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
In Objectverkenner selecteert u de AdventureWorks-database. Gebruik Bestand> openen>om het script tlog_io.sql te openen om de latentie voor schrijfbewerkingen van transactielogboeken te observeren. Het venster van de query-editor moet er als volgt uitzien:
```
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);
```

Het workloadscript voorbereiden voor uitvoering

Open en bewerk het workloadscript order_rating_insert_single.cmd .

Vervang de unique_id die u in de eerste oefening hebt gekregen door de servernaam voor de -S parameter.
Vervang het wachtwoord dat u hebt opgegeven bij de database-implementatie uit de eerste oefening door de -P parameter.
Sla de wijzigingen in het bestand op.

De workload uitvoeren

Ga vanuit een PowerShell-opdrachtprompt naar de map voor deze module-activiteit:
```
cd c:<base directory>\04-Performance\tuning_applications
```
Voer de workload uit met de volgende opdracht:
```
.\order_rating_insert_single.cmd
```
Dit script maakt gebruik van het programma ostress.exe voor het uitvoeren van 25 gelijktijdige gebruikers die de volgende T-SQL-instructie uitvoeren (in het script 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 dit script kunt u zien dat het niet echt een daadwerkelijke weergave is van de gegevens die afkomstig zijn van de website. Maar het simuleert veel orderclassificaties die worden opgenomen in de database.

Prestaties van dynamische beheerweergaven en workload observeren

Voer nu de query's uit in SQL Server Management Studio (SSMS) die u eerder hebt geladen om de prestaties te bekijken. Voer de query's uit voor sqlrequests.sql, top_waits.sql en tlog_io.sql.

Met deze query's kunt u de volgende feiten bekijken:

Veel aanvragen hebben voortdurend een wait_type WRITELOG met een waarde > 0.
Het WRITELOG wachttype is een van de hoogste aantallen voor wachttypen.
De gemiddelde tijd voor het schrijven naar het transactielogboek (de avg_tlog_io_write_ms kolom in de tlog_io.sql-resultatenset ) bevindt zich ergens rond 2 ms.

De duur van deze workload op een SQL Server 2022-exemplaar met een SSD-station is ongeveer 10-12 seconden. De totale duur van Azure SQL Database met behulp van een Gen5 v8 core is ongeveer 25 seconden.

WRITELOG wachttypen met hogere wachttijden geven aan dat latentie wordt leeggemaakt aan het transactielogboek. Een wachttijd van 2 ms per schrijfbewerking lijkt niet zo veel, maar op een lokale SSD-schijf kunnen deze wachttijden minder dan 1 ms zijn.

Een oplossing kiezen

Het probleem is geen hoog percentage schrijfactiviteit van het logboek. In Azure Portal worden sys.dm_db_resource_stats geen getallen weergegeven die hoger zijn dan 20-25 procent (u hoeft geen query's uit te voeren). Het probleem is ook niet een IOPS-limiet. Het probleem is dat de workload van deze toepassing gevoelig is voor een lage latentie voor het schrijven van transactielogboeken en dat de categorie Algemeen gebruik niet is ontworpen voor dit type latentievereisten. De verwachte I/O-latentie voor Azure SQL Database is 5-7 ms.

Notitie

In Azure SQL Database voor Algemeen gebruik worden geschatte I/O-latentiegemiddelden van 5-7 (schrijfbewerkingen) en 5-10 (leesbewerkingen) gedocumenteerd. U kunt een latentie ervaren die overeenkomt met deze cijfers. Latenties voor algemeen gebruik van Azure SQL Managed Instance zijn vergelijkbaar. Als uw toepassing zeer gevoelig is voor I/O-latenties, kunt u de categorie Bedrijfskritiek overwegen.

Bekijk het T-SQL-script order_rating_insert_single.sql-workload . Elk INSERT is één transactiedoorvoering, waarvoor een transactielogboek moet worden leeggemaakt.

Eén doorvoering voor elke INSERT is niet efficiënt, maar de toepassing is niet beïnvloed op een lokale SSD omdat elke doorvoering zeer snel was. De prijscategorie Bedrijfskritiek (servicedoelstelling of SKU) biedt lokale SSD-stations met een lagere latentie. Het is mogelijk dat er een toepassingsoptimalisatie is, dus de workload is niet zo gevoelig voor I/O-latentie voor het transactielogboek.

U kunt de T-SQL-batch voor de workload wijzigen om een BEGIN TRAN/COMMIT TRAN rond de INSERT iteraties te verpakken.

Een aangepaste efficiëntere workload uitvoeren

Bewerk scripts en voer deze uit om efficiëntere I/O-prestaties te bekijken. U vindt de gewijzigde workload in het script order_rating_insert.sql .

Bereid het workloadscript voor door order_rating_insert.cmd te bewerken om de juiste servernaam en het juiste wachtwoord te gebruiken.
Voer de gewijzigde workload uit met behulp van het script order_rating_insert.cmd , vergelijkbaar met de manier waarop u het vorige workloadscript hebt uitgevoerd.

Bekijk de nieuwe resultaten

Bekijk de resultaten van het T-SQL-script voor sqlrequests.sql in SQL Server Management Studio (SMSS). U ziet veel minder WRITELOG-wachttijden en een kortere totale wachttijd voor deze wachttijden.

De workload wordt nu veel sneller uitgevoerd vergeleken met de vorige uitvoering. Dit is een voorbeeld van het afstemmen van een toepassing voor SQL-query's die binnen of buiten Azure worden uitgevoerd.

Notitie

Deze workload kan nog sneller worden uitgevoerd voor een exemplaar van Azure SQL Database met een verbindingstype Omleiding . Voor de implementatie die u in deze oefening hebt uitgevoerd, wordt een standaardverbindingstype gebruikt. Dit is een proxytype omdat u buiten Azure bent verbonden. Met behulp van Omleiding kunt u een dergelijke workload significant versnellen vanwege de retouren van de client naar de server.
Bekijk de duur van de workload. De workload wordt zo snel uitgevoerd, dat het lastig kan zijn om diagnostische gegevens te observeren van query's die eerder in deze activiteit zijn gebruikt.

Batchverwerking kan de meeste toepassingen helpen, inclusief de toepassingen die zijn gekoppeld met Azure SQL.

Tip

Resourcegovernance in Azure kan van invloed zijn op zeer grote transacties en de symptomen zijn LOG_RATE_GOVERNOR. In dit voorbeeld vult de kolom char(500) not null spaties op, wat leidt tot grote transactielogboekrecords. U kunt de prestaties nog meer optimaliseren door van de kolom een kolom met variabele lengte te maken.

In de volgende les leert u meer over intelligente prestaties in Azure SQL.

Doorgaan