【监控实践】怎样获取windows性能计数器到sql server表中去（利用typePerf及excel）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【监控实践】怎样获取windows性能计数器到sql server表中去（利用typePerf及excel），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5600字，纯文字阅读大概需要9分钟。
内容图文
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性能计数器值
原文：https://www.cnblogs.com/gered/p/12774067.html
内容总结
以上是互联网集市为您收集整理的【监控实践】怎样获取windows性能计数器到sql server表中去（利用typePerf及excel）全部内容，希望文章能够帮你解决【监控实践】怎样获取windows性能计数器到sql server表中去（利用typePerf及excel）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。