讨论一:性能优化-(执行计划,statistics,cost模型,参数嗅探,plan compile&recompile等)

开着拖拉机泡妞 2013-12-02 09:18:08
加精
经过一周时间的收集,今天开始针对这次活动中大家感兴趣的问题进行讨论:

讨论一:

性能优化-(执行计划,statistics,cost模型,参数嗅探,plan compile&recompile等)
...全文
4880 60 打赏 收藏 举报
写回复
60 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
最爱午夜 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
好东西啊,学习很多
  • 打赏
  • 举报
回复
下午跟大家交流一下set statistics io on的结果如何理解以及set statistics profile on输出的结果的一些重要字段的数据是什么含义,说明什么问题。
  • 打赏
  • 举报
回复
引用 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是什么意思?谢谢
  • 打赏
  • 举报
回复
引用 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建好了,这样可以避免排序带来的消耗。 。。。。。
  • 打赏
  • 举报
回复
oreoconansisu 2013-12-14
1.想请教可否由浅入深的讲解如何根据执行计划做优化? 2.有时查看执行计划时,会显示'建议添加Index...‘,是否每次都要根据提示添加Index呢? 3.怎么根据执行计划中的'嵌套,合并,哈希'连接做优化 先谢谢了
  • 打赏
  • 举报
回复
Neo_whl 2013-12-13
完全萌,帮顶!
  • 打赏
  • 举报
回复
-江沐风- 2013-12-12
帮顶一下!
  • 打赏
  • 举报
回复
Leon_He2014 2013-12-07
涨姿势了。。。。
  • 打赏
  • 举报
回复
flowerjack 2013-12-07
mark
  • 打赏
  • 举报
回复
天後2013 2013-12-07
statistics,cost模型我还没摸透呢。
  • 打赏
  • 举报
回复
加载更多回复(37)
相关推荐
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2013-12-02 09:18
社区公告
暂无公告