前言
通常，MS SQL Server DBMS的用户，开发人员和管理员通常都会遇到数据库性能问题或DBMS，因此，对MS SQL Server进行监视非常重要。
本文是对
使用Zabbix监视MS SQL Server数据库的文章的补充，并且将研究监视MS SQL Server的某些方面，特别是：如何快速确定缺少哪些资源，以及有关设置跟踪标志的建议。
为了使以下脚本起作用，必须按如下所示在所需数据库中创建inf模式：
创建一个inf模式use <_>; go create schema inf;
一种内存不足的检测方法
缺少RAM的第一个指标是MS SQL Server实例耗尽分配给它的所有RAM的情况。
为此，请创建以下inf.vRAM视图：
创建一个inv.vRAM视图 CREATE view [inf].[vRAM] as select a.[TotalAvailOSRam_Mb]
然后，您可以确定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_committed_target_Mb，则需要检查期望的统计信息。
要通过期望统计信息确定是否缺少RAM，请创建一个inf.vWaits视图：
创建一个inv.vWaits视图 CREATE view [inf].[vWaits] as WITH [Waits] AS (SELECT [wait_type],
在这种情况下，可以通过以下查询确定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实例。 为每个系统分别确定基本值。 但是，您可以从以下指标开始：百分比> = 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' );
根据这两个指标获得的值的动态，我们可以得出结论，是否有足够的RAM用于MS SQL Server实例。
CPU过载检测方法
要确定缺少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版本限制）
- 并行性的成本阈值-并行性的估计成本（默认为5）
- 最大DOP设置可在数据库级别分配给每个查询的最大线程数（但不超过“最大并行度”属性的值）（默认值是0限制，仅适用于操作系统和MS SQL Server版本，以及整个MS SQL Server实例的“最大并行度”属性的限制）
不可能为所有情况提供同样好的配方，也就是说，您需要分析困难的请求。
根据我的经验，我建议OLTP系统使用以下操作算法来配置并行性属性：
- 通过将“最大并行度”的整个实例的级别设置为1来首先禁止并发
- 分析最困难的请求并为其选择最佳线程数
- 将最大并行度设置为从项目2获得的所选最佳线程数，对于特定的数据库，请为每个数据库设置从项目2获得的最大DOP值
- 分析最困难的请求并确定多线程的负面影响。 如果是，则增加并行成本阈值。
对于大多数系统，例如1C，Microsoft CRM和Microsoft NAV等，禁止多线程是合适的。
同样，如果安装了Standard Edition，则鉴于该版本受CPU内核数量的限制，在大多数情况下，禁止多线程是合适的。
对于OLAP系统，上述算法不适合。
根据我自己的经验，我建议OLAP系统使用以下操作算法来设置并行性属性：
- 分析最困难的请求并为其选择最佳线程数
- 将最大并行度设置为从项目1获得的所选最佳线程数，并且对于特定的数据库，还为每个数据库设置从项目1获得的Max DOP值
- 分析最困难的请求，并确定并发限制的负面影响。 如果是，则降低并行成本阈值，或重复此算法的步骤1-2
也就是说，对于OLTP系统，我们从单线程转为多线程，而对于OLAP系统，我们从多线程转为单线程。 因此，可以为特定数据库和整个MS SQL Server实例选择最佳的并发设置。
同样重要的是要了解，根据监视MS SQL Server性能的结果，并发属性设置需要随时间进行更改。
有关设置跟踪标志的建议
根据我自己和同事的经验，我建议在MS SQL Server服务的2008-2016年版本的启动级别上设置以下跟踪标志，以实现最佳性能：
- 610-减少索引表中插入的记录。 它可以帮助将具有大量记录和许多事务的表插入到表中，并且长期期望WRITELOG对索引的更改有很高的期望
- 1117-如果文件组中的文件达到自动增长阈值，则该文件组中的所有文件都会展开
- 1118-强制所有对象位于不同的扩展区中（禁止混合扩展区），这最大程度地减少了扫描用于跟踪混合扩展区的SGAM页面的需要
- 1224-根据锁数禁用锁升级。 过多的内存使用可能包括锁升级。
- 2371-将固定的自动统计信息更新的阈值更改为动态的自动统计信息更新的阈值。 重要的是更新大表的查询计划，在大表中错误地确定记录数会导致错误的执行计划
- 3226-在错误日志中禁止成功备份消息
- 4199-包括对累积更新和SQL Server Service Pack中发布的查询优化器的更改
- 6532-6534-包括针对空间数据类型的改进的查询性能
- 8048-将NUMA分区的内存对象转换为CPU分区
- 8780-启用其他时间分配以安排请求。 某些没有此标志的请求可能会被拒绝，因为它们没有请求计划（非常罕见的错误）
- 9389-包括一个用于批处理模式操作员的动态临时提供的附加内存缓冲区，使批处理模式操作员可以请求附加的内存，并避免在可用附加内存的情况下将数据传输到tempdb
在2016版之前，包含跟踪标记2301非常有用，该标记包括扩展决策支持的优化，从而有助于选择更正确的查询计划。 但是，从版本2016开始，它通常会在相当长的总体查询执行时间内产生负面影响。
同样对于具有很多索引的系统（例如，对于1C数据库），我建议您启用跟踪标志2330，这将禁用使用索引的收集，这通常会对系统产生积极影响。
在此处了解有关跟踪标志的更多信息。
使用上面的链接，考虑MS SQL Server的版本和程序集也很重要，因为对于较新的版本，某些跟踪标志默认情况下处于启用状态或无效。 例如，在2017版本中，仅设置以下5个跟踪标志是有意义的：1224、3226、6534、8780和9389。
您可以分别使用DBCC TRACEON和DBCC TRACEOFF命令启用或禁用跟踪标志。 有关更多详细信息，请参见
此处 。
您可以使用DBCC TRACESTATUS命令获得跟踪标志的状态：
more 。
为了使跟踪标志包含在MS SQL Server服务的自动运行中，您需要进入SQL Server配置管理器，并通过-T在服务属性中添加这些跟踪标志：
总结
在本文中，研究了MS SQL Server监视的某些方面，借助这些方面，您可以快速确定缺少RAM和CPU空闲时间以及其他一些不太明显的问题。 考虑了最常用的跟踪标志。
资料来源
»
SQL Server备用统计»
SQL Server的期望统计信息，或者请告诉我它在哪里痛»
系统视图sys.dm_os_schedulers»
使用Zabbix跟踪MS SQL Server数据库»
SQL生活方式»
跟踪标志»
Sql.ru