如何找到sql语句对应的存储过程?
大量的存储过程中包含有动态sql,这样的存储过程每次遇到不同的参数都会产生一个新的执行计划,一个时间段内plan cache内包含大量的执行计划,虽然很多都对应同一个存储过程.http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/5bd1c44a-c4ea-44e4-9e5d-f03e8719ce7c
下面这个查询可以返回产生多个执行计划的sql(仅对sql server 2008有效,引自Troubleshooting Performance Problems in SQL Server 2008)
代码
下面这个查询可以返回产生多个执行计划的sql(仅对sql server 2008有效,引自Troubleshooting Performance Problems in SQL Server 2008)
代码
select q.query_hash,
q.number_of_entries,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
q.number_of_entries,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
现在新的问题出现了,虽然找到了需要改进的t-sql语句(去除动态的方式),但无法找到这些t-sql对应的是哪个存储过程:(
