使用 PowerShell 腳本讀取 SQL 性能計數器

我正在嘗試創建一個 PS 腳本來為我的 1 級合作夥伴讀取一些關於我的 SQL 的性能計數器，但我遇到了實例名稱的問題。

我正在使用這段程式碼：

$computer = "\\\" + $ENV:Computername
$instance = $computer + '\MSSQL$SG25DB'
write-Host $instance
Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80."
Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio"

輸出是：

\\\\[hostname]\\MSSQL$SG25DB     <--- Here appears the proper value :)
Use of Memory Buffer. The expected value must be greater than 80.
Get-Counter : The specified counter path could not be interpreted. At
line:5 char:12
+ Get-Counter <<<<  "$instance:Buffer Manager\Buffer cache hit ratio"
+ CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

我已經用 Write-Host 驗證了變數的值 $ instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $ ? 我怎樣才能讓它正常執行？

嘗試這個：

Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio"

發生的情況是您的$instance變數不是孤立的，因此您需要明確說明變數是什麼以及它在哪裡結束。您可以使用上述符號（變數名稱周圍的美元符號和括號）來做到這一點。

看看以下的不同輸出：

Write-Host "$instance:Buffer Manager\Buffer cache hit ratio"

輸出是：

Manager\Buffer cache hit ratio

現在用這個正確的符號：

Write-Host "$($instance):Buffer Manager\Buffer cache hit ratio"

輸出是這樣的：

\\MyMachineName\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio

解決此類問題的一種常見方法是使用上述方法，通過準確查看您傳遞給其他 cmdlet/函式/等的內容。

我建議你可以在這個頁面上找到的腳本。

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = "Stop"
$VerbosePreference = "Continue"

cls

Write-Output "Collecting counters..."
Write-Output "Press Ctrl+C to exit."

$counters = @("\Processor(_Total)\% Processor Time", 
"\LogicalDisk(_Total)\Disk Reads/sec", 
"\LogicalDisk(_Total)\Disk Writes/sec", 
"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec") 

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | 
   Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force

引用自：https://dba.stackexchange.com/questions/46388