Всем привет!

У меня имеется ETL процесс, который запускается несколько раз в день,извлекает данные из источника, трансформирует данные (внутри временной таблицы), затем мёрджит с существующей целевой таблицей. Среднее время выполнения около 10 минут. Проблема в том,что этот процесс иногда выполняется в разы дольше, чем обычно, а именно:
1. Во всех системных вьюшках(sys.dm_os_tasks,sys.dm_exec_requests и т.п.) этот процесс помечен, как "running", значит он не умер.
2. В списке ожиданий картина такая:




session_id wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms resourcewaittime52 IO_COMPLETION 114196 1982917 33385 1369 198154852 PAGEIOLATCH_SH 13540 63539 13144 101 6343852 SQLTRACE_FILE_BUFFER 67 46633 1015 3 4663052 RESERVED_MEMORY_ALLOCATION_EXT 15930654 17880 544 0 1788052 TRACEWRITE 77 13517 632 2 1351552 SLEEP_TASK 399 5569 35 21 554852 MEMORY_ALLOCATION_EXT 3738294 4104 47 0 410452 SLEEP_BUFFERPOOL_HELPLW 233 2636 31 70 256652 SOS_SCHEDULER_YIELD 2172821 147913 260 146684 122952 LCK_M_SCH_S 1 95 95 0 9552 PAGEIOLATCH_EX 4 45 13 0 4552 WRITELOG 2 40 25 0 4052 CMEMTHREAD 3 1 0 1 052 IO_AUDIT_MUTEX 2 0 0 0 0
3. При трансформации данных используются функции, в которых есть pivot. Знаю, что это плохо, т.к. скорее всего здесь проблема в постоянном "перевороте" данных, но это наследие мне досталось и пока не доходят руки это отрефакторить.
4. Можно, как вариант, запустить perfmon, но на ПРОМ сервере крутятся ещё джобы ,которые не блокируют интересующий меня процесс, но при этом расходуют ресурсы, поэтому боюсь, что perfmon будет не информативен. Какие мысли, господа, что посоветуете?

invm, спасибо! я запускал profiler, фильтранул по сессии, и смотрел. В принципе понял, где медленно работает джоб. Вот непонятно только когда он работает медленно, а когда быстро. Попробую и расширенные события настроить

Александр Бердышев, хороший вопрос! но неужели в этом проблема:

Сообщение
Executed as user: *** 2021-04-06 12:04:56 227 sec from start. Updated *** 4103349 rows. Updated *** 112 rows. . Inserted 0 rows. Updated *** 1331 rows. The step succeeded.


Сообщение
Executed as user: *** 2021-04-06 16:04:11 11544 sec from start. Updated *** 4104888 rows. Updated *** 630 rows. Inserted 0 rows. Updated *** 889 rows. The step succeeded.