{"id":101783,"date":"2024-05-27T19:24:25","date_gmt":"2024-05-27T19:24:25","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101783"},"modified":"2025-02-05T11:23:44","modified_gmt":"2025-02-05T11:23:44","slug":"snake-draft-sorting-in-sql-server-part-4","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/snake-draft-sorting-in-sql-server-part-4\/","title":{"rendered":"Snake Draft Sorting in SQL Server Part 4: Coordinating Parallel Job Completion"},"content":{"rendered":"
Part of a series: [\u00a0 Part 1<\/a> \u00a0|\u00a0 Part 2<\/a> \u00a0|\u00a0 Part 3<\/a> \u00a0|\u00a0 Part 4<\/b> \u00a0]<\/div>\n

In the previous posts in this series, I described how I have optimized a long-running set of routines by processing databases, tables, and even subsets of tables in parallel. This leads to many separate jobs that all kick off at roughly the same time, but because it is difficult to get a perfect balance of work across the jobs, the end times are jagged and unpredictable:<\/p>\n

<\/p>\n

Imagine you’ve split up this chunk of work so that, collectively, it finishes much faster. When all of the threads are done, you want to kick off the next round of work. For example, if the threads are writing data to flat files, when they’re all<\/em> done, you might want to then kick off a process that zips them, or moves them, or bulk loads them into a different system, or all of the above.<\/p>\n

When you have a single<\/em> job that depends on the completion of a single<\/em> previous job, it is quite easy to chain them. The last step of the first job simply calls sp_start_job<\/code> for the subsequent job (or they’re simply steps in a single job).<\/p>\n

When you have one of more jobs that depend on the completion of multiple<\/em> previous jobs, it can be a little harder to synchronize everything. Many people accomplish this by looking at past runtimes and scheduling in a buffer. For example, if the jobs typically take two hours to complete, they’ll schedule the next job(s) to start three hours after the start of the first set.<\/p>\n

<\/p>\n

You might notice that this introduces two problems:<\/p>\n

    \n
  1. Wasted time<\/b>. If the last job in the first round finishes at the expected time (or earlier), then there is at least an hour of idle time:<\/li>\n
  2. Conflict<\/b>. If any job in the first round runs longer<\/em> than expected, the next job(s) will start too early:<\/li>\n<\/ol>\n

    Both of these scenarios are problematic. Obviously we want the second job to finish as early as possible, and that can’t happen if it’s sitting around waiting for nothing. And if the second job starts<\/em> too early, it could be operating on data that isn’t ready – or even present.<\/p>\n

    A localized solution<\/h3>\n

    I’m not going to pretend to have a magic, comprehensive answer for a complex job management solution, but I can show you exactly how I solved this problem for my scenario. My goal was simple: to ensure the next round of work starts right when it should – no sooner, no later.<\/p>\n

    <\/p>\n

    The first thought I had was this: why not just have job 2 check if all the jobs in the first round have finished (successfully, of course)? The problem with that is, if the answer is no, then what? Sleep for 10 seconds and try again? This is not a great solution – even if it uses no resources to loop over and over again until the answer is yes, it looks to DBAs and monitoring tools like the job is running, while at least one of the previous jobs is also<\/em> running. It also skews metrics involving job runtimes, if you use that information for anything (I do).<\/p>\n

    A more logical approach is this: have each job in the first round check, as their last step, if the other jobs in the first round have also finished. If the answer is no, quit with success. If the answer is yes, run sp_start_job<\/code>, and quit with success. This way, the second job won’t start until the last<\/em> job in the first round has finished.<\/p>\n

    <\/p>\n

    Before I share the code to handle this, there are some caveats here. We need to:<\/p>\n