有效利用SQL事件探查器排除与性能相关的问题

　　SQL事件探查器除了可以用于找出执行成本最高的那些TSQL或存储过程外，还可以利用它许多强大的功能诊断和解决其它不同类型的问题。当你收到一个性能问题报告后，或者想提前诊断潜在的性能问题时都可以使用SQL事件探查器。下面是一些SQL事件探查器使用技巧，或许对你有帮助。

　　1)使用现有的模板，但需要时应创建你自己的模板

　　大多数时候现有的模板能够满足你的需求，但当诊断一个特殊类型的数据库性能问题时(如数据库发生死锁)，你可能需要创建自己的模板，在这种情况下，你可以点击“文件”*“模板”*“新建模板”创建一个新模板，需要指定模板名、事件和列。当然也可以从现有的模板修改而来。

　　图 7 创建一个新模板

　　图 8 为新模板指定事件和列

　　2)捕捉表扫描(TableScan)和死锁(DeadLock)事件

　　没错，你可以使用SQL事件探查器监听这两个有趣的事件。

　　先假设一种情况，假设你已经在你的测试库上创建了合适的索引，经过测试后，现在你已经将索引应用到生产服务器上了，但由于某些不明原因，生产数据库的性能一直没达到预期的那样好，你推测执行查询时发生了表扫描，你希望有一种方法能够检测出是否真的发生了表扫描。

　　再假设另一种情况，假设你已经设置好了将错误邮件发送到一个指定的邮件地址，这样开发团队可以第一时间获得通知，并有足够的信息进行问题诊断。某一天，你突然收到一封邮件说数据库发生了死锁，并在邮件中包含了数据库级别的错误代码，你需要找出是哪个TSQL创造了死锁。

　　这时你可以打开SQL事件探查器，修改一个现有模板，使其可以捕捉表扫描和死锁事件，修改好后，启动事件探查器，运行你的应用程序，当再次发生表扫描和死锁事件时，事件探查器就可以捕捉到，利用跟踪信息就可以找出执行代价最高的TSQL。

　　注意：从SQL Server日志文件中可能也可以找到死锁事件记录，在某些时候，你可能需要结合SQL Server日志和跟踪信息才能找出引起数据库死锁的数据库对象和TSQL。

　　图 9 检测表扫描

　　图 10 检测死锁

　　3)创建重放跟踪

　　某些时候，为了解决生产数据库的性能问题，你需要在测试服务器上模拟一个生产环境，这样可以重演性能问题。使用SQL事件探查器的TSQL_Replay模板捕捉生产库上的事件，并将跟踪信息保存为一个.trace文件，然后在测试服务器上播放跟踪文件就可以重现性能问题是如何出现的了。

　　图 11 创建重放跟踪

　　4)创建优化跟踪

　　数据库调优顾问是一个伟大的工具，它可以给你提供很好的调优建议，但要真正从它那获得有用的建议，你需要模拟出与生产库一样的负载，也就是说，你需要在测试服务器上执行相同的TSQL，打开相同数量的并发连接，然后运行调优顾问。SQL事件探查器的Tuning模板可以捕捉到这类事件和列，使用Tuning模板运行事件探查器，捕捉跟踪信息并保存，通过调优顾问使用跟踪文件在测试服务器上创建相同的负载。

　　图 12 创建Tuning事件探查器跟踪

　　5)捕捉ShowPlan在事件探查器中包括SQL执行计划

　　有时相同的查询在测试服务器和生产服务器上的性能完全不一样，假设你遇到这种问题，你应该仔细查看一下生产数据库上TSQL的执行计划。但问题是现在不能在生产库上执行这个TSQL，因为它已经有严重的性能问题。这时SQL事件探查器可以派上用场，在跟踪属性中选中ShowPlan或ShowPlan XML，这样可以捕捉到SQL执行计划和TSQL文本，然后在测试服务器上执行相同的TSQL，并比较两者的执行计划。

　　图 13 指定捕捉执行计划

　　图 14 在事件探查器跟踪中的执行计划

　　使用性能监视工具(PerfMon)诊断性能问题

　　当你的数据库遇到性能问题时，大多数时候使用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事件探查器中启动性能监视工具(“工具”*“性能监视器”);

　　图 15 启动性能监视工具

　　2)点击“计数器日志”*“新建日志设置”创建一个新的性能计数器日志

　　图 16 创建一个性能计数器日志

　　指定日志文件名，点击“确定”。

　　图 17 为性能计数器日志指定名字

　　3)点击“添加计数器”按钮，选择一个需要的计数器

　　图 18 为性能计数器日志指定计数器

　　4)从列表中选择要监视的对象和对应的计数器，点击“关闭”

　　图 19 指定对象和对应的计数器

　　5)选择的计数器应显示在窗体中

　　图 20 指定计数器

　　6)点击“日志文件”标签，再点击“配置”按钮，指定日志文件保存位置，如果需要现在还可以修改日志文件名

　　图 21 指定性能计数器日志文件保存位置

　　7)点击“调度”标签，指定一个时间读取计数器性能，写入日志文件，也可以选择“手动”启动和停止计数器日志。

　　图 22 指定性能计数器日志运行时间

　　8)点击“常规”标签，指定收集计数器数据的间隔时间

　　图 23 设置计数器间隔采样时间

　　9)点击“确定”，选择刚刚创建的计数器日志，点击右键启动它。

　　图 24 启动性能计数器日志

　　10)为了查看日志数据，再次打开性能监视工具，点击查看日志图标(红色)，在“源”标签上选中“日志文件”单选按钮，点击“添加”按钮添加一个日志文件。

　　图 25 查看性能计数器日志

　　11)默认情况下，在日志输出中只有三个计数器被选中，点击“数据”标签可以追加其它计数器。

　　图 26 查看日志数据时追加计数器

　　12)点击“确定”，返回图形化的性能计数器日志输出界面

　　图 27 查看性能计数器日志

　　关联性能计数器日志和SQL事件探查器跟踪信息进行深入的分析

　　通过SQL事件探查器可以找出哪些SQL执行时间过长，但它却不能给出导致执行时间过长的上下文信息，但性能监视工具可以提供独立组件的性能统计数据(即上下文信息)，它们正好互补。

　　如果相同的查询在生产库和测试库上的执行时间差别过大，那说明测试服务器的负载，环境和查询执行上下文都和生产服务器不一样，因此需要一种方法来模拟生产服务器上的查询执行上下文，这时就需要结合SQL事件探查器的跟踪信息和性能监视工具的性能计数器日志。

　　将二者结合起来分析可以更容易找出性能问题的根本原因，例如，你可能发现在生产服务器上每次查询都需要10秒，CPU利用率达到了100%，这时就应该放下SQL调优，先调查一下为什么CPU利用率会上升到100%。

　　关联SQL事件探查器跟踪信息和性能计数器日志的步骤如下：

　　1)创建性能计数器日志，包括下列常见的性能计数器，指定“手动”方式启动和停止计数器日志：

　　--网络接口\输出队列长度

　　--处理器\%处理器时间

　　--SQL Server：缓冲管理器\缓冲区缓存命中率

　　--SQL Server：缓冲管理器\页面生命周期

　　--SQL Server：SQL统计\批量请求数/秒

　　--SQL Server：SQL统计\SQL 编译

　　--SQL Server：SQL统计\SQL 重新编译/秒

　　创建好性能计数器日志，但不启动它。

　　2)使用SQL事件探查器TSQL Duration模板创建一个跟踪，添加“开始时间”和“结束时间”列跟踪，同时启动事件探查器跟踪和前一步创建的性能计数器日志;

　　3)跟踪到足够信息后，同时停掉SQL事件探查器跟踪和性能计数器日志，将SQL事件探查器跟踪信息保存为一个.trc文件;

　　4)关闭SQL事件探查器跟踪窗口，再使用事件探查器打开.trc文件，点击“文件”*“导入性能数据”关联性能计数器日志，此时会打开一个文件浏览器窗口，选择刚刚保存的性能计数器日志文件进行关联;

　　5)在打开的窗口中选择所有计数器，点击“确定”，你将会看到下图所示的界面，它同时显示SQL事件探查器的跟踪信息和性能计数器日志;

　　图 28 关联SQL事件探查器和性能监视工具输出

　　6)在事件探查器跟踪信息输出中选择一条TSQL，你将会看到一个红色竖条，这代表这条TSQL执行时相关计数器的统计数据位置，同样，点击性能计数器日志输出曲线中高于正常值的点，你会看到对应的TSQL在SQL事件探查器输出中也是突出显示的。

　　我相信你学会如何关联这两个工具的输出数据后，一定会觉得非常方便和有趣。

　　小结

