What is the SQL Server CLR Integration Life Cycle?

Question

How are CLR (.NET) objects managed in SQL Server?

The entry point to any CLR code from SQL Server is a static method. Typically you'll only create objects that exist within the scope of that method. However, you could conceivably store references to objects in static members, letting them escape the method call scope. If SQL Server retains these objects in memory across multiple stored procedure/function calls, then they could be useful for caching applications -- although they'd be more dangerous too.

How does SQL Server treat this? Does it even allow (non-method) static members? If so, how long does it retain them in memory? Does it garbage collect everything after every CLR call? How does it handle concurrency?

Answer 1

In "Pro SQL Server 2005 Assemblies" by Robin Dewson and Julian Skinner, it says that "Assemblies loaded into a database, like other database objects, are owned by a database user. All assemblies owned by the same user in the same database will run within the same AppDomain. Assemblies owned by a different user will run within a separate AppDomain."

What this tells me is that if you're working with a single database and all the assemblies you load in with the CREATE ASSEMBLY statement have the same owner, then your assemblies will all run in the same app domain. However, being in the same AppDomain does not mean using the same code-base, so even the same dll can be loaded into the same application domains multiple times, and it's types will not match up, even though they have the same name. When same-named types are from different code-bases, their static variables will be different instances as well.

The only way I can see to use static variables safely in SQL Server CLR environment with multiple assemblies is to actually just use a single assembly. You can use the ILMerge utility with the "UnionMerge" option to pack all your assemblies into one and merge classes with the same name. This should guarantee that for a given database, in your sole assembly, your static variables will work just like they would in a stand-alone application. I think it's safe to assume the application domain isn't unloaded and reloaded at every request, but you can't depend on it never being unloaded, since that will happen whenever there is an unhandled error (at least if it's running in unsafe mode).

Answer 2

SQL Server allows static readonly members if assembly is deployed with Unsafe permission level.

Practically objects are retained in memory until SQL service is stopped/restarted.

Regarding concurrency, your object and methods should be thread-safe as everywhere else.

For example:

public static class MyCLRClass
{
    private static readonly ReaderWriterLock rwlock = new ReaderWriterLock();
    private static readonly ArrayList list = new ArrayList();

    private static void AddToList(object obj)
    {
        rwlock.AcquireWriterLock(1000);
        try
        {
            list.Add(obj);
        }
        finally
        {
            rwlock.ReleaseLock();
        }
    }

    [SqlProcedure(Name="MyCLRProc")]
    public static void MyCLRProc()
    {
        rwlock.AcquireReaderLock(1000);
        try
        {
            SqlContext.Pipe.Send(string.Format("items in list: {0}", list.Count));
        }
        finally
        {
            rwlock.ReleaseLock();
        }
    }
}

I use such things in SQL CLR and it works.

Answer 3

Not conceivably; you very much can create static members. BUT, they need to be marked as readonly for assemblies that have a PERMISSION_SET of either SAFE or EXTERNAL_ACCESS. Only an assembly marked as UNSAFE can have writable static members. And this restriction is due to the very nature of static members: they are shared between threads and sessions.

An assembly is loaded the first time a method within it is accessed. It is shared for all sessions to use, which is why only static methods are accessible. The idea is to write functions, not an application, so there isn't a lot of use in keeping state. And it can easily (though certainly not always) lead to unpredictable behavior if various sessions are overwriting each other. So concurrency isn't handled at all, unless you write that part yourself.

It should be expected that once loaded, the class (and the App Domain it resides in) will remain in memory until either the SQL Server service restarts or the PERMISSION_SET value is changed. But this is not guaranteed. According to this page, Memory Usage in SQL CLR:

when there is memory pressure on the server, SQL CLR will try to release memory by explicitly running garbage collection and, if necessary, unloading appdomains.

So you are correct on both counts regarding static members:

• they can be used for caching (very cool)
• they can be more dangerous:
  • they can cause unexpected behavior
  • they can tie up memory as there is no inherent mechanism or natural event to clean them up because the class stays active.

And, the amount of memory available for CLR routines varies greatly depending on whether SQL Server is 32 or 64 bit, and whether you are using SQL Server 2005 / 2008 / 2008 R2 or using SQL Server 2012 / 2014. For more info on how much memory SQLCLR has to play with, check out SQL Server 2012 Memory and Memory Usage in SQL CLR (same as the first link, posted above the quote).

Answer 4

Here's some info that I've found.

Troubles with shared state and anonymous delegates in SQLCLR

Not only is shared state not allowed in a non-UNSAFE assembly, but anonymous delegates (unfortunately) trigger this "shared state" restriction.