Cvičení – monitorování a odstraňování potíží s výkonem

  • 10 min

V tomto cvičení se naučíte monitorovat a řešit potíže s výkonem Azure SQL pomocí známých a nových nástrojů a možností.

Nastavení: Nasazení služby Azure SQL Database pomocí skriptů

Relace terminálu napravo, Azure Cloud Shell, umožňuje interakci s Azure pomocí prohlížeče. V tomto cvičení spustíte skript pro vytvoření prostředí, což je instance Azure SQL Database s AdventureWorks databází. (Použijeme menší, jednodušší ukázkovou AdventureWorksLT databázi, ale my ji AdventureWorks zavoláme, abychom zabránili nejasnostem.) Ve skriptu se zobrazí výzva k zadání hesla a místní IP adresy, aby se vaše zařízení připojilo k databázi.

Dokončení tohoto skriptu trvá 3 až 5 minut. Nezapomeňte si poznamenat heslo, jedinečné ID a oblast. Tyto údaje se znovu nezobrazí.

  1. Začněte získáním své místní IP adresy. Ujistěte se, že jste odpojení od jakékoli služby VPN, a na svém zařízení otevřete místní terminál PowerShellu. Spusťte následující příkaz a poznamenejte si výslednou IP adresu:

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

  2. V Azure Cloud Shellu napravo zadejte následující kód a po zobrazení výzvy zadejte složité heslo a místní veřejnou IP adresu, kterou jste získali v předchozím kroku. Stisknutím klávesy Enter spusťte poslední řádek skriptu.

    $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. V Azure Cloud Shellu spusťte následující skript. Uložte výstup; Tyto informace budete potřebovat v celém modulu. Po vložení kódu stiskněte Enter , takže poslední řádek kódu vytiskne požadovaný výstup.

    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

    Uložte výstup a poznamenejte si heslo, jedinečné ID a server. Tyto položky budete potřebovat v celém modulu.

  4. Spuštěním následujícího skriptu nasaďte instanci Azure SQL Database a logický server s ukázkou AdventureWorks. Tento skript přidá vaši IP adresu jako pravidlo brány firewall, povolí Advanced Data Security a vytvoří účet úložiště pro použití ve zbývajících cvičeních v tomto modulu. Dokončení skriptu může trvat několik minut a několikrát se pozastaví. Počkejte na příkazový řádek.

    # 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. Na místním zařízení otevřete SQL Server Management Studio (SSMS) a vytvořte nové připojení k logickému serveru.

  6. V dialogovém okně pro přihlášení k serveru Připojení zadejte následující informace:

    Pole Hodnota
    Typ serveru Databázový stroj (výchozí).
    Název serveru $serverName vrácená v Cloud Shellu a zbytek identifikátoru URI. Například: aw-server<unique ID>.database.windows.net.
    Ověřování Ověřování SQL Serveru (výchozí)
    Přihlášení cloudadmin AdminSqlLogin přiřazený v kroku 1 tohoto cvičení.
    Password Heslo, které jste zadali v kroku 1 tohoto cvičení.
    Zapamatovat heslo checked

  7. Vyberte Připojit.

    Screenshot of connection dialog for SQL Database in SSMS.

    Poznámka:

    Podle toho, jaká je vaše místní konfigurace (například VPN), se vaše IP adresa klienta může lišit od IP adresy, kterou Azure Portal používal během nasazování. Pokud ano, zobrazí se následující zpráva: Vaše IP adresa klienta nemá přístup k serveru. Přihlaste se k účtu Azure a vytvořte nové pravidlo brány firewall pro povolení přístupu. Pokud se zobrazí tato zpráva, přihlaste se pomocí účtu, který používáte pro sandbox, a přidejte pravidlo brány firewall pro vaši IP adresu klienta. Všechny tyto kroky můžete provést v průvodci v SSMS (SQL Server Management Studio).

Příprava cvičení načtením a úpravou skriptů

