Procedure timing out, yet...
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
Erland Sommarskog has an article[^] about how to troubleshoot this kind of problems.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
Check that it is not a deadlock condition. I believe SQL server will terminate (pick a victim) and terminate a session so that others can continue.
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
-
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer. When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available. Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure. Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
Did u check the amount of data the procedure is returning. In case the procedure is returning too many rows of data, then that might be the cause of timeout when you run the procedure programmatically. But when you run the same procedure from database itself which i guess is Sql Management studio, only top 500 or 1000 rows would be returned depending on the SSMS setting. Hence the result comes up without time out.