为什么SQL Server实例处在压力下
翻译自：https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/
当你在一个SQL Server实例上遇到性能问题，有些度量指标足够告诉你本质，你随后可以快速专注于实际原因上。有很多不同的性能指标可以用来理解你的SQL Server实例怎么样了。这些指标中的每个会给你一个积极暗示。也许有内部或外部内存压力、过度的或不规则的CPU负载，或者IO瓶颈。如果你获得一个寻找问题的积极暗示，你随后可以深入详情。例如，如果你确定在SQL Server里有过度的CPU压力，接下来需要确定哪个查询导致了大多数CPU。
那么这些常用暗示是什么呢？对于获得有关总处理器时间，服务器上CPU的利用率，或者从磁盘读取数据的时间花费的平均读取时间的直接的信息是有用的。当你完成了这些，然后，有其他找出系统上发生什么的方法是不容易的，它们甚至提供了更有用的信息。让我们更详细的浏览这些方法中的一些。
是否你的实例经受着内存压力？
sys.dm_os_ring_buffers
操作系统内的环形缓冲是特定系统信息类型的一个收集点。大部分是鲜为人知的系统信息，它们中的很多与环形缓冲自己的管理有关，但是缓冲信息中的一些极其有趣。例如，操作系统意识到它运行在低内存下时，有一条信息记录到环形缓冲里，如果有一个内存警告，你可以通过使用DMV对象sys.dm_os_ring_buffers来找出来。
实际上有两类内存警告。对于正在运行的机器的物理内存，你可以得到一个内存警告。从SQL Server的角度来讲，这被称为外部内存，因为它不是SQL Server服务管理的内存。你也获得虚拟内存警告，这个内存正被SQL Server服务管理。我们常称为内部内存。当任何一个运行低时，你可以看到一个警告记录到了环形缓冲里。当你有足够的内存或者有一个大的内存增长，你也可以看到警告。
你只需要像这样查询这个DMV：
|
1
|
SELECT
*
FROM
sys.dm_os_ring_buffers
AS
dorb;
|
那将返回可用信息，你很快发现大多数返回的增长信息是在“record”列。这是一个存储XML的文本列，你可以使用以下更加深入的查询从这里获取感兴趣的信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
WITH
RingBuffer
AS
(
SELECT
CAST
(dorb.record
AS
XML)
AS
xRecord,
dorb.
TIMESTAMP
FROM
sys.dm_os_ring_buffers
AS
dorb
WHERE
dorb.ring_buffer_type =
'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT
xr.value(
'(ResourceMonitor/Notification)[1]'
,
'varchar(75)'
)
AS
RmNotification,
xr.value(
'(ResourceMonitor/IndicatorsProcess)[1]'
,
'tinyint'
)
AS
IndicatorsProcess,
xr.value(
'(ResourceMonitor/IndicatorsSystem)[1]'
,
'tinyint'
)
AS
IndicatorsSystem,
DATEADD(ss,
(-1 * ((dosi.cpu_ticks /
CONVERT
(
FLOAT
, (dosi.cpu_ticks / dosi.ms_ticks)))
- rb.
TIMESTAMP
) / 1000), GETDATE())
AS
RmDateTime,
xr.value(
'(MemoryNode/TargetMemory)[1]'
,
'bigint'
)
AS
TargetMemory,
xr.value(
'(MemoryNode/ReserveMemory)[1]'
,
'bigint'
)
AS
ReserveMemory,
xr.value(
'(MemoryNode/CommittedMemory)[1]'
,
'bigint'
)
AS
CommitedMemory,
xr.value(
'(MemoryNode/SharedMemory)[1]'
,
'bigint'
)
AS
SharedMemory,
xr.value(
'(MemoryNode/PagesMemory)[1]'
,
'bigint'
)
AS
PagesMemory,
xr.value(
'(MemoryRecord/MemoryUtilization)[1]'
,
'bigint'
)
AS
MemoryUtilization,
xr.value(
'(MemoryRecord/TotalPhysicalMemory)[1]'
,
'bigint'
)
AS
TotalPhysicalMemory,
xr.value(
'(MemoryRecord/AvailablePhysicalMemory)[1]'
,
'bigint'
)
AS
AvailablePhysicalMemory,
xr.value(
'(MemoryRecord/TotalPageFile)[1]'
,
'bigint'
)
AS
TotalPageFile,
xr.value(
'(MemoryRecord/AvailablePageFile)[1]'
,
'bigint'
)
AS
AvailablePageFile,
xr.value(
'(MemoryRecord/TotalVirtualAddressSpace)[1]'
,
'bigint'
)
AS
TotalVirtualAddressSpace,
xr.value(
'(MemoryRecord/AvailableVirtualAddressSpace)[1]'
,
'bigint'
)
AS
AvailableVirtualAddressSpace,
xr.value(
'(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]'
,
'bigint'
)
AS
AvailableExtendedVirtualAddressSpace
FROM
RingBuffer
AS
rb
CROSS
APPLY rb.xRecord.nodes(
'Record'
) record (xr)
CROSS
JOIN
sys.dm_os_sys_info
AS
dosi
ORDER
BY
RmDateTime
DESC
;
|
使用这个查询，我首先创建了一个叫做RingBuffer的公用表表达式（CTE）。在那里我只做了两件事，首先过滤了一个特定的ring buffer类型“RING_BUFFER_RESOURCE_MONITOR”。这里是产生内存信息的地方。第二，我将“Record”列从文本转换为XML。在那我使用CTE查询，并使用XQuery命令从XML数据获取所有感兴趣的信息。
作为一个额外因素，在sys.dm_os_ring_buffers里的timestamp列实际上是一个datetime值，但是它是基于CPU频率，因此你得使用那个公式来将数据转换为可读的date和time。
使用sys.dm_os_buffers作为监控进程的一部分，你只需查找这两个事件，RESOURCE_MEMPHYSICAL_LOW或RESOURCE_MEMVIRTUAL_LOW。这些是在XML里可用的ResourceMonitor/Notification属性。他们是机器上低内存条件的一个绝对指标，所以如果你获得了警告，你要么外部的/OS/物理内存低，要么内部的/SQL Server/虚拟内存低。
是否系统在负载之下？
一个问题随之而来，“是否系统在负载之下？”
有大量不同的方法尝试理解这个，但是只有一些让你肯定的知道是否你处于压力下。
sys.dm_os_workers
我最喜欢的精准确定系统中有多少工作进程的方法之一是查看sys.dm_os_workers。这个度量指标不会告诉你导致系统负载的是什么，它也不会允许你理解负载的影响是什么。然而，它是系统上负载的完美衡量。
从sys.dm_os_workers返回了大量的信息。这个DMV返回操作系统上工作进程的信息。你可以查看关于进程的信息，像最后等待类型，是否工作进程有异常，它经历了多少次上下文切换，各种东西。联机帮助文档里的DMV甚至显示了如何确定一个在可运行状态的进程有多长时间。
只是使用它作为一个负载的度量，你的查询极其容易：
|
1
2
3
|
SELECT
COUNT
(*)
FROM
sys.dm_os_workers
AS
dow
WHERE
state =
'RUNNING'
;
|
这真的有那么简单。随着数量上下，系统上的负载也上下。今天有大量的昨天没有的“RUNNING”工作进程，你系统上有负载增加。但是牢记你需要比较一段时间。只捕获一个数字不能说明问题。你得能够在两个值间比较。
sys.dm_os_schedulers
衡量系统负载的另一个方法是查看调度器。这是管理工作进程的进程。再次，这是系统上负载的绝对度量。它可以告诉你系统上有多少工作正在做。
查看调度器产生了大量有关系统正被管理的感兴趣的信息。你可以看到多少工作进程正被一个特定的调度器所处理。你可以看到调度器退让CPU的次数（放弃访问另一个进程，因为每个进程只获得对CPU限制的访问），调度器里大量当前活动的工作进程和一些其他详情。
但是，去看一个负载的度量，你可以运行一个非常简单的查询：
|
1
2
3
|
SELECT
COUNT
(*)
FROM
sys.dm_os_schedulers
AS
dos
WHERE
dos.is_idle = 0;
|
再次，这个数字只有与之前的值比较时才有意义。使用工作进程或者调度器作为工作负载的度量与你的极限数据一样准确，但如果你维护着这些值一段时间的集合，你可以确定操作系统上的负载。
SQL Server有足够的内存么？
DBCC MEMORYSTATUS
这简直是一个令人惊喜的数据集合。你所获得的是在SQL Server里各种内存管理的输出。你可以看到在SQL Server里分配和管理的每位内存。当你与来自微软的客户支持工程师处理特定问题的故障排除时，这个命令频繁使用。但是，这是另一个精准确定系统上内存工作得怎么样的方式。
如果你只运行这个命令：
|
1
|
DBCC MEMORYSTATUS();
|
你会看到SQL Server里所有的各种内存分配和管理进程。它们全部。事实上，有如此多的信息很快变成没有意义的尝试。好消息是，好消息是可以面向一些特定的信息片段。如果我们专门追求Target Committed值和Current Committed值，我们可以确定是否SQL Server有足够内存。很简单。如果Target值高于Current值，在SQL Server里你没有需要的内存。但是获得这些值有点头疼。这里有个方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
DECLARE
@MemStat
TABLE
(ValueName SYSNAME,
Val
BIGINT
);
INSERT
INTO
@MemStat
EXEC
(
'DBCC MEMORYSTATUS() WITH TABLERESULTS'
);
WITH
Measures
AS
(
SELECT
TOP
2
CurrentValue,
ROW_NUMBER() OVER (
ORDER
BY
OrderColumn)
AS
RowOrder
FROM
(
SELECT
CASE
WHEN
(ms.ValueName =
'Target Committed'
)
THEN
ms.Val
WHEN
(ms.ValueName =
'Current Committed'
)
THEN
ms.Val
END
AS
'CurrentValue'
,
0
AS
'OrderColumn'
FROM
@MemStat
AS
ms
)
AS
MemStatus
WHERE
CurrentValue
IS
NOT
NULL
)
SELECT
TargetMem.CurrentValue - CurrentMem.CurrentValue
FROM
Measures
AS
TargetMem
JOIN
Measures
AS
CurrentMem
ON
TargetMem.RowOrder + 1 = CurrentMem.RowOrder;
|
我创建了一个表变量，然后使用TABLERESULTS从MEMORYSTATUS导入所有的输出，确保输出是一个表。通过使用公共表表达式（CTE）来定义从表变量选择的信息，我可以使用SELECT语句并基于ROW_NUMBER来JOIN着两个值来参照它两次。它真的有用。如果获得了一个负值，查看内存问题。
理解DBCC MEMORYSTATUS是指一个微软的支持机制。它不是标准工具集的一部分。这就意味着它完全是针对SQL Server从一个版本到下一个，或者甚至一个补丁包到下一个的未公布的修改。有了这一深刻的理解立刻使用它来诊断内存问题。
是否我需要一个更好、更快的磁盘系统？
sys.dm_io_virtual_file_stats
这个动态管理视图返回了你的数据库上文件行为的统计。这里最感兴趣的信息片段是stalls、waits、gathered和available。如果你简单运行这个查询：
|
1
2
|
SELECT
*
FROM
sys.dm_io_virtual_file_stats(DB_ID(DB_NAME()),
NULL
)
AS
divfs;
|
你必需传给它两个信息：数据库ID，通过使用DB_NAME来识别当前附加上的数据库，然后传给DB_ID；和文件ID，我可以传递NULL作为参数，将返回数据的所有文件。
返回的信息是非常棒的，尤其这四列：sample_ms、io_stall_read_ms、io_stall_write_ms和io_stall。让我们看看这些代表什么，你讲很快理解到这些对于作为DBA的你多么感兴趣。sample_ms是非常直接的。从上次SQL Server重启后的时间。它提供了理解所有其他值的度量信息。下一个是io_stall_read_ms。这表示进程被迫从这个设备上等待读操作的时间总量。如果你结合is_stall_read_ms和sample_ms，你将会获得你的应用程序正从一个特定数据库的一个特定文件上等待读操作的时间百分比的精确度量。你也得到is_stall_write_ms，它表示进程已等待写操作的时间总量。你可以收集这些性能指标一段时间来查看它如何增长，或者与读操作一样，使用sample_ms用相同的方法。最后，io_stall显示了对于任何io操作，发生在那个文件上的等待时间的总量。再次，你可以收集一段时间来看它是如何增长的（因为它永远只会增长）或者你可以通过比较sample_ms来获得等待磁盘的时间百分比。
这些方法会准确告诉你，关于系统上的io等待问题如何严峻。但是它们不能定位特定的查询。相反，这个方法主要专注于确定是否你的系统有问题。你需要更多磁盘、更快的磁盘等。
CPU执行的怎样？
sys.dm_os_wait_stats
我将它列为一个收集性能指标的鲜为人知的方法，但是它真的不是。迄今为止每个人听到理解服务器正等待什么是理解什么导致服务器运行慢的好方法。但是我仍然看到很多人惊讶于他们可以找到这个信息。
sys.dm_os_wait_stats显示了自从上次它启动以来（或者从等待统计信息被清理以来）服务器正等待什么的一个聚合视图。这个信息分解为特定的等待类型，其中一些的确晦涩难懂。我不会尝试将它们文档化，甚至微软都不支持关于这些的完整的文档化。你需要依赖于网络搜索来识别什么一些等待类型表示什么。其他的在联机帮助文档里有文档输出，因此请利用这个很棒的资源。
去查询sys.dm_os_wait_stats，像这样运行一个查询：
|
1
2
|
SELECT
*
FROM
sys.dm_os_wait_stats
AS
dows;
|
输出只有五列：wait_type、waiting_tasks_count、wait_time_ms、max_wait_time_ms和singal_wait_time_ms。这其中唯一一个不可立即通过名字理解的是singal_wait_time_ms。这列表示当线程被调用和它实际开始执行的时间数量。这个时间包含在总时间wait_time_ms。single_wait_time_ms然后是获得CPU的等待时间的一个实际度量。这是一个CPU支撑多少负载的很好的衡量。因为这，通常当你应该完全查看等待统计时，为了理解CPU执行得怎样，你应该总是单独专注于single_wait_time_ms。你会对这变得富有经验，后者你可以只专注于以下查询：
SELECT SUM(dows.signal_wait_time_ms)
FROM sys.dm_os_wait_stats AS dows;
这表示发生在系统上的CPU等待的一个累加总计。这是一个短时间负载的不错表示器。你需要比较不同方法来看它增长如何。
总结
这些只是常见的压力方面的示例，你可以通过检查快速专注于系统特定的方面，来帮助了解运行得怎样。使用这些方法你可以快速确认或评估导致性能问题的可能性。
每一种方法提供了一个足够积极的暗示，让你确认有内存压力或者CPU处于负载下。一旦你理解了压力的一般性质，为了理解像哪个查询导致最多CPU，你需要知道其他更多标准指标。
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1793354 ，如需转载请自行联系原作者
