SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kyle Neier / Working with SQL Agent Durations / Latest Posts

RE: Working with SQL Agent Durations

Neha05 — Tue, 25 Dec 2012 08:46:57 GMT

Good article.

RE: Working with SQL Agent Durations

OldFashionGang — Thu, 30 Aug 2012 04:25:35 GMT

+5 cent's of mine[code="sql"]select top 100name , convert(datetime,(convert(varchar(8),h.run_date)))+(+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2))begining ,convert (varchar ,convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))))++':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)+':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)duration, -- EdsTime as begining+durationconvert( datetime, convert(varchar(8),h.run_date)) + ( substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2) +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2) +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2) )+ ( -- add hours dateadd (hh, convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4)) ), 0) + -- add mins dateadd (mi, convert (int, substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2) ), 0) + -- add seconds dateadd (ss, convert (int, substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2) ), 0) ) endtime from msdb..sysjobhistory h with( nolock), msdb..sysjobs j with( nolock)where h.step_id=0 and h.job_id=j.job_id order by instance_id desc[/code]works for several years to make this:[img]http://img-fotki.yandex.ru/get/6506/49393641.0/0_9b75f_2d181269_L.jpg[/img]

RE: Working with SQL Agent Durations

rmechaber — Sat, 25 Aug 2012 07:12:53 GMT

[quote][b]Kyle Neier , (8/24/2012)[/b][hr]It seems that my article about Powershell has sparked some T-SQL discussion, so I decided to take the prompt and run some performance analysis on the different solutions presented here.You can read the detail over on my blog ([url]http://sqldbamusings.blogspot.com/2012/08/t-sql-performance-of-sql-agent-duration.html[/url]), but essentially, the conversions that use math instead of string manipulation seem to be faster and cover a wider range of potential values.Before I ran the tests, I really had no clue what the results would be, which is why I wanted to run them. Thanks to all of you the great suggestions - another testament to how great the SQL community is![/quote]Well, that is interesting. Carolyn, I wasn't aware of that msdb sys function, so thanks for pointing it out.That said, I was pretty surprised to find that my hack string manipulation performed 7X faster than the msdb function agent_datetime. Thanks for testing it!I suppose if I were running a lot of code against a very large table of job histories, I might go back and tweak my code. I suspect that most DBAs running code against msdb.dbo.sysjobhistory are doing it for similar reasons though: a once-a-day (or thereabouts) system check and reporting. Still, I prefer your approach, both for performance and pedagogically: why deal with times as strings? Kyle, have you seen any documentation anywhere as to [b]why[/b] MS has coded times this way?Thanks RichThanks for

RE: Working with SQL Agent Durations

Kyle Neier , — Fri, 24 Aug 2012 15:46:34 GMT

It seems that my article about Powershell has sparked some T-SQL discussion, so I decided to take the prompt and run some performance analysis on the different solutions presented here.You can read the detail over on my blog ([url]http://sqldbamusings.blogspot.com/2012/08/t-sql-performance-of-sql-agent-duration.html[/url]), but essentially, the conversions that use math instead of string manipulation seem to be faster and cover a wider range of potential values.Before I ran the tests, I really had no clue what the results would be, which is why I wanted to run them. Thanks to all of you the great suggestions - another testament to how great the SQL community is!

RE: Working with SQL Agent Durations

Carolyn Sellers — Fri, 24 Aug 2012 14:05:24 GMT

Dear All,If running script in SQL 2005 or greater, you can save yourself some time by using msdb system function agent_datetime. Check out this thread http://www.sqlservercentral.com/Forums/Topic542581-145-2.aspx.Give this script a try;select [step_name],run_duration,run_date,msdb.dbo.agent_datetime(run_date,run_time) as StartDateTime,dateadd(s,datediff(s,msdb.dbo.agent_datetime(run_date,0),msdb.dbo.agent_datetime(run_date,run_duration)),msdb.dbo.agent_datetime(run_date,run_time)) as EndDateTimefrom msdb.dbo.sysjobhistorywhere datediff(hh, msdb.dbo.agent_datetime(run_date,run_time), getdate()) <= 240order by StartDateTime desc;:hehe:

RE: Working with SQL Agent Durations

Cyclone — Thu, 23 Aug 2012 22:41:26 GMT

Yo Dude!Great post. Thanks for sharing!:-)

RE: Working with SQL Agent Durations

intrope — Thu, 23 Aug 2012 16:09:16 GMT

You can actually avoid using trunctation. Consider that if @t is the sql agent time, @t % 100 will be seconds, @t % 10000 - @t % 100 will be minutes * 100, and @t - @t % 10000 will be hours * 10000. So, the subsequent division by 100 or 10000 is already an integer value.[code="other"]DECLARE @t int = 1234556; --4546 --5; --56; --1234556;SELECT @t AS AgentDuration, (@t - @t % 10000) /10000 AS hours, (@t % 10000 - @t % 100) /100 AS minutes, @t % 100 AS SECONDS, ((@t - @t % 10000) /10000) * 60 * 60 + ((@t % 10000 - @t % 100) /100) * 60 + @t % 100 AS totalseconds[/code]Added bonus: it'll work for negative values too. Not that output will mean anything (GIGO!)

RE: Working with SQL Agent Durations

rmechaber — Thu, 23 Aug 2012 12:00:09 GMT

[quote][b]Jason- (8/23/2012)[/b][hr][quote][b]rmechaber (8/23/2012)[/b]That code errors out on my trial:[code="plain"]Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.[/code]Rich[/quote]I've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"[/quote]Good call, that was the issue. Why the Agent log has a negative run duration, I don't know; Googling only turned up more "me, too *shrugs*" posts, without an explanation. If anyone here has an answer, that would be nice. The job in question was an Index rebuild subplan in a maintanance plan.Rich

RE: Working with SQL Agent Durations

Jason- — Thu, 23 Aug 2012 11:49:52 GMT

[quote][b]rmechaber (8/23/2012)[/b]That code errors out on my trial:[code="plain"]Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.[/code]Rich[/quote]I've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"EDIT: Could also be a duration greater than 99 hours. In which case the where clause would be "WHERE run_duration BETWEEN 0 and 999999"

RE: Working with SQL Agent Durations

rmechaber — Thu, 23 Aug 2012 10:10:19 GMT

[quote][b]SQL-Tucker (8/23/2012)[/b][hr]Thanks for the article, I always enjoy exploring new ways to tackle persistent challenges. I particularly like that it handles variable length run_duration values.Is there a performance gain using the math approach vs the string manipulation?I'm novice to PowerShell so I don't have experience benchmaking its performance. I'm curious what the performance would be compared to the conventional method converting the time at extraction using string functions. How can the PowerShell option be applied as a function?[i][size="1"](forgive the novice, the only way I've called PowerShell in T-SQL was through xp_cmdshell)[/size][/i]And does it serve as a performant alternative to the string manipulation (see example)?[i][size="1"]I'm aware the below example will fail when the duration exceeds 99 hours, but if a job is running for 99 hours in my environment there are bigger issues than being able to return the time in HH:MM:SS format. So handling a variable length run_duration is not a good use of resources for my environment.[/size][/i][code="sql"]SELECT stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') ,run_duration ,*FROM msdb.dbo.sysjobhistory WITH(NOLOCK)[code="plain"]result sample(No column name) run_duration instance_id job_id00:00:02 2 12723 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12724 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12725 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12726 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12727 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12728 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12729 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:03 3 12730 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:10 10 12731 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:11 11 12732 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:25:34 2534 12733 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E00:25:35 2535 12734 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E[/code][/code]Thoughts? Tips on benchmarking PS performance?[/quote]That code errors out on my trial:[code="plain"]Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.[/code]Rich

RE: Working with SQL Agent Durations

SQL-Tucker — Thu, 23 Aug 2012 10:04:53 GMT

Thanks for the article, I always enjoy exploring new ways to tackle persistent challenges. I particularly like that it handles variable length run_duration values.Is there a performance gain using the math approach vs the string manipulation?I'm novice to PowerShell so I don't have experience benchmaking its performance. I'm curious what the performance would be compared to the conventional method converting the time at extraction using string functions. How can the PowerShell option be applied as a function?[i][size="1"](forgive the novice, the only way I've called PowerShell in T-SQL was through xp_cmdshell)[/size][/i]And does it serve as a performant alternative to the string manipulation (see example)?[i][size="1"]I'm aware the below example will fail when the duration exceeds 99 hours, but if a job is running for 99 hours in my environment there are bigger issues than being able to return the time in HH:MM:SS format. So handling a variable length run_duration is not a good use of resources for my environment.[/size][/i][code="sql"]SELECT stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') ,run_duration ,*FROM msdb.dbo.sysjobhistory WITH(NOLOCK)[code="plain"]result sample(No column name) run_duration instance_id job_id00:00:02 2 12723 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12724 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12725 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12726 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12727 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12728 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12729 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:03 3 12730 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:10 10 12731 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:11 11 12732 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:25:34 2534 12733 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E00:25:35 2535 12734 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E[/code][/code]Thoughts? Tips on benchmarking PS performance?[b][update][/b]I had an oversight... the point in the article is to return the duration in seconds. However what I posted above only returns formatted time, for duration in seconds I should have posted this:[code]datediff(ss,0,cast(stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') AS DATETIME))[/code]

RE: Working with SQL Agent Durations

Kyle Neier , — Thu, 23 Aug 2012 08:19:04 GMT

Marc ~I don't believe you are missing anything. That seems to be a perfectly viable alternative - one that I was unaware of until today.Thanks for sharing!

RE: Working with SQL Agent Durations

Michael.Beeby — Thu, 23 Aug 2012 07:02:18 GMT

I did wonder about STUFF, e.g.SELECT STUFF ('200', 2 , 0, ':')DECLARE @MJBtime INTSET @MJBtime = '10200'SELECT LEN(@MJBtime)SELECT STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':') --Not 2 as 1st value before first characterSELECT STUFF(STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':'),LEN(@MJBtime)-3,0,':')

