CLR Triggers state

I consider using CLR trigger instead of traditional T-SQL one because I need to use some logic that is already implemented in C#. I'm aware that SQL server supports CLR integration and in my case it seems like a solution that's worth a shot.

However, the operations I want to perform can be somewhat slow. Not slow enough to rule out using them in triggered actions completely, but probably noticeably slow when it comes to inserting hundreds of thousands of records. The slowest part can strongly benefit from caching, I suppose that it will be very few cache misses and thousands of cache hits. At this point it all leads to a question: can CLR triggers have any state? And, more 开发者_如何学运维important, what's the life cycle of this state?

I suppose I could use static fields of trigger class to hold some state, but I have no idea when it gets initialized (When the server is started? At transaction start? Not specified?). I am not sure if it's the safe route and therefore ask what the common practices for using some state in CLR triggers are (if any).

To avoid confusion: I need to cache CLR objects, not the results of some SQL queries, so it's not about how good SQL Server itself is at caching, I want to cache some data that doesn't belong to database. Also, I consider CLR not because I can't do string manipulations and bound checking in T-SQL. I need to execute some logic that is implemented in CLR class library and has a lot of dependencies. Wether I should use triggers in this case is another question that has almost nothing to do with this one.

Many thanks in advance.

PS: I will appreciate any comments and insights on topic, even the ones that don't answer my question directly, but please don't make it all about "triggers are evil and shouldn't ever be used" and "CLR integration is slow and a major compatibility pain". Also, I know that it may scream "premature optimization" to someone, but at the moment I just want to know what my optimization options are going in since I'm new to CLR integration in SQL server. I won't optimize it unless profiling results suggest so, but I don't want to implement the whole thing to realize it's too slow and there is nothing I can do about it.

I use SQL Server 2008 and .NET 3.5.

While it is possible to use static class fields in the SQLCLR Trigger class to cache values, there are several things you need to be very cautious about:

1. How much data do you plan on caching? You don't want to take up too much memory that SQL Server should instead be using for queries.

2. There is a single AppDomain per Database per Assembly Owner (i.e. AUTHORIZATION on the Assembly). This means that the code in any particular Assembly is shared across all SQL Server Sessions (i.e. SPIDs). If the data is just lookup data that won't change based on which process is interacting with the static field, then this is fine. But if the data is different per process, then this will produce "odd" behavior unless you associate a value such as the current TransactionID with the process.

3. If the data is per process, assuming you find a way to differentiate each particular SPID / SESSION, how are you going to clean up the old data? It will exist in memory until explicitly removed or the AppDomain is unloaded. This is not a problem for common lookup data that is meant to be shared with everyone as that type of data doesn't increase with each new process. But per-process data will continually increase unless cleared out.

4. AppDomains can be unloaded at any time and for a variety of reasons (memory pressure, drop/recreate of the Assembly, security change related to the Assembly, security change related to the DB, running DBCC FREESYSTEMCACHE('ALL'), etc). If the data being cached can cause different outcomes between sequential processes if one process relies upon data cached by a prior process, then this cannot be guaranteed to work. If the cache being dropped between processes results in nothing more than the need to reload the cache, then it should be fine.

Other notes (but nothing to be cautious about):

1. AppDomains are loaded when the first method is called in an Assembly where there is no currently running AppDomain for the Database that the Assembly exists in and the User that is the Authorizer of that Assembly.

2. AppDomains will remain loaded until they are unloaded by SQL Server for the one of the reasons noted above, but none of those scenarios will necessarily occur. Meaning, the AppDomain can remain loaded for a very long time (i.e. until server / service restart).

3. Each Assembly is loaded the first time a method inside of it is referenced.

4. In order to make use of the loading event, you can place code in the static class construct. Just be aware that there is no SqlContext available, so you can't make any SqlConnections in a static class constructor that use the in-process Context Connection (i.e. Context Connection = true).