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.
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.
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.
