|
|
知识路径： > 信息系统数据资源维护 > 信息系统数据资源例行管理 > SQLServer监控技术 >
|
|
相关知识点：69个
|
|
|
|
|
以下介绍一下如何诊断SQL Server数据库性能问题。使用SQL事件探查器和性能监控工具有效地诊断性能问题
|
|
|
|
在SQL Server应用领域SQL事件探查器可能是最著名的性能故障排除工具，大多数情况下，当得到一个性能问题报告后，一般首先启动它进行诊断。
|
|
|
|
SQL事件探查器是一个跟踪和监控SQL Server实例的图形化工具，主要用于分析和衡量在数据库服务器上执行的TSQL性能，可以捕捉服务器实例上的每个事件，将其保存到文件或表中供以后分析。例如，如果生产数据库速度很慢，可以使用SQL事件探查器查看哪些存储过程执行时耗时过多。
|
|
|
|
|
|
（1）启动SQL事件探查器，连接到目标数据库实例，创建一个新跟踪，指定一个跟踪模板（跟踪模板预置了一些事件和用于跟踪的列），如下图所示。
|
|
|
|
|
|
|
|
（2）作为可选的一步，还可以选择特定事件和列，如下图所示。
|
|
|
|
|
|
|
|
（3）另外还可以单击“组织列”按钮，在弹出的窗口中指定列的显示顺序，单击“列过滤器”按钮，在弹出的窗口中设置过滤器，例如，通过设置数据库的名称（在like文本框中），只跟踪特定的数据库，如果不设置过滤器，SQL事件探查器会捕捉所有的事件，跟踪的信息会非常多，如下图所示。
|
|
|
|
|
|
|
|
|
|
（5）跟踪了足够的信息后，停掉事件探查器，将跟踪信息保存到一个文件中，或者保存到一个数据表中，如果保存到表中，需要指定表名，SQL Server会自动创建表中的字段。
|
|
|
|
（6）执行下面的SQL查询语句找出执行代价较高的TSQL，如下图所示。
|
|
|
|
|
|
|
|
|
|
|
|
SQL事件探查器除了可以用于找出执行成本最高的那些TSQL或存储过程外，还可以利用它许多强大的功能诊断和解决其他不同类型的问题。当收到一个性能问题报告后，或者想提前诊断潜在的性能问题时都可以使用SQL事件探查器。下面是一些SQL事件探查器使用技巧。
|
|
|
|
|
|
|
|
|
|
大多数时候现有的模板能够满足需求，但当诊断一个特殊类型的数据库性能问题时（如数据库发生死锁），可能需要创建自己的模板，在这种情况下，可以单击“文件”|“模板”|“新建模板”创建一个新模板，如上图所示。需要指定模板名、事件和列，如下图所示。当然也可以从现有的模板修改而来。
|
|
|
|
|
|
|
|
捕捉表扫描（TableScan）和死锁（DeadLock）事件
|
|
|
|
|
|
先假设一种情况，假设已经在测试库上创建了合适的索引，经过测试后，现在已经将索引应用到生产服务器上了，但由于某些不明原因，生产数据库的性能一直没达到预期的那样好，推测执行查询时发生了表扫描，希望有一种方法能够检测出是否真的发生了表扫描。
|
|
|
|
再假设另一种情况，假设已经设置好了将错误邮件发送到一个指定的邮件地址，这样开发团队可以第一时间获得通知，并有足够的信息进行问题诊断。某一天，突然收到一封邮件说数据库发生了死锁，并在邮件中包含了数据库级别的错误代码，需要找出是哪个TSQL创造了死锁。
|
|
|
|
这时可以打开SQL事件探查器，修改一个现有模板，使其可以捕捉表扫描和死锁事件，修改好后，启动事件探查器，运行应用程序，当再次发生表扫描和死锁事件时，事件探查器就可以捕捉到，利用跟踪信息就可以找出执行代价最高的TSQL。
|
|
|
|
注意：从SQL Server日志文件中可能也可以找到死锁事件记录，在某些时候，可能需要结合SQL Server日志和跟踪信息才能找出引起数据库死锁的数据库对象和TSQL。
|
|
|
|
|
|
某些时候，为了解决生产数据库的性能问题，需要在测试服务器上模拟一个生产环境，这样可以重演性能问题。使用SQL事件探查器的TSQL_Replay模板捕捉生产库上的事件，并将跟踪信息保存为一个.trace文件，然后在测试服务器上播放跟踪文件就可以重现性能问题是如何出现的了。
|
|
|
|
|
|
数据库调优顾问是一个伟大的工具，它可以提供很好的调优建议，但要真正从它那获得有用的建议，需要模拟出与生产库一样的负载，也就是说，需要在测试服务器上执行相同的TSQL，打开相同数量的并发连接，然后运行调优顾问。SQL事件探查器的Tuning模板可以捕捉到这类事件和列，使用Tuning模板运行事件探查器，捕捉跟踪信息并保存，通过调优顾问使用跟踪文件在测试服务器上创建相同的负载。
|
|
|
|
捕捉ShowPlan在事件探查器中包括SQL执行计划
|
|
|
|
有时相同的查询在测试服务器和生产服务器上的性能完全不一样，假设遇到这种问题，应该仔细查看一下生产数据库上TSQL的执行计划。但问题是现在不能在生产库上执行这个TSQL，因为它已经有严重的性能问题。这时SQL事件探查器可以派上用场，在跟踪属性中选中ShowPlan或ShowPlan XML，这样可以捕捉到SQL执行计划和TSQL文本，然后在测试服务器上执行相同的TSQL，并比较两者的执行计划，如下图所示。
|
|
|
|
|
|
|
|
|
|
当数据库遇到性能问题时，大多数时候使用SQL事件探查器就能够诊断和找出引起性能问题的背后原因了，但有时SQL事件探查器并不是万能的。
|
|
|
|
例如，在生产库上使用SQL事件探查器分析查询执行时间时，对应的TSQL执行很慢（假设需要10秒），但同样的TSQL在测试服务器上执行时间却只要200毫秒，通过分析执行计划和数据列，发现它们都没有太大的差异，因此在生产库上肯定有其他问题，那该如何揪出这些问题呢？
|
|
|
|
此时性能监视工具（著名的PerfMon）可以派上用场，它可以定期收集硬件和软件相关的统计数据，还有它是内置于Windows操作系统的一个免费的工具。
|
|
|
|
当向SQL Server数据库发送一条TSQL语句时，会产生许多相关的执行参与者，包括TSQL执行引擎、服务器缓存、SQL优化器、输出队列、CPU、磁盘I/O等，只要这些参与者任何一环执行节奏没有跟上，最终的查询执行时间就会变长，使用性能监视工具可以对这些参与者进行观察，以找出根本原因。
|
|
|
|
使用性能监视工具可以创建多个不同的性能计数器，通过图形界面分析计数器日志，此外还可以将性能计数器日志和SQL事件探查器跟踪信息结合起来分析。
|
|
|
|
|
|
Windows内置了许多性能监视计数器，安装SQL Server时会添加一个SQL Server性能计数器，下面是创建一个性能计数器日志的过程。
|
|
|
|
|
|
|
|
（1）在SQL事件探查器中启动性能监视工具（“工具”|“性能监视器”），如上图所示。
|
|
|
|
|
|
|
|
（2）右击“数据收集器集”|“用户定义”|“新建数据日志”|“手动创建”，输入数据收集器集名称，单击“下一步”，如上图所示。勾选“性能计数器”复选框，单击“完成”按钮，创建一个新的性能计数器日志，如下图所示。
|
|
|
|
|
|
|
|
（3）单击“添加”按钮，选择一个需要的计数器，可以指定收集计数器数据的间隔时间，如下图所示。
|
|
|
|
|
|
|
|
（4）从列表中选择要监视的对象和选定对象的实例，单击“确定”按钮，如下图所示。
|
|
|
|
|
|
|
|
|
|
|
|
|
|
（6）右击刚建的数据收集器，然后单击“目录”标签，指定日志文件保存位置，如果需要还可以修改日志文件名，如下图所示。
|
|
|
|
|
|
|
|
（7）右击“计划”标签，然后单击“添加”按钮，指定计数器开始和结束日期，如下图所示。
|
|
|
|
|
|
|
|
（8）单击“停止条件”标签，设置计数器停止条件，如下图所示。
|
|
|
|
|
|
|
|
单击“完成”，选择刚刚创建的计数器日志，右击，单击“开始”按钮，如下图所示。
|
|
|
|
|
|
|
|
|
|
|
|
（9）为了查看日志数据，再次打开性能监视工具，单击查看日志图标（红色），在“来源”标签上选中“日志文件”单选按钮，单击“添加”按钮添加一个日志文件。如上图所示。查看日志数据时可以追加计数器，单击“确定”，如下图一所示，返回图形化的性能计数器日志输出界面，如下图二所示。
|
|
|
|
|
|
|
|
|
|
|
