Analyzing SQL SERVER 2005 LONG RUNNING QUERIES

3 comments

I’m leaving this tip for you to find what exactly is running on sql server.

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

Advertisements

3 comments on “Analyzing SQL SERVER 2005 LONG RUNNING QUERIES”

  1. Following script includes the jobs running:

    SELECT r.session_id, s.HOST_NAME, s.PROGRAM_NAME,sj.name as jobname,
    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,s.transaction_isolation_level
    ,s.lock_timeout,s.deadlock_priority,r.blocking_session_id,
    r.cpu_time,(r.total_elapsed_time/(1000*60)) as TimeElapsed_inMins,r.reads,r.writes,
    r.logical_reads,r.percent_complete,
    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
    left outer join msdb.dbo.sysjobs sj on substring(s.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))
    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

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s