SQLCLR Execution Hangs

Question

I have a number of CLR Dlls in the database. Some of these are Safe and some are Unsafe. None of them use Reset Events. Occasionally I find that all the CLR execution against the database stops for anywhere up to a few minutes. Looking at the transactions against the database they are all in the CLR_MANUAL_RESET wait state. The CPU usage of sqlserver is neglibale at these times.

We are running SQL Server 2005 Enterprise Edition (64bit) 9.00.1399.06

Can anybody help my out with some pointers as to what to look for?

Is it worth upgrading to the latest service pack?

Thanks in advance

Alan

Answer 1

It is always worth upgrading to the latest service pack in my opinion with testing.  You are on the RTM release of 2005, very behind on updates.  I doubt seriously that the latest service pack will fix this problem though.  What do your memory counters look like when this is occuring?  How much memory does the server have, how is you max server memory configured?

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Yeah I agree with updating to the latest service packs, its just agreeing down time with our users. Will have to try and get some in, in the next few months. When I get into work I will try and have a look at the memory counters next time I notice this happening. The server has 68GB of RAM. Not sure what you mean about how its configured?

Alan

Answer 3

Thinking about it on the way into work I was wondering if it could be a lack of CPU resource causing it. The box has 8 cores. And we currently have 23 import machines, which do relatively complex CLR queries splitting XML files into tables. And clients which are doing relatively trival CLR queries. And its the clients who are seeing the waits. Well as problems. Which has got me thinking could SQL Server just be waiting for a cpu to become available before it is able to run the users statement?

Answer 4

When this is happening SQL Server doesnt appear to be doing an awful lot. However the system process is normally using 100% of 1 cpu. Could windows / .net be doing something external to sql server which is effecting this?