Are multi-threaded CLR stored procs possible?
I have a data-intensive task with lots of potential for parallelization. CLR Stored Procs would be great to remove the overhead of moving the data out of process, my I fear that I'd have to give up parallel computing.
What are my options?
Note: We're on SQL Server 2005 with plans in the < 4 month range to upgrade to SQL Server 2008
If is data intensive with potential to parallelization, you should process it in set oriented manner and let SQL paralelize the processign as it sees fit. You will not be able to do anything smarter than SQL already does in regard to partition the data access per CPU, it simply has access to information you don't (buffer pool fill state, page lifetime expectancy, CPU/NUMA affinity etC).
If your processing is scalar oriented and CPU intensive (and even some set oriented processing), place the processing in an UDF CLR function and again, let the query execution paralelize your function execution.
If your processing has any sort of I/O (ie. web calls), do not put it in SQL, place outside the server process.
If your processing really falls outside all these categories and you still believe you could benefit from multithreading, in theory is possible to start threads inside SQL. Be warned that the CLR host inside SQL is not your normal CLR host (ie. the well known application host or the ASP host). SQL CLR is a third host type, offering its own primitives (threads, locking, memory management etc) layered on top of SOS constructs (workers, latches, memory clerks etc). I would strongly advise against doing explicit multi-threaded CLR processing in SQL.