如何构建 SQL Server 性能监视器脚本

Microsoft SQL Server 可能是资源最密集的服务器应用程序之一。您应该使用 PowerShell 构建 SQL Server 性能监控脚本来帮助对其进行监控！

SQL Server 也可以在 Linux 上运行，但不幸的是，您没有 Windows 上的性能计数器系统。

Windows 内置了丰富的资源，可用于深入了解操作系统本身以及许多其他应用程序（称为性能计数器）的各种性能指标。

Windows 性能监视器是许多 IT 专业人员的主要工具。虽然 Windows 性能监视器是一个可以直观地查看性能统计数据的好工具，但它不一定适合自动化。这就是 PowerShell 发挥作用的地方。

通过使用 PowerShell 查询性能统计信息，您可以使用这些数字作为其他功能的触发器，例如做出决定以自动将数据库移动到更快的存储、自动添加额外的 CPU、内存等。

在本文中，我们将介绍一些最常见的 SQL 性能指标以及如何使用 PowerShell 查询它们。

使用获取计数器

您最熟悉的 PowerShell cmdlet 是 Get-Counter。 Get-Counter 是一个 cmdlet，可让您从 Windows 查询任意数量的性能指标。单独运行它，您将获得各种常见性能计数器及其当前值的列表。

Get-Counter，不带参数使用查询本地计算机。我们将查询 SQL 服务器，因此使用 ComputerName 参数来指定您的远程 SQL 服务器。

PS> Get-Counter -ComputerName SQLSRV

当您仅使用 ComputerName 参数运行 Get-Counter 时，cmdlet 会返回一组默认的计数器，这可能不是您想要看到的。

查找 SQL Server 性能计数器

Get-Counter 将返回相同的计数器值。然而，我们将寻找一些对 SQL Server 很重要的计数器。我们将查询：

• 平均。 CPU队列长度
• 平均。磁盘队列长度
• 内存页/秒
• 锁存等待时间
• 缓冲区页预期寿命
• 平均锁定等待时间。

要获取每个计数器的当前值，我们首先需要确定它们属于哪个集合以及它们的正确名称。在任何可以使用多个实例的指标中，我使用星号来获取所有可用实例的平均值，或使用 _total 来获取所有实例的总和。

• 平均。 CPU 队列长度=\System\处理器队列长度
• 平均。磁盘队列长度=\PhysicalDisk()\Avg。磁盘队列长度
• 内存页/秒=\内存\页/秒
• 闩锁等待时间=\SQLServer:Latches\平均闩锁等待时间（毫秒）
• 缓冲区页预期寿命=\SQLServer:Buffer Manager\Page lifeexpectancy
• 平均锁定等待时间=\SQLServer:Locks(_total)\平均等待时间（毫秒）

现在我们已经定义了正确的计数器名称以及计数器所属的计数器集，现在我们可以开始组合一些 PowerShell 代码来创建 SQL Server 性能监视器。

查询SQL性能计数器

为了全面了解性能并消除任何计数器出现一次性峰值的可能性，我将查询每个计数器十次。我将使用一秒的默认采样间隔，这意味着我将在十秒内查询每个计数器十次。这应该给我一个比简单地查询每个计数器一次更实际的数字。

为此，我将使用 Get-Counter 上的 MaxSamples 参数。这将允许我指定每个计数器被查询的最大次数。我将收集所有这些数字，然后在完成后取每个数字的平均值。

因为我只需要值，不一定关心格式，所以我将直接指定 CounterSamples 属性，并指定 CookedValue 属性作为其中的一部分。这只给我实际的整数值。

因为我将查询多个性能计数器，所以最好将它们分组到数组中。我在下面这样做了，并将其分配给变量 $counters。我选择使用带有“友好”计数器名称和实际计数器名称的哈希表。然后，我可以使用 foreach 循环读取一些内容。

$counters = @(
    @{
        'Name' = 'Avg. CPU Queue Length'
        'CounterName' ='\System\Processor Queue Length'
    }
    @{
        'Name' = 'Avg. Disk Queue Length'
        'CounterName' ='\PhysicalDisk()\Avg. Disk Queue Length' }
    @{
        'Name' = 'Memory Pages/Sec'
        'CounterName' = '\Memory\Pages/sec'
    }
    @{
        'Name' = 'Latch Wait Time'
        'CounterName' ='\SQLServer:Latches\Average Latch Wait Time (ms)'     }
    @{
        'Name' = 'Buffer Page Life Expectancy'
        'CounterName' ='\SQLServer:Buffer Manager\Page life expectancy'     }
    @{
        'Name' = 'Average Lock Wait Time'
        'CounterName' ='\SQLServer:Locks(_total)\Average Wait Time (ms)'
    }
)

一旦定义了哈希表数组，我将开始读取每个计数器，收集所有十个样本并将平均值添加到计数器实例本身。

最后，我将创建一个 PowerShell 自定义对象，以便在必要时可以轻松地将输出用于其他目的。

$sqlServerName = 'LABSQL'
foreach ($counter in $counters) {
    $values = (Get-Counter -ComputerName $sqlServerName -Counter $counter.CounterName -MaxSamples 10).CounterSamples.CookedValue
    $counter.Add('Value', ($values | Measure-Object -Average).Average)
    [pscustomobject]$counter
}

当您运行上面的代码片段时，您将看到 Get-Counter 将查询 $counters 数组中定义的每个计数器并返回值。由于每个迭代器 ($counter) 都是一个哈希表，因此脚本会添加一个名为 Value 的属性并对这些值求平均值。

最终得到的结果是单个 pscustomobject 对象，该对象具有表示每个计数器值平均值的单个属性 Value。

这最终为我们提供了一份很好的报告，显示了 SQL 服务器的性能计数器统计信息！

概括

您可以看到，在 PowerShell 中使用性能计数器非常简单。对我来说最困难的部分就是试图找到计数器名称本身！现在，使用此处显示的 SQL Server 性能监视器脚本的启动并扩展它！