With the help of the following query, you can access the answers to the questions listed below.
You can filter the query as you like by opening the command that was added and currently inactive.
- What queries are coming into your database right now?
- How long does the queries work?(total_elapsed_s)
- What queries are causing the lock and Which queries are waiting for the lock?(blocking_session_id)
- What is the percentage of backups completed?(percent_complete)
- What is the last type of wait for queries?(last_wait_type)
- Which query is waiting in suspend mode and which query is in running mode?(status)
- Which query was sent from which program?(program_name)
- Which query was sent from which host?(host_name)
- Which query started when?(start_time)
- How much memory does the queries use?(memory_mb)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete, r.blocking_session_id,r.last_wait_type, s.login_name,'MySessionID= ' + cast(r.session_id as varchar) as MySessionID, DB_NAME(r.database_id) as DatabaseName,command ,SUBSTRING(t.text, (r.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text ,r.status,wait_time ,wait_type,wait_resource,text,start_time,s.program_name ,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb from sys.dm_exec_requests r inner join sys.dm_exec_sessions s on r.session_id = s.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) t where r.session_id <> @@SPID --and r.database_id =DB_ID('Your_DB_Name') --and t.text like '%Part_Of_Your_Query_That_You_Want_To_Filter%' --and r.session_id = sessionid --and s.Login_Name like '%Your_Login_name%' --and s.program_name LIKE '%The Program_Name_You_Want_To_Filter%' --and r.wait_type !='The_Wait_Type_You_Want_To_Filter' --and r.blocking_session_id<>0" order by start_time asc |
