SQL Server优化入门系列（三）—— 性能计数器(performance counter)
说明
Performance Counter是windows系统中通用的性能分析工具。Windows OS和SQL Server暴露了很多Performance Counter，可用户分析整个系统的运行情况。performance counter结合等待事件一起使用，能取得更好的优化效果。
在这篇文章中，我们将介绍：
1、如何使用工具查看performance counter
2、sql server常用的performance counter
3、windows常用的performance counter
使用工具查看Performance Counter
很多工具可以用来查看和分析performance counter，常用的方法：
使用perfmon程序
使用resmon程序
使用typeperf命令行工具
使用SQL Server Performance Counter DMV
perfmon
perfmon是windows下查看性能计数器的首选工具。我们可以使用perfmon
1、查看当前性能计数器
2、将性能计数器的数据采集下来，供后续分析
打开perfmon
可以通过管理工具 -> 性能监视器 打开perfmon，或者在命令行输入perfmon打开。
使用perfmon
比例：有的指标在显示到趋势图上时，有个比例。图上的纵坐标数值乘以比例，才是指标的真实数值。
选择显示方式
线条：方便查看指标的趋势
报告：方便查看指标当前值
添加计数器
选择指标
选定对象。有的计数器可选择_Total或指定实例
选择采样频率
性能监视器 -> 右键 -> 属性:
采样间隔
持续时间：perfmon 趋势图展示的时间长度
使用数据采集器
使用数据采集器，可以自定义数据采集任务，将性能数据采集到文件。便于事后分析问题。
resmon（资源监视器）
使用Resmon可以很方便的查看windows系统层面的资源使用情况。
资源监视器，可以在管理工具中找到。或者在命令行输入resmon打开。
通过性能监视器可以方便地查看windows系统层面的资源使用情况，包括：
CPU
内存
磁盘IO
打开的文件
网络流量和监听端口
typeperf
typeperf是查看性能计数器的命令行工具。一般比较少使用。
我们可以使用typepef查看和搜索指标
typeperf -qx | findstr "PhysicalDisk"
SQL Server Performance Counter DMV
SQL Server的Performance Counter也可以通过 sys.dm_os_performance_counters视图查询。
便于监控程序以SQL语句的方式获取指标：
select * from sys.dm_os_performance_counters
关键字段：
object_name: 计数器的类别。
counter_name：计数器名称
instance_name：实例名称。如数据库
cntr_value： 计数器的值
cntr_type：计数器类型。计数器类型对于指标计算非常关键，不同的类型需要使用不同的公式计算
cntr_type | 描述 | 例子 |
1073939712 | PERF_LARGE_RAW_BASED |
计算公式：这2个指标的差值相除， (n1 - n0) / (d1 - d0) |
1073874176 | PERF_AVERAGE_BULK | |
537003264 |
计算公式：取2个指标的当前值相除，乘以100%， 100.0 * n/d | |
65792 | PERF_COUNTER_LARGE_RAWCOUNT |
取当前值 |
272696576 | PERF_COUNTER_BULK_COUNT |
计算差值，n1 - n0 |
SQL Server常用Performance Counter
其中有的计数器，需要采集每一个instance的数据，如：
Databases：采集每一个数据库的相关指标
Workload Group Stats： 分别采集每一个workload group的指标
Object Name | Counter Name | 描述 |
General Statistics | Active Temp Tables | |
Temp Tables Creation Rate | ||
Logins/sec | ||
Connection Reset/sec | ||
Logouts/sec | ||
User Connections | ||
Logical Connections | ||
Transactions | ||
Processes blocked | ||
Access Method | Full Scans/sec | |
Range Scans/sec | ||
Probe Scans/sec | ||
Workfiles Created/sec | ||
Worktables Created/sec | ||
Forwarded Records/sec | ||
Index Searches/sec | ||
Page Splits/sec | ||
Buffer Manager | Buffer cache hit ratio | |
Page lookups/sec | ||
Database pages | ||
Target pages | ||
Lazy writes/sec | ||
Readahead pages/sec | ||
Page reads/sec | ||
Page writes/sec | ||
Checkpoint pages/sec | ||
Background writer pages/sec | ||
Page life expectancy | ||
Plan Cache | Cache Hit Ratio | |
SQL Statistics | Batch Requests/sec | |
Forced Parameterizations/sec | ||
Auto-Param Attempts/sec | ||
Failed Auto-Params/sec | ||
SQL Compilations/sec | ||
SQL Re-Compilations/sec | ||
Memory Manager | Total Server Memory (KB) | |
Database Cache Memory (KB) | ||
Free Memory (KB) | ||
Stolen Server Memory (KB) | ||
Lock Memory (KB) | ||
Log Pool Memory (KB) | ||
SQL Cache Memory (KB) | ||
Connection Memory (KB) | ||
Optimizer Memory (KB) | ||
Reserved Server Memory (KB) | ||
Memory Grants Outstanding | ||
Memory Grants Pending | ||
Lock | Average Wait Time (ms) | |
Lock Requests/sec | ||
Lock Timeouts/sec | ||
Lock Wait Time (ms) | ||
Lock Waits/sec | ||
Number of Deadlocks/sec | ||
Latch | Average Latch Wait Time (ms) | |
Latch Waits/sec | ||
Number of SuperLatches | ||
Total Latch Wait Time (ms) | ||
Database | Active Transactions | |
Data File(s) Size (KB) | ||
Log Bytes Flushed/sec | ||
Log File(s) Size (KB) | ||
Log File(s) Used Size (KB) | ||
Log Flush Wait Time | ||
Log Flush Waits/sec | ||
Log Flush Write Time (ms) | ||
Log Flushes/sec | ||
Percent Log Used | ||
Transactions/sec | ||
Write Transactions/sec | ||
Transaction | Free Space in tempdb (KB) | |
Longest Transaction Running Time | ||
Snapshot Transactions | ||
Transactions | ||
Version Cleanup rate (KB/s) | ||
Version Generation rate (KB/s) | ||
Version Store Size (KB) | ||
Workload Group Stats | Active parallel threads | |
Active requests | ||
Blocked tasks | ||
CPU usage % | ||
Queued requests | ||
Reduced memory grants/sec | ||
Requests completed/sec |
Windows常用performance counter
Object | Counter | 描述 |
LogicalDisk | % Free Space | |
Free Megabytes | ||
% Disk Time | ||
Avg. Disk Queue Length | ||
% Disk Read Time | ||
% Disk Write Time | ||
Avg. Disk sec/Read | ||
Avg. Disk sec/Write | ||
Disk Reads/sec | ||
Disk Writes/sec | ||
Disk Read Bytes/sec | ||
Disk Write Bytes/sec | ||
Memory | Page Faults/sec | |
Available Bytes | ||
Committed Bytes | ||
Pages/sec | ||
Page Reads/sec | ||
Pages Output/sec | ||
Page Writes/sec | ||
Cache Bytes | ||
System Code Resident Bytes | ||
System Driver Resident Bytes | ||
System Cache Resident Bytes | ||
% Committed Bytes In Use | ||
Available KBytes | ||
Free & Zero Page List Bytes | ||
Modified Page List Bytes | ||
Processor | % Processor Time | |
% User Time | ||
% Privileged Time | ||
Interrupts/sec | ||
% Interrupt Time | ||
% Idle Time | ||
System | File Read Operations/sec | |
File Write Operations/sec | ||
File Read Bytes/sec | ||
File Write Bytes/sec | ||
Context Switches/sec | ||
System Calls/sec | ||
System Up Time | ||
Processor Queue Length | ||
Processes | ||
Threads | ||
Network adaptor | Packets Received/sec | |
Packets Sent/sec | ||
Current Bandwidth | ||
Bytes Received/sec | ||
Packets Received Discarded | ||
Packets Received Errors | ||
Bytes Sent/sec | ||
Packets Outbound Discarded | ||
Packets Outbound Errors | ||
Output Queue Length |
