Archives
for this blog

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.

In my article published yesterday on using Powershell for SQL Durations (Working with SQL Agent Durations), I proposed using a combination of math functions to extract the durations of SQL Agent jobs in a dependable and accurate manner. While the article sought to provide a better alternative for those using Powershell, the discussion of the article was primarily about T-SQL solutions. The question of both capability and performance were both discussed, so I decided to research the limitations and performance metrics of the various solutions posted.

A combination of string and math based solutions were posted as potential alternatives. A recent post highlighted the use of the msdb function agent_datetime. All of these math functions posted will provide an accurate second value from a SQL Agent duration without regard to sign (positive or negative) or size (greater than 6 digits).

All of the string methods mentioned, including the agent_datetime function, rely on three facts to be true.

1) All values in the table must either be zero or positive integers2) No value greater than 235959 can exist in the table because of datetime conversion issues.3) Positions 2-3 and Positions 4-5 must not be greater than 60 due to datetime conversion issues.

While I understand that the existence of any of these values is a sign of a larger issue – if the code generating the report fails, the report is incapable of telling me there is a problem. I generally run reports on new environments to understand what the current state is and what needs to be worked on. It is not uncommon to find jobs that have run more than a day in a new environment. While less common, I have also had to deal with negative duration values stored in the sysjobhistory. I agree – neither is ideal and neither should exist. However, it is much more common for me to be called to fix what is broken, not to watch an already stable and perfect environment.

Testing Performance

I have never actually tested the performance of using the math based solution vs. the string manipulation solutions. So, I devised a process to put all of these suggestions to a performance test. I first created a table with integers –1971000 through 1971000. I understand this is extreme, in both directions, but I had to use something large enough to get some idea of performance.

You can examine the T-SQL at the end of this post. At each test, the T-SQL represents a proposed solution on the forum thread. Each test converts each of the integer values in the table to a number of seconds based upon the HHMMSS rule of how these durations are stored in sysjobhistory. These are populated into a variable so that any transfer to the client or IO issues are eliminated in the performance test. The only thing being tested should be CPU and memory throughput. In order to adjust for the performance of any given environment, I establish a control. This control does no conversion on the integer value, simply selects each one into a variable. This should provide a good baseline for what the maximum performance could be.

Given that each of the string functions proposed fail unless provided very specific numeric values, I eliminated any negative values, any values > 235959, and any values where the HH and SS values were > 59 to avoid datetime conversion errors.

Below is the average of each of these methods (and a control) over 10 executions for both my laptop and an enterprise class server.

AvgDurationMS

RunType

Laptop

Server

235959 Control

60

27

235959 Mod Math Implicit

87

38

235959 Mod Math

90

44

235959 Mod Math Intrope

123

44

235959 SQL-Tucker

163

79

235959 rmechaber

182

90

Full Control

983

406

235959 agent_datetime

1516

658

Full Mod Math

1755

790

Full Mod Math Implicit

1814

867

Full Mod Math Intrope

2377

1077

Admittedly, the difference in these test scenarios is negligible. However, in all cases, using the mod operator and the implicit truncation within SQL Server was the fastest conversion method. In addition to being the fastest – it’s the most compact visibly and works for just about any values – not just those less than a single day. I encourage you to run these tests in your environment and see which solution comes out on top.

As with anything in SQL Server, when asked which method is “better” I would have to say “It depends.” However, if you ask which appears to be fastest, which works for most conceivable situations, and which will I use? I’m going to stick with math – and leave the strings be.