Esercizio - Monitorare e risolvere i problemi di prestazioni

  • 10 minuti

In questo esercizio si apprenderà come monitorare e risolvere un problema di prestazioni con Azure SQL usando strumenti e funzionalità familiari e nuovi.

Configurazione: usare gli script per distribuire il database SQL di Azure

La sessione di terminale a destra, Azure Cloud Shell, consente di interagire con Azure usando un browser. Per questo esercizio si eseguirà uno script per creare l'ambiente, un'istanza di database SQL di Azure con il AdventureWorks database. Il database di esempio AdventureWorksLT più piccolo e semplice viene usato, ma verrà AdventureWorks chiamato per evitare confusione. Nello script viene richiesto di immettere una password e l'indirizzo IP locale per consentire al dispositivo di connettersi al database.

Il completamento di questo script richiede 3-5 minuti. Assicurarsi di annotare la password, l'ID univoco e l'area. Queste informazioni non verranno visualizzate di nuovo.

  1. Per iniziare, ottenere l'indirizzo IP locale. Assicurarsi di essere disconnessi da qualsiasi servizio VPN e aprire un terminale di PowerShell locale nel dispositivo. Eseguire il comando seguente e prendere nota dell'indirizzo IP risultante:

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

  2. In Azure Cloud Shell a destra immettere il codice seguente e, quando richiesto, specificare una password complessa e l'indirizzo IP pubblico locale recuperato nel passaggio precedente. Premere INVIO per eseguire l'ultima riga dello script.

    $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. Eseguire lo script seguente in Azure Cloud Shell. Salvare l'output. Queste informazioni saranno necessarie nel corso del modulo. Premere INVIO dopo aver incollato il codice affinché l'ultima riga di codice stampi l'output necessario.

    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

    Suggerimento

    Salvare l'output e annotare la password, l'ID univoco e il server. Questi elementi saranno necessari in tutto il modulo.

  4. Eseguire lo script seguente per distribuire un'istanza del database SQL di Azure e un server logico con l'esempio AdventureWorks. Questo script aggiunge l'indirizzo IP come regola del firewall, abilita Sicurezza dei dati avanzata e crea un account di archiviazione da usare negli esercizi rimanenti di questo modulo. Il completamento dello script può richiedere diversi minuti e verrà sospeso più volte. Attendere un prompt dei comandi.

    # 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. Nel dispositivo locale aprire SQL Server Management Studio (SSMS) e creare una nuova connessione al server logico.

  6. Nella finestra di dialogo Connetti al server specificare le informazioni seguenti:

    Campo Valore
    Tipo di server Motore di database (impostazione predefinita).
    Nome server Il valore di $serverName restituito in Cloud Shell, oltre al resto dell'URI. Ad esempio, aw-server<unique ID>.database.windows.net.
    Authentication Autenticazione di SQL Server (impostazione predefinita).
    Account di accesso cloudadmin: valore di adminSqlLogin assegnato nel passaggio 1 di questo esercizio.
    Password Password specificata nel passaggio 1 di questo esercizio.
    Memorizza password checked

  7. Selezionare Connetti.

    Screenshot of connection dialog for SQL Database in SSMS.

    Nota

    A seconda della configurazione locale, ad esempio della VPN, l'indirizzo IP client può essere diverso dall'indirizzo IP usato dal portale di Azure durante la distribuzione. In caso affermativo, verrà visualizzato il messaggio seguente: "L'indirizzo IP client non ha accesso al server. Accedere a un account Azure e creare una nuova regola del firewall per abilitare l'accesso." Se viene visualizzato questo messaggio, accedere usando l'account usato per la sandbox e aggiungere una regola del firewall per l'indirizzo IP del client. È possibile completare tutti questi passaggi usando la procedura guidata in SSMS.

Preparare l'esercizio caricando e modificando gli script

