App timing out, but sproc runs in 3 seconds

Question

App timing out, but sproc runs in 3 seconds

SqlServerDBA318

SSCrazy

Points: 2353
More actions
October 26, 2017 at 9:01 pm

#340248

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
I don't have much experience with apps, tried to solve this and looking for an answer. I have seen some previous discussions on here, but they all seem to have a specific error other that the generic timeout.
Why would a simple query/sproc that takes 3 seconds to run in SSMS blow a timeout error when using an app?
I will say all stats are updated, no indexes needed, no blocking and no issues with running in ssms in 3 seconds.
We did just migrate to sqlserver 2016 from 2012 and all apps were reset/recycled, but now suddenly a few different apps have this same issue.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

butcherking13 - Thursday, October 26, 2017 9:01 PM
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
I don't have much experience with apps, tried to solve this and looking for an answer. I have seen some previous discussions on here, but they all seem to have a specific error other that the generic timeout.
Why would a simple query/sproc that takes 3 seconds to run in SSMS blow a timeout error when using an app?
I will say all stats are updated, no indexes needed, no blocking and no issues with running in ssms in 3 seconds.
We did just migrate to sqlserver 2016 from 2012 and all apps were reset/recycled, but now suddenly a few different apps have this same issue.

Have you determined that the execution plan is being cached and reused or is it recompiling every time it's used? Do your connection strings enable "MARS" (Multiple Active Result Sets)?

We had similar problems. The proc would run in 100ms. What took us a while to figure out was that it would do a recompile everytime it was called (thousands of times per hour) and it was taking anywhere from 2 to 22 seconds to recompile.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

butcherking13 - Thursday, October 26, 2017 9:01 PM
Why would a simple query/sproc that takes 3 seconds to run in SSMS blow a timeout error when using an app?
.

Most commonly because SSMS and .net use different SET options (specifically Arithabort) and hence the same query can have two different plans, one used when run from .net, one used when run from SSMS.
Check what the plan in cache that .net is using, see how it differs from the one that you get from SSMS.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SqlServerDBA318 SSCrazy Points: 2353 More actions · Answer 3

SqlServerDBA318

SSCrazy

Points: 2353

October 27, 2017 at 11:14 pm

#1965650

Thank you both! I will look into your suggestions.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

Have you looked at the performance of the Citrix or Web server the client actually runs on, also is it formatting or sorting any of the data on the client side?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Microsoft recommends you always use ArithAbort ON: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql#remarks

If ArithAbort setting is different between Management Studio (defaults to ON) and your application (may be defaulted to OFF), there is a setting at the instance level you can change to default so that ArithAbort is ON.
Right click on the instance in Object Explorer, and select Properties. On the Connections page, in Default Connection Options part is a setting "arithmetic abort". Make sure this is checked: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page