CPU에 할당된 Task 보기

SQL SERVER 2005 이상

 

CPU에 해당하는 Task 보기

select  
	 ss.scheduler_id ,ss.parent_node_id,object_name(sql_text.objectid, sql_text.dbid)  as 'SP명'
	,ses.session_id , ses.host_name , ses.program_name , ses.client_interface_name 
	,ses.login_name 
	 ,substring(sql_text.text,sr.statement_start_offset/2,
			(case when sr.statement_end_offset = -1
			then len(convert(nvarchar(max), sql_text.text)) * 2
			else sr.statement_end_offset end - sr.statement_start_offset)/2) as query
from sys.dm_os_schedulers as ss with (nolock) 
inner join sys.dm_os_tasks as st with (nolock) on ss.scheduler_id = st.scheduler_id
inner join sys.dm_exec_requests as sr with (nolock) on st.request_id = sr.request_id
inner join sys.dm_exec_sessions as ses with (nolock) on sr.session_id = ses.session_id 
cross apply sys.dm_exec_sql_text(sr.sql_handle) as sql_text
-- where ss.parent_node_id = 2 -- numa 노드에서 실행되는 쿼리
-- where ss.scheduler_id = 24  -- cpu 스케줄에 할당된 쿼리