讨论一：性能优化-（执行计划，statistics,cost模型,参数嗅探，plan compile&recompile等）

开着拖拉机泡妞 2013-12-02 09:18:08

加精

经过一周时间的收集，今天开始针对这次活动中大家感兴趣的问题进行讨论：

讨论一：

性能优化-（执行计划，statistics,cost模型,参数嗅探，plan compile&recompile等）

...全文

4927 60 打赏收藏转发到动态举报

写回复

用AI写文章

60 条回复

切换为时间正序

请发表友善的回复…

发表回复

最爱午夜 2014-08-22

打赏
举报

引用 37 楼 SQL_Beginner 的回复:

经常在论坛看到问高CPU的问题，简单的总结一下。 1，首先你要确认，高CPU是不是是SQL SERVER进程引起的还是别的进程引起的，这个很容易，直接看任务管理器。 2，如果从任务管理器看出高CPU确实是用SQL SERVER引起的。 3，如果是SQL SERVER引起的。 3.1SQL SERVER的一个比较BAD的执行计划引起的，比如说缺少必要的INDEX，引起了hash join什么的。这个也分成2种： 1，造成高CPU的语句已经执行结束，这个时候可以用下面的语句来检查。 select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc 3.1.2，造成高CPU的语句正在运行，这个时候可以用下面的语句来检查。 SELECT st.text, qp.query_plan, rq.* FROM sys.dm_exec_requests RQ CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(rq.plan_handle) as qp order by RQ.CPU_time desc 3.2 开启了SQL profiler. 通过3.1.2可以看到 sp_trace_getdata这个SP在运行。 3.3 过度的编译跟重编译。可以通过下面的SQL来查 select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc 3.4 某个系统进程造成的，比如说 Ghost cleanup， Lazy writer等。可以通过select * from sys.sysprocesses where spid<51来查。造成具体的引起高CPU到底是什么引起的，然后对症下药。

我觉得，第二步应该是这样的：查看并行度，并行阀值，然后修改并行度和并行阀值。还有就是锁定多表连接查询的SQL和存储过程，如果执行频繁，有可能是数据库设计有问题或查询逻辑需要改。

我喝多了 2014-08-20

打赏
举报

学习

xxfvba 2014-08-12

打赏
举报

菜鸟学习ing

gem227 2014-08-11

打赏
举报

学习下

wangnaisheng 2014-07-31

打赏
举报

mark一下，学习~

yoan2014 2014-05-17

打赏
举报

这贴挖的不错，各位继续

jason131203 2014-05-16

打赏
举报

好东西啊，学习很多

开着拖拉机泡妞 2013-12-25

打赏
举报

下午跟大家交流一下set statistics io on的结果如何理解以及set statistics profile on输出的结果的一些重要字段的数据是什么含义，说明什么问题。

开着拖拉机泡妞 2013-12-25

打赏
举报

引用 47 楼 mcxhh2005 的回复:

执行计划中Lookup或key Lookup是什么意思？谢谢

RID LOOKP和KEY look up吧？这个是指的"书签查找" 这个可以通过修改索引来优化掉。但不一定什么时候都需要优化掉

杉子木 2013-12-24

打赏
举报

顶一下

专注or全面 2013-12-19

打赏
举报

Optimize for Ad hoc Workloads设为1和强制化参数的区别

专注or全面 2013-12-19

打赏
举报

请教执行计划的生成过程



dbcc freeproccache

select * from table1 where id=1
select * from table1 where id=2

select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects  where dbid=DB_ID('dbtest1')


cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Compiled Plan     Adhoc                1           select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects  where dbid=DB_ID('dbtest1')
Compiled Plan     Adhoc                1           select * from table1 where id=1
Compiled Plan     Adhoc                1           select * from table1 where id=2

(3 行受影响)



--直接拼sql的方式，生成的是Adhoc计划，没问题

--第二次测试，开启强制化参数
--下面三个sql分别执行一次，生成了一个Prepared类型的计划，并且重用了三次，可以理解，
--是强制化参数的结果
--但是这三句sql为什么还有三个Adhoc类型的计划生成，
--所以不太理解sql执行过程中，执行计划的产生过程


select * from table1 where id=1
select * from table1 where id=2
select * from table1 where id=3

select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects  where dbid=DB_ID('dbtest1')


cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Compiled Plan     Adhoc                4           select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects  where dbid=DB_ID('dbtest1')
Compiled Plan     Prepared             1           (@0 varchar(8000))select cacheobjtype , objtype , usecounts , sql from sys . syscacheobjects where dbid = DB_ID ( @0 )
Compiled Plan     Adhoc                1           select * from sys.syscacheobjects  where dbid=DB_ID('dbtest1')
Compiled Plan     Adhoc                1           select * from table1 where id=1
Compiled Plan     Prepared             1           (@0 varchar(8000))select * from sys . syscacheobjects where dbid = DB_ID ( @0 )
Compiled Plan     Adhoc                1           select * from table1 where id=2
Compiled Plan     Adhoc                1           select * from table1 where id=3
Compiled Plan     Prepared             3           (@0 int)select * from table1 where id = @0

(8 行受影响)

ChinaITOldMan 2013-12-19

打赏
举报

执行计划中Lookup或key Lookup是什么意思？谢谢

魔者侠心之明星级程序员 2013-12-16

打赏
举报

引用 45 楼 oreoconansisu 的回复:

1.想请教可否由浅入深的讲解如何根据执行计划做优化？ 2.有时查看执行计划时，会显示'建议添加Index...‘，是否每次都要根据提示添加Index呢? 3.怎么根据执行计划中的'嵌套，合并，哈希'连接做优化先谢谢了

都是好问题 1,这个要讲的太多了，可以用2本书来讲了，不过我简单的说一下思路，首先，就像做备份跟还原一样，你不应该考虑如何做一个备份计划，还是应该考虑如何做一个还原计划，然后根据还原计划来建备份计划一样。执行计划跟优化也是一样，你不应该首先考虑那个执行计划到底多不对，需不需要优化，还是应该考虑，你认为查询优化应该生成什么样的计划才是最好的，比如说select * from table1 where id=100, 你觉得这个语句的计划，到底是table scan好还是index seek好，其实在你的心里早有了答案，如果你认为table scan比index seek更优，但是实际生成的计划确是index seek，这个时候你就要去调查，为什么没有生成table scan，你可以强制他们用table scan或者index seek，看看到底那个计划的COST更少，如果跟你想象的不一样，你就要考虑，为什么就这样，比如说你明明认为table scan的COST更少，但是在执行的时候你发现index seek的COST更少，这个时候你就要去调查为什么会这样，是不是statistics有问题？之类的。 2，不一定，最明显的例子就是，比如说你那个查选只会运行一次，那肯定就没有必要加INDEX了，另外，建议添加Index并不是最优的，比如说，Index可以包含包含索引跟非包含索引，很多情况下包含索引的性能比非包含的要好，但是建议添加Index一般不会给你包含索引的提示。 3，这个你要非常的清楚每种的'嵌套，合并，哈希'连接它背后的算法，时间复杂度，空间复杂度，以及它们的一些限制条件。比如说合并它必须要求join的列都是排序的，如果没有排序那SQL 就会执行排序，所以这个时候的注意点就是不是有相关的INDEX建好了，这样可以避免排序带来的消耗。。。。。。