練習 - 監視效能並進行疑難排解

  • 10 分鐘

在此練習中，您將了解如何使用熟悉和新的工具及功能，來監視 Azure SQL 的效能問題並進行疑難排解。

設定：使用指令碼來部署 Azure SQL Database

Azure Cloud Shell 右側的終端工作階段可讓您使用瀏覽器來與 Azure 互動。 針對此練習，您將執行指令碼以建立環境，也就是具有 AdventureWorks 資料庫的 Azure SQL 資料庫執行個體。 (使用較小型、更簡單的範例 AdventureWorksLT 資料庫，但我們會將其稱為 AdventureWorks 以避免混淆。)在指令碼中，將提示您輸入密碼和本機 IP 位址，讓您的裝置能夠連線到資料庫。

這段指令碼大約需要 3-5 分鐘才能夠完成。 務必記下您的密碼、唯一識別碼及區域。 這些資訊不會出現第二次。

  1. 先取得您的本機 IP 位址。 確定已中斷與任何 VPN 服務的連線，並在裝置上開啟本機 PowerShell 終端。 執行下列命令，並記下產生的 IP 位址：

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

  2. 在 Azure Cloud Shell 右側，輸入下列程式碼，並在出現提示時，提供複雜密碼及您在上一個步驟中擷取的本機公用 IP 位址。 按 Enter 鍵，以執行指令碼的最後一行。

    $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. 在 Azure Cloud Shell 中執行下列指令碼。 儲存輸出；您在整個課程模組中都需要此資訊。 貼上程式碼之後，按 Enter 鍵，如此一來，最後一行程式碼就會列印您需要的輸出。

    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

    提示

    儲存輸出，並記下您的密碼、唯一識別碼及伺服器。 在整個課程模組中，您將會需要這些項目。

  4. 執行下列指令碼，以部署 Azure SQL Database 執行個體和具有 AdventureWorks 範例的邏輯伺服器。 此指令碼會將您的 IP 位址新增為防火牆規則、啟用進階資料安全性，並建立儲存體帳戶以用於此課程模組的其餘練習中。 指令碼可能需要幾分鐘的時間才能完成，而且將暫停數次。 等候命令提示字元。

    # 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. 在本機裝置上，開啟 SQL Server Management Studio (SSMS)，以建立與邏輯伺服器的新連線。

  6. 在 [連接到伺服器] 登入對話方塊中，提供下列資訊：

    欄位 值
    伺服器類型 資料庫引擎 (預設)。
    伺服器名稱 在 Cloud Shell 中傳回的 $serverName，再加上 URI 的其餘部分。 例如：aw-server<unique ID>.database.windows.net。
    驗證 SQL Server 驗證 (預設)。
    登入 cloudadmin：在此練習的步驟 1 中指派的 adminSqlLogin。
    密碼 您在此練習的步驟 1 中提供的密碼。
    記住密碼 已選取

  7. 選取 Connect。

    此螢幕擷取畫面顯示 SSMS 中 SQL Database 的連線對話方塊。

    注意

    根據本機設定 (例如 VPN) 而定，您的用戶端 IP 位址可能會與 Azure 入口網站在部署期間所使用的 IP 位址不同。 如果不同，您將看到下列訊息：「您的用戶端 IP 位址無法存取伺服器。 請登入 Azure 帳戶，並建立新的防火牆規則以啟用存取。」如果收到此訊息，請使用在沙箱中所用的帳戶登入，並為用戶端 IP 位址新增防火牆規則。 您可透過使用 SSMS 中的精靈來完成上述所有步驟。

藉由載入和編輯指令碼來準備練習

