Sql优化

查询语句/SP优化

SET STATISTICS PROFILE ,IO,TIME on 打开性能各种指数

1. 尽量避免使用非SARG运算符

在筛选条件中, 使用SARG运算符, 包括=, > , <, >=, <=, IN, BETWEEN, LIKE 等, 尽量避免使用非SARG运算符，SQL很可能不使用该字段上的索引，其中非SARG运算符包括NOT， <>， NOT EXISTS, NOT IN, NOT LIKE和内部函数，例如：ISNULL, CONVERT等。或者对字段先计算再比较，也极可能导致不使用索引。

举例说明：

……

SELECT  ……

FROM FGAuditRoom AS d WITH (NOLOCK)

          JOIN

          FGOrders AS c WITH (NOLOCK)

          ON c.OrderID = d.OrderID

          LEFT JOIN

          FGAuditRoomOther AS other WITH (NOLOCK)

          ON other.FGID = d.FGID

          WHERE Isnull(c.GetOrderDate,c.NoShowAuditDate) BETWEEN '2014-07-01' AND '2014-08-09'  ……

执行计划如下，计划并未走到NoShowAuditDate的索引上，而是用了主键索引扫描：

这里可以将此查询分为两条查询语句并将结果做UNION。

2. 尽量避免在有索引的字符字段上，通过非打头字母搜索

如果是使用like进行查询的话，尽量避免在通过非打头字符搜索，使用非打头字母搜索不使用该字段上的索引，比如：like 'a%' 使用索引，like '%a' 不使用索引。

3. EXISTS vs IN

对于EXISTS和IN，若两者逻辑相同SQL Optimizer通常会产生相同的执行计划；当子查询中返回较多记录时，EXISTS会优于IN；根据三值逻辑，对于NULL值的处理，IN将产生一个UNKNOWN的逻辑结果，因为UNKNOWN在filter中被当成false处理，所以IN和EXISTS会产生同样的结果，但对于NOT IN和NOT EXISTS，对于NULL值则会产生不同结果。

4. 对于NOT IN, NOT EXISTS，通过外链接实现

对于NOT IN, NOT EXISTS，考虑通过外连接，并判断为空来实现，连接的查询条件通过索引查找。

5. 若要使用NOT IN, NOT EXISTS, 选择NOT EXIST，尽量避免NOT IN

对于NOT IN与NOT EXISTS，并非等价替换，只有当子查询中不含有NULL值时，二者才会产生同样的结果；若子查询中有NULL值，NOT IN将返回不正确的空集结果，而NOT EXISTS将返回正确的结果。因为NOT IN (Value1, Value2, NULL)，将返回NOT TRUE或者NOT UNKNOWN(NOT UNKNOWN 等同于UNKNOWN，还是false)，都是非TRUE条件，所以始终返回空集。

6. 尽量避免使用OR运算符

对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集。

7. UNION ALL性能上优于UNION

UNION/UNION ALL，UNION会对联合的表格返回的结果集，做排序和去重复的操作Sort(Distinct…)，会对性能有一定影响，如果返回结果集中没有重复记录，应使用UNION ALL。

8. 同样查询语句除了参数外要完全一样以重用执行计划

为了让执行计划重用，必须把同样的查询语句写得完全一样，这样SQL Server才会认为它们是相同的语句，而重用执行计划。.

例如：exec sp_executesql @stmt1, @params; exec sp_executesql @stmt2, @params

Stmt1与stmt2要完全一样，执行计划才有可能被重用。

9. 使用Top语句时，尽量与ORDER BY子句联合使用

在使用Top子句时，要与ORDER BY子句联合使用，这样访问到数据和IO才是可预测的，否则SQL Server访问多少数据，造成多大IO都是不可预测的。

举例说明：

对于以下语句，

查询一天的执行计划，访问的行数远大于查询一个月的计划：

查询一个月时访问的数据：

10264 1 | |--Index Scan(OBJECT:([OrderDB].[dbo].[ORD_Orders].[idx_uid_orderid_status] AS [o]), WHERE:((CONVERT_IMPLICIT(int,[OrderDB].[dbo].[ORD_Orders].[Status] as [o].[Status],0)&[@0])=[@1])) 1

