IN SQL Server is CLR Threadsafe

views:

106

answers:

IN SQL Server is CLR Threadsafe

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..

+3 A:

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 2010-08-05 15:28:58

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 2010-08-07 01:05:49

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 2010-08-07 02:19:38

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 2010-08-07 10:37:12

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 2010-08-07 22:17:32

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 2010-08-07 23:03:20

Never trust what MCS tells you. Most of these guys have zero clue.

2010-08-11 06:16:19

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 2010-08-07 01:10:53

Is this just a 32 bit issue?

Martin Smith 2010-08-07 01:12:58

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 2010-08-07 10:38:19

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

u07ch 2010-08-11 06:07:28

ansaurus

tags:

views:

answers:

IN SQL Server is CLR Threadsafe

related questions