Are multi-threaded CLR stored procs possible?
多线程CLR存储过程是否可行?
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.
我有一个数据密集型任务,具有很多并行化的潜力。 CLR Stored Procs可以很好地消除将数据移出进程的开销,我担心我不得不放弃并行计算。
What are my options?
我有什么选择?
Note: We're on SQL Server 2005 with plans in the < 4 month range to upgrade to SQL Server 2008
注意:我们在SQL Server 2005上的计划在<4个月范围内升级到SQL Server 2008
1 个解决方案
#1
4
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).
如果是具有并行化潜力的数据密集型,则应以面向集合的方式处理它,并让SQL在其认为合适时对其进行并行处理。对于每个CPU的数据访问分区,你将无法做到比SQL更聪明的事情,它只能访问你没有的信息(缓冲池填充状态,页面寿命预期,CPU / NUMA亲和力等)。
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.
如果您的处理是面向标量和CPU密集型的(甚至是一些面向集合的处理),请将处理放在UDF CLR函数中,然后让查询执行对您的函数执行进行并行处理。
If your processing has any sort of I/O (ie. web calls), do not put it in SQL, place outside the server process.
如果您的处理有任何类型的I / O(即Web调用),请不要将其放在SQL中,放在服务器进程之外。
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.
如果您的处理确实超出了所有这些类别,并且您仍然相信您可以从多线程中受益,理论上可以在SQL中启动线程。请注意,SQL中的CLR主机不是您的普通CLR主机(即众所周知的应用程序主机或ASP主机)。 SQL CLR是第三种主机类型,它提供了自己的原语(线程,锁定,内存管理等),分层在SOS结构(工作者,锁存器,内存职员等)之上。我强烈建议不要在SQL中进行显式多线程CLR处理。
#1
4
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).
如果是具有并行化潜力的数据密集型,则应以面向集合的方式处理它,并让SQL在其认为合适时对其进行并行处理。对于每个CPU的数据访问分区,你将无法做到比SQL更聪明的事情,它只能访问你没有的信息(缓冲池填充状态,页面寿命预期,CPU / NUMA亲和力等)。
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.
如果您的处理是面向标量和CPU密集型的(甚至是一些面向集合的处理),请将处理放在UDF CLR函数中,然后让查询执行对您的函数执行进行并行处理。
If your processing has any sort of I/O (ie. web calls), do not put it in SQL, place outside the server process.
如果您的处理有任何类型的I / O(即Web调用),请不要将其放在SQL中,放在服务器进程之外。
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.
如果您的处理确实超出了所有这些类别,并且您仍然相信您可以从多线程中受益,理论上可以在SQL中启动线程。请注意,SQL中的CLR主机不是您的普通CLR主机(即众所周知的应用程序主机或ASP主机)。 SQL CLR是第三种主机类型,它提供了自己的原语(线程,锁定,内存管理等),分层在SOS结构(工作者,锁存器,内存职员等)之上。我强烈建议不要在SQL中进行显式多线程CLR处理。