查询一天时访问的数据：
646570 9330.188 1 3.949725 | |--Index Scan(OBJECT:([OrderDB].[dbo].[ORD_Orders].[idx_uid_orderid_status] AS [o]), WHERE:((CONVERT_IMPLICIT(int,[OrderDB].[dbo].[ORD_Orders].[Status] as [o].[Status],0)&[@0])=[@1]))

10. 访问数据分布极不平均表格的查询或SP，考虑使用本地变量

在存储过程或查询中，访问了一张数据分布很不平均的表格，这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上，造成High CPU及大量IO Read等问题，使用本地变量防止走错执行计划。

采用本地变量的方式，SQL在编译的时候是不知道这个本地变量的值，这时候SQL会根据表格里数据的一般分布，“猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少，做出来的计划都是一样的。这样的计划一般会比较中庸一些，不一定是最优的计划，但一般也不会是最差的计划。

举例说明：

以下语句SQL错误估计了查询返回的行数，而选择了一个错误的执行计划，导致了查询执行时间很长，且导致了大量IO操作；Ord_operations有超过１亿条数据，对于这个查询最快的方式，是先在ord_orders上通过UID返回记录，然后通过取到的order_id到ord_operations表上通过其上的OrderID索引进行index seek。

SELECT op.OperateID, oo.OrderID,op.OperateTime,op.OperateType,op.OperateDesc,op.OperateType1

FROM ord_orders oo(NOLOCK)

INNER JOIN ord_operatetime op(NOLOCK) ON oo.orderid=op.orderid

WHERE op.OperateTime>'2014-08-22 00:00:00' AND oo.UID='obkwingon’

错误的计划：

在使用本地变量后，

declare @opt datetime

select @opt = '2014-08-15 00:00:00'

SELECT op.OperateID, oo.OrderID,op.OperateTime,op.OperateType,op.OperateDesc,op.OperateType1

FROM ord_orders oo(NOLOCK)

INNER JOIN ord_operatetime op(NOLOCK) ON oo.orderid=op.orderid

WHERE  op.OperateTime > @opt and oo.UID ='obkwingon'

order by oo.OrderID,op.OperateID

正确的计划：

11. 做JOIN操作，JOIN的查询条件上要有索引

对于Joins的使用，在连接的表中，若表的记录数很多，关联的字段上要建立索引，例如：A join B on A.Col1 = B.Col1，A表记录数很多时，在A表的Col1上建立索引。

在SQL中，连接的实现包括Nested Loop Join, Merge Join 以及Hash Join三种物理运算符。

对于Nested Loop Join的实现，SQL会选择一张返回记录较少的表格作为Outer table，其中的每一行会在Inner table中进行匹配找到对应的行，因此在返回记录较多的Inner Table上要建立对应索引，让查询走到Index Seek上。算法复杂度为：Inner Table * Outer Table。

对于Merge Join，对于连接的两张表，两个数据集要先排好序，有两种方式实现One-To-Many和Many-To-Many，当一张表的Join条件字段上的值是唯一时，会走到One-To-Many Join，这样算法复杂度仅为最大表的行数。但如果两张表中连接字段上的值都不唯一，则会使用Many-To-Many Join逻辑，这是需要内存中创建一个worktable来保存其中一张表的值，以用于与另一张表格做Match。这样会耗费大量的内存，如果表格较大的话。

对于Hash Join，SQL选择其中返回行数较少的表格的连接字段在内存中做成Hash Table(Hash Input)，另一张表中的值依次代入hash function，然后把结果在Hash Table中进行匹配。

12. 对于复杂的字符串操作，建议在.Net App端实现

对于字符串的操作，包括字符串匹配和字符串替换等，尽量在.Net应用程序端来实现，而不要在SQL端进行复杂的字符串操作，因为SQL Server对于字符串的处理效率很低，比如：正则表达式的匹配在.Net的实现效率就比在SQL Server中要高很多。

这里可以将结果取到应用程序中进行处理，或者开发者写好CLR assembly处理字符串操作，然后将此assembly Load到数据库中进行调用。

对于很大的文档的处理，可以考虑使用Full-Text Search来实现。

13. 尽量避免使用游标

不要使用游标，可以通过Select…into… 或者Insert…into…到临时表来实现。游标造成的问题有很多，比如延长锁问题，无法缓存执行计划以及加大内存与CPU开销问题，同时性能也很差。

