May 27, 2017 at 4:53 pm
This might sound very common question but unfortunately i haven't found a good script yet. I do use sp_whoisactive quite a bit, wait column on my spid comes back null, i am sure my query is waiting on something, i just don't know. Does anyone have a good script which tells what is the query currently waiting on when you filter on a given spid.
May 30, 2017 at 10:42 am
curious_sqldba - Saturday, May 27, 2017 4:53 PMThis might sound very common question but unfortunately i haven't found a good script yet. I do use sp_whoisactive quite a bit, wait column on my spid comes back null, i am sure my query is waiting on something, i just don't know. Does anyone have a good script which tells what is the query currently waiting on when you filter on a given spid.
There are a lot of options depending on what you are looking for and how you would want to drill down into things.
Maybe try querying just querying sys.dm_exec_requests to start - you can filter that by session id and it also has waits.
If you just want to see waits for user processes and if there is any blocking you could just run something basic to start like: SELECT
r.session_id,
r.command,
t.text,
r.wait_type,
r.wait_resource,
r.wait_time,
r.last_wait_type,
r.blocking_session_id
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
ON s.session_id=r.session_id
WHERE s.is_user_process=1
Even if you just query dm_os_waiting_tasks you can filter on session id. Just seeing if you have waits for your session id using dm_os_waiting_tasks is another place to start.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply