如何监控您的SQL Server实例和数据库

监控SQL Server实例和数据库提供必要的信息去诊断和解决SQL Server性能问题,对SQL Server进行调优。通常怎样的性能为好怎样的性能为差是比较难定义和设定的,因为通常要软件和硬件的因素之间考虑一下权衡。它也要依赖于您的环境、业务需求和公司策略。

因为一个很小的因素而报告比较慢是可以接受的,因为这不像在巨大的企业环境里,速度慢、打嗝和瓶颈都不会影响大量用户和重要业务。上面清单列出的问题通常是不可以接受的而且必须要尽快修复。

为让SQL Server实例有一个良好的可以调优的性能,数据库管理员必须积极主动并不断监控系统性能,定义正常趋势线和设定基线来指定SQL Server的性能指标,
当超出正常的性能指标值的时候得到报警并采取适当的措施。

需要监控哪些SQL Server性能指标

需要监控的性能指标依靠于您的性能目标。然而,有一系列的常见监控指标提供足够的信息来应对基本的故障排查。基于它们的值,还可以指定更多的更具体的监控指标来找到问题的根源。

这些常见的监控SQL Server性能的指标一般都是内存和CPU使用率、网络流量和磁盘活动。

除了监控SQL Server的参数,它也推荐监控特定数据库的参数和Windows系统的参数。

这些常用监控SQL Server的性能指标是内存和处理器的使用,网络流量和磁盘活动。
除了监控SQL Server的参数,它的建议,以监视的特定数据库的参数,以及Windows系统参数。
常见的监控指标是:处理器时间、处理器队列深度、每秒页面读取和写入、页面声明周期、目标和全部服务器内存、 buffer cache命中率、批处理请求、处理器占用率、懒惰写入、网络使用量、换页次数、用户连接数等等。

SQL Server提供了两个内置的监控特性:活动监视器和数据收集器。

活动监视器

活动监视器只跟踪最重要的SQL Server性能指标。要获取这些指标,它会对所在机器的SQL Server实例每10分钟执行一下查询。只要活动监视器一打开就开始监控性能,这让活动监视器成为一种轻量级解决方案而几乎没有开销。

该指标会显示在5个折叠的面板里:概览、进程、资源等待、数据文件I/O和最近的昂贵查询。

概览面板显示了处理器时间百分比、等待任务的数量、数据库I/O操作(单位 MB/sec)和批处理请求的数量。

进程面板显示了实例上每个数据库当前正在运行的SQL Server进程。显示的信息是:login、application、host、task stat、command、wait time等。 表中的信息可以根据特定列值进行过滤。

进程面板右键菜单提供了深入分析和排除故障的特性。那就是在SQL Server Profiler里跟踪所选进程。

资源等待面板显示了不同资源的等待信息:内存、编译时间、网络等。

它显示了等待时间(SQL Server任务等待系统资源的时间)、最近等待时间、累计等待时间和平均等待计数器。

数据文件I/O面板显示了所有数据库文件的列表:MDF、NDF、LDF,还有它们的名字和路径,最近读取和写入活动和响应时间。

最近的昂贵查询面板显示了最后30秒执行的和使用最多硬件资源的查询:处理器、内存、磁盘和网络。右键菜单允许在SQL Server Management Studio 查询窗口里打开查询并打开它的查询计划

如何使用活动监视器

活动监视器可以在SQL Server Management Studio 工具栏的活动监视器图标打开,或者按键盘上的Ctrl+Alt+A快捷键,或者在SQL Server实例上的对象浏览器上点击右键菜单。

正如上面显示的那样,活动监视器只跟踪预先定义的一组最重要的SQL Server性能指标。额外的性能指标是不会被监控的。也不能移除已被监控的指标。
只能实时监控,而不能保存指标历史信息。因此,活动监视器只对当前监控和基本的故障排除有用。
要进行进一步的分析和性能调优,一个可以选择监控指标、性能阀值定义和历史数据保存的监控工具是有必要的。

数据收集器

数据收集器是另一个SQL Server Management Studio 内置的性能监控和调优功能。它会从SQL Server实例收集性能指标,保存在一个本地仓库里,
因此它们可以用来做后续的分析。它会使用数据仓库,SQL Server 代理和集成服务。

不像活动监视器,数据收集器允许您定义您要监控的指标。它提供了三个内置监控指标集(数据收集器)最重要的和常见的性能监控指标。
为了监控额外的性能指标,您可以使用T-SQL代码或者API自定义数据收集器。

如何使用数据收集器

确保SQL Server集成服务已经安装了并且已经开启SQL Server 代理、管理数据仓库和数据收集器。

  1. 在SQL Server Management Studio对象浏览器里展开“管理”
  2. 在数据收集右键菜单选择配置管理数据仓库
  3. 选择设置数据收集

  4. 点击“下一步”

  5. 选择数据库实例名和要作为数据管理仓库主机的数据库,和本地文件夹作为收集数据的缓存文件夹

  6. 点击“下一步”,审查设置,并点击“完成”

数据收集提供了三个预定义组,在对象浏览器里 管理|数据收集节点系统数据收集组可用:磁盘使用情况、查询统计信息、服务器活动。每一个预定义组都有内置的报表。

磁盘使用情况数据收集组会收集数据库数据文件(MDF和NDF)、事务日志文件(LDF)的数据,还会做I/O统计。

在数据收集右键菜单里可以看到磁盘使用情况内置报表。它会展示数据库文件使用的磁盘空间、增长趋势和每日平均增长速度。

查询统计信息数据收集组收集10个最昂贵的查询的TSQL代码、统计信息、活动和查询的执行计划

服务器活动数据收集组收集关于处理器、内存、磁盘I/O和网络数据。报表显示了CPU、内存、磁盘I/O和网络的使用情况。
还有SQL Server等待、SQL Server实例和操作系统活动。

就像上面显示的那样,数据收集要进行数据捕获必须要进行配置和启动。它提供了三个内置的收集组和后续的报表。没有向导来添加指标,要添加指标需要通过代码来添加。

数据收集器的一个优势是它不需要总是收集数据,只需要预定义一个收集间隔,并因此减少了开销。该功能的一个缺点是没有选项来给您选择要监控SQL Server实例上的哪个库,所有数据库都要被监控,这样就增加了收集数据的开销和噪声,并且需要更多的磁盘空间。

这个功能在SQL Server2008以下版本和不是企业版的版本例如标准版、商业智能版和Web版是不可用的。

不像活动监视器,没有选项来实时查看图表,但是捕获到的数据可以指定保存多少天。这个功能提供了基本的性能指标,但是要监控额外的性能指标还是需要编程知识。

ApexSQL Monitor

ApexSQL Monitor是一个 SQL Server监控软件 ,它能实时监控系统、SQL Server和数据库性能通过超过30个计数器。它允许用户选择要监控的SQL Server实例和数据库,选择每个库,SQL Server实例,机器要监控的指标,并且指定指标和阀值,如果达到阀值就报警。 ApexSQL Monitor 能够实时显示性能指标图,并将相应的指标数据存储在一个中央存储数据库中以备将来使用。

捕获到的性能指标数据会显示在5个默认tab窗口中,当然用户也可以创建自定义的tab窗口。

概览tab窗口显示了最重要的SQL Server信息和配置,还有SQL Server性能指标(buffer cache命中率、每秒全表扫描、每秒惰性写入和每秒锁请求)

Overview tab - image 1

Overview tab - image 2

系统性能tab窗口显示了操作系统性能指标:处理器时间/处理器队列深度、网络利用率、分页文件使用率、页面输入每秒、页面每秒、平均每秒磁盘读/写每字节、平均和当前磁盘队列深度、平均磁盘读取/写入每秒、每秒磁盘读/写(单位MB)、磁盘空闲空间和可用内存(单位MB)

System performance tab

SQL Server性能tab窗口显示了跟SQL Server实例性能相关的指标:每秒批请求、最长运行事务、空闲页面链表长度、全表扫描、惰性写入、锁请求、每秒死锁次数、用户连接数 、服务器所有内存(单位MB)、内存授予挂起、buffer cache命中率、页面生命周期、页面读/写、每秒页面拆分。

SQL Server performance tab

数据库性能tab窗口显示了关于数据库数据和事务日志文件大小的详细信息。

Database Performance tab

查询性能tab窗口显示了在选择的时间间隔内用了最多资源的最靠前的查询,包括了以图形显示的平均CPU时间最慢的查询、平均执行时间和每秒I/O。
用户可以自由选择查询的数量。

Query performancetab

自定义tabs窗口可以塑造个人用户的需要。用户可以自己设定和整理要显示的tab窗口,这样可以在他们的 ApexSQL Monitor启动后马上针对已知的关键指标快速和容易地进行监控。自定义的tab窗口可以用来创建对最重要的系统、SQL Server实例和数据库指标提供快速浏览的概览,并且对这些指标以特定顺序进行排序,
这样可以更容易地洞察潜在问题。

如何使用 ApexSQL Monitor

  1. 启动 ApexSQL Monitor
  2. 在菜单里点击“Add server”来添加您想监控的SQL Server实例。所有被监控的SQL Server实例会被显示在左边的服务器列表里。
  3. 要配置监控性能指标,在菜单里点击“Configure metrics”。被监控指标将会在数据库、SQL Server实例在机器级别上进行配置。

    Metrics tab

  4. 在同样的表单中,您可以配置警报。要开启警报,选择“Alerting ”复选框并输入阀值。

  5. 当您已经设置完警报和指标后,在表单上面点击“Apply ”。

如果有一个警报,在服务器列表里的SQL Server实例旁边会有图标指示。要查看活动的警报列表和详细信息,在菜单中点击“Alerts ”。
它会显示出性能指标、开始和结束时间、和每个严重级别的并发数。

Alerts information

ApexSQL Monitor是一个容易启动和配置,不需要编程知识的监控解决方案。它提供了不同粒度的监控,不像SQL Server自带的监控方案,它允许选择监控的指标和数据库。针对每个数据库、SQL Server实例和操作系统设定不同的指标。这减少了添加监控处理的开销。

不像活动监视器和数据收集器,ApexSQL Monitor 展示了实时的指标图表,在中央数据库里存储历史数据和在图表里展现历史数据。
只需要点击几下就可以配置警报,根据不同的阀值会自动进入低、中等、高严重级别的警报。

有用的资源:
活动监视器
数据收集器
监控SQL Server性能

翻译者: 林勇桦

July 2, 2015