查看job和存储过程的运行时长

做两个系统之前的数据同步时，常常会把源数据（txt. csv. excel. RFC-read table)先存到一个中间数据库的临时表，然后用SSIS JOB去调用一些存储过程，同步数据到目标系统的目标表。

最近有一些存储过程同样在JOB中被调用，执行很慢，导致其它系统（如CRM需要查询）死锁，这里提供两组SQL。

1.查询当前数据库中存储过程的执行情况，包括执行次数，执行时长，存储过程的脚本等。

SELECT s2.dbid,     (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,     execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,     max_worker_time,    total_physical_reads,     last_physical_reads,     min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  WHERE s2.objectid is null ORDER BY s1.total_worker_time desc

2.看到慢的存储过程，可以找到相关的JOB，再查询这个JOB最近每次执行所需的时间;

SELECT  j.name AS 'JobName' ,          s.step_id AS 'Step' ,          s.step_name AS 'StepName' ,          msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,          ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60              + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'  FROM    msdb.dbo.sysjobs j          INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id          INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id 
                                            AND s.step_id = h.step_id 
                                            AND h.step_id <> 0 
WHERE   j.enabled = 1   AND j.name='DailyBillingReport_DB(crm)_Mail(xlsx)' 
ORDER BY           RunDurationMinutes DESC