SQL2005性能分析一些细节功能你是否有用到?

原文:SQL2005性能分析一些细节功能你是否有用到?

     我相信很多朋友对现在越来越大的数据量而感到苦恼,可是总要面对现实啊,包括本人在内的数据库菜鸟们在开发B/S程序时,往往只会关心自己的数据是否正确的查询出来,一旦自己写的程序哪天要花上十秒或者是一分种才会出来,此时就技穷了.如何优化成为菜鸟们的难题.本人不才,最近看了些园友关于数据库优化的文章,觉的有必要总结下,让更多像我一样只关心结果,并不关心质量的朋友少走些弯路.

    本文主旨:本文并非大谈高深技术(也没这本事),只是想总结一些数据库性能分析最基本的方法,有时候往往就是这些看似平常的功能能解决大问题.起码我工作三年差点,很少关心这些性能分析方法,我想目前也有很多朋友和我以前一样.

    第一: 您在执行SQL的时候是否对下图特别熟悉:图一

    

     一般根据大众分析SQL性能的基本方法:首先是看此SQL的执行IO成本,其次是看执行计划.

     图一就是SQL执行时的IO等统计信息.包括本人在内的数据库菜鸟对于这种IO信息一般都不太关注,我们更加关注的时把查询的数据查询出来就OK,至于这些内部的执行情况我们并不关心.以至于在看到园友的相关数据库文章后,就会有如下问题:

     Q:你这些信息是从哪得来的,有什么用,各参数都怎么去理解,对我们开发有什么帮助.

     要想让SQL把这些IO统计信息显示出来,我们要在执行的SQL前面显示调用如下命令SET STATISTICS IO ON, 本人现在就厚着脸皮贴下MSDN的说明:

     定义: SET STATISTICS IO { ON | OFF }如果 STATISTICS IO 为 ON，则显示统计信息。如果为 OFF，则不显示统计信息。如果将此选项设置为 ON，则所有后续的 Transact-SQL 语句将返回统计信息，直到将该选项设置为 OFF 为止。

     参数说明:
Table
 表的名称。
 
scan count
 执行的扫描次数。
 
logical reads
 从数据缓存读取的页数。
 
physical reads
 从磁盘读取的页数。
 
read-ahead reads
 为进行查询而放入缓存的页数。
 
lob logical reads
 从数据缓存读取的 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max)) 页的数目。
 
lob physical reads
 从磁盘读取的 text、ntext、image 或大值类型页的数目。
 
lob read-ahead reads
 为进行查询而放入缓存的 text、ntext、image 或大值类型页的数目。
 
    权限 :若要使用 SET STATISTICS IO，用户必须具有执行 Transact-SQL 语句的适当权限。SHOWPLAN 权限不是必需的。

    第二:执行计划图:如图二:

 

   

    它是分析SQL性能的重要指标,里面一般会包含执行语句的各部分开销占用比例情况.拿查询来说,它会非常清晰的显示出各种查询算法,查找索引,排序等占用的比例.开发员就可以根据这些参数来做些适当的优化方案.

    如何实现:

        第一:在执行语句前,按快捷键:Ctrl+M;
        第二:在菜单栏中点击图三中间的按钮即可.
  

    第三:如何解决SQL中的共享锁产生的死锁.

           定义: S(共享锁）：在执行查询数据时，SQL server会将行锁定，这时只能查询数据，删，改被阻塞。

    有的时候,在一个复杂的查询事务中由于种种原因可能非常耗时,这里我说下我理解的可能原因:

           原因一:数据量实在太多.

           原因二:查询语句本身有严重的性能问题.例如不合理的嵌套查询,子查询,不能充分利用索引等.

    所以在查询中出现的死锁,我们一般会在查询的表名后面加上(nolock)的参数,但是总觉的这样做不爽,既不美观,也好像不治本,难免下次又忘记了,所以本人推荐以下解决方案:引用下MSDN
    
    推荐方案:使用基于行版本控制的隔离级别

         行版本控制框架在 Microsoft SQL Server 中始终处于启用状态，并被多个功能使用。它除了提供基于行版本控制的隔离级别之外，还用于支持对触发器和多个活动结果集 (MARS) 会话的修改，以及 ONLINE 索引操作的数据读取。

        基于行版本控制的隔离级别是在数据库级别上启用的。访问已启用数据库的对象的任何应用程序可以使用以下隔离级别运行查询：已提交读隔离级别，通过将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 来使用行版本控制，如下面的代码示例所示：

       ALTER DATABASE AdventureWorks
       SET READ_COMMITTED_SNAPSHOT ON;

       为 READ_COMMITTED_SNAPSHOT 启用数据库后，在已提交读隔离级别下运行的所有查询将使用行版本控制，这意味着读取操作不会阻止更新操作。

      总结:虽然明白了这些基本的方法不一定能让你马上成为非常出色的数据库优化员,起码我们有了这个基础.天空任鸟飞,但我已飞过.重要的是过程,是分析问题的方法和理论.数据库优化的面太广,并非几篇短文就说的清的,本文的目的在于提醒现在还不关心SQL性能基本分析方法的朋友,知道总比不知道强.

 

注:

  本文引用: MSDN

                http://www.51cto.com/art/200707/52480.htm