SQL监控及性能优化

SQL 性能监控及SQL 语句优化性能监控作为SQL的数据库服务器，我们可以将其比作一个人，而SQL则是他的心脏，管理员就是他的大脑。

要监控心脏是否健康首先要看他这个人是否健康。

这两者是相辅相成的，少了一方都是不健康的。

数据库服务器的性能监视器性能监视器性能工具的介绍性能监视器是一种简单而功能强大的可视化工具，用于实时收集系统状态并从日志文件中查看性能数据。

使用性能监视器可以:获得对诊断系统问题和规划系统资源增长有用的性能数据、了解工作负载及其对系统资源的影响、观察工作负载和资源使用情况的变化和趋势，以便计划未来的升级、通过监视结果来测试配置变化、诊断问题并确定需要优化的组件或进程。

现在，可以开始选择这些对象和要监视的计数器了。

应用程序性能计数器有关 应用程序性能计数器的大部分信息最近已被合并到一个题为“改善 .NET 应用程序的性能和伸缩性”的综合文档中。

下表描述了一些可用于监视和优化 应用程序（包括 Reporting Services）性能的重要计数器。

除了上表中介绍的这些核心监视要素之外，在您试图诊断 应用程序具有的特定性能问题时，下表中的性能计数器也可对您有所帮助。

Reporting Services 性能计数器 Reporting Services 包括一组它自己的性能计数器，用于收集有关报告处理和资源消耗方面的信息。

可通过 Windows 性能监视器工具中出现的两个对象来监视实例和组件的状态和活动：MSRS 2005 Web Service 和 MSRS 2005 Windows Service 对象。

MSRS 2005 Web Service 性能对象包括一组用来跟踪 Report Server 处理过程的计数器，这些处理过程通常通过在线交互式报告浏览操作而引发。

这些计数器在 停止该 Web 服务后被重设。

下表列出了可用于监视 Report Server 性能的计数器，并描述了它们的目的。

性能对象：RS Web ServiceRS Windows Service 性能对象包括一组用于跟踪报告处理过程的计数器，这些处理过程是通过预定操作而引发的。

预定操作可能包括订阅和交付、报告执行快照以及报告历史。

微软的工作负载中并不包含任何预定操作或交付操作，此处列出这些性能计数器仅是便于您进行参考。

可使用此性能对象监视 Report Server Windows 服务。

如果您准备在一个横向伸缩配置中运行 Report Server，那么这些计数器应用于所选的服务器，而不是应用于横向伸缩配置整体。

这些计数器在应用程序域回收之时将被重设。

下表列出了可用于监视预定和交付操作的计数器，并描述了它们的目的。

性能对象：RS Windows Service如果您打算排除 Reporting Services 存在的性能问题，记录以下性能计数器通常很有帮助：、 Applications、Process、System、Memory、Physical Disks、.NET Exceptions、.NET Memory、.NET Loading、.NET CLR Locks and Threads 以及 .NET CLR Data。

可选的 Reporting Services 性能计数器以下列出了一些适用于 RS Web Service 但在默认情况下并未安装的性能计数器。

但是，在执行性能优化工作时，可以通过这些计数器来改善您洞察性能的能力。

为实现这个目的，请在命令提示符中执行以下语句：installutil.exe /u ReportingServicesLibrary.dll然后再执行： installutil.exe ReportingServicesLibrary.dll为了成功执行该语句，您可能首先需要修改您的路径，在路径中包含 Microsoft .NET Framework 的安装目录。

在路径修改完毕后，请从包含ReportingServicesLibrary.dll 文件的目录下执行先前语句。

默认情况下，该文件安装在C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.instance\ReportingServices\ReportServer\bin 目录下。

这些计数器没有进行彻底的本地化。

Row Count（行计数）对于上一次请求，由当前报告返回的行的数量。

这与对应的执行日志条目相类似。

Time in Compression（压缩时间）对于上一次请求，在快照和 PDF 报告压缩上花费的时间（以毫秒计）。

Time in data source access（数据源访问时间）对于上一次请求，在获取报告的数据源信息上花费的时间（以毫秒计）。

其中包括执行查询和取回结果所需的时间。

这与对应的执行日志条目相类似。

Time in database（数据库时间）对于上一次请求，在获取 Report Server 目录信息上花费的时间（以毫秒计）。

Time in processing（处理时间）对于上一次请求，在报告处理上花费的时间（以毫秒计）。

这与对应的执行日志条目相类似。

Time in rendering（呈现时间）对于上一次请求，在呈现报告上花费的时间（以毫秒计）。

这与对应的执行日志条目相类似。

以上红色文字的性能指标为常用这里并不完全下面以OA数据库实例来说明。

Cpu使用监控添加处理器(Processor)的计数器计数器中常用的是处理时间（processor time）右边的选项框中是处理器的实例。

processor time 指处理器用来执行非闲置线程时间的百分比。

计算方法是，测量范例间隔内非闲置线程活动的时间，用范例间隔减去该值。

(每台处理器有一个闲置线程，该线程在没有其他线程可以运行时消耗周期)。

这个计数器是处理器活动的主要说明器，显示在范例间隔时所观察的繁忙时间平均百分比。

这个值是用 100% 减去该服务不活动的时间计算出来的。

服务器有多少cpu就有多少实例，编号从0至n-1。

OA为4核4线程cpu 故有16个实例。

缓存使用监控添加缓存（Cache）的计数器计数器中常用的是复制读取百分比（Copy Read Hits）以及数据命中百分比（Data Map Hits）它是指：由于页面已经在物理内存中，可以不从磁盘上检索页面的情况下解析在文件系统缓存中的数据映射的百分比。

内存使用监控Page Reads/sec Page Writes/sec Page Input/sec Page Output/sec Pages/sec 这些值都正常都很小可以用来监控sql事务的量这里有两个计数器 Lock Waits/sec（每秒钟的锁数量） Number of Deadlocks/sec（每秒钟的死锁数量）。

相信对sql锁关心的人也会注意到这两个计数器。

当然，SQL自身的监控可以捕获到有关锁的更加具体、清晰的信息。

物理磁盘的监控这里常用的计数器是 Avg. Disk Queue Length（读取和写入请求的平均数）Avg. Disk sec /Read （指以秒计算的在此盘上读取数据的所需平均时间。

）Avg. Disk sec/Write（指以秒计算的在此盘上写入数据的所需平均时间。

）这些事关乎到物理磁盘的性能，提供检测硬件的信息。

我们知道数据使用久了会产生索引碎片，这也是不容忽视的。

索引碎片在磁盘上的表现就是磁盘碎片多，当然磁盘碎片多还有其他原因，但作为数据库服务器来说，大多的碎片都是由数据产生的。

我们也可以使用微软自带的磁盘碎片整理工具，但是要特别注意，在生产环境上不要使用，或者热备份后转移，或者暂停服务（当然大多生产都是不能停机的）。

日志事件信息查询在数据库服务器或者SQL实例出现问题的情况下如何去查看由服务器记录的日志事件等信息？打开计算机管理，在系统工具中我们可以看到事件查看器、性能日志和警报这两栏。

在应用程序中可以看到右边很多警告、信息、审核失败等条目。

同事记录了发生的时间、来源、用户、事件等信息。

双击条目后展现出事件属性对话框。

如果你对于该时间不是很熟悉，你可以看事件的描述，还可以利用时间ID 去网上寻找资源，这一点非常有用。

选择系统后可以看到记录的相关事件信息。

用同样的方法来查看明细，确保到每个细节。

而性能日志和警报栏里则显示的是监控保存的记录。

SQL的性能监控在SQL的性能工具里有个SQL Server Profiler新建跟踪这里我们可以看到有个使用模板下拉框，在此可以选择模板，有Stanard TSQL TSQL_Duration TSQL_Grouped TSQL_Locks TSQL_Replay TSQL_Sps Tuning 常用的是Stanard TSQL TSQL_Locks Tuning这里的模板也就是侧重点不同而选择了不同的事件以及事件的列名。

我们看到这里有个TSQL_Locks监控，我很高兴看到这个SQL的锁监控，因为它更加具体的展现了锁的情况。

这里选择保存到文件，如果不选择，SQL自动临时保存，关闭后就删除。

设置文件大小，看数据库的应用而定。

这里设置1G的意思是如果保存的文件达到1G则自动新建一个文件。

SQL 的命名是原始文件后添加阿拉伯数字12345。

勾选启用跟踪停止时间，这样可以省去人工关闭。

再来看看事件选择当鼠标移动到相应的事件以及列名时会在左下方显示相关的说明，方便操作人员了解。

当然在跟踪的时候可以进行筛选。

将自己特别重点考虑的性能记录下来以供参考，其他的数据忽略，这样方便以后的查找。

点击列筛选器弹出对话框：例如我们设定事件使用的CPU时间（毫秒）列并设置大于等于700 同时勾选排除不包含值的行，这样我们就对数据进行了筛选。

那如果你又要查CPU时间超过1000的怎么办？不怕，当跟踪结束后，打开Profiler读取已经保存的跟踪文件，在设置属性里，同样可以进行筛选。

这样就方便了操作员进行数据的分析。

在同个基础数据中进行分析，我非常赞同这点。

有的人可能会问，会不会出现我需要的事件或者列模板没有显示怎么办？不怕，看到右下角的显示所有事件，显示所有列了吗？勾选上，然后去挑自己需要的事件和列吧。

呵呵。

当然，如果你觉得每次都要自己勾选列名比较麻烦，那么就新建一个模板吧，一劳永逸，下次监控，你只需要选用自己的模板即可。

运行后显示：当选中一行后，在下方将显示执行的SQL语句，如果此语句的CPU时间过长，我们可以拿出该语句进行分析，优化。

另外SQL还有一个活动监视器，可以用来监视数据库服务器的性能情况。

这里活动监视器把收集到的数据图视化了，以便能够更加直观的观察服务器的运行状况。