In a recent blog post, Jonathan Kehayias demonstrates a clever way to “multi-thread” maintenance tasks, when they need to run against very large (i.e. multiple terabyte) databases. The tool he used? Good old-fashioned Service Broker. Simply create some basic Service Broker objects, an activation procedure to automate Ola Hallengren’s maintenance procedures, bind the activation procedure to a service broker queue and create a job to feed in the tasks.

As Jonathan points out, the time-consuming part was not so much building this solution, as the hours spent scratching his head deciding what solution he ought to use, and fighting the tendency to over-complicate, and over-engineer. He experimented with implementing multiple threading within a custom C# application or PowerShell, as well as creating multiple agent jobs, before finally realizing that the correct and simplest solution was the one already baked into Service Broker.

Over-thinking a problem and then over-engineering the solution is a common mistake. Inexperienced database developers are often convinced they are the first to need a particular functionality and with pioneering zeal set about creating complex custom solutions. As a result, they use PowerShell and all sorts of exotic frameworks, just to automate a task such as centrally managing a range of SQL Server instances, which can be done far more simply using SQLCMD! They use exotic window functions and recursive CTEs to achieve a result that merely requires a temporary table.

SQL Server has reached a level of maturity now such that basic problems can be solved in many different ways. There is a temptation to dismiss the more mature technologies in SQL Server such as Service Broker, SSIS, BCP and ODBC in favor of the new. However, many new features in SQL Server tend to be appropriate to an increasingly ‘niche’ use-cases. They are there to fill in the gaps rather than to open up a wide new range of functionality. In short, it’s probably been done before, and achieved simply, using existing tools.

I’d be interested to hear of the most over-engineered solutions you’ve seen used to solve relatively simple SQL Server problems.