MSSQL on Windows: SQL Server Agent Job Duration

Microsoft.SQLServer.Windows.CollectionRule.AgentJob.Duration (Rule)

Gets the duration of the SQL Server Agent job (in minutes). Note: This rule is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

This rule measures the average execution time of the SQL Server Agent job (in minutes). Note: The SQL Agent job duration is reset automatically after restarting the SQL Server Agent service.

Overridable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	No
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	600
Synchronization Time	The synchronization time specified by using a 24-hour format. Can be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event if it cannot access the database during the specified period.	15

Element properties:

Target	Microsoft.SQLServer.Windows.AgentJob
Category	PerformanceCollection
Enabled	False
Alert Generate	False
Remotable	True

Member Modules:

ID	Module Type	TypeId	RunAs
DS	DataSource	Microsoft.SQLServer.Windows.DataSource.AgentJobDurationFiltered	Default
Mapper	ConditionDetection	System.Performance.DataGenericMapper	Default
WriteToDB	WriteAction	Microsoft.SystemCenter.CollectPerformanceData	Default
WriteToDW	WriteAction	Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData	Default

Source Code:

<Rule ID="Microsoft.SQLServer.Windows.CollectionRule.AgentJob.Duration" Target="SqlDiscW!Microsoft.SQLServer.Windows.AgentJob" Enabled="false">

  <Category>PerformanceCollection</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Microsoft.SQLServer.Windows.DataSource.AgentJobDurationFiltered">

      <MachineName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

      <NetbiosComputerName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

      <InstanceName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

      <JobName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AgentJob"]/Name$</JobName>

      <ConnectionString>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

      <InstanceVersion>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

      <InstanceEdition>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

      <MonitoringType>$Target/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>

      <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

      <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

      <TimeoutSeconds>300</TimeoutSeconds>

      <IntervalSeconds>600</IntervalSeconds>

      <SyncTime/>

    </DataSource>

  </DataSources>

  <ConditionDetection ID="Mapper" TypeID="SystemPerf!System.Performance.DataGenericMapper">

    <ObjectName>SQL Agent Job</ObjectName>

    <CounterName>Duration (min)</CounterName>

    <InstanceName/>

    <Value>$Data/Property[@Name='Duration']$</Value>

  </ConditionDetection>

  <WriteActions>

    <WriteAction ID="WriteToDB" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData"/>

    <WriteAction ID="WriteToDW" TypeID="SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData"/>

  </WriteActions>

</Rule>