做两个系统之前的数据同步时，常常会把源数据（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