{"id":101600,"date":"2024-04-12T10:34:31","date_gmt":"2024-04-12T10:34:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101600"},"modified":"2026-04-14T11:37:19","modified_gmt":"2026-04-14T11:37:19","slug":"snake-draft-sorting-in-sql-server-part-1","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-1\/","title":{"rendered":"Snake Draft Sorting in SQL Server: Distributing Workloads Evenly (Part 1)"},"content":{"rendered":"
Snake draft sorting is a technique for distributing processing tasks across workers or time windows as evenly as possible – borrowed from sports fantasy draft order, where picks alternate direction each round to prevent any one team from getting all the best picks consecutively. In SQL Server, the same principle applies to workload distribution: instead of processing tasks in sequential order (which can overload one worker with all the heavyweight tasks), you assign tasks in a serpentine pattern so each worker gets a balanced mix of expensive and cheap operations. This article introduces the T-SQL implementation with a worked index rebuild example.<\/strong><\/div>\n
\u00a0<\/div>\n
Part of a series: [\u00a0 Part 1<\/b> \u00a0|\u00a0 Part 2<\/a> \u00a0|\u00a0 Part 3<\/a> \u00a0|\u00a0 Part 4<\/a> \u00a0]<\/div>\n

I recently had a restore job where I needed to split the work up into multiple parallel processes (which I’ll refer to here as “threads”). I wanted to balance the work so that the duration was something significantly less than the sum of the restore times.<\/p>\n

Imagine a job that loops through and restores each database in sequence (let’s start with just four):<\/p>\n

\"Timeline<\/a><\/p>\n

The restores are not finished (allowing the next step(s) to start) until the sum<\/em> of the restore times for all four databases.<\/p>\n

Instead of one job, imagine four<\/em> jobs that all start at roughly the same time, each restoring one<\/em> of those four databases (assuming the server can handle the extra work):<\/p>\n

\"Timeline<\/a><\/p>\n

With this strategy, all of the restores are finished by the time it takes the longest<\/em> restore to finish, instead of the sum of all four restore times. This allows subsequent tasks to start a lot sooner, and frees up the server for other work.<\/p>\n

Well, duh<\/h3>\n

This concept is not overly clever or imaginative on my part – this has been the premise of parallel computing since computing was a thing and, more generally, division of labor has been a strategy throughout recorded history.<\/p>\n

That said, the above was a simplistic example.<\/p>\n

In reality, we have a lot more databases, in a periodic job where we restore a slew of backups on an internal server (and then perform further processing once they’re all finished). Since this is a supporting server and not production-facing, it is configured to restore and support all of the production databases, with no other workload, so it has generous headroom for additional work. While I didn’t want to overwhelm the server, I wanted the entire set of restores to happen faster in parallel than serially. But how could I split the work up? Alphabetical? Random? Those might lead to quite skewed work – all the biggest databases could be alphabetically together or just randomly placed on the same thread.<\/p>\n

Borrowing from sports<\/h3>\n

I play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most<\/em> fair and balanced. A snake draft works like this (a simple example with four players):<\/p>\n