Capturing Windows Performance Counters for SQL Server
【1】SQL Server Performance Counters
使用下面的性能计数器列表来在线查看sql server性能指标。
- Memory Available Mbytes (already added above)
- Memory Page Faults/sec (already added above)
- Memory Page Reads/sec (already added above)
- Memory Page Writes/sec (already added above)
- Paging File % Usage (_Total) (already added above)
- Processor % Privileged Time (_Total)
- Processor % Processor Time (_Total)
- Processor % User Time (_Total)
- Physical Disk % Idle Time (_Total)
- Physical Disk Avg. Disk Queue Length (_Total)
- Physical Disk Avg. Disk sec/Read (_Total)
- Physical Disk Avg. Disk sec/Transfer (_Total)
- Physical Disk Avg. Disk sec/Write (_Total)
- SQL Server: Access Methods/Page Splits/sec
- SQL Server: Buffer Manager Buffer Cache Hit Ratio
- SQL Server: Buffer Manager Page Life Expectancy
- SQL Server: Buffer Manager Page Lookups/sec
- SQL Server: Buffer Manager Page Reads/sec
- SQL Server: Buffer Manager Page Writes/sec
- SQL Server: General Statistics User Connections
- SQL Server: Memory Manager Memory Grants Pending
- SQL Server: SQL Statistics Batch Requests/sec
- SQL Server: SQL Statistics SQL Compilations/sec
- SQL Server: SQL Statistics SQL Recompilations/sec
- SQL Server: Wait Statistics Memory Grant Queue Waits <all instances>
- SQL Server: Wait Statistics Network IO Waits <all instances>
- SQL Server: Wait Statistics Non-Page Latch Waits <all instances>
- SQL Server: Wait Statistics Page IO Latch Waits <all instances>
- SQL Server: Wait Statistics Page Latch Waits <all instances>
- System Processor Queue Length
【2】TypePerf
Microsoft provides a command line utility “TypePerf” which allows capturing counters from a remote system to a monitoring system and logging these either to the command window or a log file.
More information about the TypePerf command can be found at:
https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_typeperf.mspx?mfr=true
【2.1】使用typeperf以及应用一个输入文件（Using TypePerf with an Input File）
《1》创建一个 ‘TypePerf‘ 文件夹（Create a folder on the monitoring server named “TypePerf”.）
《2》在 TypePerf 文件夹下 创建一个 TypePerf_SQLCounters.txt 文件（Create a new file in the TypePerf folder named TypePerf_SQLCounters.txt.）
《3》把下列内容编辑保存到 TypePerf_SQLCounters.txt 文件中去（Edit the TypePerf_SQLCounters.txt file by copying the following contents into the file:）
\\SERVERNAME\Memory\Available Bytes \\SERVERNAME\Memory\Page Reads/sec \\SERVERNAME\Memory\Page Writes/sec \\SERVERNAME\Paging File(_Total)\% Usage \\SERVERNAME\PhysicalDisk(_Total)\% Disk Time \\SERVERNAME\PhysicalDisk(_Total)\Avg. Disk Queue Length \\SERVERNAME\PhysicalDisk(_Total)\Avg. Disk sec/Read \\SERVERNAME\PhysicalDisk(_Total)\Avg. Disk sec/Transfer \\SERVERNAME\PhysicalDisk(_Total)\Avg. Disk sec/Write \\SERVERNAME\Processor(_Total)\% Privileged Time \\SERVERNAME\Processor(_Total)\% Processor Time \\SERVERNAME\Processor(_Total)\% User Time \\SERVERNAME\SQLServer:Access Methods\Page Splits/sec \\SERVERNAME\SQLServer:Buffer Manager\Buffer cache hit ratio \\SERVERNAME\SQLServer:Buffer Manager\Page life expectancy \\SERVERNAME\SQLServer:Buffer Manager\Page lookups/sec \\SERVERNAME\SQLServer:General Statistics\User Connections \\SERVERNAME\SQLServer:Memory Manager\Memory Grants Pending \\SERVERNAME\SQLServer:SQL Statistics\Batch Requests/sec \\SERVERNAME\SQLServer:SQL Statistics\SQL Compilations/sec \\SERVERNAME\SQLServer:SQL Statistics\SQL Re-Compilations/sec \\SERVERNAME\SQLServer:Wait Statistics(*)\Memory grant queue waits \\SERVERNAME\SQLServer:Wait Statistics(*)\Network IO waits \\SERVERNAME\SQLServer:Wait Statistics(*)\Page IO latch waits \\SERVERNAME\SQLServer:Wait Statistics(*)\Page latch waits \\SERVERNAME\System\Processor Queue Length
【2.2】使用实例名 替换配置文件中的 SERVERNAME
在 TypePerf_SQLCounters.txt 文件中使用【实例名】替换配置文件中的 SERVERNAME,如上图
Perform a global replace of the SERVERNAME to match the actual server name for the SQL Server that is to be monitored. Save the file and Close.
【2.3】实际演示
--------------------------------------------------------------分割线---------------------------------------------------------------------------
Go to the Start menu -> Command Prompt.
Change the directory to the location where the TypePerf directory was created.
模板代码：At the command prompt, modify the next command to match the information for the SQL Server being monitored:
TYPEPERF -cf F:\TypePerf\TypePerf_SQLCounters.txt -f csv -o F:\TypePerf\MSLAB2191_yyyymmdd_hhmm.csv -s BRTC_MSLAB2191
-cf 包含要捕获的计数器列表的文件的名称（Name of the file that contains the list of counters to capture）
-f 格式化捕获内容，默认是CSV存储格式（format of the log file, defaults to csv）
-o 输出捕获信息到指定文件（Path\name of output file）
-s 在计算器代码中没有指定服务器的情况下监控操作的服务器（Name of server to collect the counters from if one does not exist in the counter file）
实际演示代码：
TYPEPERF -cf F:\TypePerf\TypePerf_SQLCounters.txt -f csv -o F:\TypePerf\MSLAB2191_20161020_2025.csv -s BRTC_MSLAB2191
回车运行代码.......Press Enter to start the counter collection…
The [\] will run the command and capture the counters every second.
Verify the file you specified for the output was created and is growing:
-------------------------------------------------------分割线-----------------------------------------------------------------------
一般不会自动停止，你要是想停止就按ctrl+c.................When you have completed the SQL Server test, Press CTRL-C to stop the counter collection.
Close the Command Prompt window.
【参考文档】
用Excel创建SQL server性能报告：https://www.cnblogs.com/Jadie/p/6117165.html
本文部署阶段大部分转自：怎样获取Windows平台下SQL server性能计数器值