Home Forums Programming CLR Integration and Programming. SQL 2008 CLR Assembly with Permission Set Unsafe RE: SQL 2008 CLR Assembly with Permission Set Unsafe

  • May 9, 2014 at 4:49 am

    #1712108

    Regarding the memory issue and SAFE vs UNSAFE, there is not much you can do:

    1) The TimeZoneInfo class is known to have memory leaks which is why it has a Host Protection Attribute on it. See http://msdn.microsoft.com/en-us/library/cc645949(v=sql.100).aspx and look for "TimeZoneInfo" and you will see it listed with a Host Protection Attribute of "MayLeakOnAbort". So I don't think garbage collection has anything to do with it.

    2) Because it has a Host Protection Attribute, you cannot use System.TimeZoneInfo in either SAFE or EXTERNAL_ACCESS assemblies. Your only option is UNSAFE, and that is due to the memory leak (as opposed to the memory leak due to the Assembly being set to UNSAFE).

    Regarding the memory not being released until the SQL Server process is restarted:

    you can free up the memory by causing the App Domain to unload, and that can be achieved by doing an ALTER ASSEMBLY to set the PERMISSION_SET to something other than the current, such as SAFE. Then do another ALTER to set it back to UNSAFE so you can use the function again.

    Regarding performance:

    1) in the SqlFunction() attribute, add "IsDeterministic = true". That might help the optimizer.

    2) Why are you dealing with strings instead of dates? is the field itself a string? The conversion from String to DateTime back to String is part of what is taking so long. If possible, use the SqlDateTime type in the C# code.

    Hi Solomon,

    Thank you for the reply, that is very helpful.

    When the memory footprint was so large and it wasn't been freed up, I tried dropping the user defined function and assembly and waited. Nothing happened and then I restarted the sql server service. I will try changing the PERMISSION_SET.

    Can you please explain a little what exactly you mean by unloading the app domain? Is the app domain here, a host process which hosts CLR assemblies?

    I used SqlString instead of SqlDateTime because, some of those many fields are strings.. Can you think of another alternative? Would it be better if I overload the function definition? (because in that case, return type also has to be different..)

    (Also the tables and the fields on which this operation has to be done will be different across various client databases. So, the update statement is generated in a C# console app, using our own metadata layer..)

    Thanks and Regards,

    Venkat