Created On:  02 May 2012

Problem:

Is it possible to create a report(s) that will calculate the Average and Total execution times for an Execution Plan?

Resolution:

The following SQL can be used to create an "Advanced" SilkCentral report to calculate the average and total execution times for an Execution Plan:

Total Execution time:
SELECT convert(varchar(10), SUM(Duration)/86400000) + ' Days  ' +
   convert(varchar(10), (SUM(Duration)%86400000)/3600000) + ' Hours  '+
   convert(varchar(10), (SUM(Duration)%3600000)/60000) + ' Mins  '+
   convert(varchar(10), (SUM(Duration)%60000)/1000) + ' sec ' +
   convert(varchar(10), SUM(Duration)%1000) + ' ms  ' AS [DD:HH:MM:SS:MS]
FROM TM_ExecDefinitionRuns WHERE ExecDefID_fk=${ExecDefID|0} AND Duration <> -1

When executed would generate the following data:



Average Execution time:
SELECT CONVERT(VARCHAR,DATEADD(ms,(SUM(Duration)/COUNT(*)),0),114) AS 'Average_Time'
FROM TM_ExecDefinitionRuns WHERE ExecDefID_fk=${ExecDefID|0} AND Duration <> -1


When executed would generate the following data in HH:MM:SS:MS