前言
MS SQL Server DBMS 的用户、开发人员和管理员经常会遇到数据库或整个 DBMS 的性能问题，因此 MS SQL Server 监控非常重要。
这篇文章是对文章的补充 它将涵盖监控 MS SQL Server 的一些方面，特别是：如何快速确定哪些资源丢失，以及设置跟踪标志的建议。
为了使以下脚本正常工作，您需要在所需的数据库中创建一个 inf 架构，如下所示：
创建 inf 架构
use <имя_БД>;
go
create schema inf;
检测RAM不足的方法
RAM 不足的第一个迹象是 MS SQL Server 实例耗尽了分配给它的所有 RAM。
为此，我们将创建 inf.vRAM 的以下表示形式：
创建 inf.vRAM 视图
CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb] --сколько свободно ОЗУ на сервере в МБ
, a.[RAM_Avail_Percent] --процент свободного ОЗУ на сервере
, a.[Server_physical_memory_Mb] --сколько всего ОЗУ на сервере в МБ
, a.[SQL_server_committed_target_Mb] --сколько всего ОЗУ выделено под MS SQL Server в МБ
, a.[SQL_server_physical_memory_in_use_Mb] --сколько всего ОЗУ потребляет MS SQL Server в данный момент времени в МБ
, a.[SQL_RAM_Avail_Percent] --поцент свободного ОЗУ для MS SQL Server относительно всего выделенного ОЗУ для MS SQL Server
, a.[StateMemorySQL] --достаточно ли ОЗУ для MS SQL Server
, a.[SQL_RAM_Reserve_Percent] --процент выделенной ОЗУ для MS SQL Server относительно всего ОЗУ сервера
--достаточно ли ОЗУ для сервера
, (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer]
from
(
select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb
, cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent]
, a0.system_low_memory_signal_state
, ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb]
, ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb]
, ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb]
, cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent]
, cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent]
, (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL]
from sys.dm_os_sys_memory as a0
cross join sys.dm_os_process_memory as a
cross join sys.dm_os_sys_info as b
cross join sys.dm_os_sys_memory as v
) as a;
然后，您可以通过以下查询确定 MS SQL Server 的实例消耗了分配给它的所有内存：
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
如果 SQL_server_physical_memory_in_use_Mb 始终大于或等于 SQL_server_comfilled_target_Mb，则应检查等待统计信息。
要通过等待统计信息确定 RAM 的不足，让我们创建 inf.vWaits 视图：
创建 inf.vWaits 视图
CREATE view [inf].[vWaits] as
WITH [Waits] AS
(SELECT
[wait_type], --имя типа ожидания
[wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
[signal_wait_time_ms] / 1000.0 AS [SignalS],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[waiting_tasks_count] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [waiting_tasks_count]>0
and [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
)
, ress as (
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
)
SELECT [WaitType]
,MAX([Wait_S]) as [Wait_S]
,MAX([Resource_S]) as [Resource_S]
,MAX([Signal_S]) as [Signal_S]
,MAX([WaitCount]) as [WaitCount]
,MAX([Percentage]) as [Percentage]
,MAX([AvgWait_S]) as [AvgWait_S]
,MAX([AvgRes_S]) as [AvgRes_S]
,MAX([AvgSig_S]) as [AvgSig_S]
FROM ress
group by [WaitType];
在这种情况下，您可以使用以下查询确定 RAM 是否不足：
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
这里需要关注Percentage和AvgWait_S两个指标。 如果它们总体上很重要，则很有可能没有足够的 RAM 用于 MS SQL Server 实例。 有效值是针对每个系统单独确定的。 但是，您可以从以下值开始：Percentage>=1 且 AvgWait_S>=0.005。
要将指标输出到监控系统（例如Zabbix），您可以创建以下两个查询：
- RAM 占用了多少种等待类型的百分比（所有此类等待类型的总和）：
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - 有多少 RAM 等待类型以毫秒为单位（所有此类等待类型的所有平均延迟的最大值）：
select coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
根据获取的这两个指标值的动态变化，我们可以得出MS SQL Server实例是否有足够的RAM的结论。
CPU过载检测方法
要确定处理器时间不足，使用 sys.dm_os_schedulers 系统视图就足够了。 这里，如果runnable_tasks_count持续大于1，那么很有可能MS SQL Server实例的核心数量不够。
要将指标输出到监控系统（例如Zabbix），您可以创建以下查询：
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
根据该指标获得的值的动态，我们可以得出MS SQL Server实例是否有足够的处理器时间（CPU核心数量）的结论。
但是，请务必记住，请求本身可以同时请求多个线程。 有时优化器无法正确估计查询本身的复杂性。 那么该请求可能被分配了太多的线程，无法在给定时间同时处理。 这也会导致与处理器时间不足相关的等待类型，以及使用特定 CPU 核心的调度程序的队列增长，即 runnable_tasks_count 指标在这种情况下会增长。
这种情况下，在增加CPU核心数之前，需要正确配置MS SQL Server实例本身的并行属性，从2016版本开始，正确配置所需数据库的并行属性：
这里需要注意以下几个参数：
- 最大并行度 - 设置可以分配给每个请求的最大线程数（默认值为 0 - 仅受操作系统本身和 MS SQL Server 版本的限制）
- Cost Threshold for Parallelism - 估计的并行成本（默认值为 5）
- Max DOP - 设置可以在数据库级别分配给每个查询的最大线程数（但不超过“最大并行度”属性的值）（默认值为 0 - 仅受操作系统本身限制，并且MS SQL Server 的版本，以及对 MS SQL Server 整个实例的“最大并行度”属性的限制）
在这里不可能为所有情况提供同样好的方法，即您需要分析繁重的查询。
根据我自己的经验，我建议 OLTP 系统使用以下操作算法来设置并行属性：
- 首先通过将实例范围的最大并行度设置为 1 来禁用并行性
- 分析最重的请求并为其选择最佳线程数
- 将最大并行度设置为从步骤 2 中获得的选定最佳线程数，对于特定数据库，设置从步骤 2 中为每个数据库获得的最大 DOP 值
- 分析最重的请求并确定多线程的负面影响。 如果是，则增加并行度的成本阈值。
对于1C、Microsoft CRM和Microsoft NAV等系统，大多数情况下，禁止多线程是合适的
另外，如果有标准版，那么在大多数情况下，禁止多线程是合适的，因为该版本的CPU核心数量有限。
对于OLAP系统，上述算法并不适合。
根据我自己的经验，我建议 OLAP 系统使用以下操作算法来设置并行属性：
- 分析最重的请求并为其选择最佳线程数
- 将最大并行度设置为从步骤 1 中获得的选定最佳线程数，对于特定数据库，设置从步骤 1 中为每个数据库获得的最大 DOP 值
- 分析最繁重的查询并确定限制并发性的负面影响。 如果是，则降低并行成本阈值，或重复此算法的步骤 1-2
也就是说，对于OLTP系统，我们从单线程转向多线程，而对于OLAP系统，相反，我们从多线程转向单线程。 因此，您可以为特定数据库和整个 MS SQL Server 实例选择最佳并行设置。
同样重要的是要了解，根据 MS SQL Server 性能监控的结果，并行属性的设置需要随着时间的推移而更改。
设置跟踪标志的指南
根据我自己和同事的经验，为了获得最佳性能，我建议在 2008-2016 版本的 MS SQL Server 服务的运行级别设置以下跟踪标志：
- 610 - 减少索引表插入的记录。 可以帮助插入到具有许多记录和许多事务的表中，并频繁地长时间等待索引的更改
- 1117 - 如果文件组中的某个文件满足自动增长阈值要求，则文件组中的所有文件都会增长
- 1118 - 强制所有对象位于不同的范围（禁止混合范围），这最大限度地减少了扫描 SGAM 页的需要，该页用于跟踪混合范围
- 1224 - 根据锁的数量禁用锁升级。 但是，过多的内存使用可能会触发锁升级
- 2371 - 将固定自动统计更新阈值更改为动态自动统计更新阈值。 对于更新大型表的查询计划很重要，其中不正确的记录计数会导致错误的执行计划
- 3226 - 禁止在错误日志中显示备份成功消息
- 4199 - 包括对 CU 和 SQL Server Service Pack 中发布的查询优化器的更改
- 6532-6534 - 包括空间数据类型查询操作的性能改进
- 8048 - 将 NUMA 分区内存对象转换为 CPU 分区内存对象
- 8780 - 为查询计划启用额外的时间分配。 一些没有此标志的请求可能会被拒绝，因为它们没有查询计划（非常罕见的错误）
- 8780 - 9389 - 为批处理模式语句启用额外的动态授予内存缓冲区，这允许批处理模式操作员请求额外的内存，并避免在额外内存可用时将数据移动到 tempdb
同样在 2016 年之前，启用跟踪标志 2301 很有用，它可以增强决策支持优化，从而有助于选择更正确的查询计划。 然而，从 2016 版本开始，它通常会对相当长的整体查询执行时间产生负面影响。
另外，对于具有大量索引的系统（例如，对于 1C 数据库），我建议启用跟踪标志 2330，它会禁用索引使用情况的收集，这通常会对系统产生积极影响。
有关跟踪标志的更多信息，请参阅
从上面的链接来看，考虑 MS SQL Server 的版本和构建也很重要，对于较新的版本，某些跟踪标志默认启用或无效。
您可以分别使用 DBCC TRACEON 和 DBCC TRACEOFF 命令打开和关闭跟踪标志。 欲了解更多详情，请参阅
您可以使用 DBCC TRACESTATUS 命令获取跟踪标志的状态：
为了将跟踪标志包含在 MS SQL Server 服务的自动启动中，您必须转到 SQL Server 配置管理器并通过服务属性中的 -T 添加这些跟踪标志：
结果
本文对监控 MS SQL Server 的一些方面进行了分析，借助这些方面您可以快速识别 RAM 和空闲 CPU 时间的不足，以及其他一些不太明显的问题。 已经回顾了最常用的跟踪标志。
来源：
»
»
»
»
»
»
»
来源： habr.com