Menu
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:
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))))
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!