counters.dat_使用sys.dm_os_performance_counters对SQL Server问题进行故障排除

counters.dat

sys.dm_os_performance_counters (sys.dm_os_performance_counters )

sys.dm_os_performance_counters是一个系统动态管理视图（DMV），它为每个SQL Server性能计数器返回一行。 这对于获取有关当前性能计数器值的信息很有用。 除了其他操作系统计数器之外，这些计数器值还显示在Windows Performance Monitor中

The permission needed to query this view is VIEW SERVER STATE

查询此视图所需的权限是VIEW SERVER STATE

However, the values the view returns can be confusing and misleading if not properly interpreted, as they can significantly vary from the values considered as normal for your SQL Server. That’s why it’s necessary to understand all the columns the view returns, especially the counter type

但是，如果未正确解释，则视图返回的值可能会造成混淆和误导，因为它们可能与您SQL Server认为正常的值有很大差异。 这就是为什么必须了解视图返回的所有列，尤其是计数器类型的原因

The columns returned by the view are:

该视图返回的列为：

• object_name – this is the counter category name – Memory Node, Exec Statistics, Broker TO Statistics, Query Execution, Latches, Memory Manager, and more
  object_name –这是计数器类别名称–内存节点，执行统计，代理TO统计，查询执行，闩锁，内存管理器等
• counter_name – Target Server Memory (KB), Buffer cache hit ratio, Free list stalls/sec, etc.
  counter_name –目标服务器内存（KB），缓冲区高速缓存命中率，空闲列表停顿/秒等。
• instance_name – the counter instance name, usually this is the database name
  instance_name –计数器实例名称，通常是数据库名称
• cntr_value – cumulative for counters where the unit is a second. This means that sampling is required in order to calculate the counter value per one second
  cntr_value –单位为秒的计数器的累积值。 这意味着需要采样才能计算每秒的计数器值
• cntr_type – specified the type of counter, based on its value the current value is calculated
  cntr_type –指定计数器的类型，根据其值计算当前值

sys.dm_os_performance_counters视图提供什么信息？ (What information does the sys.dm_os_performance_counters view provide?)

If you’re using the view for the first time, to find the SQL Server performance counters that can be tracked, run:

如果您是第一次使用该视图，请查找可跟踪SQL Server性能计数器：

 
SELECT DISTINCT [object_name]
FROM sys.dm_os_performance_counters
    

There are 33 different counter categories monitored in SQL Server 2012. All object names start with MSSQL$ , e.g. MSSQL$SQL2012:Memory Manager. Each category has multiple counters – there are Free Memory (KB), Lock Memory (KB), Memory Grants Pending, Target Server Memory (KB), Total Server Memory (KB), and many more in the Memory Manager category

SQL Server 2012中监视33种不同的计数器类别。所有对象名称均以MSSQL $开头 ，例如MSSQL $ SQL2012：Memory Manager。 每个类别都有多个计数器– 内存管理器类别中有可用 内存（KB），锁定内存（KB），待批内存授予，目标服务器内存（KB），总服务器内存（KB），以及更多其他计数器

Some counters are repeated in different categories, e.g. Cache Hit Ratio appears in the Plan Cache, Cursor Manager by Type, and Catalog Metadata categories. Also, some of the counters have multiple instances – e.g. Log File(S) Used Size (KB) has an instance for each database on the SQL Server instance

有些计数器在不同的类别中重复，例如，“缓存命中率”出现在“计划缓存”，“按类型划分的游标管理器”和“目录元数据”类别中。 另外，某些计数器具有多个实例–例如， 日志文件已用大小（KB）在SQL Server实例上为每个数据库都有一个实例。

In total there are 405 different counters

总共有405个不同的柜台

 
SELECT DISTINCT [counter_name]
FROM sys.dm_os_performance_counters
    

The information that is usually overlooked, but that requires attention is the cntr_types column. It defines the type of the counter and thus the method that should be used to calculate the current counter value. If the counter type is misinterpreted, the values will be incorrectly calculated, or taken as is, without any calculation, which will result in having a wrong picture about SQL Server performance

通常会忽略但需要注意的信息是cntr_types列。 它定义了计数器的类型，从而定义了应用于计算当前计数器值的方法。 如果计数器类型被误解，则将不正确地计算或按原样计算值，而不进行任何计算，这将导致对SQL Server性能的了解不正确。

The cntr_types column can have five different values

cntr_types列可以具有五个不同的值

PERF_COUNTER_LARGE_RAWCOUNT (PERF_COUNTER_LARGE_RAWCOUNT)

The cntr_types column value for the PERF_COUNTER_LARGE_RAWCOUNT counter type is 65792. These counters show the last observed, not the average value. It’s usually used to monitor object counts

PERF_COUNTER_LARGE_RAWCOUNT计数器类型的cntr_types列值为65792。这些计数器显示最后观察到的值，而不是平均值。 通常用于监视对象计数

This means that if you’re monitoring a counter type 65792, the value you get in the counter_value column when you query the view is the current value of the counter and no additional calculation is required

这意味着，如果您正在监视计数器类型65792，则在查询视图时在counter_value列中获得的值是计数器的当前值，因此不需要进行其他计算

To find out the current values for the Buffer Manager Page life expectancy, execute:

要找出“ 缓冲区管理器页面预期寿命”的当前值，请执行：

 
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Life expectancy'
	    AND object_name LIKE '%buffer manager%';
    

The Page life expectancy is 47, no additional calculation is needed

网页的预期寿命为47，无需进行其他计算

Counters of this type are: General Statistics User connections, Buffer Manager Page life expectancy and Database pages, Databases – Data and Log file size (KB), Log file used size (KB), Percent Log used, Memory Manager – Free Memory (KB), and more

这种类型的计数器是： 常规统计信息用户连接，缓冲区管理器页面预期寿命和数据库页面，数据库–数据和日志文件大小（KB），使用的日志文件大小（KB），使用的日志百分比，内存管理器–可用内存（KB） ），以及更多

PERF_LARGE_RAW_BASE (PERF_LARGE_RAW_BASE)

The cntr_types column value for the PERF_LARGE_RAW_BASE counter type is 1073939712. These counters collect the last observed value

PERF_LARGE_RAW_BASE计数器类型的cntr_types列值为1073939712。这些计数器收集最后观察到的值

This counter value is used as the denominator for further calculation. The counters of this type are only used to calculate other counters available via the view

此计数器值用作进一步计算的分母。 这种类型的计数器仅用于计算通过视图可用的其他计数器

All counters that belong to this counter type have the word base in their names, so it’s a clear indication that this is not a counter that provides useful info, it’s just a base value for further calculations

属于此计数器类型的所有计数器名称中都带有单词base，因此可以清楚地表明这不是提供有用信息的计数器，它只是进行进一步计算的基础值

 
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
    

These counters are: Buffer Cache Hit Ratio Base, Log Cache Hit Ratio Base, Average Latch Wait Time Base, Cache Hit Ratio Base, CPU usage % base, and more

这些计数器是： 缓冲区高速缓存命中率基准，日志高速缓存命中率基准，平均锁存等待时间基数，高速缓存命中率基准，CPU使用率百分数等。

PERF_AVERAGE_BULK (PERF_AVERAGE_BULK)

The cntr_types column value for the PERF_AVERAGE_BULK counter type is 1073874176. The cntr_value column value is cumulative. To calculate the current value of the counter, you have to monitor the PERF_AVERAGE_BULK and its corresponding PERF_LARGE_RAW_BASE counter, take two samples of each at the same time, and use these values for the calculation

对于PERF_AVERAGE_BULK计数器类型cntr_types列值是1073874176.的cntr_value列值是累积的。 要计算计数器的当前值，必须监视PERF_AVERAGE_BULK及其对应的PERF_LARGE_RAW_BASE计数器，同时分别取两个样本，并将这些值用于计算

The formula for the current metric value is:

当前度量标准值的公式为：

• (A2-A1)/(B2-B1)
  （A2-A1）/（B2-B1）
• Where
  哪里
• 
  B1 and B2 are the values of the monitored PERF_LARGE_RAW_BASE counter taken at sample times T1 and T2
  B1和B2是在采样时间T1和T2上获取的监视的PERF_LARGE_RAW_BASE计数器的值

In this example, we’ll use Average Wait Time (ms) and Average Wait Time Base values. You can use any two counters one of the 1073939712 type and the other 1073874176, that have identical names except for the word base: Update conflict ratio base and Update conflict ratio, Avg. Time to Write Batch Base and Avg. Time to Write Batch (ms) , Avg. Time Between Batches Base and Avg. Time Between Batches (ms), etc.

在此示例中，我们将使用平均等待时间（ms）和平均等待时间基准值。 您可以使用两个计数器之一，它们的名称分别为1073939712类型和另一个1073874176，但单词基数除外： 更新冲突率基准和更新冲突率平均。 是时候写批次基础和平均 批量写入时间（ms），平均 批次基本和平均之间的时间。 批次之间的时间（毫秒）等

 
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Average Wait Time%'
	    AND instance_name = 'database'
    

The values taken at T1

T1处的值

The values taken at T2

T2处的值

Average Wait Time (ms) for the interval between these two measurements is:

这两次测量之间的时间间隔的平均等待时间（毫秒）为：

    (53736 ms -52939 ms)/(23-18) = 797 ms / 5 = 159.4 ms

（53736毫秒-52939毫秒）/（23-18）= 797毫秒/ 5 = 159.4毫秒

Counters of this type are: Average Wait Time (ms), Average Latch Wait Time (ms), Update conflict ratio, Avg. Length of Batched Writes, Avg. Time to Write Batch (ms), Avg. Time Between Batches (ms), and more

这种类型的计数器是： 平均等待时间（ms），平均锁存等待时间（ms），更新冲突率，平均。 批量写入的长度，平均 批量写入时间（毫秒），平均 批次之间的时间（ms）等

PERF_LARGE_RAW_FRACTION (PERF_LARGE_RAW_FRACTION)

The cntr_types column value for the PERF_LARGE_RAW_FRACTION counter type is 537003264. These counters show a ratio, i.e. fraction between two values – the PERF_LARGE_RAW_FRACTION counter and its corresponding PERF_LARGE_RAW_BASE counter value

PERF_LARGE_RAW_FRACTION计数器类型的cntr_types列值为537003264。这些计数器显示比率，即两个值之间的分数– PERF_LARGE_RAW_FRACTION计数器及其对应的PERF_LARGE_RAW_BASE计数器值

Again, additional calculation is needed to find out the value that can be used for monitoring and troubleshooting performance issues. The ratio is presented in percents

同样，还需要进行其他计算才能找出可用于监视和解决性能问题的值。 该比率以百分比表示

 
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
    

Buffer Cache Hit Ratio shows how SQL Server utilizes buffer cache and it is the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests. The recommended values are higher than 90%. The maximal possible value is 100% – when SQL Server reads all pages from the buffer cache and none from disk

缓冲区高速缓存命中率显示了SQL Server如何利用缓冲区高速缓存，它是从SQL Server缓冲区高速缓存中发现和读取的数据页与所有数据页请求的比率。 推荐值高于90％。 可能的最大值是100％–当SQL Server从缓冲区缓存中读取所有页面而从磁盘中读取所有页面时

Knowing the counter definition and possible values can help if you’re not sure what the counter type is, or to check whether the calculated value falls into a possible value range. For this example, the counter shows that Buffer Cache Hit Ratio is 2,135%, which is not possible

如果您不确定计数器的类型，或者检查计算的值是否落在可能的值范围内，了解计数器的定义和可能的值会有所帮助。 对于此示例，计数器显示缓冲区高速缓存命中率为2135％，这是不可能的

To calculate Buffer Cache Hit Ratio, we’ll use the counter value of the Buffer Cache Hit Ratio counter and the Buffer Cache Hit Ratio Base value show in in the example for the PERF_LARGE_RAW_BASE counter type above

为了计算缓冲区高速缓存命中率 ，我们将使用上面示例中针对PERF_LARGE_RAW_BASE计数器类型的示例中显示的缓冲区高速缓存命中率计数器的计数器值和缓冲区高速缓存命中率基准值

• Buffer Cache Hit Ratio % = 100 * 缓冲区缓存命中率 ％= 100 * Buffer Manager\Buffer Cache Hit Ratio / Buffer Manager\Buffer Cache Hit Ratio Base缓冲区管理器\缓冲区缓存命中率/缓冲区管理器\缓冲区缓存命中率基准
•                                         = 100 * 2,135 / 3,573
  = 100 * 2,135 / 3,573
•                                         = 59.75%
  = 59.75％

The counters of this type are: Buffer Cache Hit Ratio, Log Cache Hit Ratio, Worktables From Cache Ratio, Cache Hit Ratio, CPU usage %, and Rem Req Cache Hit Ratio

这种类型的计数器是： 缓冲区高速缓存命中率，日志高速缓存命中率，来自高速缓存的工作表比率，高速缓存命中率，CPU使用率百分比和Rem Req高速缓存命中率

PERF_COUNTER_BULK_COUNT (PERF_COUNTER_BULK_COUNT)

The cntr_types column value for the PERF_COUNTER_BULK_COUNT counter type is 272696576. The value these counters show is cumulative, it’s accumulated values since the last SQL Server instance restart, so to get their real value sampling is required, the same as for the PERF_AVERAGE_BULK counter type

