Övning – Övervaka och felsöka prestanda

  • 10 minuter

I den här övningen får du lära dig hur du övervakar och felsöker ett prestandaproblem med Azure SQL med hjälp av välbekanta och nya verktyg och funktioner.

Konfigurera: Använda skript för att distribuera Azure SQL Database

Med terminalsessionen till höger, Azure Cloud Shell, kan du interagera med Azure med hjälp av en webbläsare. I den här övningen AdventureWorks kör du ett skript för att skapa din miljö, en instans av Azure SQL Database med databasen. (Den mindre, enklare exempeldatabasen AdventureWorksLT används, men vi anropar den AdventureWorks för att förhindra förvirring.) I skriptet uppmanas du att ange ett lösenord och din lokala IP-adress så att enheten kan ansluta till databasen.

Det tar 3–5 minuter att köra skriptet. Glöm inte att anteckna lösenordet, det unika ID:t och regionen. De visas inte igen.

  1. Börja med att hämta din lokala IP-adress. Kontrollera att du inte är ansluten till någon VPN-tjänst och öppna en lokal PowerShell-terminal på enheten. Kör följande kommando och notera den resulterande IP-adressen:

    (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content

  2. I Azure Cloud Shell till höger anger du följande kod, och när du uppmanas till det anger du ett komplext lösenord och din lokala offentliga IP-adress som du hämtade i föregående steg. Tryck på Retur för att köra den sista raden i skriptet.

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local ip address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address':"
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>Sandbox resource group name</rgn>"
$resourceGroupName = "<rgn>Sandbox resource group name</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
$serverName = "aw-server$($uniqueID)"

  3. Kör följande skript i Azure Cloud Shell. Spara utdata. du behöver den här informationen i hela modulen. Tryck på Retur när du har klistrat in koden, så den sista kodraden skriver ut utdata som du behöver.

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    Dricks

    Spara utdata och anteckna ditt lösenord, unika ID och server. Du behöver dessa objekt i hela modulen.

  4. Kör följande skript för att distribuera en Azure SQL Database-instans och en logisk server med exempeldatabasen AdventureWorks. Det här skriptet lägger till din IP-adress som en brandväggsregel, aktiverar Advanced Data Security och skapar ett lagringskonto för användning i de återstående övningarna i den här modulen. Skriptet kan ta flera minuter att slutföra och pausa flera gånger. Vänta på en kommandotolk.

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress 
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Advanced Data Security
$advancedDataSecurity = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a Storage Account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. På den lokala enheten öppnar du SQL Server Management Studio (SSMS) för att skapa en ny anslutning till den logiska servern.

  6. Ange följande information i dialogrutan Anslut till serverinloggning:

    Fält Värde
    Servertyp Databasmotor (standard).
    Servernamn Den $serverName som returnerades i Cloud Shell, plus resten av URI:n. Till exempel aw-server<unique ID>.database.windows.net.
    Autentisering SQL Server-autentisering (standard).
    Inloggning cloudadmin AdministratörenSqlLogin som tilldelades i steg 1 i den här övningen.
    Password Lösenordet som du angav i steg 1 i den här övningen.
    Kom ihåg lösenordet markerad

  7. Välj Anslut.

    Screenshot of connection dialog for SQL Database in SSMS.

    Kommentar

    Beroende på den lokala konfigurationen (till exempel VPN) kan klientens IP-adress skilja sig från IP-adressen som användes till distributionen i Azure-portalen. Om det gör det får du följande meddelande: "Klientens IP-adress har inte åtkomst till servern. Logga in på ett Azure-konto och skapa en ny brandväggsregel för att aktivera åtkomst." Om du får det här meddelandet loggar du in med det konto som du använder för sandbox-miljön och lägger till en brandväggsregel för klientens IP-adress. I SSMS kan du utföra alla de här stegen i guiden.

Förbereda övningen genom att läsa in och redigera skript

Du hittar alla skript för den här övningen i mappen 04-Performance\monitor_and_scale på GitHub-lagringsplatsen som du klonade eller zip-filen som du laddade ned. Nu förbereder vi övningen genom att läsa in och redigera skript.

  1. I SSMS expanderar du mappen Databaser i Object Explorer och väljer databasen AdventureWorks.

  2. Välj Öppna fil>> och öppna skriptet dmexecrequests.sql. 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;

  3. Använd samma metod i SSMS för att läsa in skriptet dmdbresourcestats.sql. Texten i ett nytt fönster för frågeredigeraren borde se ut så här:

    SELECT * FROM sys.dm_db_resource_stats;

    Den här DMV:n spårar den övergripande resursanvändningen för arbetsbelastningen mot Azure SQL Database. Till exempel spåras CPU, I/O och minnesanvändning.

  4. Öppna och redigera skriptet sqlworkload.cmd (som använder programmet ostress.exe).

    • Ersätt det unique_id du sparade från distributionsskriptet i servernamnet.
    • Ersätt lösenordet som du använde för inloggningen för Azure SQL Database-servern för -P parameter.
    • Spara ändringarna i skriptet.

Köra arbetsbelastningen

I den här uppgiften kör du en arbetsbelastning i en T-SQL-fråga för att se dess prestanda som simulerar samtidiga användare.

  1. Använd SSMS för att öppna skriptfilen topcustomersales.sql för att observera frågan. Du kör inte frågan från SSMS. Texten i frågeredigerarens fönster borde se ut så här:

    DECLARE @x int
DECLARE @y float
SET @x = 0;
WHILE (@x < 10000)
BEGIN
SELECT @y = sum(cast((soh.SubTotal*soh.TaxAmt*soh.TotalDue) as float))
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product p
ON p.ProductID = sod.ProductID
GROUP BY c.CompanyName
ORDER BY c.CompanyName;
SET @x = @x + 1;
END
GO

    Den här databasen är liten. Frågan för att hämta en lista över kunder och deras tillhörande försäljningsinformation, som sorteras efter kunder med flest försäljningar, bör inte generera en stor resultatuppsättning. Du kan finjustera den här frågan genom att minska antalet kolumner i resultatuppsättningen, men de behövs i demonstrationssyfte i den här övningen.

  2. Från en PowerShell-kommandotolk anger du följande kommando för att gå till rätt katalog för den här övningen. Ersätt <base directory> med ditt användar-ID och sökväg för den här modulen:

    cd <base directory>\04-Performance\monitor_and_scale

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

    .\sqlworkload.cmd

    Det här skriptet använder 10 samtidiga användare som kör arbetsbelastningsfrågan två gånger. Lägg märke till att skriptet kör en enda batch, men upprepar den 10 000 gånger. Resultatet tilldelas också till en variabel, vilket eliminerar nästan all resultatuppsättningstrafik till klienten. Detta är inte nödvändigt, men hjälper till att visa en "ren" CPU-arbetsbelastning som körs helt på servern.

    Dricks

    Om du inte ser CPU-användningsbeteendet med den här arbetsbelastningen i din miljö kan du justera parametern -r parameter för antalet användare och parametern -n parameter för antalet iterationer.

    Utdata i kommandotolken bör se ut ungefär som följande utdata:

    [datetime] [ostress PID] Max threads setting: 10000
[datetime] [ostress PID] Arguments:
[datetime] [ostress PID] -S[server].database.windows.net
[datetime] [ostress PID] -isqlquery.sql
[datetime] [ostress PID] -U[user]
[datetime] [ostress PID] -dAdventureWorks
[datetime] [ostress PID] -P********
[datetime] [ostress PID] -n10
[datetime] [ostress PID] -r2
[datetime] [ostress PID] -q
[datetime] [ostress PID] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F
[datetime] [ostress PID] Default driver: SQL Server Native Client 11.0
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_1.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_2.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_3.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_4.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_5.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_6.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_7.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_8.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_9.out]
[datetime] [ostress PID] Starting query execution...
[datetime] [ostress PID]  BETA: Custom CLR Expression support enabled.
[datetime] [ostress PID] Creating 10 thread(s) to process queries
[datetime] [ostress PID] Worker threads created, beginning execution...

