SQL语句执行效率瓶颈

影响SQL瓶颈的因素有很多，包括内存不足，硬件不行，或者没有足够的内存供SQL Server 使用，缺少有用的索引等，网络通讯不好，磁盘配置了，如tempdb 的配置，是否为查询优化器提供了优化复杂查询的最有利条件

1.1  T-SQL代码返回了不必要的数据

1、缺少WHERE子句，除非你要返回表里所有的数据，而这种情况几乎很少，

在减少返回行的数量时使用WHERE子句是必要的。

例如，如果你仅需返回特定日期的记录，而不是返回月或年的所有记录。设计WHERE语句以便能正好仅仅返回需要的那些行，而不要有额外的行。

2、在SELECT语句里，仅仅包括需要的那些列，而不是所有列。同样，当最可能要返回需要的更多的行时，不是使用SELECT *。

3、引用不必要的数据会产生哪些性能问题：读数据需要额外的I/O开销；浪费缓存空间；产生不必要的网络流量；在客户端，内存不得不存储这些额外的数据，而这部分内存可以被其他应用更好的使用；更重要的是：有时，返回太多的数据会强迫查询优化器执行表扫描而不是索引查找。

 

1.2  在不必要的地方使用了游标

游标一直是一个备受正义的东西，因为游标是过程逻辑，关系模型是基于集合的逻辑。也就是说，游标是要写大量的代码都专注于“如何”处理数据，但是你要使用基于集合的逻辑是，只需要很少的代码。而且这些代码专注于你要获取“什么”而不是如何获取。在需要程序逻辑或便利的情况，每次处理一行的情况下使用游标。例如：给用户发送E-mail，每行都要去调用一个发邮件的存储过程，还有用户订单处理。

定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，尽量避免使用全局（GLOBAL，这是数据库的默认行为）游标；没有特殊需要的话，尽量使用FORWA RD_ONLY  READ_ONLY STATIC游标；

FORWA RD_ONLY指定游标只能从第一行滚动到最后一行.

 

注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。

游标的解决方案基本上都是强制优化器去执行固定的执行计划的,没有基于集合那样优化器会出来很多解决方法然后挑选比较合适的。

在我们的开发过程中游标要慎用，但确实需要逐条处理的时候，那就不用要用其他方案。

例如我们的存储过程中比较常见的一种做法是数据按照顺序放在一个自增的临时表中，然后用循环来处理，实际上这种做法没有游标的方案好。

下面的情况比较适合使用游标来处理（参考demo3__游标1.sql和demo4__游标2.sql）

 

1.3  UNION和UNION ALL使用是否得当

许多人没完全理解UNION和UNIONALL是怎样工作的，因此，结果浪费了大量不必要的SQLServer资源.当使用UNION时，它相当于在结果集上执行UNIONALL。换句话说，UNION将联合两个相类似的记录集，然后搜索潜在的重复的记录并排重。如果这是你的目的，那么使用UNION是正确的。但如果你使用UNION联合的两个记录集没有重复记录，那么使用UNION会浪费资源，因为它要寻找重复记录，即使它们不存在。所以如果你知道你要联合的记录集里没有重复，那么你要使用UNION，而不是UNION。UNION ALL联合记录集，但不搜索重复记录，这样减少。

SQLServer资源的使用，从而全面提升性能。

1.4  SELECT DISTINCT使用是否得当

因为DISTINCT子句要求存储结果集然后去重，这样增加SQLServer有用资源的使用。当然，如果你需要去做，那就只有去做了。当如果你知道SELECT语句将从不返回重复记录，那么使用DISTINCT语句对SQLServer资源不必要的浪费。

需要DISTINCT的时候，尽量把它作用在小少的列的上，例如有两个表JOIN，如果DISTINCT作用在一个表上的列已经达到了目的（这个一般从业务上可以确定），那么另一个表的JOIN，在DISTINCT之后才去做（子查询）例：

SELECTDISTINCTa.id,b.name,b.score

FROMtaASaWITH(NOLOCK)

JOINtbbWITH(NOLOCK)

ONa.id=b.id

--改写为

SELECTa.id,b.name,b.score

FROM

(

    SELECTDISTINCTidFROMtaWITH(NOLOCK)

)ASas

JOINtbbWITH(NOLOCK)ONa.id=b.id

 

1.5  WHERE子句是可SAGE的吗？

SARG源于"Search ARGument"（搜索参数/ 扫描参数）的首字母拼成

它是指WHERE子句里列和常量的比较。如果SELECT子句是sargable（可SARG的），这意味着它能利用索引加速查询的完成。如果SELECT子句不是可SARG的，这意味着SELECT子句不能利用索引（或

 

查询1走得是TABLE SCAN 因为，查询分析器预估该查询返回的行数会很多，也就是满足条件的记录站总数的比例比较大，所以会选择TABLE SCAN

查询2 走的是INDEX SEEK因为该查询预估返回的行很少，或者没有结果

查询3 用到了函数，它和2比起来它只能走INDEX SCAN或者TABLE SCAN,所以尽量不要在索引列上使用函数，这样会降低索引的使用率。

比较常见的是使用其实函数如：

WHEREDATEDIFF(DD,adddate,GETDATE())=1

建议改为

WHEREadddate>=CONVERT(VARCHAR(10),GETDATE(),120)

在看下面的语句，已知name的数据类型是VARCHAR

SELECT*FROMtest_objectsWHEREname=N'maxint'

可以看到SQL对此作了数据类型转换，比去掉N多了一个步骤，这种细微的开销会随着数据量的增大或查询的频繁而增加，在SQL SERVER 2008中优化器选择INDEX SEEK，但是在05中它会选择INDEX SCAN

所以在查询的时候要确定等号两边的数据类型一致，在做联接查询的时候尤为重要。

1.6  在不必要的时候使用了临时表

1、果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

2、需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

3、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

4、他情况下，应该控制临时表和表变量的使用。

5、表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

6、关于临时表产生使用SELECTINTO和CREATETABLE+INSERTINTO的选择，我们做过测试，一般情况下，SELECTINTO会比CREATETABLE+INSERTINTO的方法快很多，但是SELECTINTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATETABLE+INSERTINTO，而大数据量的单个语句使用中，使用SELECTINTO。

1.7  存储过程中是否使用了SET NOCOUNT ON

每次存储过程执行时，一个消息会从服务端发给客户端以显示存储过程影响的行数。这些信息对客户端来说很少有用。通过关闭这个缺省值，你能减少在服务端和客户端的网络流量，帮助全面提升服务器和应用程序的性能。在每个存储过程的开头包含SETNOCOUNTON语句

1.8  要所有的存储过程的拥有者是DBO吗？引用的形式是databaseowner.objectname吗？

为了最好的性能，同一个存储过程里调用的所有对象的拥有者都应该相同，DBO更适宜。在我们的系统中所有对象的拥有者都是DBO，如果不是那样，即对象名相同而拥有者不同，那么SQLServer必须执行名称判断。当发生这样的情形时，SQLServer不能使用存储过程里在内存里的执行计划，相反，它必须重新编译存储过程，从而影响性能。
当从应用程序里调用存储过程时，使用分隔符名称来调用也是重要的。如： 
EXEC dbo.myProcedure

代替：

EXEC myProcedure

1.9  事务的影响范围应该尽可能小

在使用事务时，原则上应该使事务尽可能得短并且要避免事务嵌套。事务应该尽可能得短，这是因为比较长的事务增加了事务占用数据的时间，使其它必须等待访问该事务锁定数据的事务，延长了等待访问数据的时间。在使用事务时，为了使事务尽可能得短，应该采取一些相应的方法，例如，不应该把SELECT放在事务中，因为所有的SELECT都要求使用WITH(NOLOCK)，这个放在事务中没有任何意义。一些没有涉及到实体表的操作都放在事物外面。

提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQLSERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更优，但是数据量和数据分布在将来是会变化的。