RE: Working with SQL Agent Durations

rmechaber — Thu, 23 Aug 2012 06:18:26 GMT

I confess this bit me hard in the #$% a while ago, twice: once when I found out that date and time were stored as an INT representation, once again when code failed b/c I forgot there could be a zero value for time with only 1 digit.I won't present the following code I developed as a great solution, but b/c it's admin. code that runs but once a day as part of maintenance, I don't really care about performance. It works, and that was sufficient:[code="sql"] SELECT j.name AS JobName, -- NVARCHAR(128) jh.step_id, -- INT jh.step_name, -- NVARCHAR(128) jh.sql_message_id, -- INT jh.sql_severity, -- INT -- sysjobhistory natively stores run_date and run_time as separate integers. Combine and convert to DATETIME. Why MS, why?? CAST ( -- Date portion, which will always be an 8-digit INT in the form yyyymmdd: CAST(jh.run_date AS VARCHAR(8)) + ' ' + -- Time portion is harder, b/c it can be 0, nnnnn (5 digits), or nnnnnn (6 digits) in the form hmmss. No leading zero. -- This construct will prepend 6 zeroes, then take the rightmost 6 characters, yielding a 6-character string: -- RIGHT('000000' + CAST(run_time AS VARCHAR(6)), 6) -- We then slice and re-format to hh:mm:ss and combine with the date, then cast the whole shebang as DATETIME. LEFT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) + ':' + Substring(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) AS DATETIME) As RunDateTime, jh.message, -- NVARCHAR(1024) jh.run_status, -- INT jh.run_duration -- INT INTO #t FROM MSDB.dbo.sysjobs j INNER JOIN MSDB.dbo.sysjobhistory jh ON j.job_id = jh.job_id WHERE jh.sql_severity > 0 OR jh.run_status = 0[/code]Rich

RE: Working with SQL Agent Durations

jennym — Thu, 23 Aug 2012 05:54:48 GMT

Interesting and good to know! I come from a functional background, and when I encountered this did not know it was a 'standard' way to do things. At the time I was much stronger in Crystal than straight TSQL, and did parse & concatonate as you mention. Ended up with the below to get user friendly times. Obviously this only worked because the value was a time, not a duration. Had it been a duration I would have been one of those you mention who put something together that "fell down" for anything over 24 hours. Thanks for an improved way to approach this!!!select {tblScheduleDetail.StartTime}Case 0:"12:00 AM"Case 1 to 999:"12:0"&left(cstr({tblScheduleDetail.StartTime}),1)&" AM"Case 1000 to 9999:"12:"&left(cstr({tblScheduleDetail.StartTime}),1)& mid(cstr({tblScheduleDetail.StartTime}),3,1)&" AM"Case 10000 to 99999:left(cstr({tblScheduleDetail.StartTime}),1)&":"& mid(cstr({tblScheduleDetail.StartTime}),2,1)&mid(cstr({tblScheduleDetail.StartTime}),4,1)&" AM"case 100000 to 119999:left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&mid(cstr({tblScheduleDetail.StartTime}),5,1)&" AM"case 120000 to 240000:left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&mid(cstr({tblScheduleDetail.StartTime}),5,1)&" PM";;

RE: Working with SQL Agent Durations

Marc Kuppens — Thu, 23 Aug 2012 05:33:13 GMT

Hi Kyle,at the end of your article you state : "There is not an equivalent to Truncate in T-SQL"Maybe I missed something but what's wrong with the round function (with the third argument <> 0)?try :declare @t1 decimalset @t1 = 23456 select 'using round(?,0) ', round((@t1 / 100),0) as [23456/100], round((-@t1 / 100),0,0) as [-23456/100]union allselect 'using round(?,0,1)', round((@t1 / 100),0,1)as [23456/100], round((-@t1 / 100),0,1)as [-23456/100]union allselect 'using floor ', floor((@t1 / 100))as [23456/100], floor((-@t1 / 100))as [-23456/100]Did I miss something?Marc

Working with SQL Agent Durations

Kyle Neier , — Thu, 23 Aug 2012 00:02:17 GMT

Comments posted to this topic are about the item [B]Working with SQL Agent Durations[/B]