此練習中的所有指令碼都可在您複製的 GitHub 存放庫中的 04-Performance\monitor_and_scale 資料夾，或您下載的 ZIP 檔案中找到。 讓我們透過載入和編輯指令碼來準備此練習。

  1. 在 SSMS 中，在 [物件總管] 中，展開 [資料庫] 資料夾，並選取 [AdventureWorks] 資料庫。

  2. 選取 [檔案][開啟]>>[檔案]，然後開啟 dmexecrequests.sql 指令碼。 您的 [查詢編輯器] 視窗看起來應類似下列文字：

    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. 在 SSMS 中，使用相同方法來載入 dmdbresourcestats.sql 指令碼。 新的查詢編輯器視窗看起來應該類似下列文字：

    SELECT * FROM sys.dm_db_resource_stats;

    這個動態管理檢視 (DMV) 將針對 Azure SQL Database，追蹤工作負載的整體資源使用量。 例如，其會追蹤 CPU、I/O 和記憶體。

  4. 開啟和編輯 sqlworkload.cmd 指令碼 (其將使用 ostress.exe 程式)。

    • 以您從部署指令碼儲存的 unique_id 替代伺服器名稱。
    • 以您用來針對 Azure SQL Database 伺服器登入的密碼替代 -P parameter。
    • 將變更儲存至指令碼。

執行工作負載

在此工作中，您將在 T-SQL 查詢中執行工作負載，以觀察其在模擬並行使用者時的效能。

  1. 使用 SSMS 來開啟 topcustomersales.sql 指令檔以觀察查詢。 您將不會從 SSMS 執行查詢。 您的 [查詢編輯器] 視窗看起來應類似下列文字：

    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

    此資料庫很小。 因此，擷取客戶清單及其相關聯銷售資訊的查詢 (依銷售額最高的客戶排序)，應該不會產生大型結果集。 您可透過減少結果集中的資料行數目來調整此查詢，但基於練習目的，這些在此示範中是必要的。

  2. 從 PowerShell 命令提示字元中，輸入下列命令，以移至此練習的正確目錄。 將 <base directory> 取代為適用於此課程模組的使用者識別碼和路徑：

    cd <base directory>\04-Performance\monitor_and_scale

  3. 使用下列命令執行工作負載：

    .\sqlworkload.cmd

    此指令碼將使用 10 位執行工作負載查詢兩次的並行使用者。 請注意，指令碼本身會執行單一批次，但會執行 10,000 次迴圈。 其也會將結果指派給變數，因此，幾乎可排除所有流向用戶端的結果集流量。 這並非必要，但有助於顯示完全在伺服器上執行的「純」CPU 工作負載。

    提示

    如果在環境中看不到此工作負載的 CPU 使用行為，則可調整使用者數目的 -n parameter，以及反覆項目的 -r parameter。

    命令提示字元中的輸出看起來應該類似下列輸出：

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

觀察工作負載的效能

我們將使用您稍早載入的 DMV 查詢來觀察效能。

  1. 在先前載入的 SSMS 中執行查詢來監視 dm_exec_requests (dmexecrequests.sql)，以觀察使用中的要求。 執行此查詢五或六次，並觀察部分結果：

    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;

    您應該看到許多要求的狀態為 RUNNABLE，且 last_wait_type 為 SOS_SCHEDULER_YIELD。 許多 RUNNABLE 要求和許多 SOS_SCHEDULER_YIELD 等候的一個指標是可能缺少作用中查詢的 CPU 資源。

    注意

    您可能會看到一或多個作用中要求，其中包含 SELECT 命令和 XE_LIVE_TARGET_TVF 的 wait_type。 這些都是由受 Microsoft 管理之服務所執行的查詢。 它們透過使用擴充事件，協助提供效能見解等功能。 Microsoft 不會發佈這些工作階段的詳細資料。

    將此查詢編輯器視窗保持在開啟狀態。 您將在下一個練習中再次執行。

  2. 在您先前載入的 SSMS 中，執行查詢來監視 sys.dm_db_resource_stats (dmdbresourcestats.sql)。 執行查看此 DMV 結果的查詢三次或四次。

    SELECT * FROM sys.dm_db_resource_stats;

    此 DMV 每隔 15 秒就會記錄資料庫的資源使用量快照集 (保留 1 小時)。 您應該會看到數個快照集的 avg_cpu_percent 資料行接近 100%。 這是工作負載為資料庫推送 CPU 資源限制的徵兆。

    針對 SQL Server 內部部署環境，您通常會使用作業系統特定的工具來追蹤整體資源使用狀況 (例如 CPU)。 例如，您可能會針對此目的使用 Windows 效能監視器。 如果在內部部署 SQL Server 或虛擬機器 (配備 2 個 CPU) 中的 SQL Server 上執行此範例，則會在伺服器上看到接近 100% 的 CPU 使用率。

    注意

    您可以在 Azure SQL Database 伺服器的 master 資料庫的內容中執行另一個 DMV，sys.resource_stats，以查看與伺服器相關聯之所有 Azure SQL Database 資料庫的資源使用量。 此檢視的細微度較低，但每隔 5 分鐘就會顯示資源使用狀況 (保留 14 天)。

    將此查詢編輯器視窗保持在開啟狀態。 您將在下一個練習中再次執行。

  3. 讓工作負載完成，並記錄其整體持續時間。 當工作負載完成時，您應該會看到類似下列輸出的結果，並返回命令提示字元：

    [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

    您的持續時間可能有所不同，但這通常至少需要 1-3 分鐘。 請務必讓此執行完成。 當工作負載完成時，您將返回命令提示字元。

使用查詢存放區進行進一步分析

查詢存放區是 SQL Server 中的一項功能，可追蹤查詢的效能執行。 效能資料會儲存在使用者資料庫中。 預設不會針對在 SQL Server 中建立的資料庫啟用查詢存放區，但預設會針對 Azure SQL Database (和 Azure SQL 受控執行個體) 加以開啟。

查詢存放區隨附一系列的系統目錄檢視，可用來檢視效能資料。 SSMS 使用這些檢視來提供報告。

  1. 使用 SSMS 中的 [物件總管] 來開啟 [查詢存放區] 資料夾，以尋找 [資源耗用量排名在前的查詢] 的報告。

    查詢存放區的螢幕擷取畫面。

  2. 選取報告，以找出哪些查詢耗用的資源量最平均，以及那些查詢的執行詳細資料。 根據工作負載到目前為止的執行，您的報表應該會如下圖所示：

    排名最前的查詢報告螢幕擷取畫面。

    顯示的查詢是來自客戶銷售工作負載的 SQL 查詢。 這份報告包含三個元件：高持續時間總計的查詢 (您可變更計量)、相關聯的查詢計劃和執行階段統計資料，以及視覺效果地圖中相關聯的查詢計劃。

  3. 針對查詢選取橫條圖 (query_id 可能與您的系統不同)。 結果應該如下圖所示：

    查詢識別碼的螢幕擷取畫面。

    您可以看到查詢和查詢文字的持續時間總計。

  4. 此橫條圖右邊是與查詢相關聯之查詢計劃的統計資料圖表。 將滑鼠停留在與計劃相關聯的點上。 結果應該如下圖所示：

    慢速查詢統計資料的螢幕擷取畫面。

    記下查詢的平均持續時間。 您的時間可能有所不同，但請將此平均持續時間與此查詢的平均等候時間進行比較。 之後，我們將引進效能改善，而您將再次進行此比較以查看差異。

  5. 最後一個元件是視覺化查詢計劃。 此查詢的查詢計劃應該如下圖所示：

    工作負載查詢計劃的螢幕擷取畫面。

    此資料庫資料表的資料列太少，以致於不需要計劃，因其可能效率不佳。 微調查詢，將不會透過可測量的數量來改善效能。 您可能會在計劃中看到一則警告，指出針對叢集索引搜尋缺少其中一個資料行的統計資料。 這不會影響整體效能。

  6. 在 SSMS 的「最高資源耗用量查詢」報告底下，有一個稱為「查詢等候統計資料」的報告。 我們從先前的診斷中得知，大量要求經常處於 RUNNABLE 狀態，且 CPU 使用率幾乎都是 100%。 查詢存放區會隨附報告，可用來查看因等候資源而可能造成的效能瓶頸。 選取此報告，並將滑鼠暫留在橫條圖之上。 結果應該如下圖所示：

    此資料螢幕擷取畫面顯示排名最前的等候統計。

    您可看到排名最前的等候類別是 CPU (這相當於可在 sys.dm_os_wait_stats 中看到的 wait_type SOS_SCHEDULER_YIELD)，以及平均等候時間。

  7. 選取報告中的 CPU 橫條圖。 排名最前的查詢 (等候 CPU) 是來自於您正在使用之工作負載的查詢。

    此螢幕擷取畫面顯示排名最前的等候統計資料查詢。

    請注意，在此查詢中，CPU 的平均等候時間在查詢的整體平均持續時間中佔了很高的百分比。

    考量到這一點，在不微調任何查詢的狀況下，工作負載所需的 CPU 容量會超過針對 Azure SQL Database 執行個體所部署的容量。

  8. 關閉這兩個查詢存放區報告。 在下一個練習中，您將使用相同的報告。

使用 Azure 監視器來觀察效能

讓我們使用另一個方法來檢視工作負載的資源使用量。 您可使用各種方法來檢視 Azure 監視器提供的效能計量，包括透過 Azure 入口網站。

  1. 開啟 Azure 入口網站，然後尋找 AdventureWorks SQL 資料庫的執行個體。 在資料庫的 [概觀] 頁面上，選取 [監視] 索引標籤。[監視] 窗格中的默認檢視是 [計算使用率]：

    此螢幕擷取畫面顯示具有慢速查詢的 Azure 入口網站。

    在此範例中，最近一個時間範圍內的 CPU 百分比接近 100%。 此圖表顯示過去一小時內的資源使用量 (預設值為 CPU 和 I/O)，並會持續重新整理。 選取此圖表，讓您能夠加以自訂，並查看其他資源使用量。

  2. 在 [SQL 資料庫] 功能表上，選取 [新增計量]。 另一種檢視計算使用率計量及 Azure 監視器針對 Azure SQL Database 自動收集之其他計量的方式，就是使用計量瀏覽器。

    注意

    計算使用率只是計量瀏覽器的預先定義檢視。 如果您在 [新增計量] 視窗中選取 [計量] 下拉式清單，您會看到下列結果：

    Azure 監視器計量的螢幕擷取畫面。

    如螢幕擷取畫面中所見，您可以搭配使用計量瀏覽器來檢視的計量有數個。 [計量瀏覽器] 的預設檢視時間範圍為 24 小時，細微性為 5 分鐘。 [計算使用率] 檢視的時間為過去一小時，細微性為一分鐘 (您可加以變更)。 若要查看相同的檢視，請選取 [CPU 百分比]，並將擷取變更為 1 小時。 細微性會變更為一分鐘，看起來應該如下圖所示：

    Azure 監視器計量的螢幕擷取畫面，包含 1 分鐘後的 CPU。

    預設值為折線圖，但是 [瀏覽器] 檢視可讓您變更圖表類型。 計量瀏覽器有多個選項，包括能在相同圖表中顯示多個計量。

Azure 監視器記錄

在此練習中，您並未設定 Azure 監視器記錄，但有機會不妨看看 CPU 資源使用量案例的記錄長什麼樣子。 Azure 監視器記錄提供的歷程記錄比 Azure 計量長。

如果您已使用 Log Analytics 工作區來設定 Azure 監視器記錄，則可使用下列 Kusto 查詢來檢視資料庫的相同 CPU 使用率結果：

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

您的結果應該如下圖所示：

此螢幕擷取畫面顯示測量 CPU 的查詢。

第一次設定資料庫的記錄診斷時，Azure 監視器記錄會有延遲，所以，這些結果需要一些時間才會出現。

在此練習中，您已了解如何觀察常見的 SQL Server 效能案例，並深入探索詳細資料，以決定改善效能的可能解決方案。 在下一個單元中，您將了解加速與調整效能的方法。