T-SQL 查询CPU/IO占用最大的进程

在服务器/SQL Server出现性能问题时，查询CPU/IO占用的状况：

CPU :

 

use tempdb

go

IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1]

GO

declare @ServerTime datetime = getdate()

, @ConvertMiliSeconds bigint = 1000

, @FilterMoreThanMiliSeconds bigint = 1

, @FilterHours bigint = 2

, @execution_count bigint = 2

, @debugFlg bit = 0



if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds

, @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours 

, @execution_count as execution_count





select TOP 300 @@servername as servername,@ServerTime as runtime
,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName 
,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1, 
(isnull((CASE QueryStats.statement_end_offset
WHEN -1 THEN DATALENGTH(QueryText.text)
WHEN 0 THEN DATALENGTH(QueryText.text)
ELSE QueryStats.statement_end_offset
END - QueryStats.statement_start_offset),0)/2) 
+ 1) AS QueryExecuted
,total_worker_time AS total_worker_time
,QueryStats.execution_count as execution_count
,statement_start_offset,statement_end_offset
,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName
,query_hash
,plan_handle
,sql_handle
into ##FindTopCPUQueries_set1
from sys.dm_exec_query_stats as QueryStats
cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText
where QueryStats.query_hash IN 
(select QueryStatsBaseTable.query_hash from sys.dm_exec_query_stats QueryStatsBaseTable where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE())
 group by query_hash
 having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count
)
ORDER BY total_worker_time/execution_count DESC;

if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted
IF object_id('tempdb..##FindTopCPUQueries_set2') is not null 
DROP TABLE [dbo].[##FindTopCPUQueries_set2]



select 
servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime
,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName
into ##FindTopCPUQueries_set2
from ##FindTopCPUQueries_set1
group by query_hash,servername,runtime
order by AvgCPUTime desc



select * from ##FindTopCPUQueries_set2
order by AvgCPUTime desc

 

IO :

SELECT TOP 10
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC