People can do amazing things with T-SQL alone even when it may not be the best language to use. For instance, T-SQL was never designed or intended as a language to manage running multiple queries concurrently or running queries asynchronously. But I have seen many stored procedures written to start SQL Agent jobs and then manage the termination of these jobs by constantly polling the job status. In effect, these stored procedures leverage SQL Agent jobs to execute T-SQL code in multiple threads in parallel or execute T-SQL code asynchronously.
For some tasks, this approach works fine and is manageable. But in general, if you require running multiple queries in parallel either inside the same SQL Server instance or across multiple SQL Server instances, T-SQL is not the best language of choice.
Let’s look at the case of a typical data archiving task where you have multiple tables to archive. For each of these tables, you may need to (1) determine what must be archived, (2) copy the data to be archived from the table into an archive table, and (3) delete the data from the table. If you write everything in T-SQL, typically you’d loop through the tables and perform the above logic for each table in turn. This is obviously ‘single-threaded’. Although any of the queries may be processed with multiple threads inside the SQL Server engine because of the query parallelism support, the overall process is single threaded and synchronous in that step N+1 can only start when step N is finished. No doubt there is great potential to reduce the total batch processing time, if we can run some of these steps in parallel.
You could use the approach described earlier by creating multiple SQL Agent jobs, perhaps one for each table, and all your main procedure needs to do is to start these jobs and wait for their completion. In such as case, the data archive task is performed in multiple threads in parallel, one for each table. Even if the tables are scattered across multiple servers, you could still use this approach. You just have to configure some linked servers and start the SQL Agent jobs on their corresponding servers.
Is there any problem with this approach? As mentioned, for some tasks this is a perfect approach. Plus, the beauty is that everything is done in T-SQL only, and that’s right in your comfort zone. But then, that’s also where the problem is for some other tasks where more granular control is a must and interacting with non-SQL Server components may be necessary.
Let me continue with the data archiving task. More granular control can come in two areas: (1) concurrency or parallelism throttle, and (2) exception handling.
Except in the case where the number of tables is very small and does not change, you most likely would want to be able to dynamically throttle the degree of concurrency instead of simply running one SQL Agent job per table. Though doable, it would be very ugly to dynamically create SQL Agent jobs and assign each job what tables to process. Moreover, for certain tasks, the overhead of managing the creation, execution, and removal of SQL Agent jobs just doesn’t quite justify it. A case in point is, for instance, when you have a large number of tables scattered across half a dozen servers, and you want to do a count of each table on how many rows to be archived.
For exception handling, T-SQL offers very little support when you rely on SQL Agent jobs to achieve parallelism. Other than polling the job status and checking some status tables that the job is expected to update, your main T-SQL procedure basically has little control over the SQL Agent job once the job has started running. If the task can be partitioned as such that each SQL Agent job can handle its own errors independently, you are fine. Otherwise, this approach can become quite clumsy.
So what’s the point? The point is that as good as it is to stay within one’s comfort zone that is T-SQL, for some tasks T-SQL is not the best language even if you can manage to accomplish the tasks with T-SQL alone. Tasks that require parallelism or that can benefit from parallelism (beyond SQL Server engine’s query parallelism) may be better accomplished if you venture beyond T-SQL into a robust programming language, such as C#, that truly supports multi-threading, and use it (instead of T-SQL) to construct the main controlling mechanism for your tasks.
Granted that when you only have a wrench, you may not have a choice but to use it as a hammer. But I submit that you’d be far better off if you actually have a hammer in your toolbox! Then again, if you live in a wrench friendly but hammer hostile environment, you could be in quite a quandary.