Adding the currently running SQL Agent job into the Perfstack?

itengineer

Hi All,

Using the latest sets of Orion Platform SAM & DPA, how can I view the Perfstack of SQL agent job that is currently running and chewing up resources?

Thank you in advance.

Seashore

If you are using Appinsight for SQL and want to show that info you can use below queries to create a "SQL DBA view". Add three custom query widgets and paste below SWQL code, three parts:

-- Failing SQL Jobs (and currently running)

SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl,

CASE
	WHEN J.Lastrunstatus=0 THEN 'Failed'
	WHEN J.Lastrunstatus=1 THEN 'Succeded'
	WHEN J.Lastrunstatus=4 THEN 'In progress'
	ELSE 'Retry or error'
END as JobStatusText,

CASE
	WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png'
	WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_Succeded.png'
	WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png'
	ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png'
END as JobStatusIcon,
J.Jobstatus, s.nodeid, s.ApplicationID
FROM Orion.APM.SqlServerApplication S
Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID
Inner join Orion.nodes N on S.NodeID=N.NodeID
WHERE J.LastRunStatus=0
  AND J.LastRunDate>ADDDAY(-7,GETUTCDATE())
  AND Length(J.Name)<>36



--SQL Jobs - Long runners
SELECT S.FullyQualifiedName,J.Name, J.LastRunDuration, J.Lastrundate, s.DetailsUrl,

CASE
	WHEN J.Lastrunstatus=0 THEN 'Failed'
	WHEN J.Lastrunstatus=1 THEN 'Succeeded'
	WHEN J.Lastrunstatus=4 THEN 'In progress'
	ELSE 'Retry or error'
END as JobStatusText,

CASE
	WHEN J.Lastrunstatus=0 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png'
	WHEN J.Lastrunstatus=1 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png'
	WHEN J.Lastrunstatus=4 THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_inprogress.png'
	ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_retry.png'
END as JobStatusIcon,
J.Jobstatus, s.nodeid, s.ApplicationID
FROM Orion.APM.SqlServerApplication S
Inner join Orion.APM.SQLJobInfoAlert J ON S.ApplicationID=J.ApplicationID
Inner join Orion.nodes N on S.NodeID=N.NodeID
WHERE --J.LastRunStatus<>1
	J.LastRunDuration>2700 -- 45 MIN
	AND J.LastRunDate>ADDDAY(-7,GETUTCDATE())



-- Databases not taken backup on for the last 36 horus

SELECT
	N.Caption,
	S.Name AS SQLInstanceName,
	D.Name AS Database,
	D.DatabaseID, 
	D.ServerDatabaseID, 
	D.RecoveryModel, 
	D.LastBackup, 
	D.DetailsUrl, 
	D.WebUri
FROM Orion.APM.SqlDatabase D
Inner join Orion.APM.SqlServerApplication S ON S.ApplicationID=D.ApplicationID
Inner join Orion.nodes N on S.NodeID=N.NodeID
WHERE 
	ISNULL(D.LastBackup,0)