Övning – Optimera programprestanda

  • 10 minuter

I den här övningen ser du ett nytt prestandascenario och löser det genom att optimera programmet och frågorna.

Optimera programprestanda med Azure SQL

I vissa fall kan migrering av ett befintligt program och en SQL-frågearbetsbelastning till Azure avslöja nya möjligheter att optimera och finjustera frågorna.

Du behöver lägga till en ny tabell för en omfattande uppsättning samtidig INSERT-aktivitet som stöd för att bygga ut webbplatsen för AdventureWorks-beställningar med ett betygssystem för kunderna. Du har testat SQL-frågearbetsbelastningen på en utvecklingsdator med SQL Server 2022 som har en lokal SSD-enhet för databasen och transaktionsloggen.

När du flyttar testet till Azure SQL Database med nivån Generell användning (8 virtuella kärnor) är INSERT-arbetsbelastningen långsammare. Ska du ändra tjänstmålet eller tjänstnivån för att underlätta den nya arbetsbelastningen, eller ska du titta på programmet?

Du hittar alla skript för den här övningen i mappen 04-Performance\tuning_applications på GitHub-lagringsplatsen som du klonade eller zip-filen som du laddade ned.

Skapa en ny tabell för programmet

Gå till Object Explorer och välj databasen AdventureWorks. Använd Öppna fil>>för att öppna skriptet order_rating_ddl.sql för att skapa en tabell i AdventureWorks databasen. Texten i frågeredigerarens fönster borde se ut så här:

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

Välj Kör för att köra skriptet.

Läsa in frågor för att övervaka frågekörningen

Nu ska vi läsa in några T-SQL-frågor för dynamiska hanteringsvyer (DMV:er) för att se frågeprestanda för aktiva frågor, väntetider och I/O. Läs in alla de här frågorna i kontexten för databasen AdventureWorks.

  1. Gå till Object Explorer och välj databasen AdventureWorks. Använd Öppna fil>>för att öppna skriptet sqlrequests.sql för att titta på aktiva SQL-frågor. Texten i frågeredigerarens fönster borde se ut så här:

    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. Gå till Object Explorer och välj databasen AdventureWorks. Använd Filöppningsfil>> för att öppna skriptet top_waits.sql för att titta på de vanligaste väntetyperna efter antal. Texten i frågeredigerarens fönster borde se ut så här:

    SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;

  3. Gå till Object Explorer och välj databasen AdventureWorks. Använd Öppna fil>>för att öppna skriptet tlog_io.sql för att observera svarstiden för transaktionsloggskrivningar. Texten i frågeredigerarens fönster borde se ut så här:

    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);

Förbereda arbetsbelastningsskriptet för körning

Öppna och redigera order_rating_insert_single.cmd arbetsbelastningsskriptet.

  • Ersätt din unique_id du fick i den första övningen med servernamnet för -S parameter.
  • Ersätt lösenordet som du angav i databasdistributionen från den första övningen -P parameterför .
  • Spara ändringarna i filen.

