October 23, 2018 at 8:46 am
Hi!
Any ideias about what problem could be in the following scenario:
1. 2 stored procedures are invoked from an application repeatedly in regular time intervals: (both of them use CTE)
» first to get the total number of rows;
» second to get details of part of those rows;
2. after some hours, the second one keeps returning time out exception (while the first return the correct results)
System.Data.SqlClient.SqlException (0x80131904):Execution Timeout Expired. The timeout period elapsed prior to completionof the operation or the server is not responding. --->System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
3. but if I invoque the same SP from SSMS, the results are returned fast (for both of them)
Best Regards
October 23, 2018 at 10:40 am
amns - Tuesday, October 23, 2018 8:46 AMHi!Any ideias about what problem could be in the following scenario:
1. 2 stored procedures are invoked from an application repeatedly in regular time intervals: (both of them use CTE)
» first to get the total number of rows;
» second to get details of part of those rows;2. after some hours, the second one keeps returning time out exception (while the first return the correct results)
System.Data.SqlClient.SqlException (0x80131904):Execution Timeout Expired. The timeout period elapsed prior to completionof the operation or the server is not responding. --->System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
3. but if I invoque the same SP from SSMS, the results are returned fast (for both of them)
Best Regards
Try using sp_WhoISActive when you are getting the timeouts to see what is going on during that time.
sp_whoisactive: Blocking, Blockers, and Other B Words
sp_whoisactive: Seeing the Wait That Matters Most
Sue
October 25, 2018 at 1:38 am
First up, check the connecting settings between your SSMS and the clients calling the procedure. There may be differences. Also look at the execution plans to see if they are different between the two calls. You can use the system_health Extended Events session to look at all the details for long running queries. If sp_whoisactive doesn't help, then look to using Extended Events to capture query behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply