Out of curiosity I was searching on Google to see if you could do multi-threading in an SQL Server CLR based Stored Procedure. I came across one example on code project where Raymond Macaalay had a working procedure allowing you to run multiple instances of a query concurrently. I think this project was a proof of concept. I am not sure why you would want to run the same query multiple times. But, the use of multiple threads is interesting.
I note that Raymond is using SQL Server 2008 for his example. I also found that multiple threads were highly frowned upon in SQL Server 2005, and all requests for help were rewarded with, “YOU REALLY SHOULDN’T DO THIS. IT SHOULDN’T WORK.”
I find it interesting that threads are discouraged because Microsoft clearly did work to make threads work in the SQL Server Environment. In fact, Microsoft added BeginThreadAffinity() as a solution to how threads operating specifically in self hosted environments such as SQL Server. This was done in the 2005 release.
So, from what I can tell, threading is supported. The question is, why would you want to use it. SQL Server is already a multi-threaded engine. It will take queries you supply and attempt to divide it into multiple threads under certain conditions. One thing I found that continued to be brought up in many different questions and comments is that the use of threading for long running processes is much more difficult to predict. As I understand it, by default SQL Server will move a process from one thread to another for load balancing purposes. This can have un-expected results.
Most of the things I saw people using threads for in SQL Server were for things that, in my opinion, don’t belong on your database engine in the first place. I saw things like calling out to a remote web site to seek data. This would be a much better SSIS process in my experience.
Have you found a scenario where you would like to have multiple threads in a Stored Procedure? Please share a comment with your story.
Cheers,
Ben