Threading in CLR in SQL Server 2008

Question

I have a CLR process which runs under SQL Server2008. It builds a cache of several tables data to hold in a static class for use later by other calls.

My question is could I improve the process of loading this cache by spawning threads to load each data set/ table in my cache?

I've steared clear of this in the past as various posts have suggested leave the thread management to SQL Server. However I could really do with speeding up this process. Currently its a sequential process to load each data set. If I could run these concurrently it would be very handy. A process I've done many atime outside of the CLR cover to get some extra performance gains.

Any ideas help tips very much appreciated.

Answer 1

You can use threads, but they must behave. Otherwise you'll lose the benefits of using them.

From the CLR Host Environment

How SQL Server and the CLR Work Together

This section discusses how SQL Server integrates the threading, scheduling, synchronization, and memory management models of SQL Server and the CLR. In particular, this section examines the integration in light of scalability, reliability, and security goals. SQL Server essentially acts as the operating system for the CLR when it is hosted inside SQL Server. The CLR calls low-level routines implemented by SQL Server for threading, scheduling, synchronization, and memory management. These are the same primitives that the rest of the SQL Server engine uses. This approach provides several scalability, reliability, and security benefits.

Scalability: Common threading, scheduling, and synchronization

CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. In order to synchronize between multiple threads, the CLR calls SQL Server synchronization objects. This allows the SQL Server scheduler to schedule other tasks when a thread is waiting on a synchronization object. For example, when the CLR initiates garbage collection, all of its threads wait for garbage collection to finish. Because the CLR threads and the synchronization objects they are waiting on are known to the SQL Server scheduler, SQL Server can schedule threads that are running other database tasks not involving the CLR. This also enables SQL Server to detect deadlocks that involve locks taken by CLR synchronization objects and employ traditional techniques for deadlock removal.

Managed code runs preemptively in SQL Server. The SQL Server scheduler has the ability to detect and stop threads that have not yielded for a significant amount of time. The ability to hook CLR threads to SQL Server threads implies that the SQL Server scheduler can identify "runaway" threads in the CLR and manage their priority. Such runaway threads are suspended and put back in the queue. Threads that are repeatedly identified as runaway threads are not allowed to run for a given period of time so that other executing workers can run.

Answer 2

Static data shared across invocations - not a good plan for CLR calls: SQL Server documentation

Programming Model Restrictions

The programming model for managed code in SQL Server involves writing functions, procedures, and types which typically do not require the use of state held across multiple invocations or the sharing of state across multiple user sessions. Further, as described earlier, the presence of shared state can cause critical exceptions that impact the scalability and the reliability of the application.

Given these considerations, we discourage the use of static variables and static data members of classes used in SQL Server. For SAFE and EXTERNAL_ACCESS assemblies, SQL Server examines the metadata of the assembly at CREATE ASSEMBLY time and fails the creation of such assemblies if it finds the use of static data members and variables.