Microsoft的优化SQL方法(转)

第一种方法: 复制代码 代码如下:
Minimsdn.com为您提供的代码: — Turn ON [Display IO Info when execute
SQL] SET STATISTICS IO ON — Turn OFF [Display IO Info when execute
SQL] SET STATISTICS IO OFF Link: 第二种方法: 复制代码 代码如下: MINIMSDN.com为您提供的代码:
–Turn ON [Display detail info and the request for resources] SET
SHOWPLAN_ALL ON — Turn OFF [Display detail info and the request for
resources] SET SHOWPLAN_ALL OFF Link: 第三种方法: Links: ;
(v=SQL.80).aspx Demo For three kinds of Method: For SQL Script: 复制代码 代码如下: select * from
dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

        本文是SQL Server
SQL语句优化系列文章的第一篇。该系列文章描述了在Micosoft’s
SQLServer2000关系数据库管理系统中优化SELECT语句的基本技巧,我们讨论的技巧可在Microsoft’s
SQL Enterprise Manager或 Microsoft SQL Query
Analyzer(查询分析器)提供的Microsoft图形用户界面使用。

v Its Execution plan: ()

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

v Its IO info: ()

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

– – You can try one table with 100/10000/1000000 rows but create/don’t
create Clustered/NONCLUSTERED Index.

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

v Its Detail info Etc.: ()

  1. 使用SET STATISTICS IO 检查查询所产生的读和写
  2. 使用SET STATISTICS TIME检查查询的运行时间
  3. 使用SET SHOWPLAN 分析查询的查询计划

For SQL Script:

 

复制代码 代码如下: select top 100 * from
dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

    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 ON
         GO
         SELECT COUNT(*) FROM employees
         GO
         SET STATISTICS IO OFF
         GO
         Results:
         —————
         2977

         Table ‘Employees’ . Scan count 1, logical read 53, physical
reads 0, readahead reads 0.

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

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

         sp_spaceused employees
         Results:
         name rows reserved data index_size unused
         ————– ——– ——— ——- ————– ———
         Employees 2977 2008KB 1504KB 448KB 56KB

v Its Execution plan: ()

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

  • 这个查询没有扫描整个表,在表中的数据量超过1.5M字节,而仅仅执行了53个逻辑I/O操作就得到了结果。这表明该查询发现了一个可用来计算结果的索引,并且扫描索引比扫描所有数据页花费更少的I/O操作。
  • 索引页几乎全部放在数据缓存中,所以物理读的值是零。这是因为我们之前不久是在employees表上执行了其他查询,此时表和它的索引已经被缓存。你的查询开销可能有不同。
  • Microsoft报告没有read-ahead(预读)活动。在这种情况下,数据和索引页已经被缓存起来了。当对一个很大的表作表扫描时,read-ahead可能会半路插入进来,并且在你的查询用到它们之前缓存起所需的页。当SQL
    Server确定你的事务是顺序读取数据库页并且认为它能预测到你下一步将用到的页面时,Real-ahead会自动打开。实际上一个独立的SQL
    Server连接在你的进程之前已开始运行并为它缓存数据页。(配置和优化read-ahead
    参数已超出这篇文章的讨论范围。

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

 

v Its IO info: ()

    SET STATISTICS TIME

 

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

        SET STATISTICS TIME ON
         GO
         SELECT COUNT(*) FROM titleauthers
         GO
         SET STATISTICS TIME OFF
         GO
         Results:
         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还在处理一些其他事务,你得到的统计信息都可能有一点不同。

        如果你需要测量一系列的查询或者存储过程的实耗持续时间,更好的办法是采用程序设计的方式(如下所示)。当你运行多个命令时你不得不进行手工合计,这是因为STATISTICS
TIME只报告单个查询的持续时间。想象一下,当你对一个在循环里执行成千上万次查询的脚本进行计时的情况下,将面临大量的输出和大量的手工工作。

         相反,考虑下面这个脚本在事务的前后分别捕获时间并以秒的形式报告总持续时间(你也可以使用毫秒):

         DECLARE @start_time DATETIME
         SELECT @start_time=GETDATE()
         <any query or a script that you want to time, without a
GO>
         SELECT  ’Elapsed Time,sec’=DATEDIFF(second,
@start_time,GETDATE())
         GO

         如果你的脚本被GO分成几步,你不能用本地变量来保存开始时间。变量在GO命令执行后就被销毁。但你可以象这样在临时表里保存开始时间。

         CREATE TABLE #save_time (start_time DATETIME NOT NULL)
         INSERT #save_time VALUES ( GETDATE())
         GO

          < any script that you want to time (may include GO) >
         GO
         SELECT ‘Elapsed Time, sec’ = DATEDIFF ( second, start_time,
GETDATE())
         FROM TABLE #save_time
         DROP TABLE #save_time

         GO

         请注意,SQL Server’s DATETIME
数据类型存储的时间是以3毫秒为增量。使用DATETIME数据类型不可能获得比这更细的时间粒度。

v Its Detail info Etc.: ()

    SHOWPLAN 输出和分析

 

         这篇文章通过explain plan(解析计划)解释Microsoft SQL Server 2000
使用SET SHOWPLAN_TEXT ON 所输出内容的意义和用处。一个explain
plan(也被叫做查询计划,执行计划,或者优化计划)提供了数据库查询引擎执行SQL事务的十分详细的步骤。知道如何阅读explain
plan有助于提高高端查询调整和最优化的能力。

         注:大部分的例子要么是基于PUBS数据库,要么是基于SQL
Server系统表的.针对这些实例,我们给很多表增加了好几万条记录以便于在评估查询计划时体现查询优化器的实际作用。

For SQL Script:

    SHOWPLAN 输出

 

         我们喜欢查询优化器的一个功能就是以查询执行计划的形式提供反馈。现在我们可以更为详细地说明语句的执行,并描述你可能在查询计划中遇到的消息。理解这个输出可以使你的优化水平达到一个新高度。你可以不再把优化器视为一个可以处理你的查询语句的有魔力的“黑盒子”,

         下面的命令指示SQL
Server显示在同一个连接(或进程)中每个查询的执行计划,或将这个选项关闭。

        SET SHOWPLAN_TEXT { ON | OFF }

         默认情况下,SHOWPLAN_TEXT
ON使得你正在审查的代码不被执行。而是,SQL Server
编译这些代码并且显示这个查询的执行计划。直到你发出SET.SHOWPLAN_TEXT
OFF命令后它才停止。

发表评论

电子邮件地址不会被公开。 必填项已用*标注