September 25, 2009 at 7:47 am
Hi all - not sure if this is the proper forum for this post, since it kinda spans several different areas, but this was the closest fit I could find.
I have a set of CLR functions, written in C#, which invoke a web service call, and then return a value back based on the result of the query.
Recently, this CLR has been timing out on the database.
I've tested this to the limit of my knowledge, but could not locate the problem.
If I run the web service from my browser, it works - I get the response fine.
If I run the CLR straight from Visual Studio, it still works.
If I log on to the machine that the server is hosted on, and try the web service, it works.
From the server, if I run a very similar CLR, which accesses the same web service, but against a different website, it works.
So, my conclusion from all of this, is that it must have something to do with the communication between the server and the website. But I can't really continue any further, to try and figure out exactly what the problem is, and how to address it.
Any insight would be appreciated!
September 25, 2009 at 9:10 am
Alright - so we just addressed the issue, by restarting SQL Server.
Not exactly the solution that I'd like to rely upon though, especially since this is a production server.
One thing we noticed is that the server is running on a 64-bit machine, but the machine only has 4GB of RAM, of which 3ish GB was being used by SQL Server. Upon restarting, it went down to around 150MB.
Now, we haven't noticed any performance problems from the fact that it has only 4GB of RAM, and I can't imagine why a shortage of RAM might impact the communication between the server and one website, while leaving the communication between the server and another website unaffected.
If anyone can help me find the problem that was there, and address it, I'd greatly appreciate it, as we plan on having other CLR processes running in the future, the importance of which will be far greater than that which we have currently running.
August 14, 2014 at 4:26 am
Hi,
I am experiencing the same issue right now.
scenario is same as your. but if web service and SQl server are on same machine, then it works fine (so URL is localhost). if i tried to call remote web service (or website), then it works 2 times, JUST 2 TIMES. from third call, it starts to time out.
It starts to work again after i restarted sql server. (just 2 calls only)
Could you please help me to overcome this.
September 11, 2014 at 1:47 pm
amilapradeep (8/14/2014)
I am experiencing the same issue right now.scenario is same as your. but if web service and SQl server are on same machine, then it works fine (so URL is localhost). if i tried to call remote web service (or website), then it works 2 times, JUST 2 TIMES. from third call, it starts to time out.
It starts to work again after i restarted sql server. (just 2 calls only)
Could you please help me to overcome this.
Hi there. Considering that it does work a couple of times and then stops, that leads me to think that it is a resource issue. Are you properly closing the HttpWebRequest and any other resources that have a Close() method? I would not rely on a using() statement to take care of it but instead make sure to have the Close() calls in a finally block.
Any File System and Network resources opened will become orphaned and remain open if not properly closed.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
November 29, 2018 at 9:58 am
Solomon Rutzky - Thursday, September 11, 2014 1:47 PMHi there. Considering that it does work a couple of times and then stops, that leads me to think that it is a resource issue. Are you properly closing the HttpWebRequest and any other resources that have a Close() method? I would not rely on a using() statement to take care of it but instead make sure to have the Close() calls in a finally block.Any File System and Network resources opened will become orphaned and remain open if not properly closed.Take care,Solomon..amilapradeep (8/14/2014)
I am experiencing the same issue right now. scenario is same as your. but if web service and SQl server are on same machine, then it works fine (so URL is localhost). if i tried to call remote web service (or website), then it works 2 times, JUST 2 TIMES. from third call, it starts to time out.It starts to work again after i restarted sql server. (just 2 calls only)
Could you please help me to overcome this.
Actually, as I learned some time after posting my initial response 4 years ago, if the "2 times" refers to 2 concurrent connections, then this behavior is the default .NET behavior and is by design. As I have since noted in several answers on StackOverflow (e.g. SQL CLR Parallelism):
if that web service call is being executed by more than 2 sessions concurrently, and if those concurrent web service calls are to the same URI, then you are running into the default max connections per URI limit, which is 2. When this limit is reached, all additional calls to that URI are on hold until one of the 2 calls completes. You can increase this max connections per URI by setting the ConnectionLimit property of the
ServicePointclass, which itself is a property ofHttpWebRequest(and any other *WebRequest):
_MyHttpWebRequest.ServicePoint.ConnectionLimit = 10;
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply