Microsoft的优化SQL方法

qianjin558 2007-04-09 01:59:24

除调优方法外，我们给你展示了最佳实践，你可应用到你的SQL语句中以提高性能（所有的例子和语法都已在Microsoft SQL Server 2000中验证）。

阅读该系列文章后，你应该对Microsoft 工具包中提供的查询优化工具和技巧有一个基本的了解，我们将提供包含各种各样的以提高性能和加速数据读取操作的查询技巧。

Microsoft提供了三种调优查询的主要的方法：

1、使用SET STATISTICS IO 检查查询所产生的读和写

2、使用SET STATISTICS TIME检查查询的运行时间

3、使用SET SHOWPLAN 分析查询的查询计划

SET STATISTICS IO

命令SET STATISTICS IO ON 强制SQL Server 报告执行事务时I/O的实际活动。它不能与SET NOEXEC ON 选项配对使用，因为它仅仅对监测实际执行命令的I/O活动有意义。一旦这个选项被打开，每个查询产生包括I/O统计信息的额外输出。为了关闭这个选项，执行SET STATISTICS IO OFF.

注：这些命令也能在 Sybase Adaptive Server中运行，虽然结果集可能看起来有点不同。

例如，下面是在Northwind 数据库中对于employees表上的一个行统计的简单查询脚本而获得的I/O统计信息。

SET STATISTICS IO ONGOSELECT COUNT(*) FROM employeesGOSET STATISTICS IO OFFGOResults:---------------2977Table ‘Employees’ . Scan count 1, logical read 53, physical reads 0, readahead reads 0.

这个扫描统计告诉我们扫描执行的数量，逻辑读显示的是从缓存中读出来的页面的数量，物理读显示的是从磁盘中读的页面的数量，Read-ahead 读显示了放置在缓存中用于将来读操作的页面数量。

此外，我们执行一个系统存储过程获得表大小的统计信息以供我们分析：

sp_spaceused employeesResults:name rows reserved data index_size unused-------------- -------- --------- ------- --------------Employees 2977 2008KB 1504KB 448KB 56KB

通过看这些信息我们能得到些什么呢？

◆这个查询没有扫描整个表，在表中的数据量超过1.5M字节，而仅仅执行了53个逻辑I/O操作就得到了结果。这表明该查询发现了一个可用来计算结果的索引，并且扫描索引比扫描所有数据页花费更少的I/O操作。

◆索引页几乎全部放在数据缓存中，所以物理读的值是零。这是因为我们之前不久是在employees表上执行了其他查询，此时表和它的索引已经被缓存。你的查询开销可能有不同。

◆Microsoft报告没有read-ahead（预读）活动。在这种情况下，数据和索引页已经被缓存起来了。当对一个很大的表作表扫描时，read-ahead可能会半路插入进来，并且在你的查询用到它们之前缓存起所需的页。当SQL Server确定你的事务是顺序读取数据库页并且认为它能预测到你下一步将用到的页面时，Real-ahead会自动打开。实际上一个独立的SQL Server连接在你的进程之前已开始运行并为它缓存数据页。（配置和优化read-ahead 参数已超出这篇文章的讨论范围。

在这个例子中，该查询已经尽可能有效率地执行了，不必进一步优化。

SET STATISTICS TIME

一个事务的实耗时间是一个不稳定的测量，因为这些时间与在服务器上其他用户的活动有关。然而，相比那些对你的用户没有任何意义的数据页数字，它提供了一些实际的测量。他们关心等待查询返回的时间消耗，不关心数据的缓存和有效的read-ahead。SET STATISTICS TIME ON命令报告下面的查询的实际占用时间和CPU使用情况。执行SET STATISTICS TIME OFF禁止这个选项。

SET STATISTICS TIME ONGOSELECT COUNT（*） FROM titleauthersGOSET STATISTICS TIME OFFGOResults:SQL Server Execution Times;Cup time=0 ms. Elapsed time=8672 ms.SQL Server Parse and Compile Time:Cpu time=10 ms----------------25(1 row(s) affected)SQL Servre Execution Times:Cpu time=0 ms.? Elapsed time=10 ms.SQL Server Parse and Compile Time:Cup time=0 ms

第一条信息报告了多少使人困惑的占用（实耗）时间，8672豪秒，这个数据与我们的脚本不相关，这显示的是之前一个命令执行以来逝去的时间。你可以忽略这条信息。SQL Server仅仅花费10毫秒时间去分析和编译该查询。花费0毫秒去执行它（在查询结果可看到）。其真实的意思是这个查询所花费的时间太短以至不能计量。最后的信息报告了这个SET STATISTICS TIME OFF命令相关的分析及编译花费了0毫秒。你可以忽略这个信息。最重要的信息以加重字体突出显示。

注意实耗时间和CPU时间是以毫秒显示。这个数字在你的电脑上可能会改变（但是不要尝试与我们的笔记本电脑比较你机器的性能，因为这不是代表性的指标）。而且，每次你执行这个脚本，考虑到你的SQL Server还在处理一些其他事务，你得到的统计信息都可能有一点不同。

转http://database.ccidnet.com

...全文