I hope this is in the right board, the SQL CLR section seems to be under SQL Server 2005.
I have a CLR Assembly that contains methods to encrypt and decrypt a table of fields using AES.
We see a large performance hit on the first run of these methods whilst the CLR is loaded into memory. This takes between 15 and 20 seconds. Subsequent calls take < 1 second until the Assembly is unloaded.
I have done some reading around AppDomains and it seems that the AppDomain is held in memory 'to improve performance' until such time as SQL Server deems that 'memory pressure' requires it to be unloaded.
We will be hitting these methods quite often but not consistently enough to prevent the CLR from unloading.
Is there anyway to adjust the server settings or assembly options to make them more persistent?