Všechny skripty pro toto cvičení najdete ve složce 04-Performance\monitor_and_scale v úložišti GitHub, které jste naklonovali, nebo v souboru ZIP, který jste stáhli. Připravíme si cvičení načtením a úpravou skriptů.

  1. V nástroji SSMS v Průzkumník objektů rozbalte složku Databáze a vyberte databázi AdventureWorks.

  2. Vyberte Soubor Otevřít>soubor a otevřete skript dmexecrequests.sql.> V okně editoru dotazů by se měl zobrazit text podobný následujícímu:

    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. V nástroji SQL Server Management Studio stejným způsobem načtěte skript dmdbresourcestats.sql. V novém okně editoru dotazů by se měl zobrazit text podobný následujícímu:

    SELECT * FROM sys.dm_db_resource_stats;

    Toto zobrazení dynamické správy bude trasovat celkové využití prostředků vaší úlohou oproti Azure SQL Database. Sleduje například procesor, vstupně-výstupní operace a paměť.

  4. Otevřete a upravte skript sqlworkload.cmd (který bude používat program ostress.exe).

    • Nahraďte unique_id soubor, který jste uložili ze skriptu nasazení v názvu serveru.
    • Nahraďte heslo, které jste použili pro přihlášení k serveru azure SQL Database pro server -P parameter.
    • Uložte změny skriptu.

Spuštění úlohy

V této úloze spustíte úlohu v dotazu T-SQL, abyste mohli sledovat jeho výkon simulující souběžné uživatele.

  1. Pomocí aplikace SSMS otevřete soubor skriptu topcustomersales.sql a prohlédněte si dotaz. V nástroji SQL Server Management Studio dotaz spouštět nebudete. V okně editoru dotazů by se měl zobrazit text podobný následujícímu:

    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

    Tato databáze je malá. Dotaz na načtení seznamu zákazníků a souvisejících prodejních informací seřazených zákazníky s největším prodejem by neměl generovat velkou sadu výsledků. Tento dotaz můžete vyladit snížením počtu sloupců v sadě výsledků, ale ty jsou potřeba pro demonstrační účely tohoto cvičení.

  2. Z příkazového řádku PowerShellu zadejte následující příkaz, který přejde do správného adresáře pro toto cvičení. Nahraďte <base directory> ID uživatele a cestu pro tento modul:

    cd <base directory>\04-Performance\monitor_and_scale

  3. Ke spuštění úlohy použijte následující příkaz:

    .\sqlworkload.cmd

    Tento skript použije 10 souběžných uživatelů, kteří dotaz úlohy spustí dvakrát. Všimněte si, že skript sám spustí jednu dávku, ale 10 000krát dokola. Také přiřadil výsledek k proměnné, čímž se eliminuje téměř veškerý přenos sady výsledků dotazu do klienta. To není nutné, ale pomáhá ukázat "čistou" úlohu procesoru spuštěnou zcela na serveru.

    Tip

    Pokud nevidíte chování využití procesoru u této úlohy pro své prostředí, můžete nastavit -n parameter pro počet uživatelů a -r parameter pro iterace.

    Výstup na příkazovém řádku by měl vypadat podobně jako v následujícím výstupu:

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

Sledování výkonu úlohy

Pojďme použít dotazy dynamické správy, které jste načetli dříve, abyste mohli sledovat výkon.

  1. Spusťte dotaz v nástroji SQL Server Management Studio, který jste dříve načetli do monitoru dm_exec_requests (dmexecrequests.sql), abyste mohli sledovat aktivní žádosti. Spusťte tento dotaz pětkrát nebo šestkrát a sledujte některé výsledky:

    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;

    Měli byste vidět, že mnoho požadavků má stav RUNNABLEa last_wait_type je SOS_SCHEDULER_YIELD. Jedním z ukazatelů mnoha RUNNABLE požadavků a mnoha SOS_SCHEDULER_YIELD čekání je možný nedostatek prostředků procesoru pro aktivní dotazy.

    Poznámka:

    Může se zobrazit jeden nebo více aktivních požadavků pomocí příkazu SELECT a wait_type z XE_LIVE_TARGET_TVF. Jedná se o dotazy spouštěné službami spravovanými Microsoftem. Pomáhají s funkcemi, jako jsou přehledy výkonu, s využitím rozšířených událostí. Microsoft nezveřejňuje podrobnosti těchto relací.

    Nechejte toto okno editoru dotazů otevřené. V dalším cvičení ho znovu spustíte.

  2. Spusťte v SSMS dotaz, který jste předtím načetli, abyste monitorovali sys.dm_db_resource_stats (dmdbresourcestats.sql). Spusťte dotaz tak, abyste třikrát nebo čtyřikrát uviděli výsledky tohoto zobrazení dynamické správy.

    SELECT * FROM sys.dm_db_resource_stats;

    Toto zobrazení dynamické správy zaznamená snímek využití prostředků pro databázi každých 15 sekund (během 1 hodiny). V několika snímcích byste měli ve sloupci avg_cpu_percent vidět hodnotu blízkou 100 %. Signalizuje to, že úloha posouvá hranice prostředků procesoru pro databázi.

    V místním prostředí SQL Serveru byste obvykle použili nástroj specifický pro operační systém ke sledování celkového využití prostředků, jako je procesor. K tomuto účelu byste mohli použít například Sledování výkonu systému Windows. Pokud jste tento příklad spustili na místním SQL Serveru nebo SQL Serveru ve virtuálním počítači se dvěma procesory, zobrazilo by se na serveru téměř 100% využití procesoru.

    Poznámka:

    V kontextu databáze serveru master Azure SQL Database můžete spustit další zobrazení dynamické správy, sys.resource_statsabyste viděli využití prostředků pro všechny databáze Azure SQL Database přidružené k serveru. Toto zobrazení je méně podrobné a zobrazuje využití prostředků každých pět minut (uchovávané po dobu 14 dnů).

    Nechejte toto okno editoru dotazů otevřené. V dalším cvičení ho znovu spustíte.

  3. Nechte úlohu dokončit a poznamenejte si její celkovou dobu trvání. Po dokončení úlohy byste měli uvidět výsledky jako v následujícím výstupu a vrátit se do příkazového řádku:

    [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

    Doba trvání se může lišit, ale obvykle bývá minimálně 1–3 minuty. Nechte úlohu doběhnout. Po dokončení úlohy se vrátíte do příkazového řádku.

Další analýza s využitím Úložiště dotazů

Úložiště dotazů je schopnost SQL Serveru sledovat výkon provádění dotazů. Údaje o výkonu se ukládají v uživatelské databázi. Úložiště dotazů není ve výchozím nastavení povolené pro databáze vytvořené v SQL Serveru, ale je standardně zapnuté pro Azure SQL Database (a spravovanou instanci Azure SQL).

Úložiště dotazů nabízí řadu zobrazení systémového katalogu, která umožňují zobrazit údaje o výkonu. SSMS poskytuje sestavy s využitím těchto zobrazení.

  1. Pomocí Průzkumníka objektů v SSMS otevřete složku Úložiště dotazů, ve které najdete sestavu Top Resource Consuming Queries (Dotazy nejvíce využívající prostředky).

    Screenshot of the Query Store.

  2. Tuto sestavu vyberte, abyste zjistili, které dotazy nejvíce využívaly průměrné prostředky, a podrobnosti o provádění těchto dotazů. V závislosti na tom, jaká úloha do tohoto okamžiku běžela, by sestava měla vypadat přibližně jako na následujícím obrázku:

    Screenshot of the top query report.

    Zobrazený dotaz je dotaz SQL z úlohy pro prodej zákazníkům. Tato sestava má tři komponenty: dotazy s vysokou celkovou dobou trvání (metriku můžete změnit), přidružený plán dotazu a běhovou statistiku a přidružený plán dotazu ve vizuální mapě.

  3. Pro dotaz vyberte pruhový graf ( query_id se může pro váš systém lišit). Výsledky by měly vypadat jako na následujícím obrázku:

    Screenshot of the query ID.

    Uvidíte celkovou dobu trvání dotazu a text dotazu.

  4. Vpravo od tohoto pruhového grafu je graf statistiky pro plán dotazu přidružený k dotazu. Najeďte myší na tečku přidruženou k plánu. Výsledky by měly vypadat jako na následujícím obrázku:

    Screenshot of slow query statistics.

    Všimněte si průměrné doby trvání dotazu. Vaše časy se můžou lišit, porovnejte ale tuto průměrnou dobu trvání s průběžnou dobou čekání pro tento dotaz. Později si představíme vylepšení výkonu a toto porovnání provedeme znovu, abyste viděli rozdíl.

  5. Poslední komponentou je vizuální plán dotazu. Plán dotazu pro tento dotaz vypadá jako na následujícím obrázku:

    Screenshot of the workload query plan.

    Tato tabulka databáze obsahuje tolik řádků, že nepotřebuje plán; může být neefektivní. Ladění dotazu nezlepší výkon měřitelným množstvím. V plánu se může zobrazit upozornění na nedostatek statistik pro jeden ze sloupců pro hledání clusterovaného indexu. Toto nemá vliv na celkový výkon.

  6. Sledování sestavy Dotazů s nejvyšším využitím prostředků v SSMS je sestava s názvem Statistika čekání na dotazy. Z předchozí diagnostiky víte, že vysoký počet požadavků byl neustále ve stavu RUNNABLE (SPUSTITELNÝ) s téměř 100% využitím procesorů. Úložiště dotazů nabízí sestavy, které umožňují zaměřit se na možné kritické body výkonu v důsledku čekání na prostředky. Vyberte tuto sestavu a najeďte myší na pruhový graf. Výsledky by měly vypadat jako na následujícím obrázku:

    Screenshot of the top wait statistics.

    Můžete vidět hlavní kategorii čekání CPU – procesor (to je ekvivalentní k wait_typeSOS_SCHEDULER_YIELD, který je k vidění v sys.dm_os_wait_stats) a průměrnou dobu čekání.

  7. V sestavě vyberte pruhový graf procesoru. Hlavní dotaz, který čeká na procesor, je dotaz z úlohy, kterou používáte.

    Screenshot of the top wait statistics query.

    Všimněte si, že průměrná doba čekání na procesor v tomto dotazu představuje vysoké procento celkové průměrné doby trvání dotazu.

    Vzhledem k důkazům bez ladění dotazů vyžaduje naše úloha větší kapacitu procesoru, než jsme nasadili pro naši instanci služby Azure SQL Database.

  8. Zavřete obě sestavy úložiště dotazů. Stejné sestavy budete používat v dalším cvičení.

Sledování výkonu pomocí Azure Monitoru

Pojďme k zobrazení využití prostředků naší úlohy použít jinou metodu. Azure Monitor poskytuje metriky výkonu, které můžete zobrazit různými způsoby, včetně prostřednictvím webu Azure Portal.

  1. Otevřete Azure Portal a vyhledejte svou instanci databáze AdventureWorks SQL. V podokně Přehled databáze vyberte kartu Monitorování. Výchozí zobrazení v podokně Monitorování je Využití výpočetních prostředků:

    Screenshot of the Azure portal with a slow query.

    V tomto příkladu je procento procesoru u nedávného časového rozsahu téměř 100 %. Tento graf zobrazuje využití prostředků (výchozí hodnoty procesoru a vstupně-výstupních operací) za poslední hodinu a průběžně se aktualizuje. Vyberte graf, abyste ho mohli přizpůsobit a podívat se na jiné využití prostředků.

  2. V nabídce databáze SQL vyberte Přidat metriky. Dalším způsobem, jak zobrazit metriky využití výpočetních prostředků a další metriky, které služba Azure Monitor pro Azure SQL Database automaticky shromažďuje, je použití Průzkumníka metrik.

    Poznámka:

    Využití výpočetních prostředků je předdefinované zobrazení Průzkumníka metrik. Pokud v okně Přidat metriky vyberete rozevírací seznam Metriky, zobrazí se následující výsledky:

    Screenshot of Azure Monitor metrics.

    Jak je znázorněno na snímku obrazovky, existuje několik metrik, které můžete použít k zobrazení pomocí Průzkumníka metrik. Výchozí zobrazení Průzkumníka metrik je po dobu 24 hodin s pětiminutovou členitostí. Zobrazení využití výpočetních prostředků je poslední hodina s minutovou členitostí (kterou můžete změnit). Pokud chcete zobrazit stejné zobrazení, vyberte procento procesoru a změňte zachytávání po dobu jedné hodiny. Členitost se změní na jednu minutu a měla by vypadat jako na následujícím obrázku:

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

    Výchozí je spojnicový graf, ale zobrazení Průzkumníka umožňuje typ grafu změnit. Průzkumník metrik má mnoho možností, včetně možnosti zobrazit více metrik ve stejném grafu.

Protokoly Azure Monitoru

V tomto cvičení jste nenastavili protokol Azure Monitoru, ale stojí za to, si prohlédnout, jak by protokol mohl vypadat ve scénáři využití prostředků procesoru. Protokoly Azure Monitoru můžou poskytovat mnohem delší historický záznam než metriky Azure.

Pokud jste nakonfigurovali protokoly Azure Monitoru s pracovním prostorem služby Log Analytics, můžete k zobrazení stejných výsledků využití procesoru pro databázi použít následující dotaz Kusto:

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

Výsledky by vypadaly jako na následujícím obrázku:

Screenshot of a query measuring CPU.

Protokoly Azure Monitoru mají při první konfiguraci diagnostiky protokolů pro databázi zpoždění, takže může nějakou dobu trvat, než se tyto výsledky objeví.

V tomto cvičení jste se naučili sledovat běžný scénář výkonu SQL Serveru a přejít k podrobnostem, abyste mohli rozhodnout o možném řešení, jak výkon zlepšit. V další lekci se naučíte metody zrychlení a ladění výkonu.