14. 尽量避免使用触发器

不要使用触发器(Trigger), 在存储过程中实现触发器逻辑。

15. 根据实际情况选择使用临时表/表变量

在查询语句中，使用临时表还是表变量，临时表具有统计信息，可以在临时表上建立索引，而表变量没有统计信息，不能建索引。临时表对大数据能做更多优化，但维护成本较高，因此临时表适合于返回大的结果集；表变量适合于小的结果集。

16. 复杂语句通过多个简单语句实现

用多个简单语句代替一个复杂语句；对于复杂的语句，可以分拆成多条语句，将中间结果存入临时表中。

17. 考虑通过索引视图解决多表连接问题

若多张表常常要被联合Join，而且返回里面某一些字段，考虑用索引视图(indexed view)来实现，在一个视图上创建clustered index，SQL会实例化这个视图，同时SQL Server会自动与视图中访问到的表格数据保持一致，索引视图对查询的性能有很大帮助，可以大量减少IO和执行时间。而且，对于数据聚合（SUM, Average 等）查询或高成本的联接来说，利用索引视图可以显著地提高性能。对索引视图基础表的修改需要更新索引视图，从而降低了修改的性能。

举例说明：

--如何创建索引视图

CREATE VIEW dbo.empOrders WITH SCHEMABINDING

AS

SELECT O.EmployeeID, SUM(OD.Quantity) AS TotalQty, COUNT_BIG(*) AS Cnt

FROM dbo.Orders AS O

  JOIN dbo.[Order Details] AS OD

ON OD.OrderID = O.OrderID

GROUP BY O.EmployeeID;

GO

CREATE UNIQUE CLUSTERED INDEX idx_uc_empid ON dbo.empOrders(EmployeeID);

GO

18. 也可考虑通过字段冗余解决多表连接问题

表格连接的数量，对于大表格间的连接，性能会比较差，也可以考虑通过降低范式级别，保存冗余数据列，以减少表格的连接数量。

相对于索引视图，冗余需要改变已有的表结构，而索引视图则不需要。

19. 查询条件放在Outer Join的ON/WHERE子句产生不同结果

对于Inner Join, 把条件放在WHERE or ON子句从性能及返回结果没有差异。但对于Outer Join，则会返回不同的结果集。

例如：有两张表Students和Grades,

语句1：

select name , grades.grade as eng_grade

from students left outer join grades on students.id = grades.student_id

where grades.subject = 'English'

语句2：

select name , grades.grade as eng_grade

from students left outer join grades on students.id = grades.student_id and grades.subject = 'English'

语句1 仅返回那些学过英语的学生；语句2 返回所有学生，他们的英语评分或者NULL。

20. 尽量避免返回大结果集

尽量避免返回大的结果集，返回大量结果集会耗费大量CPU，IO及网络带宽。若需要大的结果集是，可以采用分页查询，控制每次查询访问和返回的记录数。

21. 避免使用Select *

在查询时尽量只返回需要的字段，若使用Select *，且此表格包含了Blob或NVarchar等大数据字段，会造成很大的Read IO，并占用大量带宽；而且，当你仅需要返回表的一些字段，同时在这些字段上建了覆盖索引，若使用Select *会阻止SQL Optimizer使用在这些字段上的覆盖索引。

22. 在事务中按统一顺序访问数据库对象

为了避免死锁，程序中按同一顺序来访问数据库对象。若所有并发事物按同一顺序访问对象，可以大大降低死锁的可能性。

比如：在事务1中 select表1，update表2；事务2中 update表2，select表1；这样容易造成死锁。

23. 保持事物简短

保持事务简短，事务执行时间越长，持有锁的时间也就越长，造成死锁的概率也越高。这和事务隔离级别相关，若设置为Repeated Read/Serializable，共享锁会持续到事务结束才释放。

24. 使用较低的事务隔离级别以减少阻塞和死锁

使用较低的事务隔离级别。这样共享锁的持有时间更短，从而减少阻塞和死锁。SQL中缺省的事务隔离级别为Read Committed.

25. 程序需要处理事务异常，及时回滚

