找出执行时间最长的10条SQL

关注

原创最新推荐文章于 2026-07-23 11:52:44 发布 924 阅读 AI 写同款· GEO 优化 ›

查找系统中近一小时执行时间较长的前10条sql 查找系统中近一小时执行时间较长的前10条sql 1、找到耗时较久的sql的sql_id SELECT ACTIVITY_PCT, DB_TIME, SQL_ID FROM (SELECT ROUND(100 * RATIO_TO_REPORT(COUNT(1)) OVER(), 1) ACTIVITY_PCT, COUNT(1) DB_TIME, SQL_ID FROM V$ACTIVE_SESSION_HISTORY 阅读详情

ALTER TABLE [dbo].[PRIMARYCODE] DROP CONSTRAINT [PK__PRIMARYC__AA1D437813BCEBC1]
GO

ALTER TABLE [dbo].[PRIMARYCODE] ADD PRIMARY KEY CLUSTERED
(
[CODE] ASC
)

--平均耗CPU最多的前个SQL （SQL SERVER 2005或以上版本）

SELECT TOP 5 total_worker_time / execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC

5 平均耗CPU最多的前个SQL （SQL SERVER 2008或以上版本）

复制代码
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
复制代码

6 总耗CPU最多的前个SQL （SQL SERVER 2008或以上版本）

SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC