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