![]() The advantage over sp_who2 is that you control what is included and how it is sorted. If you want to find only those processes for login 'xyz' Where s.is_user_process = 1 It can be filtered by changing the where clause to whatever is needed - in my case I use it to see what is currently executing at that point in time which is why it is filtering for r.Command Is Not Null.įor example, to identify the head blocker you would comment out the line with r.Command Is Not Null - and modify the Order By to: In fact - it is the exact same query that is executed except I changed the section that is using ROW_NUMBER() to an OUTER APPLY (fixes the problem with timing out). The whole idea behind the script I posted is that it will return the same information as Activity Monitor. ![]() To show all user processes and change the order by as needed.Īnother alternative is to use sp_who2 - or download and install sp_WhoIsActive: ![]() S.login_name -r.command descĬomment out the line: And r.command Is Not Null - only show processes with active commands Left Join profiled_sessions ps On ps.profiled_session_id = s.session_idĪnd r.command Is Not Null - only show processes with active commandsĪnd s.session_id - don't need to show this session Left Join sys.sysprocesses p On s.session_id = p.spid Left Join sys.dm_resource_governor_workload_groups g On g.group_id = s.group_id Left Join sys.dm_exec_requests r2 On s.session_id = r2.blocking_session_id Where wt.waiting_task_address = t.task_address = and use it as representative of the other wait relationships this thread is involved in. Use OUTER APPLY and TOP 1 to select the longest wait for each thread, This will cause that thread to show up in multiple rows = waiting for several different threads. parallel queries, also waiting for a worker), one thread can be flagged as Left Join sys.dm_os_tasks t On r.session_id = t.session_id Left Join sys.dm_exec_requests r On s.session_id = r.session_id Left Join sys.dm_exec_connections c On s.session_id = c.session_id , = iif(r2.session_id Is Not Null And (r.blocking_session_id = 0 Or r.session_id Is Null), N'1', N'') , = isnull(convert(varchar, w.blocking_session_id), '') If that is the issue - you can use this script to get the same data as you would from the processes tab: With profiled_sessions ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |