November 22, 2012 at 9:34 am
I have been looking at the DMV's intended to replace SP_WHO and SP_WHO2. I have knocked up a query to get data from dm_exec_sessions and dm_exec_connections but dm_exec_requests only shows what is executing, and therefore the database id of what is executing, whilst SP_WHO and SP_WHO2 also show the dbname for sleeping connections.
SELECT ses.session_id, ses.status, ses.login_name, ses.host_name
, req.blocking_session_id, db_name(req.database_id)
, req.command, ses.cpu_time, ses.reads, ses.last_request_end_time
, ses.program_name
FROM [sys].[dm_exec_sessions] ses
JOIN [sys].[dm_exec_connections] con
ON [ses].[session_id] = [con].[session_id]
LEFT OUTER
JOIN [sys].[dm_exec_requests] req
ON [ses].[session_id] = [req].[session_id]
November 22, 2012 at 10:17 am
Forget sp_who & sp_who2
You want sp_whoisactive
http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 22, 2012 at 10:21 am
Have you see this utility proc MVP Adam Machanic produced for the community? Not only is it a replacement for sp_who but it's a great tool to learn more about the DMVs by inspecting the source code.
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply