我如何知道SQL Server中存储过程内的长时间运行查询 [英] How do I know the long running queries inside a stored procedure in SQL server

问题描述

How do i know the long running queries inside a stored procedure in sql server

我尝试过：



我已经尝试了以下查询，但它显示了数据库中所有长时间运行的查询。但我只想在存储中运行长期的quries程序。



SELECT st.text，

qp.query_plan，

qs。*

FROM（

SELECT TOP 50 *

来自sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

）AS qs

CROSS APPLY sys.dm_exec_sql_text（qs.sql_handle）AS st

CROSS APPLY sys.dm_exec_query_plan（qs.plan_handle）AS qp

WHERE qs.max_worker_time> 300

OR qs.max_elapsed_time> 300

What I have tried:

I have tried the below query but it is showing all the long running queries in a data base.But I want only the long running quries inside a stored procedure.

SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300

推荐答案

尝试这种方式：

Try this way:

SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_procedure_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300

这篇关于我如何知道SQL Server中存储过程内的长时间运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！