SQL2005性能分析一些细节功能

1、我相信很多朋友对现在越来越大的数据量而感到苦恼, 可是总要面对现实啊, 包括本人在内的数据库菜鸟们在开发B/S程序时 , 往往 只会关心自己的数据是否正确的查询出来, 一旦自己写的程序哪天要花上十秒或者是一分种才会出来, 此时就技穷了.如何优化成为菜鸟们的难题 . 本人不才 , 最近看了些园友关于数据库优化的文章,觉的有必要总结下, 让更多像我一样只关心结果 , 并不关心质量的朋友少走些弯路.本文主旨 : 本文并非大谈高深技术( 也没这本事 ), 只是想总结一些数据库性能分析最基本的方法 , 有时候往往就是这些看似平常的功能能解决大问题. 起码我工作三年差点, 很少关心这些性能分析方法,我想目

2、前也有很多朋友和我以前一样.第一 : 您在执行SQL的时候是否对下图特别熟悉: 图一一般根据大众分析 SQL 性能的基本方法 : 首先是看此 SQL 的执行 IO 成本 , 其次是看执行计划 .图一就是 SQL 执行时的IO 等统计信息 . 包括本人在内的数据库菜鸟对于这种IO 信息一般都不太关注, 我们更加关注的时把查询的数据查询出来就OK, 至于这些内部的执行情况我们并不关心 . 以至于在看到园友的相关数据库文章后,就会有如下问题:Q: 你这些信息是从哪得来的 , 有什么用 , 各参数都怎么去理解 , 对我们开发有什么帮助 .要想让 SQL 把这些 IO 统计信息显示出来, 我们要在执行的

3、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为进行查询而放入缓存的页

4、数。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 IOSHOWPLAN权限不是必需的。，用户必须具有执行Transact-SQL语句的适第二 : 执行计划图 : 如图二 :它是分析S