È possibile trovare tutti gli script per questo esercizio nella cartella 04-Performance\monitor_and_scale nel repository GitHub clonato o nel file ZIP scaricato. Per preparare l'esercizio, è necessario caricare e modificare gli script.

  1. In SSMS, in Esplora oggetti, espandere la cartella Database e selezionare il database AdventureWorks.

  2. Selezionare File>apri>file e aprire lo script dmexecrequests.sql. La finestra dell'editor di query avrà un aspetto simile al testo seguente:

    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. Usare lo stesso metodo in SSMS per caricare lo script dmdbresourcestats.sql. Una nuova finestra dell'editor di query avrà un aspetto simile al testo seguente:

    SELECT * FROM sys.dm_db_resource_stats;

    Questa DMV terrà traccia dell'utilizzo complessivo delle risorse del carico di lavoro nel database SQL di Azure. Verranno ad esempio monitorati CPU, I/O e memoria.

  4. Aprire e modificare lo script sqlworkload.cmd (che userà il programma ostress.exe).

    • Sostituire il valore di unique_id salvato dallo script di distribuzione nel nome del server.
    • Sostituire la password per l'accesso per il server del database SQL di Azure per -P parameter.
    • Salvare le modifiche allo script.

Eseguire il carico di lavoro

In questa attività si eseguirà un carico di lavoro in una query T-SQL per osservare le prestazioni simulando gli utenti simultanei.

  1. Usare SSMS per aprire il file di script topcustomersales.sql per osservare la query. La query non verrà eseguita da SSMS. La finestra dell'editor di query avrà un aspetto simile al testo seguente:

    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

    Questo database è piccolo. La query per recuperare un elenco di clienti e le informazioni di vendita associate, ordinate in base ai clienti con le vendite maggiori, non genererà un set di risultati di grandi dimensioni. È possibile ottimizzare questa query riducendo il numero di colonne nel set di risultati, ma sono necessarie a scopo dimostrativo di questo esercizio.

  2. Da un prompt dei comandi di PowerShell immettere il comando seguente per passare alla directory corretta per questo esercizio. Sostituire <base directory> con l'ID utente e il percorso per questo modulo:

    cd <base directory>\04-Performance\monitor_and_scale

  3. Eseguire il carico di lavoro con il comando seguente:

    .\sqlworkload.cmd

    Questo script userà 10 utenti simultanei che eseguono la query del carico di lavoro due volte. Si noti che lo script esegue un singolo batch, ma esegue il ciclo 10.000 volte. Ha inoltre assegnato il risultato a una variabile, eliminando così quasi tutto il traffico del set di risultati verso il client. Questa operazione non è necessaria, ma consente di mostrare un carico di lavoro cpu "puro" eseguito interamente sul server.

    Suggerimento

    Se non è possibile vedere il comportamento di utilizzo della CPU con questo carico di lavoro per l'ambiente, modificare -n parameter per il numero di utenti e -r parameter per le iterazioni.

    L'output al prompt dei comandi dovrebbe essere simile a quello seguente:

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

Osservare le prestazioni del carico di lavoro

A questo punto, è possibile usare le query DMV caricate in precedenza per osservare le prestazioni.

  1. Eseguire in SSMS la query caricata in precedenza per monitorare dm_exec_requests (dmexecrequests.sql) al fine di osservare le richieste attive. Eseguire questa query cinque o sei volte e osservare alcuni risultati:

    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;

    Si noterà che molte delle richieste hanno lo stato RUNNABLEe last_wait_type è SOS_SCHEDULER_YIELD. Un indicatore di molte RUNNABLE richieste e molte SOS_SCHEDULER_YIELD attese è una possibile mancanza di risorse DELLA CPU per le query attive.

    Nota

    È possibile che vengano visualizzate una o più richieste attive con un comando di SELECT e un wait_type di XE_LIVE_TARGET_TVF. Si tratta di query eseguite da servizi gestiti da Microsoft. Sono utili per funzionalità come le informazioni dettagliate sulle prestazioni usando gli eventi estesi. Microsoft non pubblica i dettagli di queste sessioni.

    Lasciare aperta questa finestra dell'editor di query. Verrà eseguita di nuovo nell'esercizio successivo.

  2. Eseguire in SSMS la query caricata in precedenza per monitorare sys.dm_db_resource_stats (dmdbresourcestats.sql). Eseguire la query per visualizzare i risultati di questa DMV tre o quattro volte.

    SELECT * FROM sys.dm_db_resource_stats;

    Questa DMV registra uno snapshot dell'utilizzo delle risorse per il database ogni 15 secondi (gli snapshot vengono conservati per 1 ora). È possibile osservare che la colonna avg_cpu_percent ha un valore vicino al 100% per diversi snapshot. Si tratta di un sintomo di un carico di lavoro che spinge i limiti delle risorse della CPU per il database.

    Per un ambiente locale di SQL Server, in genere si usa uno strumento specifico del sistema operativo per tenere traccia dell'utilizzo complessivo delle risorse di tale CPU. A questo scopo, è ad esempio possibile usare Performance Monitor di Windows. Se questo esempio è stato eseguito in un'istanza locale di SQL Server o SQL Server in una macchina virtuale con due CPU, si noterà un utilizzo della CPU quasi del 100% nel server.

    Nota

    È possibile eseguire un'altra DMV, sys.resource_stats, nel contesto del database del master server database SQL di Azure per visualizzare l'utilizzo delle risorse per tutti i database database SQL di Azure associati al server. Questa visualizzazione è meno granulare e mostra l'utilizzo delle risorse ogni cinque minuti (mantenuto per 14 giorni).

    Lasciare aperta questa finestra dell'editor di query. Verrà eseguita di nuovo nell'esercizio successivo.

  3. Consentire il completamento del carico di lavoro e prendere nota della sua durata complessiva. Al completamento del carico di lavoro, verranno visualizzati risultati come l'output seguente e si tornerà al prompt dei comandi:

    [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

    La durata può variare, ma questa operazione richiede in genere almeno 1-3 minuti. Assicurarsi di consentire l'esecuzione fino al completamento. Al termine del carico di lavoro, si tornerà al prompt dei comandi.

Usare il Query Store per un'ulteriore analisi

Query Store è una funzionalità di SQL Server per tenere traccia dell'esecuzione delle query sulle prestazioni. I dati sulle prestazioni vengono archiviati nel database utente. La funzionalità Query Store non è abilitata per impostazione predefinita per i database creati in SQL Server, ma è attivata per impostazione predefinita per il database SQL di Azure (e Istanza gestita di SQL di Azure).

Query Store viene fornito con una serie di viste del catalogo di sistema per visualizzare i dati sulle prestazioni. SSMS fornisce report tramite queste viste.

  1. Usando Esplora oggetti in SSMS, aprire la cartella di Query Store per trovare il report per Prime query per consumo di risorse.

    Screenshot of the Query Store.

  2. Selezionare il report per individuare le query che hanno usato la maggior parte delle risorse medie e i dettagli di esecuzione di tali query. In base al carico di lavoro eseguito fino a questo punto, il report dovrebbe avere un aspetto simile all'immagine seguente:

    Screenshot of the top query report.

    La query mostrata è la query SQL dal carico di lavoro per le vendite dei clienti. Questo report ha tre componenti: le query con durata totale elevata (è possibile modificare la metrica), il piano di query associato e le statistiche di runtime, nonché il piano di query associato in una mappa visiva.

  3. Selezionare il grafico a barre per la query (il valore di query_id può essere diverso per il sistema in uso). I risultati dovrebbero essere simili all'immagine seguente:

    Screenshot of the query ID.

    È possibile visualizzare la durata totale della query e il testo della query.

  4. A destra di questo grafico a barre è presente un grafico per le statistiche per il piano di query associato alla query. Passare il puntatore del mouse sul punto associato al piano. I risultati dovrebbero essere simili all'immagine seguente:

    Screenshot of slow query statistics.

    Prendere nota della durata media della query. I tempi possono variare, ma confrontare questa durata media con il tempo medio di attesa per la query. In seguito verrà introdotto un miglioramento delle prestazioni e si eseguirà di nuovo questo confronto per osservare la differenza.

  5. Il componente finale è il piano di query visivo. Il piano di query per questa query è simile all'immagine seguente:

    Screenshot of the workload query plan.

    Questa tabella di database contiene così poche righe che non richiedono un piano; può essere inefficiente. L'ottimizzazione della query non migliorerà le prestazioni in modo significativo. Potrebbe essere visualizzato un avviso nel piano relativo alla mancanza di statistiche per una delle colonne per la ricerca nell'indice cluster. Ciò non influisce sulle prestazioni complessive.

  6. Dopo il report Top Resource Consume Queries in SSMS è riportato un report denominato Query Wait Statistics( Statistiche di attesa query). Dalla diagnostica precedente si è appreso che un numero elevato di richieste si trova costantemente in uno stato RUNNABLE, con quasi il 100% della CPU. Query Store include report per esaminare i possibili colli di bottiglia delle prestazioni dovuti ad attese nelle risorse. Selezionare questo report e passare il puntatore del mouse sul grafico a barre. I risultati dovrebbero essere simili all'immagine seguente:

    Screenshot of the top wait statistics.

    È possibile osservare che la prima categoria di attesa è la CPU (equivalente a wait_type corrispondente a SOS_SCHEDULER_YIELD in sys.dm_os_wait_stats), nonché il tempo medio di attesa.

  7. Selezionare il grafico a barre della CPU nel report. La prima query in attesa della CPU è la query del carico di lavoro in uso.

    Screenshot of the top wait statistics query.

    Si noti che il tempo medio di attesa per la CPU per questa query è una percentuale elevata della durata media complessiva della query.

    Considerando i dati, senza alcuna ottimizzazione della query, il carico di lavoro richiede una capacità della CPU superiore a quella distribuita per l'istanza del database SQL di Azure.

  8. Chiudere entrambi i report di Query Store. Si useranno gli stessi report nell'esercizio successivo.

Osservare le prestazioni con Monitoraggio di Azure

Si userà un altro metodo per visualizzare l'utilizzo delle risorse del carico di lavoro. Monitoraggio di Azure fornisce metriche delle prestazioni che è possibile visualizzare in diversi modi, ad esempio tramite il portale di Azure.

  1. Aprire il portale di Azure e quindi trovare l'istanza del database SQL AdventureWorks. Nel riquadro Panoramica per il database selezionare la scheda Monitoraggio. La visualizzazione predefinita nel riquadro Monitoraggio è Utilizzo calcolo:

    Screenshot of the Azure portal with a slow query.

    In questo esempio la percentuale di CPU è vicina al 100% per un intervallo di tempo recente. Questo grafico mostra l'utilizzo delle risorse (CPU e I/O sono le impostazioni predefinite) nell'ultima ora e viene aggiornato continuamente. Se si seleziona il grafico, è possibile personalizzarlo e osservare l'utilizzo di altre risorse.

  2. Nel menu del database SQL selezionare Aggiungi metriche. Un altro modo per visualizzare le metriche di Utilizzo calcolo e altre metriche raccolte automaticamente da Monitoraggio di Azure per database SQL di Azure consiste nell'usare Esplora metriche.

    Nota

    Utilizzo calcolo è solo una visualizzazione predefinita di Esplora metriche. Se si seleziona l'elenco a discesa Metrica nella finestra Aggiungi metriche , verranno visualizzati i risultati seguenti:

    Screenshot of Azure Monitor metrics.

    Come mostrato nello screenshot, sono disponibili diverse metriche che è possibile usare per la visualizzazione con Esplora metriche. La visualizzazione predefinita esplora metriche è per un periodo di 24 ore, con una granularità di cinque minuti. La visualizzazione Utilizzo calcolo è l'ultima ora con una granularità di un minuto (che è possibile modificare). Per visualizzare la stessa visualizzazione, selezionare Percentuale CPU e modificare l'acquisizione per un'ora. La granularità cambierà in un minuto e dovrebbe essere simile all'immagine seguente:

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

    Il valore predefinito è un grafico a linee, ma la visualizzazione di Explorer consente di modificare il tipo di grafico. Esplora metriche offre numerose opzioni, tra cui la possibilità di visualizzare più metriche nello stesso grafico.

Log di Monitoraggio di Azure

In questo esercizio non è stato configurato un log di Monitoraggio di Azure, ma è opportuno esaminare l'aspetto di un log per uno scenario di utilizzo delle risorse della CPU. I log di Monitoraggio di Azure possono fornire un record cronologico molto più lungo rispetto a Metriche di Azure.

Se i log di Monitoraggio di Azure sono stati configurati con un'area di lavoro Log Analytics, è possibile usare la query Kusto seguente per visualizzare gli stessi risultati di utilizzo della CPU per il database:

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

I risultati dovrebbero essere simili all'immagine seguente:

Screenshot of a query measuring CPU.

I log di Monitoraggio di Azure presentano un ritardo quando si configura per la prima volta la diagnostica dei log per un database, quindi la visualizzazione di questi risultati può richiedere un po' di tempo.

In questo esercizio si è appreso come osservare uno scenario di prestazioni di SQL Server comune e approfondire i dettagli per stabilire una soluzione possibile per migliorare le prestazioni. Nell'unità successiva verranno illustrati i metodi per accelerare e ottimizzare le prestazioni.