3

I have an interface in CLR between SQL Server and the Exchange Web Services to Sync and send emails between applications. In testing this it works(ed) without any problems; we are seeing sporadic issues in the production environment where longer web service task appear to be have overlapped.

My question is very simple and I cant decide from reading the CLR details on MSDN - is CLR Thread Safe or not out of the box.

If not what is the best way of making calls to CLR that are thread safe is to applocking around my procedures or is there a less drastic alternative.

Thanks for your responses; we are coming around to this being an EWS / Impersonation issue rather than a SQL Issue. We have done a new set of load tests out of hours on the system and even under massive load (1000x higher than the application has seen so far) we can't see a memory leak / threading issue which is why we are now looking elsewhere..

u07ch
  • 13,324
  • 5
  • 42
  • 48
  • This link isn't exactly the same question, but it may provide some insights: http://stackoverflow.com/questions/663124/what-is-the-sql-server-clr-integration-life-cycle – dcp Aug 05 '10 at 12:32
  • Please answer your own question with some details about your solution and (in two days) accept that answer. –  Aug 10 '10 at 16:03

4 Answers4

5

Don't use in-proc CLR to connect externally, to web services or exchange or whatever. Use an ordinary process, outside SQL Server. You'll see more than just 'sporadic' issues: you'll exhaust the worker pool on CLR events and the SQL Server will freeze.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Have you actually seen a SQL Server locked up by SQL CLR assembly? SQLCLR does share a common thread pool with the DB Engine, but does this mean that the scheduler fails to manage the threads in way that avoids a complete freeze? I find this surprising. – Paul Keister Aug 07 '10 at 01:05
  • Yes I've seen. I too had created CLR procedures that were connecting to remote machines (for performance counters sampling) and after 3 server freezes over two weeks, had to replace the solution with external process. x64 server. – Remus Rusanu Aug 07 '10 at 02:19
  • Remus the funny thing is it was Microsoft who recommended this approach to handling email with EWS / CLR. AM going to close the log; we have put the system under massive loads (5000 outbound emails every 30 seconds with attachments) and can't reproduce the error which leads us to question if its an exchange / authentication issue in the EWS/ DC rather than a thread safety issue in SQL Server – u07ch Aug 07 '10 at 10:37
  • Remus: thanks, this is good information. May I ask for one clarification: are you saying the entire SQL Server process froze, or just the SQLCLR threads? – Paul Keister Aug 07 '10 at 22:17
  • 1
    Entire SQL, out of workers. When you say 'Microsoft recommended', you mean a SQL Server field MCS recommended to use in-proc CLR? – Remus Rusanu Aug 07 '10 at 23:03
  • Never trust what MCS tells you. Most of these guys have zero clue. –  Aug 11 '10 at 06:16
3

The answer actually depends on how you write your .Net code AND how you register your Assembly. By default, and by general preference, SQLCLR Assemblies are created as SAFE which means that SQL Server scans the Assembly when it is created (the actual CREATE ASSEMBLY statement) to determine if it conforms to the rules of a SAFE Assembly. A SAFE Assembly does not have any non-read-only instance variables. Meaning, all variables are instance variables and if you do declare a static variable you have to use the "readonly" modifier. This ensures that you are not sharing data across threads.

HOWEVER, you can create a static variable in a Class and modify it but the Assembly must be created as UNSAFE. Trying to create it as SAFE will give you the following error:

CREATE ASSEMBLY failed because method 'MethodName' on type 'ClassName' in safe assembly 'AssemblyName' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Storing to a static variable is not thread safe and hence you must give the Assembly the UNSAFE permission. But outside of this case SQLCLR is thread safe.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • What if your `static readonly` variable is set with reflection? does that actually break the SAFE Assembly or is the presence of `System.Reflection` enough to flag an assembly as `UNSAFE`? – Seph Apr 18 '12 at 10:43
  • @Seph, using reflection must be ok because I am doing just that ;-). I have the following code: "public static readonly int Major = Assembly.GetExecutingAssembly().GetName().Version.Major;" and the assembly is marked as SAFE with no install or run-time issues. – Solomon Rutzky Apr 19 '12 at 03:06
  • I think that the reason static variables must be marked as readonly, is because static variables could typically be modified by more than one thread at once. By making them readonly, that won't happen. However, the object itself could be accessed/modified by multiple threads, and in that situation, you'd have to use some kind of lock-free synchronization. – Triynko Nov 04 '13 at 08:11
0

The main issue that you see with SQL CLR code is running out of memory, which leads to an AppDomain reset. This is equivalent to OS crash from the perspective of your code. When using the SQLCLR, you are using a separate memory pool managed by SQL Server which is much smaller and less flexible than you are used to. I've been told that the SQLCLR team is working on this issue.

One important note: if you do get a SQLCLR AppDomain reset, the stability of your server in other respects should not be affected. The SQLCLR procedure that crashed will simply return a TSQL error to the caller.

Paul Keister
  • 12,851
  • 5
  • 46
  • 75
  • Paul Thanks for your answer; we have load tested the web services for sending mail messages by sending 5000 concurrent messages over and over for hours at a time. We aren't seeing a memory leak in the application; nor are we seeing any app restarts in the logs. The fact that the load testing messages were delivered without issue makes us think we might have a DC / EWS issue so we have written a few thousand error handling and logging events into the code to track things through for now. – u07ch Aug 07 '10 at 10:38
0

Having done much independant testing on CLR now (deliberately trying to make it fail) if your CLR code is written properly -nicely declare variables with initialisation values it appears that the CLR is thread safe.

Rob
  • 45,296
  • 24
  • 122
  • 150
u07ch
  • 13,324
  • 5
  • 42
  • 48