jump to navigation

Long running sessions May 25, 2009

Posted by msrviking in Scripts.


One of the DBA’s in my shop wanted to know which query do I use for finding long running sessions in SQL Server 2005. Here is the one I shared, and often use while doing performance tuning (snipped from SSQA.net).

select r.session_id, s.host_name, s.program_name, s.host_process_id,
r.status, r.wait_time,wait_type,r.wait_resource,
substring(qt.text,(r.statement_start_offset/2) +1,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2)
as stmt_executing,r.blocking_session_id, r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s
where r.session_id > 50 and r.session_id=s.session_id
order by r.session_id, s.host_name, s.program_name, r.status

I hope you guys could also make use of this script, and there are lots sitting here.  Thanks to SQL Master of SSQA.net for his efforts in writing these queries and I keep using one of these kinds quite often.

Cheers and enjoy!



1. SQLMaster - June 21, 2009

Thanks for spreading the knowledge, appreciate your efforts… I learn new things too from your blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: