练习 - 对性能进行监视和故障排除

  • 10 分钟

在此练习中，你将了解如何通过使用熟悉和新的工具和功能对 Azure SQL 的性能问题进行监视和故障排除。

设置：使用脚本部署 Azure SQL 数据库

通过右侧终端会话（Azure Cloud Shell），可以使用浏览器与 Azure 交互。 对于此练习，你将运行脚本来创建环境，也就是带有 AdventureWorks 数据库的 Azure SQL 数据库的实例。 （使用了更小、更简单的示例 AdventureWorksLT 数据库，但我们将其称为 AdventureWorks 以避免混淆。）在该脚本中，系统会提示输入密码和本地 IP 地址，以使设备能够连接到该数据库。

此脚本需要 3 至 5 分钟才能完成。 请确保记下密码、唯一 ID 和区域。 这些内容只显示一次。

  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

    提示

    保存输出，并记下密码、唯一 ID 和服务器。 在本模块中，你需要使用这些项目。

  4. 运行以下脚本，使用 AdventureWorks 示例部署 Azure SQL 数据库实例和逻辑服务器。 此脚本将 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 步中提供的密码。
    记住密码 checked

  7. 选择“连接”。

    SSMS 中 SQL 数据库的连接对话框的屏幕截图。

    注意

    根据本地配置（例如 VPN），客户端 IP 地址可能不同于部署期间 Azure 门户所用的 IP 地址。 若是如此，你将收到以下消息：“你的客户端 IP 地址没有访问服务器的权限。 请登录到 Azure 帐户并创建新的防火墙规则以支持访问。”如果收到此消息，请通过要对沙盒使用的帐户登录，并为客户端 IP 地址添加防火墙规则。 可在 SSMS 中使用向导来完成所有这些步骤。

通过加载并编辑脚本来准备练习

可以在克隆的 GitHub 存储库或下载的 zip 文件的 04-Performance\monitor_and_scale 文件夹中找到本练习的所有脚本。 让我们通过加载并编辑脚本来准备练习。

  1. 在 SSMS 的“对象资源管理器”中，展开 Databases 文件夹并选择 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 数据库跟踪工作负载的总体资源使用情况。 例如，它会跟踪 CPU、I/O 和内存。

  4. 打开并编辑 sqlworkload.cmd 脚本（它将使用 ostress.exe 程序）。

    • 将从部署脚本中保存的 unique_id 替换为服务器名。
    • 将用于登录 Azure SQL 数据库服务器的密码替换为 -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 命令提示符中，输入以下命令，转到正确的目录以进行此练习。 使用此模块的的用户 ID 和路径替换 <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，wait_type 为 XE_LIVE_TARGET_TVF。 这些是由 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 或在虚拟机（有两个 CPU）中的 SQL Server 上运行此示例，你会在服务器上看到 CPU 利用率接近 100%。

    注意

    可以在 Azure SQL 数据库服务器的 master 数据库的上下文中运行另一个 DMV (sys.resource_stats)，以查看与该服务器关联的所有 Azure SQL 数据库数据库的资源使用情况。 此视图粒度较低，每五分钟显示一次资源使用情况（保留 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 数据库（和 Azure SQL 托管实例）默认启用此工具。

查询存储附带一系列系统目录视图，用于查看性能数据。 SSMS 使用这些视图提供报表。

  1. 在 SSMS 中使用对象资源管理器打开“查询存储”文件夹，找到“资源消耗量最大的查询”报表。

    查询存储的屏幕截图。

  2. 选择该报表以找出消耗最多平均资源的查询以及这些查询的执行详细信息。 基于目前运行的工作负载，报表应如下图所示：

    排名靠前报表的屏幕截图。

    显示的查询是工作负载中针对客户销售额的 SQL 查询。 此报表有 3 个组成部分：总持续时间较高的查询（可更改此指标）、关联查询计划和运行时统计信息，以及可视化图中的关联查询计划。

  3. 选择查询的条形图（query_id 可能因系统而异）。 结果应如下图所示：

    查询 ID 的屏幕截图。

    可以看到查询的总持续时间和查询文本。

  4. 此条形图的右侧是一个图表，其中包含与查询关联的查询计划的统计信息。 将鼠标悬停在与计划关联的点上方。 结果应如下图所示：

    缓慢查询统计信息的屏幕截图。

    记下查询的平均持续时间。 时间可能有所不同，但是请将此平均持续时间与此查询的平均等待时间进行比较。 稍后，我们将介绍性能改进，你也将再次比较来了解不同之处。

  5. 最后一个组件是可视化查询计划。 此查询的查询计划如下图所示：

    工作负载查询计划的屏幕截图。

    此数据库表包含的行数很少，因此不需要计划；它可能会效率低下。 优化查询不会显著提高性能。 你可能会在计划中看到警告，其中显示聚集索引查找的某一列缺少统计信息。 这并不影响总体性能。

  6. 在 SSMS 中的“资源消耗量最大的查询”报表后面，有一个名为“查询等待统计信息”的报表。 通过之前的诊断得知，大量请求持续处于 RUNNABLE 状态，并且 CPU 利用率近乎 100%。 查询存储附带了报表，可用于了解由于等待资源而可能出现的性能瓶颈。 选择此报表并将鼠标悬停在条形图上方。 结果应如下图所示：

    最常见等待统计信息的屏幕截图。

    你可看到最常见的等待类别是平均等待时间和 CPU（这相当于 wait_type SOS_SCHEDULER_YIELD，可在 sys.dm_os_wait_stats 中进行查看）。

  7. 选择报表中的 CPU 条形图。 等待 CPU 的最常见的查询来自你使用的工作负载。

    最常见等待统计信息查询的屏幕截图。

    请注意，此查询中的 CPU 平均等待时间在查询总体平均持续时间中占的百分比较高。

    考虑到这一点，如果不进行查询优化，那么我们的工作负载所需的 CPU 容量比我们为 Azure SQL 数据库实例部署的容量多。

  8. 关闭这两个查询存储报表。 你将在下一练习中使用这些报表。

使用 Azure Monitor 观察性能

让我们使用另一种方法来查看工作负载的资源使用情况。 Azure Monitor 提供了可通过各种方法（包括通过 Azure 门户）查看的性能指标。

  1. 打开Azure 门户，然后找到 AdventureWorks SQL 数据库实例。 在数据库的“概述”窗格中，选择“监视”选项卡。“监视”窗格中的默认视图为“计算利用率”：

    具有缓慢查询的 Azure 门户的屏幕截图。

    在本例中，CPU 百分比在最近的时间范围内接近 100%。 此图表会显示过去一小时内的资源使用情况（CPU 和 I/O 是默认值），并会持续刷新。 选择该图表，以便对其进行自定义，以查看其他资源使用情况。

  2. 在 SQL 数据库菜单上，选择“添加指标”。 查看由 Azure Monitor 为 Azure SQL 数据库自动收集的“计算利用率”指标和其他指标的另一种方式是使用指标资源管理器。

    备注

    “计算利用率”是指标资源管理器的一个预定义视图。 如果在“添加指标”窗口中选择“指标”下拉列表，则会看到以下结果：

    Azure Monitor 指标的屏幕截图。

    如屏幕截图中所示，有几个指标可用于通过指标资源管理器进行查看。 指标资源管理器的默认视图为 24 小时周期，粒度为五分钟。 “计算利用率”视图是过去一小时内的情况，其粒度为一分钟（可以更改）。 若要查看相同视图，请选择“CPU 百分比”并更改一小时内的捕获。 粒度会更改为一分钟，应如下图所示：

    包含 1 分钟后的 CPU 的 Azure Monitor 指标屏幕截图。

    默认设置为折线图，但资源管理器视图允许更改图表类型。 指标资源管理器具有多种选项，包括能够在同一图表上显示多个指标。

Azure Monitor 日志

在本练习中，你没有设置 Azure Monitor 日志，但有必要查看 CPU 资源使用场景中日志的外观。 Azure Monitor 日志可提供比 Azure 指标长得多的历史记录。

如果使用 Log Analytics 工作区配置了 Azure Monitor 日志，则可使用以下 Kusto 查询查看针对数据库的相同类型的 CPU 利用率结果：

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

结果会如下图所示：

度量 CPU 的查询的屏幕截图。

首次为数据库配置日志诊断时，Azure Monitor 日志会存在延迟，因此可能需要一些时间才能显示这些结果。

在本练习中，你了解了如何观察常见的 SQL Server 性能场景和深入了解详细信息，从而确定可能的解决方案来提高性能。 在下一单元中，你将了解如何加速和优化性能。