　　诊断SQL Server性能问题的工具和技术有很多，例如查看SQL Server日志文件，利用调优顾问(DTA)获得调优建议，无论使用哪种工具，你都需要深入了解内部的细节原因，只有找出最根本的原因之后，解决性能问题才会得心应手。

　　本系列最后一篇将介绍如何优化数据文件和应用分区。

　　优化技巧主要是面向DBA的，但我认为即使是开发人员也应该掌握这些技巧，因为不是每个开发团队都配有专门的DBA的。

　　第九步：合理组织数据库文件组和文件

　　创建SQL Server数据库时，数据库服务器会自动在文件系统上创建一系列的文件，之后创建的每一个数据库对象实际上都是存储在这些文件中的。SQL Server有下面三种文件：

　　1).mdf文件

　　这是最主要的数据文件，每个数据库只能有一个主数据文件，所有系统对象都存储在主数据文件中，如果不创建次要数据文件，所有用户对象(用户创建的数据库对象)也都存储在主数据文件中。

　　2).ndf文件

　　这些都是次要数据文件，它们是可选的，它们存储的都是用户创建的对象。

　　3).ldf文件

　　这些是事务日志文件，数量从一到几个不等，它里面存储的是事务日志。

　　默认情况下，创建SQL Server数据库时会自动创建主数据文件和事务日志文件，当然也可以修改这两个文件的属性，如保存路径。

　　文件组

　　为了便于管理和获得更好的性能，数据文件通常都进行了合理的分组，创建一个新的SQL Server数据库时，会自动创建主文件组，主数据文件就包含在主文件组中，主文件组也被设为默认组，因此所有新创建的用户对象都自动存储在主文件组中(具体说就是存储在主数据文件中)。

　　如果你想将你的用户对象(表、视图、存储过程和函数等)存储在次要数据文件中，那需要：

　　1)创建一个新的文件组，并将其设为默认文件组;

　　2)创建一个新的数据文件(.ndf)，将其归于第一步创建的新文件组中。

　　以后创建的对象就会全部存储在次要文件组中了。

　　注意：事务日志文件不属于任何文件组。

　　文件/文件组组织最佳实践

　　如果你的数据库不大，那么默认的文件/文件组应该就能满足你的需要，但如果你的数据库变得很大时(假设有1000MB)，你可以(应该)对文件/文件组进行调整以获得更好的性能，调整文件/文件组的最佳实践内容如下：

　　1)主文件组必须完全独立，它里面应该只存储系统对象，所有的用户对象都不应该放在主文件组中。主文件组也不应该设为默认组，将系统对象和用户对象分开可以获得更好的性能;

　　2)如果有多块硬盘，可以将每个文件组中的每个文件分配到每块硬盘上，这样可以实现分布式磁盘I/O，大大提高数据读写速度;

　　3)将访问频繁的表及其索引放到一个单独的文件组中，这样读取表数据和索引都会更快;

　　4)将访问频繁的包含Text和Image数据类型的列的表放到一个单独的文件组中，最好将其中的Text和Image列数据放在一个独立的硬盘中，这样检索该表的非Text和Image列时速度就不会受Text和Image列的影响;

　　5)将事务日志文件放在一个独立的硬盘上，千万不要和数据文件共用一块硬盘，日志操作属于写密集型操作，因此保证日志写入具有良好的I/O性能非常重要;

　　6)将“只读”表单独放到一个独立的文件组中，同样，将“只写”表单独放到一个文件组中，这样只读表的检索速度会更快，只写表的更新速度也会更快;

　　7)不要过度使用SQL Server的“自动增长”特性，因为自动增长的成本其实是很高的，设置“自动增长”值为一个合适的值，如一周，同样，也不要过度频繁地使用“自动收缩”特性，最好禁用掉自动收缩，改为手工收缩数据库大小，或使用调度操作，设置一个合理的时间间隔，如一个月。

　　第十步：在大表上应用分区

　　什么是表分区?

　　表分区就是将大表拆分成多个小表，以免检索数据时扫描的数据太多，这个思想参考了“分而治之”的理论。

　　当你的数据库中有一个大表(假设有上百万行记录)，如果其它优化技巧都用上了，但查询速度仍然非常慢时，你就应该考虑对这个表进行分区了。首先来看一下分区的类型：

　　水平分区：假设有一个表包括千万行记录，为了便于理解，假设表有一个自动增长的主键字段(如id)，我们可以将表拆分成10个独立的分区表，每个分区包含100万行记录，分区就要依据id字段的值实施，即第一个分区包含id值从1-1000000的记录，第二个分区包含1000001-2000000的记录，以此类推。这种以水平方向分割表的方式就叫做水平分区。

　　垂直分区：假设有一个表的列数和行数都非常多，其中某些列被经常访问，其余的列不是经常访问。由于表非常大，所有检索操作都很慢，因此需要基于频繁访问的列进行分区，这样我们可以将这个大表拆分成多个小表，每个小表由大表的一部分列组成，这种垂直拆分表的方法就叫做垂直分区。

　　另一个垂直分区的原则是按有索引的列无索引列进行拆分，但这种分区法需要小心，因为如果任何查询都涉及到检索这两个分区，SQL引擎不得不连接这两个分区，那样的话性能反而会低。

　　本文主要对水平分区做一介绍。

　　分区最佳实践

　　1)将大表分区后，将每个分区放在一个独立的文件中，并将这个文件存放在独立的硬盘上，这样数据库引擎可以同时并行检索多块硬盘上的不同数据文件，提高并发读写速度;

　　2)对于历史数据，可以考虑基于历史数据的“年龄”进行分区，例如，假设表中存储的是订单数据，可以使用订单日期列作为分区的依据，如将每年的订单数据做成一个分区。

　　如何分区?

　　假设Order表中包含了四年(1999-2002)的订单数据，有上百万的记录，那如果要对这个表进行分区，采取的步骤如下：

　　1)添加文件组

　　使用下面的命令创建一个文件组:

　　ALTER DATABASE OrderDB ADD FILEGROUP [1999]

　　ALTER DATABASE OrderDB ADD FILE (NAME = N'1999', FILENAME

　　= N'C:\OrderDB\1999.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO

　　FILEGROUP [1999]

　　通过上面的语句我们添加了一个文件组1999，然后增加了一个次要数据文件“C:\OrderDB\1999.ndf”到这个文件组中。

　　使用上面的命令再创建三个文件组2000，2001和2002，每个文件组存储一年的销售数据。

　　2)创建分区函数

　　分区函数是定义分界点的一个对象，使用下面的命令创建分区函数：

　　CREATE PARTITION FUNCTION FNOrderDateRange (DateTime) AS

　　RANGE LEFT FOR VALUES ('19991231', '20001231', '20011231')

　　上面的分区函数指定：

　　DateTime<=1999/12/31的记录进入第一个分区;

　　DateTime > 1999/12/31 且 <= 2000/12/31的记录进入第二个分区;

　　DateTime > 2000/12/31 且 <= 2001/12/31的记录进入第三个分区;

　　DateTime > 2001/12/31的记录进入第四个分区。

　　RANGE LEFT指定应该进入左边分区的边界值，例如小于或等于1999/12/31的值都应该进入第一个分区，下一个值就应该进入第二个分区了。如果使用RANGE RIGHT，边界值以及大于边界值的值都应该进入右边的分区，因此在这个例子中，边界值2000/12/31就应该进入第二个分区，小于这个边界值的值就应该进入第一个分区。

　　3)创建分区方案

　　通过分区方案在表/索引的分区和存储它们的文件组之间建立映射关系。创建分区方案的命令如下：

　　CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION FNOrderDateRange

　　TO ([1999], [2000], [2001], [2002])

　　在上面的命令中，我们指定了：

　　第一个分区应该进入1999文件组;

　　第二个分区就进入2000文件组;

　　第三个分区进入2001文件组;

　　第四个分区进入2002文件组。

　　4)在表上应用分区

　　至此，我们定义了必要的分区原则，现在需要做的就是给表分区了。首先使用DROP INDEX命令删除表上现有的聚集索引，通常主键上有聚集索引，如果是删除主键上的索引，还可以通过DROP CONSTRAINT删除主键来间接删除主键上的索引，如下面的命令删除PK_Orders主键：

　　ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

　　在分区方案上重新创建聚集索引，命令如下：

　　CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderDate) ON

　　OrderDatePScheme (OrderDate)

　　假设OrderDate列的数据在表中是唯一的，表将基于分区方案OrderDatePScheme被分区，最终被分成四个小的部分，存放在四个文件组中。如果你对如何分区还有不清楚的地方，建议你去看看微软的官方文章“SQL Server 2005中的分区表和索引”(地址：http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx)。

 

上一篇：最佳数据仓库体系--Hadoop

下一篇：已经是最后一篇