获取SP运行花费时间最长前10位/TSQL语句已经运行了多久

最新推荐文章于 2020-12-30 11:13:42 发布

starseeker7

最新推荐文章于 2020-12-30 11:13:42 发布

阅读量754

点赞数

分类专栏： SQL应用脚本

SQL应用脚本专栏收录该内容

31 篇文章

订阅专栏

本文提供SQL Server 2008中两种查询优化的方法：一是找出运行时间最长的前十位存储过程；二是获取当前正在运行的SQL语句及其执行时间。

获取SP运行花费时间最长前10位

SELECT TOP 10 a.object_id, a.database_id,schema_name(obj.schema_id) as 'prc schema', OBJECT_NAME(a.object_id, database_id) 'proc name',
a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time],
a.execution_count,
a.total_physical_reads/a.execution_count avg_physical_reads,
a.total_logical_writes,
a.total_logical_writes/ a.execution_count  avg_logical_reads,
a.last_elapsed_time,
a.total_elapsed_time / a.execution_count   avg_elapsed_time,
b.text,c.query_plan
FROM sys.dm_exec_procedure_stats AS a
inner join sys.objects as obj on a.object_id=obj.object_id 
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle)  b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
ORDER BY [total_worker_time] DESC;

来源：http://blogs.msdn.com/b/apgcdsd/archive/2011/05/13/sql-server-2008-sys-dm-exec-procedure-stats.aspx

获取正在运行的SQL跑了多久

declare @ms_per_tick decimal(10,6) --millisecond per tick
select @ms_per_tick=1.0*datediff(second, sqlserver_start_time ,getdate())/((ms_ticks-sqlserver_start_time_ms_ticks)/1000) FROM sys.[dm_os_sys_info];
--select @ms_per_tick

select req.session_id,
req.start_time request_start_time,
((select ms_ticks-workers.task_bound_ms_ticks from sys.dm_os_sys_info) )*@ms_per_tick 'ms_since_task_bound',
DATEDIFF(second,req.start_time,getdate())*1000 'ms_since_request_start',
tasks.task_state,workers.state worker_state,req.status request_state,
st.text,
SUBSTRING(st.text, (req.statement_start_offset/2)+1,
((CASE req.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2) + 1) AS stmt
,qp.query_plan
,req.*
from
sys.dm_exec_requests req
left join sys.dm_os_tasks tasks
on tasks.task_address=req.task_address
left join sys.dm_os_workers workers
on tasks.task_address=workers.task_address
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) qp
where (req.session_id >50 or req.session_id is null)

注意上面的@ms_per_tick用来计算一个tick相当于多少ms(毫秒), 一般而言，一个tick基本就是一个毫秒。

注意其中两个datediff存在返回值超过int范围的可能性，若提示以上问题，请修改datadiff的单位，并且在结果更改相应乘积。例如原始脚本中尉ms,我这里更改为s后成乘以了1000，若还不够可以更改为m，当遇到长期执行的的SQL做必要调整，忽略精度来获得参考值。

来源：http://blogs.msdn.com/b/apgcdsd/archive/2011/05/06/tsql.aspx