SQL脚本--总耗CPU最多的前个SQL --平均耗CPU最多的前个SQL

迷南。 2021-12-21 01:03 289阅读 0赞

\--总耗CPU最多的前个SQL  
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

\--平均耗CPU最多的前个SQL  
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

转载于:https://www.cnblogs.com/renzhituteng/p/10516268.html