Friday, October 22, 2010

Query running in SQL Server

I use this sript to check what query is running in server

select r.blocking_session_id,r.session_id ,h.text,substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.wait_type, r.wait_time , r.last_wait_type , r.wait_resource ,
r.command , r.database_id , r.granted_query_memory, r.reads , r.writes , r.row_count , s.[host_name] , s.program_name , s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 order by 2 desc

No comments: