Are multi-threaded CLR saved procs possible?
I've got a data-intensive task with a lot of possibility of parallelization. CLR Saved Procs could be great to get rid of the overhead of moving the information from process, my I'm afraid that I'd have to stop parallel computing.
What exactly are my options?
Note: We are on SQL Server 2005 with plans within the < 4 month range to upgrade to SQL Server 2008
If is data intensive with possibility to parallelization, you need to process it in set oriented manner and let SQL paralelize the processign because it sees fit. You won't have the ability to do anything whatsoever wiser than SQL already does regarding partition the information access per CPU, it really can access information you do not (buffer pool fill condition, page lifetime expectancy, CPU/NUMA affinity etC).
In case your processing is scalar oriented and CPU intensive (as well as some set oriented processing), put the processing within an UDF CLR function and again, allow the query execution paralelize your function execution.
In case your processing has any kind of I/O (ie. web calls), do not place it in SQL, place outdoors the server process.
In case your processing really falls outdoors each one of these groups but you just believe you may need multithreading, theoretically can be done to begin threads inside SQL. Be cautioned the CLR host inside SQL is not your normal CLR host (ie. the well-known application host or even the ASP host). SQL CLR is really a third host type, offering its very own primitives (threads, securing, memory management etc) layered on the top of SOS constructs (employees, latches, memory clerks etc). I'd strongly advise against doing explicit multi-threaded CLR processing in SQL.