Job step wise duration counts

select step_id [Step No],step_name [Step Name],case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration
   FROM (select step_id,step_name, CASE
          WHEN run_duration > 0
          THEN
               (run_duration / 1000000) * (3600 * 24)
             + (run_duration / 10000 % 100) * 3600
             + (run_duration / 100 % 100) * 60
             + (run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
   and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
   ) T
  

Job Schedule and Execution Details

IF EXISTS (SELECT *
           FROM    tempdb.dbo.sysobjects
           WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[JobHistoryInformation]')
                )
        DROP TABLE [tempdb].[dbo].[JobHistoryInformation]
GO
CREATE TABLE [tempdb].[dbo].[JobHistoryInformation]
(
        job_id uniqueidentifier NOT NULL,
         last_run_date nvarchar (20) NOT NULL,
         last_run_time nvarchar (20) NOT NULL,
         next_run_date nvarchar (20) NOT NULL,
         next_run_time nvarchar (20) NOT NULL,
         next_run_schedule_id INT NOT NULL,
         requested_to_run INT NOT NULL,
         request_source INT NOT NULL,
         request_source_id sysname
                   COLLATE database_default NULL,
         running INT NOT NULL,
         current_step INT NOT NULL,
         current_retry_attempt INT NOT NULL,
         job_state INT NOT NULL
 )
    
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
        
INSERT INTO [tempdb].[dbo].[JobHistoryInformation]
   EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

SELECT job_name,StartTime ScheduledStartTime,EndTime ScheduledEndTime,next_scheduled_run_date,
LastRunOutcome,case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration,
schedule_name,[Description],Freq_Type,Freq_Interval
FROM
(
SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, '') as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3,0, '/') + '/' + LEFT(active_start_date, 4) + ' at '
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN 'Occurs every ' + CAST(freq_interval as varchar(10))
+ ' day(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 8 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' week(s) on '
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 16 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' month(s) on '
+ 'day ' + CAST(freq_interval as varchar(10)) + ' of that month '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 32 THEN
'Occurs ' +
CASE freq_relative_interval
WHEN 1 THEN 'every first '
WHEN 2 THEN 'every second '
WHEN 4 THEN 'every third '
WHEN 8 THEN 'every fourth '
WHEN 16 THEN 'on the last '
END
+ CASE freq_interval WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'weekday'
WHEN 10 THEN 'weekend'
END
+ ' of every ' + CAST(freq_recurrence_factor as varchar(10)) + ' month(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END as [Description]
, CASE freq_type
WHEN 1 THEN 'One Time'
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN 'Daily'
WHEN 2 THEN 'Second-ly'
WHEN 4 THEN 'Minutely'
WHEN 8 THEN 'Hourly'
ELSE ''
END
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Second(s)'
WHEN 4 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Minute(s)'
WHEN 8 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Hour(s)'
ELSE ''
END AS freq_subday_type
, CASE
WHEN freq_type = 4 /* (daily) */ THEN 'Every ' + CAST(freq_interval as varchar(10)) + ' Day(s)'
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
WHEN freq_type = 16 THEN 'On Day ' + CAST(freq_interval as varchar(10)) + ' of Every Month'
WHEN freq_type = 32 /* (monthly) */ THEN 'Every ' +
CASE freq_interval WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
END
ELSE ''
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
WHEN freq_type = 16 /* (monthly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Month(s)'
WHEN freq_type = 32 /* (monthly relative) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
ELSE ''
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as endtime
, STUFF(STUFF(active_start_date, 5, 0, '/'), 8, 0, '/') as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, '/'), 8, 0, '/') as schedule_end_date
 ,ja.next_scheduled_run_date
 ,CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Completed' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'Running' END AS LastRunOutcome
       ,CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration
FROM msdb.dbo.sysjobs SJ
LEFT Join [tempdb].[dbo].[JobHistoryInformation] x
ON x.job_id = SJ.job_id
LEFT JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
LEFT JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.sysjobactivity ja on ja.job_history_id=h.instance_id and ja.job_id=SJ.job_id
) TEMPTA

Logging details from sysssislog

select Package,
source,
StepId GroupId,
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(TimeInMinutes), '19000101'), 8), 1, 2, CAST(TimeInMinutes / 3600 AS VARCHAR(12))) Duration,
TimeInMinutes TimeInSeconds,
[Status]
from (
select
A.starttime,
A.id,
c.source Package,
A.[source],
DENSE_RANK() OVER (order by A.executionID ) StepID,
CONVERT(VARCHAR(100),DATEDIFF(SECOND,A.starttime,ISNULL(B.endtime,GETDATE()))) TimeInMinutes,
CASE WHEN B.endtime IS NULL THEN ' (running)' ELSE '' END AS [Status]
from
(select * from sysssislog where event = 'OnPreExecute') As A LEFT JOIN
(select * from sysssislog where event = 'OnPostExecute') As B ON A.sourceid = B.sourceid and A.executionid = B.executionid
LEFT JOIN (select * from
(select source,executionid,Row_Number() over (PARTITION by executionid order by executionid) RowId from sysssislog where event = 'OnPreExecute' )
t where RowId=1) C on C.source=A.source and C.executionid=a.executionid
) t
order by StepID,id

Parallel execution in SSIS

Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors. To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.

If SSIS runs on a dedicated server and you have a lot of operations that run in parallel, you will likely want to increase this setting if some of the operations do a lot of waiting for external systems to reply. On the other hand, if you do not have a dedicated SSIS machine and your data integration application runs alongside several other applications, you may need to reduce this setting to avoid resource conflicts.

The MaxConcurrentExecutables property is a property of the package. This property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package. The default value is -1, which equates to the number of physical or logical processors plus 2.

Please note that if your box has hyper threading turned on, it is the logical processor rather than the physically present processor that is counted.

The EngineThreads property is a property of each Data Flow task. This property defines how many threads the data flow engine can create and run in parallel. The EngineThreads property applies equally to both the source threads that the data flow engine creates for sources and the worker threads that the engine creates for transformations and destinations. Therefore, setting EngineThreads to 10 means that the engine can create up to ten source threads and up to ten worker threads. The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.

One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler.

One other thing to consider: If you are using the Execute Package Task, the child package to be executed can be run in-process or out-of-process by use of the ExecuteOutOfProcess property. If a child package is executed out-of-process, you will see another dtshost.exe process start. These processes will remain “live”, using up resources, for quite a while after execution is complete.

If executing in-process, a bug in a task of the child package will cause the master package to fail. Not so if executing out-of-process. On 32-bit systems a process is able to consume up to 2GB of virtual memory. Executing out-of-process means each process can claim its own 2GB portion of virtual memory. Therefore if you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don’t have the overhead of launching more processes.

A thread will process one buffer at a time, executing it against all transforms in the execution tree before working on the next buffer in the flow, at which point it would pass the current buffer to another thread executing another execution tree and it would pull a new data buffer from its buffer list which was queued from an upstream component (either a data source or the last asynchronous transform before this execution tree started).

However, the general rule is to not run more threads in parallel than the number of available processors. Running more threads than the number of available processors can hinder performance because of the frequent context-switching between threads.

MaxConcurrent

·         This is a property on the ForEachLoop which says how many instances of the loop contents can be run in parallel.

Example:

Suppose we have a package with 3 Data Flow Tasks. Each task has 10 flows in the form of “OLE DB Source -> SQL Server Destination”.

Set MaxConcurrentExecutables to 3, then all 3 Data Flow Tasks will run simultaneously.

Now whether all 10 flows in each individual Data Flow Task get started concurrently is a different story. This is controlled by the second property: EngineThreads.

The EngineThreads is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5.  

If we set EngineThreads to 10 on all 3 Data Flow Tasks, then all the 30 flows will start off at once.