Observera arbetsbelastningens prestanda

Nu ska vi använda DMV-frågorna som du läste in tidigare för att observera prestanda.

  1. Kör frågan i SSMS som du läste in tidigare för att övervaka dm_exec_requests (dmexecrequests.sql) och granska aktiva förfrågningar. Kör den här frågan fem eller sex gånger och observera resultatet:

    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;

    Du bör se att många av begärandena har statusen RUNNABLE, och last_wait_type är SOS_SCHEDULER_YIELD. En indikator på många RUNNABLE begäranden och många SOS_SCHEDULER_YIELD väntetider är en möjlig brist på CPU-resurser för aktiva frågor.

    Kommentar

    Du kan se en eller flera aktiva begäranden med kommandot SELECT och en wait_type av XE_LIVE_TARGET_TVF. De frågorna körs av tjänster som hanteras av Microsoft. De hjälper till med funktioner som prestandainsikter genom att använda utökade händelser. Microsoft publicerar ingen information om dessa sessioner.

    Lämna redigeringsfönstret öppet. Du använder det igen i nästa övning.

  2. Kör frågan i SSMS som du läste in tidigare för att övervaka sys.dm_db_resource_stats (dmdbresourcestats.sql). Kör frågan och se resultatet av denna DMV tre eller fyra gånger.

    SELECT * FROM sys.dm_db_resource_stats;

    Den här DMV:n registrerar en ögonblicksbild av resursanvändningen för databasen var 15:e sekund (behålls i 1 timme). Kolumnen avg_cpu_percent bör visa nära 100 procent för flera av ögonblicksbilderna. Det här är ett symtom på en arbetsbelastning som tänjer på gränserna för databasens CPU-resurser.

    För en lokal SQL Server-miljö använder du vanligtvis ett verktyg som är specifikt för operativsystemet för att spåra den övergripande resursanvändningen, till exempel en processor. Du kan till exempel använda Prestandaövervakaren i Windows till det här. Om du körde det här exemplet på en lokal SQL Server eller SQL Server på en virtuell dator med två processorer skulle du se nästan 100 procent processoranvändning på servern.

    Kommentar

    Du kan köra en annan DMV, sys.resource_stats, i kontexten för Azure SQL Database-serverns master databas för att se resursanvändning för alla Azure SQL Database-databaser som är associerade med servern. Den här vyn är mindre detaljerad och visar resursanvändning var femte minut (sparas i 14 dagar).

    Lämna redigeringsfönstret öppet. Du använder det igen i nästa övning.

  3. Låt arbetsbelastningen slutföras och anteckna den totala varaktigheten. När arbetsbelastningen har slutförts bör du se resultat som liknar följande utdata och en återgång till kommandotolken:

    [datetime] [ostress PID] Total IO waits: 0, Total IO wait time: 0 (ms)
[datetime] [ostress PID] OSTRESS exiting normally, elapsed time: 00:01:22.637

    Tidsåtgången kan variera, men det tar vanligtvis minst 1–3 minuter. Se till att du låter den köras till slut. När arbetsbelastningen är klar returneras du till kommandotolken.

Använda Query Store för ytterligare analys

Query Store är en funktion i SQL Server för att spåra prestandakörning av frågor. Prestandadata lagras i användardatabasen. Query Store är inte aktiverat som standard för databaser som skapats i SQL Server, men det är aktiverat som standard för Azure SQL Database (och Azure SQL Managed Instance).

Query Store innehåller en serie systemkatalogvyer för att visa prestandadata. SSMS genererar rapporter med hjälp av de här vyerna.

  1. Använd Object Explorer i SSMS och öppna mappen Query Store. Leta upp rapporten för de mest resurskrävande frågorna (Top Resource Consuming Queries).

    Screenshot of the Query Store.

  2. Välj rapporten för att ta reda på vilka frågor som har förbrukat mest resurser i genomsnitt och körningsinformation för frågorna. Baserat på arbetsbelastningskörningen hittills ska rapporten se ut ungefär som på följande bild:

    Screenshot of the top query report.

    Frågan som visas är SQL-frågan från arbetsbelastningen för kundförsäljning. Den här rapporten har tre komponenter: frågor med hög total varaktighet (du kan ändra måttet), tillhörande frågeplan och körningsstatistik samt tillhörande frågeplan på en visuell karta.

  3. Välj stapeldiagrammet för frågan (query_id kan ha ett annat värde i ditt system). Resultatet bör se ut som på följande bild:

    Screenshot of the query ID.

    Den totala varaktigheten för frågan och frågetexten visas.

  4. Till höger om det här stapeldiagrammet visas ett statistikdiagram för frågeplanen som hör till frågan. Hovra över den punkt som är kopplad till planen. Resultatet bör se ut som på följande bild:

    Screenshot of slow query statistics.

    Ta en titt på frågans genomsnittliga varaktighet. Tiderna kan variera, men jämför den genomsnittliga varaktigheten med väntetiden för den här frågan. Senare ska vi införa en prestandaförbättring, och då ska du jämföra det här igen för att se skillnaden.

  5. Den sista komponenten är den visuella frågeplanen. Frågeplanen för den här frågan ser ut som på följande bild:

    Screenshot of the workload query plan.

    Den här databastabellen har så få rader att den inte behöver någon plan. det kan vara ineffektivt. Om du justerar frågan förbättras inte prestandan med en mätbar mängd. Du kan se en varning i planen om brist på statistik för en av kolumnerna för den klustrade indexsökningen. Det här påverkar inte övergripande prestanda.

  6. Följande rapport om resurskrävande frågor i SSMS är en rapport med namnet Frågeväntestatistik. Vi vet från tidigare diagnostik att ett stort antal förfrågningar ständigt hade statusen RUNNABLE, och att processoranvändningen nästan var 100 procent. Query Store innehåller rapporter så att du kan undersöka eventuella flaskhalsar som orsakas av väntan på resurser. Välj den här rapporten och hovra över stapeldiagrammet. Resultatet bör se ut som på följande bild:

    Screenshot of the top wait statistics.

    Du ser att den vanligaste väntekategorin är CPU (det här motsvarar wait_type SOS_SCHEDULER_YIELD som du kan se i sys.dm_os_wait_stats) och den genomsnittliga väntetiden.

  7. Välj CPU-stapeldiagrammet i rapporten. Den översta frågan, som väntar på CPU, är frågan från den arbetsbelastning som du använder.

    Screenshot of the top wait statistics query.

    Observera att den genomsnittliga väntetiden för CPU i den här frågan är en hög procentandel av den totala genomsnittliga varaktigheten för frågan.

    Med tanke på bevisen, utan någon frågejustering, kräver vår arbetsbelastning mer CPU-kapacitet än vi har distribuerat för vår instans av Azure SQL Database.

  8. Stäng båda Query Store-rapporterna. Du använder samma rapporter i nästa övning.

Observera prestanda med Azure Monitor

Nu ska vi använda en annan metod för att visa resursanvändningen för vår arbetsbelastning. Azure Monitor tillhandahåller prestandamått som du kan visa på olika sätt, bland annat via Azure-portalen.

  1. Öppna Azure-portalen och leta upp din instans av AdventureWorks SQL-databasen. I fönstret Översikt för databasen väljer du fliken Övervakning . Standardvyn i fönstret Övervakning är Beräkningsanvändning:

    Screenshot of the Azure portal with a slow query.

    I det här exemplet är CPU-procentandelen nära 100 procent för ett nytt tidsintervall. Det här diagrammet visar resursanvändning (CPU och I/O är standardvärden) under den senaste timmen och uppdateras kontinuerligt. Välj diagrammet så att du kan anpassa det för att titta på annan resursanvändning.

  2. På SQL-databasmenyn väljer du Lägg till mått. Ett annat sätt att visa mått för beräkningsanvändning och andra mått som samlas in automatiskt av Azure Monitor för Azure SQL Database är att använda Metrics Explorer.

    Kommentar

    Beräkningsanvändning är en fördefinierad vy av Metrics Explorer. Om du väljer listrutan Mått i fönstret Lägg till mått visas följande resultat:

    Screenshot of Azure Monitor metrics.

    Som du ser i skärmbilden finns det flera mått som du kan använda för att visa med Metrics Explorer. Standardvyn Metrics Explorer är för en 24-timmarsperiod med en kornighet på fem minuter. Vyn Beräkningsanvändning är den senaste timmen med en minuts kornighet (som du kan ändra). Om du vill se samma vy väljer du CPU-procent och ändrar avbildningen i en timme. Kornigheten ändras till en minut och bör se ut som på följande bild:

    Screenshot of Azure Monitor metrics, including CPU after 1 minute.

    Som standard visas ett linjediagram, men du kan ändra diagramtyp i Explorer-vyn. Metrics Explorer har många alternativ, inklusive möjligheten att visa flera mått i samma diagram.

Azure Monitor-loggar

I den här övningen har du inte konfigurerat någon Azure Monitor-logg, men det är nyttigt att titta på hur en logg kan se ut i ett scenario för processoranvändning. Azure Monitor-loggar kan innehålla mycket mer historik än Azure-mått.

Om du har konfigurerat Azure Monitor-loggar med en Log Analytics-arbetsyta kan du använda följande Kusto-fråga för att visa samma cpu-användningsresultat för databasen:

AzureMetrics
| where MetricName == 'cpu_percent'
| where Resource == "ADVENTUREWORKS"
| project TimeGenerated, Average
| render columnchart

Resultatet skulle se ut som på följande bild:

Screenshot of a query measuring CPU.

Azure Monitor-loggar har en fördröjning när du först konfigurerar loggdiagnostik för en databas, så det kan ta en stund innan resultaten visas.

I den här övningen har du lärt dig att granska ett vanligt scenario för SQL-prestanda och göra en mer ingående granskning för att fatta beslut om en lösning för att förbättra prestandan. I nästa lektion får du lära dig metoder för att påskynda och finjustera prestanda.