SQL Server查询优化指南

1.找出执行时间最长的10条SQL（在Master 数据库执行）

SELECT top 10
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
) N'执行语句'
,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
)  like '%TestTbale%'   ---涉及testtable 表 的语句
ORDER BY total_elapsed_time / execution_count DESC;

 

如果想对SQL作筛选，可将  like '%TestTbale%'换成   like '%user%'就可以找出SQL语句中含有user关键字的SQL

2.总耗CPU最多的前20个SQL 

SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
--1 max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
--1 ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC -- 注释的--1为： 查询平均耗CPU最多的前20个SQL
ORDER BY total_worker_time DESC

 

1、尽量不要使用is null，否则将导致引擎放弃使用索引而进行全表扫描。

2、char是固定长度，速度快，但占空间，varchar不固定长度，不占空间，但速度慢。

3、能使用数字类型就不要使用字符，查询时字符需要逐一扫描，而数字类型一次够了。

4、尽量不要在where子句中使用!=或<>,会放弃索引而进行全表扫描,很多时候用 exists是一个好的选择。

5、应尽量避免在 where 子句中使用 or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT ID FROM T WHERE NUM=10 OR NUM=20  可以这样查询： SELECT ID FROM T WHERE NUM=10 UNION ALL SELECT ID FROM T WHERE NUM=20。

6、 IN和 NOT IN 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：SELECT ID FROM T WHERE NUM IN(1,2,3) 对于连续的数值，能用 BETWEEN就不要用 IN了，如：SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3。

7、尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子：SELECT * FROM T1 WHERE NAME LIKE '%L%'   ----> 无索引  SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)='L'   ----> 无索引   SELECT * FROM T1 WHERE NAME LIKE 'L%'  ----> 有索引

8、必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
如下面语句将进行全表扫描：SELECT ID FROM T WHERE NUM=@NUM
可以改为强制查询使用索引： SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM

9、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

• 如：SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2
• SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)='5378'应改为:  SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%'
• SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 应改为:  SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

10、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

11、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

12、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

13、很多时候用 exists是一个好的选择。

• 例：SELECT NUM FROM A WHERE NUM IN(SELECT NUM FROM B) 用下面的语句替换：SELECT NUM FROM A WHERE EXISTS(SELECT 1 FROM B WHERE NUM=A.NUM)
• SELECT SUM(T1.C1) FROM T1 WHERE(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0) 用下面的语句替换：SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)
• 两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
• 如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。
• 如： IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') 可以写成： IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
• 经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，
• 如：SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

 三种写法都可以得到同样正确的结果，但是效率依次降低。

14、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
15、避免频繁创建和删除临时表，以减少系统表资源的消耗。
16、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
17、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
注意：SELECT INTO 语句会导致表锁定，阻止其他用户访问该表
18、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 

19、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息，网络通信流量便会大量减少，可以显著提高应用程序性能。

20、充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。

21、能用DISTINCT的就不用GROUP BY。 
例如：SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID  可改为：SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

22、能用UNION ALL就不要用UNION 
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

23、尽量不要用SELECT INTO语句 。SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。

 

总结：上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试，看实现相同功能的SQL语句哪个执行时间最少，但是数据库中如果数据量很少，是比较不出来的，这时可以用查看执行计划，即：把实现相同功能的多条SQL语句考到查询分析器，按CTRL+L看查所利用的索引，表扫描次数（这两个对性能影响最大），总体上看询成本百分比即可。