8
Aug
Currently Running SQL Processes
Ever wanted to know exactly what queries were running on your database server. The query below ill show you exactly what is running at the time of the query been ran. There will be a lot of queries where the status is marked as sleeping, these can be ignored, you will mainly be interested in the ones that are running.
This query will also give you statistics such as how many reads the query has made so far, how long it has been running for, if it is been blocked, who has performed the query and much more.
Query to see what is running on SQL Server
SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id