Measuring SQL Agent with Performance Monitor: job counters, collection and per-job CPU and IO

Agent's counters aren't in sys.dm_os_performance_counters (that view only shows SQL Server engine counters). They're Windows performance counters, published by the SQL Server Agent service, so you read them with Performance Monitor, PowerShell or your monitoring agent.

#Find the counters

Get-Counter -ListSet 'SQLAgent*' | Select-Object CounterSetName, Paths | Format-List

On a default instance the counter sets are named SQLAgent:.... On a named instance they include the instance, for example SQLAgent$INST01:.... Typical sets:

Instances of the Jobs counters include _Total and the way the job was invoked (for example Schedules, Alerts, Others). Counter names can vary slightly between versions, so use the list from your server.

#Read them now

Get-Counter -Counter '\SQLAgent:Jobs(_Total)\Active jobs',
                     '\SQLAgent:Jobs(_Total)\Failed jobs',
                     '\SQLAgent:Jobs(_Total)\Job success rate',
                     '\SQLAgent:JobSteps(_Total)\Active steps' -SampleInterval 5 -MaxSamples 3

"Failed jobs" and "Successful jobs" count since the Agent service started, so trend the change, not the absolute value.

#Collect them over time (logman)

logman create counter SQLAgentJobs -o "D:\PerfLogs\SQLAgentJobs" -f bincirc -max 512 -si 00:01:00 ^
  -c "\SQLAgent:Jobs(*)\*" "\SQLAgent:JobSteps(*)\*" "\SQLAgent:Alerts(*)\*" ^
     "\Processor(_Total)\%% Processor Time" "\SQLServer:General Statistics\User Connections"
logman start SQLAgentJobs

Open the .blg in Performance Monitor, or convert it for analysis with relog SQLAgentJobs.blg -f csv -o agent.csv. To survive reboots, create the data collector set in Performance Monitor and set it to start with the system (or a scheduled task), or collect the counters through your monitoring agent instead.

Azure Monitor: add these counter paths to a data collection rule (performance counters) for the server, then alert on them in Log Analytics:

Perf
| where ObjectName startswith "SQLAgent" and CounterName == "Failed jobs"
| summarize failed = max(CounterValue) - min(CounterValue) by Computer, bin(TimeGenerated, 15m)
| where failed > 0

#Per-job resource use (what Perfmon can't show)

The counters tell you how many jobs ran and failed, not what each one costs. While a T-SQL step runs, its session's program_name looks like:

SQLAgent - TSQL JobStep (Job 0x8C1F7C1E9A1B2C4DA1B2C3D4E5F60718 : Step 2)

The hex value is the job_id in binary form. Decode it to the job name and add live resource figures:

SELECT j.name AS job_name,
       SUBSTRING(s.program_name, CHARINDEX(': Step', s.program_name) + 7, 3) AS step,
       r.status, r.cpu_time AS cpu_ms, r.logical_reads, r.reads, r.writes,
       r.total_elapsed_time / 1000 AS elapsed_s, r.wait_type, r.blocking_session_id
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
JOIN msdb.dbo.sysjobs j
  ON j.job_id = CONVERT(uniqueidentifier, CONVERT(binary(16), SUBSTRING(s.program_name, CHARINDEX('0x', s.program_name), 34), 1))
WHERE s.program_name LIKE N'SQLAgent - TSQL JobStep%';

For history of what each job step consumed, use one of these:

• Query Store (per database): steps' statements show up with their CPU and IO. Filter by the procedure or text the job runs.
• An Extended Events session on sql_batch_completed and rpc_completed, filtered on client_app_name LIKE 'SQLAgent - TSQL JobStep%', which records CPU, reads, writes and duration for every job batch:

CREATE EVENT SESSION AgentJobCost ON SERVER
ADD EVENT sqlserver.sql_batch_completed (ACTION (sqlserver.client_app_name, sqlserver.database_name)
    WHERE sqlserver.like_i_sql_unicode_string(sqlserver.client_app_name, N'SQLAgent - TSQL JobStep%')),
ADD EVENT sqlserver.rpc_completed (ACTION (sqlserver.client_app_name, sqlserver.database_name)
    WHERE sqlserver.like_i_sql_unicode_string(sqlserver.client_app_name, N'SQLAgent - TSQL JobStep%'))
ADD TARGET package0.event_file (SET filename = N'L:\XE\AgentJobCost.xel', max_file_size = 256, max_rollover_files = 10)
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION AgentJobCost ON SERVER STATE = START;

Group the results by the decoded job name to rank jobs by total CPU and reads per night. That's how you find the "maintenance" job that's really your biggest IO consumer.

#What to alert on

Tip

If the SQLAgent counter sets are missing, the Agent service may be stopped, or the counters may not be registered. A repair of the SQL Server installation usually re-registers them. Check that Agent is running first.