1

First of all I would like to point out that this is, in my eyes, not a duplicate question, because the timeout as described in the title happens 8/10 times. So not always.

We have a .NET application with Dapper, but all of a sudden the application stops the request after 30 seconds. We have had this issue before and fixed it by updating all limit options in IIS and adding executionTimeout="00:30:00" to the web.conf file of IIS. (In the aspNet section).

The above fixed the issue for one of our applications, including this one. But all of a sudden this problem seems to be back.

The strange thing is that it doesn't always happen. Sometimes the application continues with the request for 40, 50 or more seconds. But 8/10 times it stops after exactly 30 seconds.

We also have a executionTimeout set in the code where we call the query. This is set on 1800 seconds.

In our eyes this must be a IIS issue or perhaps even a database issue (MSSQL)? But we are unsure what to do next, because it doesn't always happen. If it was a IIS problem, wouldn't it always happen even if we test it a thousand times?

If someone could help us with this problem we would really appreciate it.

Thanks in advance!

EDIT code snippets:

C#:

var result = await _connection.QueryAsync<Sample>(sql, commandTimeout: 1800);

web.config:

<aspNetCore ... executionTimeout="00:30:00" ... />
KittyCat
  • 415
  • 4
  • 9
  • 26
  • There are too many variables here to be certain. I would approach this by attempting to make the process faster. Your customers will appreciate it and you won't have to worry about timeouts. That said... if you run the query directly in SQL Server Management Studio, how long does it take to execute? – George Mastros Apr 24 '20 at 18:24
  • 1
    `executionTimeout` controls the timeout of the [HTTP Runtime in IIS](https://learn.microsoft.com/en-us/iis/wmi-provider/httpruntimesection-class). You're probably looking for the [SqlCommand's CommandTimeout property](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout), which defaults to 30 seconds. – AlwaysLearning Apr 24 '20 at 22:11
  • @GeorgeMastros unfortunately the query is REALLY complex and the database is HUGE. So this results in slow times, but the customer is OK with that. The query takes a while to execute directly in management studio (only on the production database). The test database takes less than one second. (less data). – KittyCat Apr 27 '20 at 11:01
  • @AlwaysLearning We use Dapper and there it is called executionTimeout. It is set on 1800 seconds in code where the query is running. – KittyCat Apr 27 '20 at 11:02
  • What happens if you specify the timeout on all three, the [command](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-3.1#System_Data_SqlClient_SqlCommand_CommandTimeout), the [connection](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectiontimeout?view=dotnet-plat-ext-3.1) and the [context](https://learn.microsoft.com/en-us/dotnet/api/system.data.linq.datacontext.commandtimeout?view=netframework-4.8#System_Data_Linq_DataContext_CommandTimeout)? – GSerg Apr 27 '20 at 11:08
  • Do you have log files that tell you where you were in your code when the timeout occurred? Any code samples? – Christopher Painter Apr 27 '20 at 11:12
  • interesting one, if you've checked all the above timeouts, (command etc), what about web limits? https://learn.microsoft.com/en-us/iis/configuration/system.applicationHost/webLimits other things to look at, check the iis logs, windows event viewer (application section), look at app pool recycling - a recycle can be triggered by too much memory being used -> https://learn.microsoft.com/en-us/sharepoint/technical-reference/application-pools-recycle-when-memory-limits-are-exceeded – Joseph Hopkins Apr 27 '20 at 22:49
  • @KittyCat, I know nothing about Dapper but an internet search suggests one should set `commandTimeout`, not `executionTimeout`, to control SQL command execution behavior. Add the relevant code to your question. – Dan Guzman Apr 28 '20 at 12:58
  • @KittyCat - You should try to increase the command timeout while calling the execute method of the dapper.net. Have a look https://dapper-tutorial.net/knowledge-base/8794858/adjusting-commandtimeout-in-dapper-net- – Mukesh Arora Apr 29 '20 at 04:27
  • What exactly do you mean with context? The command timeout and connection timeout are both set though. @GSerg – KittyCat Apr 29 '20 at 10:32
  • @ChristopherPainter No we have no log files because it only happens in production and that environment is strictly private unfortunately (for us). – KittyCat Apr 29 '20 at 10:34
  • @JosephHopkins we did set all web limits in IIS. Are there any hidden limits? – KittyCat Apr 29 '20 at 10:34
  • @DanGuzman We already do that. I probably explained it wrongly! – KittyCat Apr 29 '20 at 10:34
  • @MukeshArora We already do this. Read the comments for more information. – KittyCat Apr 29 '20 at 10:35
  • @KittyCat `What exactly do you mean with context?` - the data access context, if you are using any, such as an EF context or a Linq2Sql context. – GSerg Apr 29 '20 at 10:37
  • @GSerg We do not use any of these. – KittyCat Apr 29 '20 at 10:40
  • @KittyCat re: hidden limits, not that I know of, if you recycle your app pool on purpose during the query do you get the same error? – Joseph Hopkins Apr 29 '20 at 11:19

2 Answers2

0

You should try to increase the CommandTimeout while calling the Execute method of the dapper.net.

Refer to these URLs for more details

dapper-tutorial.net/knowledge-base/8794858/

adjusting-commandtimeout-in-dapper-net

Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19
0

"requestTimeout" throw an error when there is no possibility to begin the query due to a lock over one resource that is uncompatible with the lock you need to put to begin the exexcution of the query.

So if the 30 seconds have been passed, the query is running, but the execution time can be greater that 30 seconds...

user7370003
  • 47
  • 3
  • One answer wich would be much appreciated is to give us the ISOLATION LEVEL used for the queries, and espcially if you have activate the SNAPSHOT isolation level that will avoid most of the "requestTimout". – user7370003 Apr 29 '20 at 10:04
  • This is correct. We see the query running in the activity monitor of MSSQL. But the request still stops after 30 seconds 8/10 times. – KittyCat Apr 29 '20 at 10:37
  • The activity monitor is not quite accurate to show all the problems. Can you execute this query ? – user7370003 Apr 29 '20 at 11:53
  • SELECT session_id, blocking_session_id, q.text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS q WHERE session_id > 50 AND database_id > 5 AND session_id <> @@SPID AND blocking_session_id > 0; – user7370003 Apr 29 '20 at 11:53
  • this returns nothing unfortunately. – KittyCat Apr 29 '20 at 12:07