当客户端数据库连接因为遇到执行超时，或其他异常原因，语句被提前终止了，连接还未做回滚就关闭了。连接会被放回连接池，而此连接上持有的锁和资源不会被释放，除非此连接被重用后，客户端才会发起rollback以释放锁和资源。这样易造成SQL Server的阻塞。

解决办法：

ü 应用程序做好意外处理，及时做Rollback。

ü 设置连接属性 "set xact_abort on"

ü 考虑关闭连接池，连接只有在被重用的时候，才会清理连接上次遗留下来的所有对象。

 

表格及索引优化

26. 对于大表进行分区，并建立分区索引

对于大的表格要进行分区，分区操作将表和索引分在多个分区，SQL Optimizer将查询定位到具体的分区上，而不是整张表，同时对于表格里旧数据的归档，也可以通过分区切换实现快速的替换，同时也可以避免在索引B-Tree上的争用。

这里的最佳实践是要创建多个文件组，不同的分区对应不同的文件组，这样可以达到更快的数据载入，同时又可以利用多个文件组的备份，恢复优势等。

根据需要选定分区字段，将分区放在不同文件组上，选择分区字段很关键，当你在创建了分区表后，想要改变分区字段，需要重建这张表。

对于SQL 2012，若用户想要针对某个分区在线重建分区索引，需要将分区offline，若要在线重建索引，需要对所有分区一起做；在SQL 2014中，可以在针对一个分区做在线分区索引重建。

查询条件中要带入在分区字段上的过滤，这样才能有效利用分区。

27. 每张表格要建立主键索引

每张表格上要有主键，以实现数据完整性约束。

28. 每张表格要建立聚集索引

每张表格上建立聚集索引，若创建了主键约束，聚集索引被自动创建。聚集索引要创建在很少重复值的字段上，这样有更高的可选择性。

29. 外键对更新和插入性能有影响

若表上建有外键，对表格上的更新和插入需要进行约束检查，从而影响性能。

30. 经常执行的查询，考虑建立覆盖索引

在创建索引时，经常被执行的查询；设计索引时应考虑，对于用户查找的字段作为key column要建上索引，对于Select的字段作为nonkey column同时也被Include在索引中这样查询可以在索引中定位到所有字段，减少bookmark lookup，从而减少IO，同时也能避免索引上字段数和index key size的限制。

31. 去除无用索引，控制索引数目

去除无用索引，若一张表上的索引数目过多，会增加很多的编译时间，将每张表上的索引数目控制在10个(? TBD)以内。

32. 索引要建立在重复值少的字段上

在创建索引时，索引要建在重复值少的字段上，且第一个字段才在统计信息中有其数据分布情况 (直方图)，所以创建联合索引时要注意尽量将重复值最少的字段放在索引的首字段。若索引建在重复数据很多的字段上，可选择性会很差，查询会走不到此索引上。

33. 非聚集索引要覆盖查询的条件

非聚集索引要覆盖查询的条件，比如：Select * from test1 where col1=’xxx’ and col2 = 5，索引建立在col1和col2上。

34. 加上联合索引的首字段作为查询条件

对于在多个字段上建立联合索引的情况下，若sarg中使用第二个或以后的字段作为查询条件，很可能会走到此索引的index scan。所以在查询条件中尽量加入索引的首字段作为条件。

举例说明：

在VendorRoomPrice表上，有websiteid, hotelid, baseroomid等字段，原来的表上在websiteid及hotelid上建立了一个非聚集索引，在运行以下语句是，计划走了此索引上的index scan，访问了大量数据，查询要经过19s才能结束。

select * from VendorRoomPrice where hotelid = <Hotel ID>

在此张表上hotelid, baseroomid加了一个非聚集索引后，此查询走到了此索引上，仅用时107ms就结束了，其中CPU时间仅为16ms。

35. 根据优先顺序创建索引

创建索引时，考虑以下的优先顺序，WHERE/JOIN>ORDER BY>SELECT。SQL在生成执行计划时，优先考虑在SARG条件中指定的字段。

数据库优化

36. TempDB的最佳实践

对于SQL Server，Tempdb对于系统性能非常重要，如何能让Tempdb达到最优的性能，对于Tempdb的优化至关重要，有以下建议，

a. 根据需要配置Tempdb的文件数目，比如：一个CPU创建一个数据文件，根据需要调整文件的数量。

