Job hanging at CDC stage

jweir · Post by **jweir** » Fri Dec 07, 2012 11:17 am

Hi all,

I have a job that uses the CDC stage to check if the source records are insert, update or copy records. I am looking up to the target table that has about 6.8 million records. In the current run of the job, only one record is looking up to the 6.8 million. For some reason, it has been taking almost 2 hours and the job is hung at the CDC stage. How can I speed this job up?

I have sorted and partitioned on the change keys on both inputs but still no luck. Any tips?

Thanks in advance.

jweir · Post by **jweir** » Fri Dec 07, 2012 11:30 am

I found the solution. Instead of sorting and partitioning on the inputs, I did an ORDER BY on the reference table select query on the CDC keys. After I did this, the job completed within a minute.

I will mark this as resolved.

elsont · Post by **elsont** » Tue Dec 11, 2012 11:49 am

Hi jweir, I saw your solution but it doesn't looks good to me. Your solution will work correctly only if all of your stages are running sequentially (then it is not a good design as you are not using parallell capability).