ms sql 活动监视
在本文的这一部分中，我们将查看用于监视MS SQL Server状态的主要系统计数器，回顾tempdb系统数据库监视的过程，并进一步了解dbForge Studio for SQL Server中SQL Server性能监视功能。
用于MS SQL Server监视的主要系统计数器
以下系统计数器通常用于监视MS SQL Server的状态：
1.内存：
Pages / sec（页面数/秒）—显示从磁盘读取或写入磁盘以解决硬页面错误的速率。 此值表示“页面输入/秒”和“页面输出/秒”值的总和，同时还包括 为满足文件系统缓存（通常由应用程序请求）和非缓存映射内存文件中的错误而检索的页面。 这是在高内存使用负载和过多页面交换时应跟踪的主要计数器。 此计数器表示页面交换的强度，因此其非峰值应接近零。 如果交换值在增加，则需要增加RAM数量或限制服务器正在运行的应用程序数量。
Page Faults / sec（页面错误数/秒）—此值表示页面错误数。 当程序在当前不在内存驻留页面集中的页面上请求地址时，就会发生页面错误。 此计数器包括需要磁盘请求的页面错误，以及由内存驻留页面集中当前不存在的页面引起的错误。 大多数CPU都可以处理后一种故障类型，而没有明显的延迟。 但是，前一种类型的页面错误在处理时可能导致显着的延迟。
可用字节—跟踪可用于执行各种进程的内存（以字节为单位）。 较低的值表示内存不足。 解决方案是增加内存量。 在大多数情况下，此计数器应高于5000 Kb。
出于以下考虑，手动设置“可用兆字节”阈值可能被证明是有益的：
- 50％的可用内存=出色
- 25％=需要关注
- 10％=可能的问题
- 少于5％的可用内存=临界状态，需要干预。
2.处理器（总计）：处理器时间百分比
此计数器显示处理器为非空闲线程执行各种操作所花费的时间百分比。 该值可以看作是花在生产工作上的时间的表示。 可以将每个处理器分配给一个空闲线程，该空闲线程使用其他线程不使用的非生产性处理器周期。 该计数器具有短暂的活动峰值（通常达到100％）是很自然的。 但是，如果在很长一段时间内处理器负载大于80％，则使用更多CPU内核将使您的系统受益。
3. MS SQL Server：访问方法
SQL Server中的Access Methods对象提供了计数器，这些计数器有助于跟踪对数据库中逻辑数据的访问。 可以使用缓冲区管理器计数器来跟踪对磁盘上数据库页的物理访问。 跟踪特定数据库中的数据访问方法将使您能够决定提高查询性能的最佳方式：添加或更改索引，节，文件或文件组，对碎片进行碎片整理或更改查询文本。 此外，可以通过控制每个服务器实例的大小和碎片来使用对象的访问方法计数器来跟踪数据大小，索引和数据库中的可用空间。 索引碎片过多会导致性能显着降低。
Page Splits / sec（页面分割数/秒）—索引页溢出导致的每秒页面分割数。 如果此值很高，则可能意味着SQL Server必须执行大量占用大量资源的操作才能拆分页面，并在插入和更新数据时将现有页面的一部分移到新位置。 如果可能，应避免此类操作。 我们可以尝试使用以下方法之一解决此问题：
- 为自动增量列创建群集索引。 在这种情况下，新记录将被顺序放置在新页面上，而不是已经被数据占用的页面上。
- 通过增加Fillfactor参数的值来重建索引。 此参数允许您在索引页中保留空间，然后可以使用它们放置新数据，而无需实际进行分页操作。
每秒完整扫描数-每秒无限制的完整扫描操作数。 这样的操作包括对主表的扫描和全索引扫描。 如果该值稳定上升，则可能表示系统性能下降（索引不足，索引碎片过多，未使用索引的存在）。 但是请记住，在小型表中使用全扫描并不总是不需要的-如果一个表可以完全放入系统的RAM中，则执行全扫描会更快。 但是在大多数情况下，该计数器值的稳定增长将表示该系统的性能下降。 所有这些信息仅适用于OLTP系统。 另一方面，在OLAP系统中，完全扫描被认为是完全正常的。
4. MS SQL Server：缓冲区管理器
缓冲区管理器对象允许您通过提供以下功能来监视SQL Server如何使用不同的资源：
- 跟踪用于数据页存储的内存；
- SQL Server读写数据库页时用于跟踪物理输入输出的计数器；
- 通过使用快速非易失性存储器（SSD）扩展缓冲池和缓冲高速缓存；
- 跟踪SQL Server的内存和计数器使用情况。 这有助于检索以下信息：
a）物理内存不足导致瓶颈的存在。 如果无法缓存常用数据，则SQL Server需要直接从磁盘读取数据；
b）通过增加总内存量或通过将额外的内存专用于数据缓存或存储SQL Server的内部结构来提高查询执行性能的可能性；
c）SQL Server多久直接从磁盘读取一次数据。 与其他操作（例如，内存访问）相比，物理输入输出要花费更多的时间。
减少输入输出量可以提高查询执行性能。
缓冲区高速缓存命中广播-显示SQL Server如何完全将数据放入高速缓存缓冲区中。 该值越高，越好-应将数据页放入缓存缓冲区中，并且应缺少物理输入输出操作，以便SQL Server有效访问这些页。 如果此计数器的值不断减少，则应考虑向系统添加更多RAM。 对于OLTP系统，此计数器应始终大于90％，对于OLAP系统，该计数器应始终大于50％。
页面预期寿命-显示页面在当前状态下在内存范围中包含的时间。 如果该值不断下降，则系统会过度使用缓冲池。 这样，工作内存可能会导致导致性能降低的问题。 需要特别注意的是，没有“页面寿命期望值”的通用值会表示系统的缓冲池过度使用（300s值自MS SQL Server 2012起不再相关）。
5. MS SQL Server：常规统计
常规统计信息对象提供了用于跟踪服务器总体活动的计数器。 例如，您可以查看运行SQL Server实例的计算机上每秒的并发连接数和用户数。 这些值可用于大型OLTP系统中，在该系统中，大量客户端连接到SQL Server实例或从SQL Server实例断开连接。
已阻止的进程-当前被阻止的进程数。
用户连接-当前连接到SQL Server实例的用户数。
6. MS SQL Server：锁
Locks对象提供有关SQL Server中不同资源类型的锁的信息。 针对诸如事务读取或更新的行之类的资源发出锁，以避免资源一次被多个事务使用。 例如，当一个事务对表中的一行发出排它（X）锁时，没有其他事务可以更新该行-直到释放该锁为止。 减少锁的使用量可以对并行性产生积极影响，进而提高整体性能。 可以跟踪Locks对象的几个实例，每个实例代表针对特定资源类型发出的锁。
平均等待时间（ms）-所有需要等待的锁定请求的平均等待时间，以ms为单位。 此计数器显示平均一个用户进程需要等待多少时间才能发出对资源的锁定。 无法为该计数器定义通用最大值，因为它完全取决于您当前的任务是什么。 此计数器的高值可能会指出数据库中的块存在问题。
锁定等待时间（ms）-最后一秒范围内的锁定等待时间总数，以ms为单位。
锁定等待数/秒—由于在最后一秒钟的时间内发生了锁定请求，线程必须等待的总次数。
锁定超时/秒-通过循环请求无法获得锁定的次数。 旋转计数器配置参数的值设置超时和线程进入空闲状态之前的最大时间（旋转）。
锁定请求/秒-每秒指定类型的锁定请求总数。
锁死锁数/秒—每秒的相互阻止量（死锁）。 死锁信号表示查询结构不正确，从而导致相互请求的资源被锁定。
7. MS SQL Server：内存管理器
内存管理器对象提供了用于跟踪整个服务器上的内存使用情况的计数器。 这种监视和评估用户操作和资源使用情况的类型可以帮助识别性能问题。 SQL Server内存控制使您可以检查：
- 在缓存中是否存在用于存储常用数据的物理内存不足。 如果没有足够的内存，SQL Server将需要直接从磁盘检索数据；
- 是否可以通过添加内存或增加可用内存的大小来缓存数据或内部SQL Server结构来提高查询性能。
未完成的内存授予数-计算成功接收到专用工作区内存的进程总数。 如果此值稳定下降，则应增加RAM量。
Memory Grants Pending（待处理的内存授予）—显示当前正在等待接收专用工作内存区域的进程总数。 如果此值稳定增长，则应增加RAM量。
8. MS SQL Server：统计
Statistics对象可确保计数器的工作，以跟踪发送到SQL Server实例的请求的编译和类型。 通过监视查询的编译次数和重复编译次数以及SQL Server收到的程序包数量，您可以大致了解SQL Server执行用户请求的速度以及查询优化器如何有效地处理用户请求。
批处理请求数/秒-每秒接收的Transact-SQL命令包数。 该值受输入，输出，用户数量，缓存大小，查询复杂性等任何限制的影响。大量的包请求表示高吞吐量能力。
9.逻辑磁盘：
平均磁盘秒数/读取数—显示从磁盘读取文件所花费的平均时间，以秒为单位。 此计数器的平均值不应超过2 ms，其最大值不应超过10 ms。 在某些情况下，可以接受25–50 ms的峰值。 但是具体细节取决于系统的性能要求。
平均磁盘秒数/写入数—显示将数据写入磁盘的平均时间，以秒为单位。 该计数器的平均值不应超过5 ms，最大值不应超过25 ms。
除了这些主要性能计数器之外，如果过程中需要这样做，还可以根据您的特定环境设置补充计数器。
监视tempdb系统数据库的性能
随着时间的流逝，专用于tempdb数据库的文件数量可能变得不足。 要检查这一点，我们可以使用以下查询：
在此查询的帮助下，我们试图在tempdb数据库中查找PFS，GAM和SGAM页面的闩锁。
如果此查询不返回任何内容或仅返回“不是PFS，GAM或SGAM页”行，则最有可能意味着在当前情况下无需增加tempdb文件。
评估tempdb系统数据库文件的等待时间也很重要，可以通过使用以下查询来完成：
SELECT files.physical_name, files.name, stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms, stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms FROM sys.dm_io_virtual_file_stats(2, NULL) as stats INNER JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id WHERE files.type_desc = 'ROWS'
可接受的值取决于您的特定系统及其性能要求。 但是，通常，以下值可以作为一个很好的参考点：
- avg_read_stall_ms不大于1毫秒
- avg_write_stall_ms不大于5毫秒
获取有关MS SQL Server实例的常规信息
通过获取最重要的基本信息来全面了解特定的MS SQL Server实例，这一点很重要。 这可以通过使用以下查询来实现：
这样，我们获得以下信息：
1）Server \ Instance-我们的MS SQL Server实例安装在的服务器的名称
2）SQLServerVersion -化合物MS SQL Server的信息（版本，CPU架构，构建日期，版本，操作系统版本）
3）ServiceInstance- MS SQL Server实例的名称（对于未命名的实例，返回“ MSSQLSERVER”）
4）CurrentDB_Name -在所有相关的信息都存储在数据库的名称
5）CurrentLogin —系统指定的当前登录名
6）CurrentUser —当前用户的名称
此后，将显示有关MS SQL Server实例的更多信息。 您可以通过以下链接了解有关这些属性的更多信息：
https://docs.microsoft.com/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
查询结束时，将显示以下字段：
1） ServerStarted — MS SQL Server实例上次启动的日期和时间
2）DaysRunning-自上次启动以来，MS SQL Server实例已工作的总天数
3）SecondsRunning —自上次启动以来，MS SQL Server实例已工作的总秒数
这三个字段是从tempdb系统数据库的属性中检索的。 这样做是由于在每次重新装载MS SQL Server实例时都会创建tempdb系统数据库。 这就是为什么可以从tempdb数据库中检索上一次MS SQL Server实例启动的日期和时间以及其自上次启动以来的总工作时间的原因。
dbForge Studio中用于SQL ServerSQL Server监视统计信息
dbForge SQL Monitor通过本文第一部分中提到的专用“概述”选项卡显示主要SQL Server性能统计信息。 在概述选项卡中，您可以查看关键SQL活动统计信息，例如CPU利用率，内存利用率，磁盘活动和IO延迟。
如果您想深入了解实时统计信息并跟踪有关数据库，资源最多的查询，当前活动的进程，等待统计信息等方面的信息，则可以使用dbForge Studio for SQL Server中的其他“ SQL监视器”选项卡。
1.数据IO-实时文件读写统计信息：
此处，延迟以毫秒为单位显示。
可以通过执行以下查询来检索此信息：
2.数据库-有关数据库的实时信息：
可以使用以下查询获取此数据：
3.等待统计信息-实时等待类型统计信息：
4.实时显示前10个最耗资源的查询。
“经过的时间”参数有助于查找对性能影响最大的查询：
此选项卡还可以显示详细的查询文本和执行计划
您可以通过执行以下操作获取此信息：
5.会话-当前在MS SQL Server实例中处于活动状态的进程：
可以通过以下查询获取此数据：
6.备份-有关最新数据库备份的信息：
您可以通过执行以下操作来检索此信息：
摘要
在本文中，我们研究了MS SQL Server活动监视的方法，这些方法使我们能够检测到RAM和可用线程的不足以及其他不太明显的问题。
我们还看到了Devart的 dbForge Studio for SQL Server如何在MS SQL Server过程中提供帮助。
翻译自: https://hackernoon.com/some-aspects-of-ms-sql-server-monitoring-part-2-548a1c3375a1
ms sql 活动监视
7282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
