You should also definitely know what queries (and what kind of queries, what they look like) are the top consumers in your SQL Server.

There are many ways to do that....

like this:

-- stop_execution_date is null;

SELECT

ja.job_id,

j.name AS job_name,

ja.start_execution_date,

ja.stop_execution_date,

ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,

Js.step_name

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

ON ja.job_id = js.job_id

AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

WHERE...

and how do you exactly determine that this or that job is still running while already has reached the @X minutes threshold and we want to be notified, and in...

JEFF,

so what do you suggest then?

(RE: //This is all because the old datatypes used to default to be "Out-of-Row" and all the new datatypes default to "in-row".  Microsoft didn't document...

not sure why attachment did not attach.. (it was a regular Text file, txt). May be because file name was too long.

Attaching the SP again. Renamed it to shorter name....

I only used it in combination with the below.

File i/o + which tables on those files and + whether top i/o reads/writes SPs

Work with those tables + What individual SQL...

the sttatement itself is not blocking anything. it searches through the results of sp_whoisactive saved to permanent table. so the parent child SPIDs are data in that table.

and, if what...

Thank you! Will give it a try tomorrow.

attaching plan in EXCEL FILE.

running query with SET STATISTICSPROFILE ON, will attach as Excel.

it now takes 3 hours to run and before was only 30 minutes. No other changes in anything, same data volumes,...

i tried to rename the sqlplan file to txt and other and no extension... but SSC keeps saying

Upload Errors:

execplanInsert-2.txt: Sorry, this file type is not permitted for security reasons.

attached .sqlplan as .txt.

Original code:

SELECT DISTINCT
         C.user_id
      ,C.date_submitted
      ...

how can i add it here?

add .sqlplan extension..