b. 每个数据文件的大小要相同，这样可以让Workload平均分在多个数据文件上。

c. 尽可能给Tempdb的数据文件预分配好空间，这样可以减少文件扩展的IO开销。

d. 将文件增量设置为合理的大小以避免 tempdb 数据库文件的增量过小。如果文件的增量与写入 tempdb 的数据量相比过小，则 tempdb 可能需要不断扩大，导致很多额外的IO开销，这将影响性能。

e. 将Tempdb放在速度较快的存储上。比如：可以放置在SSD上以提升IO速率。

f. 考虑打开Trace Flag 1118，以减轻在高并发度情况下，对系统页SGAM, PFS页的争用，具体可参考http://support.microsoft.com/kb/328551。

37. 及时更新统计信息

统计信息对SQL Server至关重要，关系着SQL Server是否可以选择正确的执行计划，及时更新统计信息非常重要，其中的性能相关设置包括Sample Rate及更新统计信息的触发条件等。

当一张表格变得很大时，缺省的阈值20%对于更新统计信息而言太高了，比如：一张表有10亿条数据，若等到有2亿条数据被更新是才被触发，这样走错计划的概率将大大增大，且一次更新统计信息的对系统性能的影响会很巨大。

这里可以考虑打开SQL Server的Trace Flag 2371，由SQL Server来根据实际情况决定阈值，这对于依赖于每天晚上更新统计信息，且统计信息更新不够频繁的系统很有用。

http://support.microsoft.com/kb/2754171

38. 谨慎使用强制参数化

强制参数化实际上，改变了查询中的常数，使其在编译时参数化，这样可以对于多次重复执行的语句可以重用执行计划，以减少编译时间及CPU开销的目的。

同时使用了强制参数化后，对于有Indexed View，在Computed Column上创建了Index，或使用分区表的情况，SQL optimizer不大可能选用Indexed View, 或Computed Column上的索引。

若表格中的数据是highly skewed，分布很不均匀，这样会导致SQL Server重用了次优的计划，从而降低了执行效率和性能。

在数据库级别做强制参数化，需要严格测试确实对系统有帮助，若经常出现执行计划做错的情况，要衡量是否应该去强制参数化。若只是其中一些查询走错了计划，可以选择通过Plan guide让这些查询使用简单参数化，而非强制参数化。

SQL调优方法及工具

1. 看执行计划, IO, 及执行时间

可以通过以下方式查看，

Set showplan_all on, SQL在编译好或找到可重用的计划后，就输出，语句本身不被执行。

Set showplan_xml on, 执行计划以XML的格式输出，语句本身不被执行。

Set statistics profile on, SQL会在执行完语句后，输出执行计划。

Set statistics IO on, 看每条语句在每张表上的IO数。

Set statistics time on, 看每次执行的CPU时间及总耗时。

2. Perfmon

通过Windows Performance Monitor查看SQL Server的运行情况，这里有太多内容，就不展开描述了。

3. SQL Trace文件

可以通过SQL Profiler，可以通过SQL Profiler产生脚本，到SQL Server中运行。

4. 自动分析Trace的工具

可以通过使用ReadTrace工具，可以从 http://support.microsoft.com/kb/944837下载。也可运行SQL内嵌函数fn_trace_gettable将Trace导入到数据库表格里，通过查询分析。

5. 若要收集较全的SQL的运行信息，包括system info, errorlogs, windows events, dump files, perfmon counters, SQL Trace，可以运行SQLDiag/PSSDiag。

6. 收集SQL dump file，SQL Server在发生CPU non-yielding，AV等情况下会缺省产生user-mode mini-dump文件，也可以通过sqldumper.exe产生SQL Server的memory dump文件。根据需要，设置不同的Trace Flag产生不同类型的dump文件。

Trace flag 2551: 生成filtered dump

Trace flag 2544: 生成full dump

Trace flag 2546: Dump SQL Server所有线程

也可使用dbcc dumptrigger，指定在遇到某类型错误时，生成dump文件。

http://support.microsoft.com/kb/917825

7. Windbg，分析dump文件，须指定Symbol file，对于非Private Build的版本，可以指定到微软的symbol服务器上下载，可以通过设置.sympath SRV*d:\localsymbols*http://msdl.microsoft.com/download/symbols。