PERF_COUNTER_BULK_COUNT计数器类型的cntr_types列值为272696576。这些计数器显示的值是累积值，是自上次SQL Server实例重新启动以来的累积值，因此需要获取其真实值采样，与PERF_AVERAGE_BULK计数器类型相同

However, here is important to know how long the sample period is. Otherwise, you would not be able to calculate the value per second. Usually a 5-minute period is used

但是，了解采样周期有多长很重要。 否则，您将无法计算每秒的值。 通常使用5分钟

To calculate per second rate, calculate the difference between two sample values and divide it by the number of seconds between the samples

要计算每秒速率，请计算两个样本值之间的差，然后将其除以样本之间的秒数

The formula for the current metric value is

当前度量标准值的公式为

• A2-A1)/(T2-T1)
  A2-A1）/（T2-T1）
• Where
  哪里
• A1 and A2 are the values of the monitored PERF_COUNTER_BULK_COUNT counter taken at sample times T1 and T2
  A1和A2是在采样时间T1和T2上获取的监视的PERF_COUNTER_BULK_COUNT计数器的值
• T1 and T2 are the times when the sample values are taken
  T1和T2是获取样本值的时间

There are several methods to do this. The first one uses the DELAY T-SQL statement, where the time between sampling is defined. In this example, it is 10 seconds

有几种方法可以做到这一点。 第一个使用DELAY T-SQL语句，其中定义了采样之间的时间。 在这个例子中，是10秒

 
DECLARE @PageLookups1 BIGINT;
 
SELECT @PageLookups1 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec';
 
WAITFOR DELAY '00:00:10';
 
SELECT (cntr_value - @PageLookups1) / 10 AS 'Page lookups/sec'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec';
    

Another method is to use get the ms_ticks value (number of milliseconds since the machine was started) from the sys.dm_os_sys_info Dynamic Management View at the same time when the counter values are taken

另一种方法是在获取计数器值的同时使用sys.dm_os_sys_info动态管理视图中的获取ms_ticks值（自启动计算机以来的毫秒数）。

 
SELECT ms_ticks
FROM sys.dm_os_sys_info;
 
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec';
    

Based on the values obtained, the Page lookups/sec value is calculated as:

基于获得的值，“页面查找/秒”值的计算公式为：

• Page lookups/sec = (854,521 – 852,433)/(621,366,686-621,303,043) = 2,088 / 63,643 ms
  页面查找/秒=（854,521 – 852,433）/（621,366,686-621,303,043）= 2,088 / 63,643毫秒
•                              = 2,088/63 sec = 32.1 /sec
  = 2,088 / 63秒= 32.1 /秒

The Getdate () statement and any other method for determining time difference can be used

可以使用Getdate（）语句和任何其他确定时差的方法

The counters are: Page lookups/sec, Free list stalls/sec, Lazy writes/sec, Page reads/sec, Page writes/sec, Logins/sec, and more

计数器是： 页面查找/秒，空闲列表停顿/秒，延迟写入/秒，页面读取/秒，页面写入/秒，登录/秒等

The view is easy to use with almost none coding knowledge. It’s available in all SQL Server editions and gives the results instantly. With additional coding, you can periodically query the view and store captured data in repository table(s). You can use this info later for various analyses and creating a custom monitoring solution. Keep in mind that the time needed for creating a custom monitoring solution may be more expensive than a third party tool

该视图易于使用，几乎没有任何编码知识。 它在所有SQL Server版本中都可用，并立即提供结果。 使用其他编码，您可以定期查询视图并将捕获的数据存储在存储库表中。 您以后可以使用此信息进行各种分析并创建自定义监视解决方案。 请记住，创建自定义监视解决方案所需的时间可能比第三方工具更昂贵

Ty to be able to use the view for SQL Server performance monitoring and troubleshooting, it is necessary to understand the existing counter types, what each type represents, and how to calculate the current value. Otherwise, the counter values returned can be misleading and useless. As shown in this article, there are five different counter types, some of which require sampling and calculation. Other disadvantages of using this view for performance monitoring are that no graphical presentation of the performance parameters and continuous monitoring solutions are available out-of-the-box

为了能够使用该视图进行SQL Server性能监视和故障排除，必须了解现有的计数器类型，每种类型代表什么以及如何计算当前值。 否则，返回的计数器值可能会产生误导且无用。 如本文所示，有五种不同的计数器类型，其中一些需要采样和计算。 使用此视图进行性能监控的其他缺点是无法以图形方式显示性能参数和连续监控解决方案

翻译自: https://www.sqlshack.com/troubleshooting-sql-server-issues-sys-dm_os_performance_counters/

counters.dat