MS SQL Server监视的某些方面。 第1部分。

通常，MS SQL Server用户，开发人员和管理员通常会遇到数据库或RDBM中的性能问题。 这就是为什么MS SQL Server性能监视非常重要的原因。

在本文中，我们将研究此过程的某些方面，主要问题是-“如何检测当前缺少哪些资源？”

在本指南的整个过程中，我们将使用许多不同的脚本。 为了使它们正常工作，我们首先需要在所需的数据库中创建“ inf”模式。 这可以通过执行以下代码来实现：

use <database_name>; go create schema inf;

如何检测RAM不足

RAM不足的第一个迹象是MS SQL Server实例使用了专用于它的所有RAM。

让我们创建以下inf.vRAM视图：

这样，我们可以检查我们的MS SQL Server实例当前是否正在使用所有专用内存：

from [inf].[vRAM] 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视图：

在这种情况下，以下查询可以帮助我们检测RAM不足：

FROM [inf].[vWaits] SELECT [Percentage] ,[AvgWait_S] FROM [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' )

具体来说，我们需要关注Percentage和AvgWait_S值。 如果这两个值都产生有关结果，则很有可能MS SQL Server缺少内存。 究竟什么结果被认为是不需要的，很大程度上取决于您的系统。 但是，我们可以从百分比> = 1开始，而AvgWait_S> = 0.005是RAM不足的体面迹象。

要将这些值输出到监视系统（即Zabbix），我们可以创建以下查询：

1. RAM等待类型使用的资源百分比（根据所有此类等待类型的总和计算）：

from [inf].[vWaits] select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' )

2）在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实例需要更多CPU内核才能实现最佳操作。

要将这个值输出到监视系统（即Zabbix），我们可以使用以下查询：

from sys.dm_os_schedulers where scheduler_id<255 select max([runnable_tasks_count]) as [runnable_tasks_count] from sys.dm_os_schedulers where scheduler_id<255

基于检索到的值的动态性，我们可以对是否有足够的处理器时间（或CPU内核数量）用于我们的MS SQL Server实例做出明智的决定。

但是，请记住，查询本身可能需要执行多个线程。 此外，优化器有时可能会误判查询的复杂性。 因此，太多的线程可能专用于执行查询，这些线程无法在此时间点同时进行处理。 这将创建一个额外的等待类型，这与缺少处理器时间以及使用特定CPU内核的调度程序的队列增加有关。 因此，在这种情况下，runnable_tasks_count值将增加。

在这种情况下，在增加专用CPU内核数量之前，我们首先需要配置MS SQL Server实例的并行设置。 此外，从2016版开始，我们还需要为所有必需的数据库正确设置并行性：

图。1。 配置MS SQL Server实例的并行设置。

图2。 为数据库设置并行性（从2016版本开始）

在这里，我们应该考虑以下参数：

1）最大并行度-设置每个查询专用的最大线程数（此默认值是0-这意味着将根据您的OS和MS SQL Server版本自动设置限制）

2）并行性的成本阈值-并行性的估计成本（默认值为5）

3）最大DOP —设置可用于数据库级别每个查询的最大线程数。 该值不能超过“最大并行度”。 （默认值为0 －这意味着将根据您的OS和MS SQL Server版本以及整个服务器的“最大并行度”值自动放置限制）

在这里，实际上不可能针对所有情况设计一种正确的策略，因此您将需要根据个案分析复杂的查询。

我个人建议使用以下算法在OLTP系统中配置并行性设置：

1）通过将整个实例的“最大并行度”值设置为1来禁用并行性

2）分析最复杂的查询并为其选择最佳线程数

3）将“最大并行度”值设置为我们在第2步中获得的数字-无论是对于单个数据库还是对于整个实例。

4）分析最复杂的查询，并检测多线程是否有负面影响。 在这种情况下，请增加“并行成本阈值”值。

对于像1C，Microsoft Crm和Microsoft NAV这样的系统，最好的决定是禁用并行性。

如果使用标准版，则适用相同的解决方案，因为它对CPU内核数有限制。

但是，此算法不适用于OLAP系统。

我将为OLAP系统推荐以下并行设置算法：

1）分析最复杂的查询并为其选择最佳线程数

2）将“最大并行度”值设置为我们在第1步中获得的数字-对于单个数据库和整个实例。

3）分析最复杂的查询并检测多线程是否有负面影响。 如果是这种情况，请增加“并行度的成本阈值”值或重复步骤1-2。

因此，对于OLTP系统，我们的目标是从单线程切换到多线程，而对于OLAP系统，情况恰恰相反-我们希望从多线程切换到单线程。 这样，我们可以为整个MS SQL Server实例和单个数据库选择最佳的并行设置。

同样重要的是要知道，随着时间的流逝，应根据MS SQL Server性能监视的结果定期重新配置并行设置。

dbForge Studio中用于SQL Server的SQL Server监视

Devart的dbForge Studio for SQL Server中提供了一个SQL Server性能监视器 ：

图3 dbForge Studio for FQL Server中的SQL Monitor

让我们看一下主工作窗口，它实时显示以下统计信息：

图4 SQL Monitor的概述选项卡

1. CPU利用率，％—显示服务器创建的CPU负载的图表
2. 内存使用率（Gb）-显示RAM使用情况的图表（总计-合并的所有进程使用多少内存，SQL Server-SQL Server使用多少内存）
3. 磁盘活动，Mb-显示磁盘写入和读取操作的图
4. 磁盘操作延迟
5. 总CPU —总CPU负载的百分比
6. 总CPU —总CPU负载的百分比
7. 死锁
8. 等待中的任务
9. 连接（用户/登录）
10. 事务/秒
11. 每秒完整扫描
12. 范围扫描/秒
13. 页面读取/秒
14. 页面写入/秒
15. 页面查询/秒
16. 页面拆分/秒和页面预期寿命
17. 页面故障/秒
18. 主机属性（ProcessorCount，PhysicalMemory，平台，WindowsVersion，虚拟内存，主机平台，主机操作系统，主机SP级别，主机操作系统语言）
19. SQL Server属性（产品版本，语言，引擎版本，产品级别，产品更新级别，产品更新参考，资源上次更新，资源版本，排序规则，比较样式，内部CLR版本，SQl字符集，SQL排序顺序，资源调控器，高级分析，Polybase，群集，全文本，始终在线，始终在线状态，安全性，文件流共享，文件流访问级别，代理）

摘要

在本文的第一部分，我们介绍了监视MS SQL Server活动以检测RAM不足的方法。

我们还看到了Devart在dbForge Studio for SQL Server中提供的主要统计信息，这些统计信息可以帮助进行MS SQL Server监视。

From: https://hackernoon.com/some-aspects-of-ms-sql-server-monitoring-part-1-b6e67b313f6f