March 28, 2013 at 11:46 am
Im checking for sessions with a lot of activity against them.
im using the following query:
select R.cpu_time ,d.name,r.logical_reads, r.writes, s.* , R. *
FROM sys.dm_exec_requests R
join sys.dm_exec_sessions s on s.session_id = r.session_id
join sys.databases d on r.database_id = d.database_id
ORDER BY R.cpu_time DESC
for certain sessions that I know are busy ( as i can see them in Idera SQL DM), this query returns nothing. How can Idera SQL DM see the sessions and requests for those sessions, if there is nothing in the requests dmv?
March 28, 2013 at 12:05 pm
Update: I think idera must use the sysprocesses as it gives a lot of the data that im seeing.
I thought sys.dm_exec_requests was the updated version of sysprocesses and so should show everything that sysprocesses shows, but it seems that it doesnt.
in my case sysprocessess shows session 150 is executing a query and gives me the sql handle.
dm_exec_requests shows nothing for session 150.
am i missing somethign here, or is there functionality missing from the newer dmv's?
March 28, 2013 at 2:32 pm
Isn't sysprocess the system table behind sp_who2?
Take a ganders at sp_whoisactive - we use this at my company and shows lots of relevant info 🙂 - safe to use in production too.
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
March 28, 2013 at 4:50 pm
Yea I believe says processes is behind sp_whoisactive.
But my issue is that dm_exec_requeata, coupled with dm_exec_connections is supposed to be the new improved method for getting at the info the deprecated sysprocesses exposes, but the new dmv's don't actually expose the same info at all.
Just wonder if I have a misunderstanding of what sys.dm_exec_requests does, and how to get all the info exposed by sysprocesses using the new dmv's
March 28, 2013 at 5:23 pm
Just to close this off, Karen Delaney and aron Bertrand help a lot in this post and comments.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/06/29/why-i-still-need-sysprocesses.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply