SQLServer Windows数据收集器

当服务器较多的时候，当收集或统计的计数器较多的时候，当时间紧迫的时候，怎样才能更方便更快使用性能计数器收集数据？过去常用有一种方法，在性能计数器图像界面中邮件，将设置另存为…。当需要跟踪的时候，可以很快将该设置拖动到计数器中，这是本地常用的方法。如下图：

对于批量收集计数器，把计数器写到配置文件中，如：MSSQL2012BaseLineCounters.config

"MemoryAvailable MBytes"
"MemoryFree System Page Table Entries"
"MemoryPages Input/sec"
"MemoryPages/sec"
"SQLServer:Access MethodsFull Scans/sec"
"SQLServer:Access MethodsPage Splits/sec"
"SQLServer:Access MethodsWorkfiles Created/sec"
"SQLServer:Access MethodsWorktables Created/sec"
"SQLServer:Buffer ManagerBuffer cache hit ratio"
"SQLServer:Buffer ManagerCheckpoint pages/sec"
"SQLServer:Buffer ManagerFree pages"
"SQLServer:Buffer ManagerLazy writes/sec"
"SQLServer:Buffer ManagerPage life expectancy"
"SQLServer:Buffer ManagerPage reads/sec"
"SQLServer:Buffer ManagerPage writes/sec"
"SQLServer:Buffer ManagerStolen pages"
"SQLServer:General StatisticsLogins/sec"
"SQLServer:General StatisticsLogouts/sec"
"SQLServer:General StatisticsUser Connections"
"SQLServer:LatchesAverage Latch Wait Time (ms)"
"SQLServer:Locks(_Total)Average Wait Time (ms)"
"SQLServer:Locks(_Total)Lock Requests/sec"
"SQLServer:Locks(_Total)Number of Deadlocks/sec"
"SQLServer:Memory ManagerTarget Server Memory (KB)"
"SQLServer:Memory ManagerTotal Server Memory (KB)"
"SQLServer:SQL StatisticsBatch Requests/sec"
"SQLServer:SQL StatisticsSQL Compilations/sec"
"SQLServer:SQL StatisticsSQL Re-Compilations/sec"
"Paging File(_Total)% Usage"
"Paging File(_Total)% Usage Peak"
"PhysicalDisk(_Total)Avg. Disk Read Queue Length"
"PhysicalDisk(_Total)Avg. Disk sec/Read"
"PhysicalDisk(_Total)Avg. Disk sec/Transfer"
"PhysicalDisk(_Total)Avg. Disk sec/Write"
"PhysicalDisk(_Total)Avg. Disk Write Queue Length"
"Process(sqlservr)% Privileged Time"
"Process(sqlservr)% Processor Time"
"Processor(_Total)% Privileged Time"
"Processor(_Total)% Processor Time"
"SystemContext Switches/sec"
"SystemProcessor Queue Length"

查看所有计数器，可以把计数器导出到文件中选择;

typeperf -q > D:counters.txt

logman create counter SQL2012Perf -f bin  -b 12/03/2015 00:00:00  -e 12/04/2015 00:00:00  -si 05 -v mmddhhmm -o "D:SQL2012Perf" -cf "D:MSSQL2012BaseLineCounters.config"

create ：创建计数器收集程序
SQL2012Perf ：计数器名称
-f bin ： 二进制格式
-b 12/03/2015 00:00:00 ： 起始时间
-e 12/03/2015 15:00:00 ： 结束时间
-si 00:00:05 ： 间隔5秒记录一次
-v mmddhhmm ：文件格式
-o "D:SQL2012Perf" ：输出文件名
-cf "D:MSSQL2012BaseLineCounters.config" ：计数器配置文件

创建完成后并没有启动，可以使用以下命令查看状态、启动或关闭：

Logman query
Logman start SQL2012Perf
Logman stop SQL2012Perf

运行一段时间可进行停止，双击查看跟踪信息：

参考：Automate Performance Monitor Statistics Collection for SQL Server and Windows

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!