（原）SQL Server 代理作业执行持续时间简述

正文：
 
1、SQL Server 代理作业概述
 
本文不是详细讲解代理作业的，仅仅从代理作业历史表（dbo.sysjobhistory）的角度来学习和研究代理作业的执行时间。执行以下T-SQL代码：
SELECT 
     [job_id]
    ,[step_id]
    ,[run_status]
    ,[run_date]
    ,[run_time]
    ,[run_duration]
FROM 
    [dbo].[sysjobhistory]
WHERE 
    [run_duration] >= 1
ORDER BY
    [run_duration] DESC;
GO

 

以上T-SQL的执行结果如下图：
从以上图中很容易看到字段列run_date、run_time和run_duration的数据格式。结合微软联机帮助文档学习到：run_date字段列值的数据格式为yyyMMdd（该列值表示代理作业开始执行时的日期部分，如上图run_date字段列值为20170316表示该作业开始执行于2017-03-16这个日期点），run_time字段列值的数据格式为hhmmss（该列值表示代理作业开始执行时的时间部分，如上图run_time字段列值为20000表示执行于02:00:00这个时间点），run_duration字段列值的数据格式为hhmmss（这个是持续时间的数据格式，例如上图中的dun_duration为127表示改作业执行从开始到完成共持续了1分27秒）。
 
通常情况下，我在数据表设计尤其涉及到记录活动发生的日期时间点时更多的使用datetime这个数据类型的字段列。针对run_date和run_time的数据格式可以通过dbo.agent_datetime标量函数来转换为datetime数据类型。执行以下T-SQL代码：
SELECT [dbo].[agent_datetime](20170316, 20000) AS [run_datetime];
GO

 

以上T-SQL的执行结果如下图：

那么如何将run_duration字段列值转换为秒、分、小时等？下面继续探究。
 
2、获取代理作业执行时间方法一
 
这个方法具体参考了dbo.agent_datetime函数的定义和查看SQLServer 代理作业的历史信息这边博文中提供的方法，具体实现的T-SQL代码如下：
 
SELECT 
     [T1].[job_id]
    ,[T1].[name] AS [job_name] 
    ,[T2].[run_status]
    ,[T2].[run_date]
    ,[T2].[run_time]
    ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
    ,[T2].[run_duration]
    ,([T2].[run_duration] /10000 * 3600 + [T2].[run_duration] % 10000 / 100 * 60 + [T2].[run_duration] % 100 + 31) AS [run_duration_s]
FROM 
    [dbo].[sysjobs] AS [T1]
    INNER JOIN [dbo].[sysjobhistory] AS [T2]
        ON [T2].[job_id] = [T1].[job_id]
WHERE 
    [T1].[enabled] = 1
    AND    [T2].[step_id] = 0
    AND [T2].[run_duration] >= 1
ORDER BY
     [T2].[job_id] ASC
    ,[T2].[run_date] ASC
GO

 

以上T-SQL的执行结果如下图：
注意：
  • 这种方法才用了精度设置，默认精度为31秒，也就是说如果作业的执行持续时间超过30秒（包括等于30秒）时会自动增加31秒。
 
3、获取代理作业执行时间方法二
 
这个方法依然使用dbo.agent_datetime函数，还会增加dateadd函数和datediff函数。具体实现的T-SQL代码如下：
SELECT 
     [T1].[job_id]
    ,[T1].[name] AS [job_name] 
    ,[T2].[run_status]
    ,[T2].[run_date]
    ,[T2].[run_time]
    ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
    ,[T2].[run_duration]
    ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM 
    [dbo].[sysjobs] AS T1
    INNER JOIN [dbo].[sysjobhistory] AS T2
        ON [T2].[job_id] = [T1].[job_id]
WHERE 
    [T1].[enabled] = 1
    AND [T2].[step_id] = 0
    AND [T2].[run_duration] >= 1
ORDER BY
     [T2].[job_id] ASC
    ,[T2].[run_date] ASC
GO

 

以上T-SQL的执行结果如下图：
注意：
  • 该方法依然采用了默认精度为31秒。
  • sql server系统提供的日期函数采用的默认基准日期是1900-01-01，这个可以使用0替代换。
 
4、总结语
 
通过研读博友的文章和学习微软联机文档，确实能够学到很多的知识。sql 的路上唯有继续修行，才能了解的更多。
 
 
posted @ 2017-03-20 21:25  剑走江湖  阅读(2031)  评论(0)  编辑  收藏  举报