5、QL 性能的重要指标, 里面一般会包含执行语句的各部分开销占用比例情况.拿查询来说 , 它会非常清晰的显示出各种查询算法 , 查找索引 , 排序 等占用的比例 . 开发员就可以根据这些参数来做些适当的优化方案.如何实现 :第一 : 在执行语句前 , 按快捷键 : Ctrl+M;第二 : 在菜单栏中点击图三中间的按钮即可 .第三 : 如何解决SQL中的共享锁产生的死锁.定义 : S( 共享锁）：在执行查询数据时，SQL server会将行锁定，这时只能查询数据，删，改被阻塞.有的时候 , 在一个复杂的查询事务中由于种种原因可能非常耗时 , 这里我说下我理解的可能原因 :原因一 : 数据量实在太多

6、.原因二 : 查询语句本身有严重的性能问题 . 例如不合理的嵌套查询 , 子查询 , 不能充分利用索引等 .所以在查询中出现的死锁, 我们一般会在查询的表名后面加上(nolock)的参数 , 但是总觉的这样做不爽, 既不美观 , 也好像不治本, 难免下次又忘记了, 所以本人推荐以下解决方案:引用下 MSDN推荐方案 : 使用基于行版本控制的隔离级别行版本控制框架在 Microsoft SQL Server 中始终处于启用状态， 并被多个功能使用。它除了提供基于行版本控制的隔离级别之外，还用于支持对触发器和多个活动结果集会话的修改，以及 ONLINE 索引操作的数据读取。 (MARS)基于行版本

7、控制的隔离级别是在数据库级别上启用的。 访问已启用数据库的对象的任何使用程序可以使用以下隔离级别运行查询：已提交读隔离级别，通过将READ_COMMITTED_SNAPSHOT数据库选项设置为ON来使用行版本控制，如下面的代码示例所示：ALTER DATABASE AdventureWorksSET READ_COMMITTED_SNAPSHOT ON;为 READ_COMMITTED_SNAPSHOT启用数据库后，在已提交读隔离级别下运行的所有查询将使用行版本控制，这意味着读取操作不会阻止更新操作。总结 :虽然明白了这些基本的方法不一定能让你马上成为非常出色的数据库优化员, 起码我们有了这个

8、基础. 天空任鸟飞 , 但我已飞过 . 重要的是过程 , 是分析问题的方法和理论. 数据库优化的面太广,并非几篇短文就说的清的,本文的目的在于提醒现在还不关心SQL 性能基本分析方法的朋友, 知道总比不知道强.前面提到了根据SQL 的执行 IO 和执行计划来分析, 还有一个特别重要的参数, 就是 SETSTATISTICS TIME.引用 :MSDN第一 : SET STATISTICS TIME定义 :SET STATISTICS TIME (Transact-SQL)句所需的毫秒数。显示分析、 编译和执行各语语法 :SET STATISTICS TIME ON | OFF 为OFF备注 :

9、 当 SET STATISTICS TIME为 ON时，会显示语句的时间统计信息。时，不显示时间统计信息。SET STATISTICS TIME的设置是在执行或运行时设置，而不是在分析时设置。权限Transact-SQL: 若要使用SET STATISTICS TIME，用户必须具有执行语句的相应权限。但不需要SHOWPLAN权限。作用 : 我们可以利用这个参数来查看SQL的 CPU开销情况, 进而做出优化方案.效果图:第二 :sp_help定义 : 报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或Microsoft? SQL Server?所提供的数据类型的信

10、息。语法:sp_help objname = name 参数: objname = name是 sysobjects中的任意对象的名称，或者是在 systypes表中任何用户定义数据类型的名称。不能使用数据库名称。效果图 :内容 :1: 表结构 , 它会列出目标表中所有的字段信息: 名称 , 数据类型等 .2: 索引情况 , 包含聚集索引和非聚集索引 . 索引所对应的字段等 .3: 表字段的约束情况4: 表的基本信息 ,表类型 , 创建时间 , 表名等 .5: 主键详细信息 .用途一 : 一般对于性能分析关系有直接关系的有1,2,5. 开发员可以非常快捷清晰的查看表的设计情况. 对于查询来说最

11、重要的就是表的索引情况和表结构. 对于其它的用途本人暂不胡说了.案例 : 一次实际开发中,运行一个单表查询, 发面页面报出SQL05 timeout错误 , 后来在数据库中直接运行语句发现时间特别长,达到 58m, 查看记录 , 一个长达的字样把我吓到了,没办法只能是优化了,sp_help下 , 发现条件中的字段没有创建索引建后再试 ,基本控制在1m 左右 .的9 位数, 创第三:SET NOCOUNT定义 : 阻止在结果集中返回可显示受Transact-SQL语句或存储过程影响的行计数的消息。语法 :SET NOCOUNT ON | OFF 备注 : 当 SET NOCOUNT为 ON时，不

12、返回计数。当SET NOCOUNTOFF 时，返回计数。即使当SET NOCOUNT为 ON时，也更新ROWCOUNT数。当SET NOCOUNT为 ON时，将不向客户端发送存储过程中每个语句的为函DONE_IN_PROC消息。如果存储过程中包含一些并不返回许多实际数据的语句，网络通信流量便会大量减少，因此，将SET NOCOUNT设置为ON可显著提高性能。NOCOUNT指定的设置是在执行或运行时生效，而不是在分析时生效。SET权限 : 要求具有public角色的成员身份。注意点:ExecuteNonQuery();将得不到返回值, 此时可以利用存储过程的返回参数来获得.缺点 : 当我们执行一条更新或者是删除语句时返回的受影响的行来判断,现在当 SET NOCOUNT为参数来判断了 .,一般判断是否成功都是根据这个ON时，不返回计数, 只能利用输出优点 : 如果存储过程中包含一些并不返回许多实际数据的语句，会大量减少，因此，将SET NOCOUNT设置