Query to Get SQL Job Info from sysprocesses

By Lori Brown | Helpful Scripts

Feb 16

We recently were troubleshooting an issue where one client reported that the SQL instance was suddenly very slow. One of the things we do is check to see what queries are currently running in SQL by using the following query:

— Finds info on queries that are running NOW

SELECT GETDATE() AS CurrentTime,

SUBSTRING(text, CASE WHEN ((ExecReq.statement_start_offset/2) + 1) < 1 THEN 1 ELSE (ExecReq.statement_start_offset/2) + 1 end,

((CASE ExecReq.statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE ExecReq.statement_end_offset END

– ExecReq.statement_start_offset)/2) + 1) AS statement_text,

ExecSQLText.[text],

ExecReq.database_id,

ExecReq.session_id AS [SPID],

ExecReq.[reads],

ExecReq.logical_reads,

ExecReq.writes,

ExecReq.total_elapsed_time / 1000.0 AS [total_elapsed_time (secs)],

ExecReq.wait_time / 1000.0 AS [wait_time (secs)],

ExecReq.cpu_time / 1000.0 AS [cpu_time (secs)],

ExecReq.start_time,

ExecReq.granted_query_memory,

ExecReq.last_wait_type,

ExecReq.blocking_session_id AS [BlockingSPID]

FROM sys.dm_exec_requests ExecReq

OUTER APPLY sys.dm_exec_sql_text(ExecReq.sql_handle) ExecSQLText

WHERE ExecReq.session_id > 50

AND ExecReq.session_id <> @@spid

AND ExecReq.last_wait_type <> ‘BROKER_RECEIVE_WAITFOR’

ORDER BY ExecReq.cpu_time DESC

— total_elapsed_time desc

— wait_time desc

— logical_reads desc

After running this a few times, we can see if any one query consistently shows up in the list as using lots of resources or is blocking or being blocked. We can also see if a lot of queries seem to be listed that usually are fast which might indicate that there is some kind of issue with the execution plan or statistics. Here is an example of what the output looks like:

Blog_20180216_1

If I was trying to further figure out what SPID 709 was doing (as in what user and what application), I might take a look at sp_who2 or sp_WhoIsActive from Adam Machanic (http://whoisactive.com/) and you might see something like this in the program name:

SQLAgent – TSQL JobStep (Job 0xCC1842F477AA1A4E84CD91228FFC799B : Step 1)

We know a job is running but what job is it? I know that we can get the job name from the hex but decided that instead of needing to copy the value from the program_name into a variable that I wanted to have a query to decipher any jobs that might be found on the fly. This would keep me from needing to open the job activity and try to find which job was running. If you have hundreds of jobs to look through or if more than one job is running then you might have to guess which one is the culprit that you are looking for. I also wanted to know what job step the job was on so that I could know where it was and so that I was better prepared to provide meaningful information back to my client.

SELECT p.spid, j.name As ‘Job Name’, js.step_name as ‘Job Step’,

p.blocked, p.lastwaittype, p.dbid, p.cpu, p.physical_io, p.memusage, p.last_batch

FROM master.dbo.sysprocesses p JOIN msdb.dbo.sysjobs j

ON master.dbo.fn_varbintohexstr(convert(varbinary(16), job_id))

COLLATE Latin1_General_CI_AI = substring(replace(program_name, ‘SQLAgent – TSQL JobStep (Job ‘, ), 1, 34)

JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id

WHERE p.program_name like ‘SQLAgent – TSQL%’

AND js.step_id = (substring(p.program_name, (CHARINDEX(‘: Step’,p.program_name, 1)+7),

((CHARINDEX(‘)’, p.program_name, 1))-(CHARINDEX(‘: Step’, p.program_name, 1)+7))))

Blog_20180216_2

It is handy to have both of the above queries in one query window so that results are returned in one place making it easier to associate running queries with their job. Hope this helps someone out as much as it did me.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

 

 

 

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>