Oefening: prestaties bewaken en problemen oplossen

  • 10 minuten

In deze oefening leert u hoe u een prestatieprobleem met Azure SQL bewaakt en oplost met behulp van vertrouwde en nieuwe hulpprogramma's en mogelijkheden.

Instellen: Scripts gebruiken om Azure SQL Database te implementeren

Met de terminalsessie aan de rechterkant, Azure Cloud Shell, kunt u met behulp van een browser communiceren met Azure. Voor deze oefening voert u een script uit om uw omgeving te maken, een exemplaar van Azure SQL Database met de AdventureWorks database. (De kleinere, eenvoudigere voorbeelddatabase AdventureWorksLT wordt gebruikt, maar we noemen deze AdventureWorks om verwarring te voorkomen.) In het script wordt u gevraagd om een wachtwoord en uw lokale IP-adres om uw apparaat in staat te stellen verbinding te maken met de database.

De uitvoering van dit script duurt ongeveer 3-5 minuten. Vergeet niet om uw wachtwoord, unieke id en regio te noteren. Deze worden niet meer weergegeven.

  1. Verkrijg eerst uw lokale IP-adres. Zorg ervoor dat u geen verbinding hebt met een VPN-service en open een lokale PowerShell-terminal op uw apparaat. Voer de volgende opdracht uit en noteer het resulterende IP-adres:

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

  2. Voer in Azure Cloud Shell aan de rechterkant de volgende code in en geef een complex wachtwoord en uw lokale openbare IP-adres op dat u in de vorige stap hebt opgehaald. Druk op Enter om de laatste regel van het script uit te voeren.

    $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. Voer het volgende script uit in Azure Cloud Shell. Sla de uitvoer op; U hebt deze informatie nodig in de module. Druk op Enter nadat u de code hebt geplakt, zodat met de laatste regel code de uitvoer wordt afgedrukt die u nodig hebt.

    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

    Tip

    Sla de uitvoer op en noteer uw wachtwoord, unieke id en server. U hebt deze items in de module nodig.

  4. Voer het volgende script uit om een instantie van Azure SQL Database en een logische server te implementeren met het AdventureWorks-voorbeeld. Met dit script voegt u uw IP-adres toe als firewallregel, schakelt u Advanced Data Security in en maakt u een opslagaccount voor gebruik in de resterende oefeningen in deze module. Het uitvoeren van het script kan enkele minuten duren en wordt meerdere keren onderbroken. Wacht op een opdrachtprompt.

    # 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. Open SQL Server Management Studio (SSMS) op uw lokale apparaat om een nieuwe verbinding met uw logische server te maken.

  6. Geef in het dialoogvenster Verbinding maken voor serveraanmelding de volgende informatie op:

    Veld Waarde
    Servertype Database-engine (standaard).
    Servernaam De $serverName die is geretourneerd in de Cloud Shell, plus de rest van de URI. Bijvoorbeeld: aw-server<unique ID>.database.windows.net.
    Verificatie SQL Server-verificatie (standaard).
    Aanmelden cloudadmin De adminSqlLogin die is toegewezen in stap 1 van deze oefening.
    Password Het wachtwoord dat u hebt opgegeven in stap 1 van deze oefening.
    Wachtwoord onthouden geselecteerd

  7. Selecteer Verbinding maken.

    Screenshot of connection dialog for SQL Database in SSMS.

    Notitie

    Afhankelijk van uw lokale configuratie (bijvoorbeeld VPN) kan het IP-adres van de client afwijken van het IP-adres dat door Azure Portal tijdens de implementatie is gebruikt. Als dit het geval is, krijgt u het volgende bericht: 'Uw client-IP-adres heeft geen toegang tot de server. Meld u aan bij een Azure-account en maak een nieuwe firewallregel om toegang in te schakelen." Als u dit bericht krijgt, meldt u zich aan met het account dat u gebruikt voor de sandbox en voegt u een firewallregel toe voor uw client-IP-adres. U kunt al deze stappen voltooien met behulp van de wizard in SSMS.

Bereid de oefening voor door scripts te laden en te bewerken

U vindt alle scripts voor deze oefening in de map 04 Performance\monitor_and_scale in de GitHub-opslagplaats die u hebt gekloond of het zip-bestand dat u hebt gedownload. Laten we de oefening voorbereiden door scripts te laden en te bewerken.

  1. Vouw in SSMS in Objectverkenner de map Databases uit en selecteer de AdventureWorks-database.

  2. Selecteer Bestand>openen> en open het script dmexecrequests.sql. 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;

  3. Gebruik dezelfde methode in SSMS om het script dmdbresourcestats.SQL te laden. Een nieuw venster van de query-editor moet er als volgt uitzien:

    SELECT * FROM sys.dm_db_resource_stats;

    Met deze DMV wordt het totale resourcegebruik van uw workload bijgehouden op basis van Azure SQL Database. Zo worden CPU, I/O en geheugen bijgehouden.

  4. Open en bewerk het script sqlworkload.cmd (dat het ostress.exe-programma gebruikt).

    • Vervang de unique_id naam die u hebt opgeslagen vanuit het implementatiescript in de servernaam.
    • Vervang het wachtwoord dat u hebt gebruikt voor de aanmelding voor de Azure SQL Database-server voor de -P parameter.
    • Sla het gewijzigde script op.

De workload uitvoeren

In deze taak voert u een workload uit in een T-SQL-query om de prestaties te bekijken die gelijktijdige gebruikers simuleren.

  1. Gebruik SSMS om het scriptbestand topcustomersales.sql te openen om de query te observeren. U kunt de query niet uitvoeren vanuit SSMS. Het venster van de query-editor moet er als volgt uitzien:

    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

    Deze database is klein. De query voor het ophalen van een lijst met klanten en de bijbehorende verkoopgegevens, gesorteerd door klanten met de meeste verkopen, mag geen grote resultatenset genereren. U kunt deze query afstemmen door het aantal kolommen in de resultatenset te verminderen, maar deze zijn nodig voor demonstratiedoeleinden van deze oefening.

  2. Voer vanaf een PowerShell-opdrachtprompt de volgende opdracht in om naar de juiste map voor deze oefening te gaan. Vervang <base directory> door uw gebruikers-id en pad voor deze module:

    cd <base directory>\04-Performance\monitor_and_scale

  3. Voer de workload uit met de volgende opdracht:

    .\sqlworkload.cmd

    Dit script maakt gebruik van 10 gelijktijdige gebruikers die de workloadquery twee keer uitvoeren. U ziet dat het script zelf één batch uitvoert, maar een lus van 10.000 keer vormt. Het resultaat wordt ook toegewezen aan een variabele, waardoor bijna alle resultaten van het resultaatverkeer naar de client worden geëlimineerd. Dit is niet nodig, maar helpt bij het weergeven van een 'pure' CPU-workload die volledig op de server wordt uitgevoerd.

    Tip

    Als u het CPU-gebruik van deze workload voor uw omgeving niet ziet, kunt u de -n parameter aanpassen voor het aantal gebruikers en de -r parameter voor iteraties.

    De uitvoer bij de opdrachtprompt moet er ongeveer uitzien als de volgende uitvoer:

    [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...

Prestaties van de workload bekijken

Laten we de DMV-query's gebruiken die u eerder hebt geladen om de prestaties te bekijken.

  1. Voer de query in SSMS uit die u eerder hebt geladen voor het bewaken van dm_exec_requests (dmexecrequests.sql) om actieve aanvragen te bekijken. Voer deze query vijf of zes keer uit en bekijk enkele resultaten:

    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;

    Als het goed is, ziet u dat veel van de aanvragen een status hebben van RUNNABLE, en last_wait_type dat is SOS_SCHEDULER_YIELD. Een indicator van veel RUNNABLE aanvragen en veel SOS_SCHEDULER_YIELD wachttijden is een mogelijk gebrek aan CPU-resources voor actieve query's.

    Notitie

    Mogelijk ziet u een of meer actieve aanvragen met een opdracht van SELECT en een wait_type van XE_LIVE_TARGET_TVF. Dit zijn query's die worden uitgevoerd door services die worden beheerd door Microsoft. Ze helpen met mogelijkheden zoals prestatie-inzichten, met behulp van uitgebreide gebeurtenissen. Microsoft publiceert niet de details van deze sessies.

    Laat dit venster van de query-editor open. U voert deze opnieuw uit in de volgende oefening.

  2. Voer de query in SSMS uit die u eerder hebt geladen voor het bewaken van sys.dm_db_resource_stats (dmdbresourcestats.sql). Voer de query uit om de resultaten van deze DMV drie of vier keer weer te geven.

    SELECT * FROM sys.dm_db_resource_stats;

    Dit DMV legt een momentopname van het resourcegebruik om de 15 seconden vast voor de database (gedurende één uur). Als het goed is, is het kolom avg_cpu_percent bijna 100% nauwkeurig voor een aantal momentopnamen. Dit is een symptoom van een workload die de uiterste limieten van de CPU-resources voor de database vergt.

    Voor een on-premises SQL Server-omgeving gebruikt u doorgaans een hulpprogramma dat specifiek is voor het besturingssysteem om het algehele resourcegebruik van een dergelijke CPU bij te houden. U kunt hiervoor bijvoorbeeld Windows-prestatiemeter gebruiken. Als u dit voorbeeld hebt uitgevoerd op een on-premises SQL Server of SQL Server op een virtuele machine met twee CPU's, ziet u bijna 100 procent CPU-gebruik op de server.

    Notitie

    U kunt een andere DMV uitvoeren, sys.resource_statsin de context van de database van de Azure SQL Database-server master om het resourcegebruik te bekijken voor alle Azure SQL Database-databases die zijn gekoppeld aan de server. Deze weergave is minder gedetailleerd en toont elke vijf minuten resourcegebruik (14 dagen bewaard).

    Laat dit venster van de query-editor open. U voert deze opnieuw uit in de volgende oefening.

  3. Zorg dat de workload voltooid is en noteer de totale duur. Wanneer de workload is voltooid, ziet u resultaten zoals de volgende uitvoer en gaat u terug naar de opdrachtprompt:

    [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

    De duur kan variëren, maar dit duurt meestal ten minste 1-3 minuten. Zorg ervoor dat deze wordt uitgevoerd tot voltooiing. Wanneer de workload is voltooid, keert u terug naar de opdrachtprompt.

De Query Store gebruiken voor verdere analyse

Query Store is een functie in SQL Server voor het volgen van prestatie-uitvoeringbewerking van query's. Prestatiegegevens worden opgeslagen in de gebruikersdatabase. Query Store is niet standaard ingeschakeld voor databases die zijn gemaakt in SQL Server, maar is standaard voor Azure SQL Database (en Azure SQL Managed Instance).

Query Store bevat een reekscatalogusweergaven van het systeem om prestatiegegevens weer te geven. SSMS biedt rapporten met behulp van deze weergaven.

  1. Open met behulp van de Objectverkenner in SSMS de map van Query Store om het rapport te doorzoeken op Query's die de meeste resources verbruiken.

    Screenshot of the Query Store.

  2. Selecteer het rapport om erachter te komen welke query's de belangrijkste resources hebben verbruikt en uitvoeringsdetails van deze query's. Op basis van de workload die op dit punt wordt uitgevoerd, moet uw rapport er ongeveer als volgt uitzien:

    Screenshot of the top query report.

    De weergegeven query is de SQL-query van de workload voor verkoop aan klanten. Dit rapport heeft drie onderdelen: query's met de hoge totale duur (u kunt de metrische gegevens wijzigen), de bijbehorende query-planning en runtime-statistieken en het bijbehorende queryplan in een visuele kaart.

  3. Selecteer het staafdiagram voor de query (de query_id kan afwijken voor uw systeem). Uw resultaten moeten eruitzien als de volgende afbeelding:

    Screenshot of the query ID.

    U kunt de totale duur van de query en querytekst bekijken.

  4. Rechts van dit staafdiagram is een grafiek voor statistieken voor het queryplan dat is gekoppeld aan de query. Beweeg de muisaanwijzer over de punt die aan het abonnement is gekoppeld. Uw resultaten moeten eruitzien als de volgende afbeelding:

    Screenshot of slow query statistics.

    Let op de gemiddelde duur van de query. Uw tijden kunnen variëren, maar vergelijk deze gemiddelde duur met de gemiddelde wachttijd voor deze query. Later worden er prestatieverbeteringen geïntroduceerd en maakt u deze vergelijking opnieuw om het verschil te bekijken.

  5. Het laatste onderdeel is het visuele queryplan. Het queryplan voor deze query ziet eruit als in de volgende afbeelding:

    Screenshot of the workload query plan.

    Deze databasetabel heeft zo weinig rijen dat er geen plan nodig is. het kan inefficiënt zijn. Het afstemmen van de query verbetert de prestaties niet met een meetbare hoeveelheid. Mogelijk ziet u een waarschuwing in het plan over een gebrek aan statistieken voor een van de kolommen voor het zoeken naar geclusterde indexen. Dit houdt geen rekening met de algehele prestaties.

  6. Het volgen van het rapport Query's die het meest worden gebruikt in SSMS, is een rapport met de naam Query Wait Statistics. We weten van eerdere diagnostische gegevens dat een groot aantal aanvragen voortdurend een UITVOERBARE status heeft met een CPU van bijna 100%. Query Store bevat rapporten om mogelijke knelpunten in de prestaties te bekijken bij het wachten op resources. Selecteer dit rapport en beweeg de muisaanwijzer over het staafdiagram. Uw resultaten moeten eruitzien als de volgende afbeelding:

    Screenshot of the top wait statistics.

    U kunt zien dat de categorie met de langste wachttijd CPU is (dit is gelijk aan de wait_type SOS_SCHEDULER_YIELD, die kan worden weergegeven in sys.dm_os_wait_stats) en de gemiddelde wachttijd.

  7. Selecteer het CPU-staafdiagram in het rapport. De belangrijkste query, wachtend op CPU, is de query van de workload die u gebruikt.

    Screenshot of the top wait statistics query.

    U ziet dat de gemiddelde wachttijd voor CPU in deze query een hoog percentage van de totale gemiddelde duur voor de query is.

    Gezien het bewijs, zonder query's af te stemmen, vereist onze workload meer CPU-capaciteit dan we hebben geïmplementeerd voor ons exemplaar van Azure SQL Database.

  8. Sluit beide Query Store-rapporten. U gaat dezelfde rapporten in de volgende oefening gebruiken.

Prestaties bekijken met Azure Monitor

Laten we een andere methode gebruiken om het resourcegebruik van onze workload weer te geven. Azure Monitor biedt metrische prestatiegegevens die u op verschillende manieren kunt bekijken, waaronder via Azure Portal.

  1. Open Azure Portal en zoek vervolgens uw exemplaar van de AdventureWorks SQL-database. Selecteer in het deelvenster Overzicht voor de database het tabblad Bewaking . De standaardweergave in het deelvenster Bewaking is Rekengebruik:

    Screenshot of the Azure portal with a slow query.

    In dit voorbeeld is het CPU-percentage bijna 100 procent voor een recent tijdsbereik. Deze grafiek toont het resourcegebruik (CPU en I/O zijn standaardwaarden) in het afgelopen uur en wordt voortdurend vernieuwd. Selecteer de grafiek zodat u deze kunt aanpassen om naar ander resourcegebruik te kijken.

  2. Selecteer In het menu SQL-database de optie Metrische gegevens toevoegen. Een andere manier om de metrische gegevens voor rekengebruik en andere metrische gegevens weer te geven die automatisch worden verzameld door Azure Monitor voor Azure SQL Database, is het gebruik van Metrics Explorer.

    Notitie

    Rekengebruik is een vooraf gedefinieerde weergave van Metrics Explorer. Als u de vervolgkeuzelijst Metrische gegevens selecteert in het venster Metrische gegevens toevoegen, ziet u de volgende resultaten:

    Screenshot of Azure Monitor metrics.

    Zoals wordt weergegeven in de schermopname, zijn er verschillende metrische gegevens die u kunt gebruiken om met Metrics Explorer weer te geven. De standaardweergave van Metrics Explorer is gedurende een periode van 24 uur, met een granulariteit van vijf minuten. De weergave Rekengebruik is het afgelopen uur met een granulariteit van één minuut (die u kunt wijzigen). Als u dezelfde weergave wilt zien, selecteert u het CPU-percentage en wijzigt u de opname gedurende één uur. De granulariteit wordt gewijzigd in één minuut en lijkt op de volgende afbeelding:

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

    De standaardinstelling is een lijndiagram, maar in de weergave Explorer kunt u het grafiektype wijzigen. Metrics Explorer heeft veel opties, waaronder de mogelijkheid om meerdere metrische gegevens in dezelfde grafiek weer te geven.

Azure Monitor-logboeken

In deze oefening hebt u geen Azure Monitor-logboek ingesteld, maar het is een goed idee om te kijken naar hoe een logboek eruitziet voor een CPU-resourcegebruiksscenario. Azure Monitor-logboeken kunnen een veel langere historisch dossier bevatten dan de metrische gegevens van Azure.

Als u Azure Monitor-logboeken met een Log Analytics-werkruimte hebt geconfigureerd, kunt u de volgende Kusto-query gebruiken om dezelfde resultaten van het CPU-gebruik voor de database weer te geven:

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

Uw resultaten moeten eruitzien als de volgende afbeelding:

Screenshot of a query measuring CPU.

Azure Monitor-logboeken hebben een vertraging als de logboekdiagnostiek voor een database voor het eerst wordt geconfigureerd, dus het duurt mogelijk even voordat resultaten worden weergegeven.

In deze oefening hebt u geleerd hoe u een gemeenschappelijk SQL Server-prestatiescenario kunt bekijken en hoe u de details van een mogelijke oplossing kunt bepalen om de prestaties te verbeteren. In de volgende les leert u methoden om de prestaties te versnellen en af te stemmen.