Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CLR Function Timeout error

Author  Topic 

shenulal
Starting Member

11 Posts

Posted - 2012-12-29 : 01:08:17
Hai Friends,
I have a CLR function which is used to Reverse Geocoding from Google Map, Bing Map and Yahoo Map. This CLR function i am calling inside the Stored Procedure which is running approx 5 times per second. Sometimes this function has no issues and returns the result in approx 90ms. But, after sometime its performance is getting worse as the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". Do you guys have any idea that where its performance getting downgraded.

Regards,
Shenulal

prett
Posting Yak Master

212 Posts

Posted - 2012-12-30 : 23:21:29
This error occurs due to Long running tasks or uncommitted transactions. Please check this article for more information & solution : http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-12-31 : 15:27:33
quote:
Originally posted by shenulal

Hai Friends,
I have a CLR function which is used to Reverse Geocoding from Google Map, Bing Map and Yahoo Map. This CLR function i am calling inside the Stored Procedure which is running approx 5 times per second. Sometimes this function has no issues and returns the result in approx 90ms. But, after sometime its performance is getting worse as the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". Do you guys have any idea that where its performance getting downgraded.

Regards,
Shenulal


You didn't provide enough information for anyone to say what the problem is but generally what i would do is see if you can isolate where the problem is happening. Does the SP have any other code besides the CLR call?

what action do you need to take to recover from the problem? Does it just start working again or do you need to do something drastic like restarting the server service? If it is just an intermittent problem that usually works fine then it sounds more like a resource contention issue. Perhaps an unrelated process is blocking these calls.

If it is not a contention problem then hopefully you can observe the problem in a non-production environment. If you can simulate a similar load outside of your production environment I would try commenting out the CLR and just run the SP under that load to eliminate the other SP code from the cause. Then just call the CLR only under that load. Hopefully with this type of isolation test you can determine which specific code is causing your problems.

If the problem is caused by the CLR then you may have a something like a memory leak issue. Because CLR routines are static you need to make sure you don't have some static member that you are not cleaning up after. That problem would usually show itself as an "out of memory" error but depending on what you're doing it could exceed the timeout value set before a memory error is thrown.

Maybe you can observe an increasing performance problem without the load but simply by calling the SP many times. That would be a good clue. It would point more to the CLR memory leak thing.


Be One with the Optimizer
TG
Go to Top of Page

shenulal
Starting Member

11 Posts

Posted - 2013-01-01 : 23:41:59
quote:
Originally posted by TG

quote:
Originally posted by shenulal

Hai Friends,
I have a CLR function which is used to Reverse Geocoding from Google Map, Bing Map and Yahoo Map. This CLR function i am calling inside the Stored Procedure which is running approx 5 times per second. Sometimes this function has no issues and returns the result in approx 90ms. But, after sometime its performance is getting worse as the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". Do you guys have any idea that where its performance getting downgraded.

Regards,
Shenulal


You didn't provide enough information for anyone to say what the problem is but generally what i would do is see if you can isolate where the problem is happening. Does the SP have any other code besides the CLR call?

what action do you need to take to recover from the problem? Does it just start working again or do you need to do something drastic like restarting the server service? If it is just an intermittent problem that usually works fine then it sounds more like a resource contention issue. Perhaps an unrelated process is blocking these calls.

If it is not a contention problem then hopefully you can observe the problem in a non-production environment. If you can simulate a similar load outside of your production environment I would try commenting out the CLR and just run the SP under that load to eliminate the other SP code from the cause. Then just call the CLR only under that load. Hopefully with this type of isolation test you can determine which specific code is causing your problems.

If the problem is caused by the CLR then you may have a something like a memory leak issue. Because CLR routines are static you need to make sure you don't have some static member that you are not cleaning up after. That problem would usually show itself as an "out of memory" error but depending on what you're doing it could exceed the timeout value set before a memory error is thrown.

Maybe you can observe an increasing performance problem without the load but simply by calling the SP many times. That would be a good clue. It would point more to the CLR memory leak thing.


Be One with the Optimizer
TG




Thank you very much for the detailed explanation you have provided for me. As you said, We are taking the action of Restarting the SQL SERVER SERVICE when this error occurs. After restarting the service it is getting alright and from your explanation i strongly believe it would be the Static member which causing the issue. I'm not cleaning up the static member once the process is done. In the stored procedure i have checked the other codes commenting the CLR routine. Then the SP was working very perfectly without any error. So, i think it is the CLR function which is the root cause. I'll change the things and will update you with the result.
thanks a lot once again.

Regards,
Shenulal
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-02 : 13:58:47
Thanks for the update Shenulal - it sounds like you're getting it under control. Let us know if you need more help.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -