ADF: TempDb to user table very slow
0
votes
1
answer
571
views
I have a Azure Data Factory Data Flow that writes ~250 Mio. rows (~100 GB) to a MS Sql Server 2019.
Currently, I have set the 'Use TempDB' option to true:
The first part of the job, i.e. writing all the data to the temporary table is fairly quick (~2h). As I can see in the activity monitor, it then basically copies to data from the temp-table into the target table.
However, this part is incredibly slow (taking several days). Checking the activity monitor and logs, it seems like the responsible task gets suspended all the time due to a
The first part of the job, i.e. writing all the data to the temporary table is fairly quick (~2h). As I can see in the activity monitor, it then basically copies to data from the temp-table into the target table.
However, this part is incredibly slow (taking several days). Checking the activity monitor and logs, it seems like the responsible task gets suspended all the time due to a PAGEIOLATCH_SH wait.
Additionally, to avoid a bottleneck due to writing logs, the recovery mode is set to SIMPLE.
The SQL server + database was freshly set up just for this task, so there are no other running/interfering tasks. How comes that it runs into these PAGEIOLATCH_SH constantly?
Asked by guid
(1 rep)
Aug 31, 2022, 07:36 AM
Last activity: Jun 5, 2025, 04:05 AM
Last activity: Jun 5, 2025, 04:05 AM