June 13, 2013 at 7:47 am
Is there a way to identify actively running queries or stored procedures ? If I am in Activity Monitor, and click "Details" on a spid, sometimes I can see what an application or user is running.
Is there a way to dynamically capture the same information in one place for all connections so I can see all processes hitting SQL ?
EDIT: Running SQL 2005, so maybe I posted in the wrong forum.
June 13, 2013 at 8:00 am
You can write your own queries, using the DMVs, which is a good learning exercise, but I'd recommend using the brilliant sp_whoisactive.
June 13, 2013 at 8:09 am
I use the following:
Any SPID above 50
sp_who2
This gives a little more info on what sp is actually running
SELECT
sp.spid
, sp.blocked AS BlockingProcess
, DB_NAME(sp.dbid) AS DatabaseName
, sp.loginame
, CAST(text AS VARCHAR(1000)) AS SqlStatement
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle)
June 13, 2013 at 9:55 am
HowardW (6/13/2013)
You can write your own queries, using the DMVs, which is a good learning exercise, but I'd recommend using the brilliant sp_whoisactive.
I wanted to second that.
-- Itzik Ben-Gan 2001
June 13, 2013 at 2:38 pm
I use sp_whoisactive; it has saved my life, but I also you the two scripts below when I need a little different info. You can comment out the where clause in the first script to see everything.
--This query finds all active queries with estimated completion time (if available)
select e.session_id
, sql.text
, e.start_time
, s.login_name
, s.nt_user_name
, e.percent_complete
, e.estimated_completion_time
from sys.dm_exec_requests e
join sys.dm_exec_sessions s on e.session_id = s.session_id
cross apply sys.dm_exec_sql_text(plan_handle) sql
where e.status IN ('running','suspended')
--To see what's currently running on the server
SELECT
der.session_id , --internal identifier for the running session
der.status , --determines if the query is active or waiting
der.start_time , --gives you an idea when the query started
der.command , --the type of command involved
der.database_id , --which database you're connected to
der.user_id , --which login is running the command
der.blocking_session_id , --session id of blocking session
der.wait_type , -- what is the waiting session it waiting on
der.wait_time , --how long has it been waiting
der.last_wait_type , --what caused it to last wait
der.cpu_time , --how much of the CPU has been used
der.total_elapsed_time , --how long has the command been running
der.reads , --has the command hit the disk for information
der.writes , --how much information was written to the disk
der.logical_reads --how many reads came out of memory
FROM sys.dm_exec_requests AS der;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply