SQLServer 基线跟踪指标（powershell 模糊匹配导出）

基线收集参考：SQLServer Windows数据收集器

####################  CPU相关  ####################
"\Processor(_Total)\% User Time"
"\Processor(_Total)\% Processor Time"
"\Processor(_Total)\% Interrupt Time"
"\Processor(_Total)\% Privileged Time"
"\System\Context Switches/sec"
"\System\Processor Queue Length"
"\System\Processes"
"\System\Threads"

####################  内存相关  ####################
"\Paging File(\??\C:\pagefile.sys)\% Usage"
"\Paging File(\??\C:\pagefile.sys)\% Usage Peak"
"\Memory\Pages/sec"
"\Memory\Page Faults/sec"
"\Memory\Available MBytes"
"\SQLServer:Buffer Manager\Buffer cache hit ratio"
"\SQLServer:Buffer Manager\Lazy writes/sec"
"\SQLServer:Buffer Manager\Page life expectancy"
"\SQLServer:Buffer Manager\Checkpoint pages/sec"
"\SQLServer:Buffer Manager\Total pages"
"\SQLServer:Buffer Manager\Target pages"
"\SQLServer:Memory Manager\Target Server Memory (KB)"
"\SQLServer:Memory Manager\Total Server Memory (KB)"
"\SQLServer:Memory Manager\Database Cache Memory (KB)"
"\SQLServer:Memory Manager\Connection Memory (KB)"
"\SQLServer:Memory Manager\Granted Workspace Memory (KB)"
"\SQLServer:Memory Manager\Lock Memory (KB)"
"\SQLServer:Memory Manager\Memory Grants Pending"
"\SQLServer:Memory Manager\Optimizer Memory (KB)"
"\SQLServer:Memory Manager\SQL Cache Memory (KB)"
"\SQLServer:Memory Manager\Stolen Server Memory (KB)"

####################  IO disk 相关  ####################
#监控指定各个数据库
#"\SQLServer:Databases(AdventureWorks2014)\Data File(s) Size (KB)"
#"\SQLServer:Databases(AdventureWorks2014)\Log File(s) Size (KB)"
#"\SQLServer:Databases(AdventureWorks2014)\Transactions/sec"
"\SQLServer:Databases(_Total)\Transactions/sec"
"\SQLServer:Databases(_Total)\Log File(s) Size (KB)"
"\SQLServer:Databases(_Total)\Data File(s) Size (KB)"
"\PhysicalDisk(_Total)\% Disk Time"
"\PhysicalDisk(_Total)\Avg. Disk Queue Length"
"\PhysicalDisk(_Total)\Current Disk Queue Length"
"\PhysicalDisk(_Total)\Avg. Disk Read Queue Length"
"\PhysicalDisk(_Total)\Avg. Disk Write Queue Length"
"\PhysicalDisk(_Total)\Avg. Disk sec/Read"
"\PhysicalDisk(_Total)\Avg. Disk sec/Write"
"\PhysicalDisk(_Total)\Avg. Disk Bytes/Read"
"\PhysicalDisk(_Total)\Avg. Disk Bytes/Write"
"\PhysicalDisk(_Total)\Split IO/Sec"

####################  访问方式  ####################
"\SQLServer:Access Methods\Page Splits/sec"
"\SQLServer:Access Methods\Full Scans/sec"
"\SQLServer:Access Methods\Range Scans/sec"
"\SQLServer:Access Methods\Workfiles Created/sec"
"\SQLServer:Access Methods\Worktables Created/sec"
"\SQLServer:General Statistics\Active Temp Tables"
"\SQLServer:General Statistics\Temp Tables Creation Rate"
"\SQLServer:General Statistics\Logins/sec"
"\SQLServer:General Statistics\Logouts/sec"
"\SQLServer:General Statistics\Connection Reset/sec"
"\SQLServer:General Statistics\Logical Connections"
"\SQLServer:General Statistics\User Connections"
"\SQLServer:SQL Statistics\Batch Requests/sec"
"\SQLServer:SQL Statistics\Auto-Param Attempts/sec"
"\SQLServer:SQL Statistics\Failed Auto-Params/sec"
"\SQLServer:SQL Statistics\Forced Parameterizations/sec"
"\SQLServer:SQL Statistics\SQL Compilations/sec"
"\SQLServer:SQL Statistics\SQL Re-Compilations/sec"

####################  network  ####################
#监控对应的网卡
"\Network Interface(Realtek PCIe FE 系列控制器)\Current Bandwidth"
"\Network Interface(Realtek PCIe FE 系列控制器)\Output Queue Length"
"\Network Interface(Realtek PCIe FE 系列控制器)\Bytes Received/sec"
"\Network Interface(Realtek PCIe FE 系列控制器)\Bytes Sent/sec"
"\Network Interface(Realtek PCIe FE 系列控制器)\Packets Received/sec"
"\Network Interface(Realtek PCIe FE 系列控制器)\Packets Sent/sec"

####################  锁相关  ####################
"\SQLServer:Latches\Latch Waits/sec"
"\SQLServer:Latches\Average Latch Wait Time (ms)"
"\SQLServer:Locks(_Total)\Lock Waits/sec"
"\SQLServer:Locks(_Total)\Lock Wait Time (ms)"
"\SQLServer:Locks(_Total)\Average Wait Time (ms)"
"\SQLServer:Locks(_Total)\Lock Requests/sec"
"\SQLServer:Locks(_Total)\Lock Timeouts/sec"
"\SQLServer:Locks(_Total)\Number of Deadlocks/sec"

powershell 模糊匹配导出

#Set-ExecutionPolicy RemoteSigned -force

$counterList = typeperf -q

$counters = (
"#other相关",
"SQL Statistics\Batch Requests/sec",
"SQL Statistics\SQL Compilations/sec",
"SQL Statistics\SQL Re-Compilations/sec",
"General Statistics\Processes blocked",
"General Statistics\User Connections",
"General Statistics\Active Temp Tables",
"General Statistics\Temp Tables Creation Rate",
"Access Methods\Full Scans/sec",
"Access Methods\Range Scans/sec",
"Access Methods\Workfiles Created/sec",
"Access Methods\Worktables Created/sec",
"Access Methods\Page Splits/sec",
"Locks(_Total)\Number of Deadlocks/sec",
"Locks(_Total)\Lock Timeouts/sec",
"Databases(_Total)\Active Transactions",
"Databases(_Total)\Log File(s) Size",
"Databases(_Total)\Log Flushes/sec",
"Databases(_Total)\Transactions/sec",
"#Memory相关",
"\Paging File(\??\C:\pagefile.sys)\% Usage",
"\Paging File(\??\C:\pagefile.sys)\% Usage Peak",
"Buffer Manager\Page life expectancy",
"Buffer Manager\Lazy writes/sec",
"Buffer Manager\Checkpoint pages/sec",
"Buffer Manager\Buffer cache hit ratio",
"Memory Manager\Connection Memory",
"Memory Manager\Lock Memory",
"Memory Manager\Memory Grants Pending",
"Buffer Manager\Total pages",
"Buffer Manager\Target pages",
"Buffer Manager\Free pages",
"Buffer Manager\Stolen pages",
"Buffer Manager\Database pages",
"\Memory\Transition Faults/sec",
"\Memory\Pages/sec",
"\Memory\Available Mbyte",
"#CPU相关",
"\Processor(_Total)\% Processor Time",
"\System\Processor Queue Length",
"\System\Threads",
"\System\Context Switches/sec",
"\Process(sqlservr*)\% Privileged Time",
"\Process(sqlservr*)\% Processor Time",
"#IO相关",
"\LogicalDisk(_Total)\Avg. Disk Queue Length",
"\LogicalDisk(_Total)\Avg. Disk Read Queue Length",
"\LogicalDisk(_Total)\Avg. Disk sec/Read",
"\LogicalDisk(_Total)\Avg. Disk sec/Write",
"\LogicalDisk(_Total)\Avg. Disk Write Queue Length",
"\LogicalDisk(_Total)\Disk Read Bytes/sec",
"\LogicalDisk(_Total)\Disk Reads/sec",
"\LogicalDisk(_Total)\Disk Write Bytes/sec",
"\LogicalDisk(_Total)\Disk Writes/sec",
"\LogicalDisk(*)\Avg. Disk Queue Length",
"\LogicalDisk(*)\Avg. Disk Read Queue Length",
"\LogicalDisk(*)\Avg. Disk sec/Read",
"\LogicalDisk(*)\Avg. Disk sec/Write",
"\LogicalDisk(*)\Avg. Disk Write Queue Length",
"\LogicalDisk(*)\Disk Read Bytes/sec",
"\LogicalDisk(*)\Disk Reads/sec",
"\LogicalDisk(*)\Disk Write Bytes/sec",
"\LogicalDisk(*)\Disk Writes/sec",
"\PhysicalDisk(_Total)\Split IO/Sec",
"\PhysicalDisk(*)\Avg. Disk Read Queue Length",
"\PhysicalDisk(*)\Avg. Disk Write Queue Length",
"\PhysicalDisk(*)\Current Disk Queue Length",
"#Network相关",
"\Network Interface(*)\Bytes Received/sec",
"\Network Interface(*)\Bytes Sent/sec",
"\Network Interface(*)\Packets Received/sec",
"\Network Interface(*)\Packets Sent/sec",
"\Network Interface(*)\Output Queue Length"
)

#foreach($i in $counterList) { $counters | ForEach-Object{if($i -like -Join("*",$_,"*")) {Write-Host $i }}}
foreach($i in $counterList) { $counters | ForEach-Object{if($i -like -Join("*",$_,"*")) {write-output $i >> ".\result.txt"}}}

（个人整理，仅供参考）