Köra arbetsbelastningen

  1. I en PowerShell-kommandotolk byter du till katalogen för den här modulaktiviteten:

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

  2. Kör arbetsbelastningen med följande kommando:

    .\order_rating_insert_single.cmd

    I det här skriptet används programmet ostress.exe till att simulera 25 samtidiga användare som kör följande T-SQL-instruktion (i skriptet 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

    Du ser i skriptet att det inte är några verkliga data som kommer från webbplatsen. Det simulerar dock att många betygsomdömen matas in i databasen.

Observera DMV:er och arbetsbelastningsprestanda

Kör nu frågorna i SQL Server Management Studio (SSMS) som du läste in för att observera prestanda tidigare. Kör frågorna för sqlrequests.sql, top_waits.sql och tlog_io.sql.

Med de här frågorna kan du observera följande fakta:

  • Många begäranden har ständigt en wait_type writelog med värdet > 0.
  • Väntetypen WRITELOG är en av de högsta antalen för väntetyper.
  • Den genomsnittliga tiden för att skriva till transaktionsloggen avg_tlog_io_write_ms (kolumnen i tlog_io.sql resultatuppsättning) är någonstans runt 2 ms.

Varaktigheten för den här arbetsbelastningen på en SQL Server 2022-instans med en SSD-enhet är cirka 10–12 sekunder. Den totala varaktigheten för Azure SQL Database med en Gen5 v8-kärna är ungefär 25 sekunder.

WRITELOG väntetyper med högre väntetider tyder på svarstidsspolning till transaktionsloggen. 2 ms per skrivning kanske inte verkar som så mycket, men på en lokal SSD-enhet kan den här svarstiden vara under 1 ms.

Välja en lösning

Problemet är inte en hög andel loggskrivningsaktivitet. Azure Portal och sys.dm_db_resource_stats visar inga tal som är högre än 20–25 procent (du behöver inte köra frågor mot dessa). Problemet gäller inte heller en IOPS-gräns. Problemet är att programarbetsbelastningen är känslig för korta svarstider för skrivning till transaktionsloggen och att nivån Generell användning inte är utformad för den här typen av svarstidskrav. Den förväntade I/O-svarstiden för Azure SQL Database är 5–7 ms.

Kommentar

I dokumentationen för Generell användning i Azure SQL Database uppskattas I/O-svarstiden till i genomsnitt 5–7 (skrivningar) och 5–10 (läsningar). Du kan uppleva svarstider som mer liknar den här nivån. Svarstiden för Generell användning i Azure SQL Managed Instance är liknande. Om ditt program är mycket känsligt för I/O-svarstider kan du överväga nivån Affärskritisk.

Granska T-SQL-skriptet för order_rating_insert_single.sql arbetsbelastning. Var och INSERT en är en enda transaktionsincheckning, vilket kräver en tömning av transaktionsloggar.

En incheckning för varje infogning är inte effektivt, men programmet påverkades inte på den lokala SSD-disken eftersom varje incheckning gick så snabbt. På nivån Affärskritisk (tjänstmål eller SKU) får du lokala SSD-diskar med kortare svarstid. Det är möjligt att det finns en programoptimering, så arbetsbelastningen är inte lika känslig för I/O-svarstid för transaktionsloggen.

Du kan ändra T-SQL-batchen för arbetsbelastningen så att den INSERT omsluter BEGIN TRAN/COMMIT TRAN iterationerna.

Köra en ändrad och effektivare arbetsbelastning

Redigera skripten och kör dem för att se om dina I/O-prestanda förbättras. Du hittar den ändrade arbetsbelastningen i skriptet order_rating_insert.sql .

  1. Förbered arbetsbelastningsskriptet genom att redigera order_rating_insert.cmd för att använda rätt servernamn och lösenord.

  2. Kör den ändrade arbetsbelastningen med hjälp av skriptet order_rating_insert.cmd , ungefär som du körde det tidigare arbetsbelastningsskriptet.

Observera de nya resultaten

  1. Titta på resultatet av T-SQL-skriptet för sqlrequests.sql i SSMS. Nu ser du betydligt färre WRITELOG-väntetider och kortare väntetider överlag.

    Nu körs arbetsbelastningen mycket snabbare jämfört med den föregående körningen. Det här är ett exempel på hur du kan justera ett program för SQL-frågor som körs i eller utanför Azure.

    Kommentar

    Den här arbetsbelastningen kan köras ännu snabbare mot en instans av Azure SQL Database med en omdirigeringsanslutningstyp . Distributionen som du har gjort i den här övningen använder en standardanslutningstyp, som är en proxytyp eftersom du är ansluten utanför Azure. Med omdirigering kan du köra arbetsbelastningar som den här betydligt snabbare med tanke på kommunikationen mellan klienten och servern.

  2. Ta en titt på arbetsbelastningens varaktighet. Arbetsbelastningen körs så snabbt att det kan vara svårt att observera diagnostikdata från frågor som har använts tidigare i den här aktiviteten.

    Begreppet ”batchbearbetning” kan hjälpa de flesta program, även de som är anslutna till Azure SQL.

Dricks

Resursstyrning i Azure kan påverka mycket stora transaktioner och symtomen blir LOG_RATE_GOVERNOR. I det här exemplet fyller char(500)-kolumnen som inte får innehålla null ut en massa tomrum och orsakar stora transaktionsloggposter. Du kan optimera prestanda ännu mer genom att göra om den till en kolumn med variabel längd.

I nästa lektion får du lära dig mer om intelligenta prestanda i Azure SQL.