Query still running in SQL Server Database even though CommandTimeout was set to terminate query after a set time in entity framework

Question

Hello,
In asp.net web api application we are using entity framework and we have set command timeout as below
var ctx = new DbContext();
ctx.Database.CommandTimeout = 180;

Web api returns an timeout error after 180 seconds but when we check for a long running queries in sql server, identified the query is still running in sql server and it is not cancelled after exceeding the command time out.

the below query returns the query which took around 250 + seconds to execute

declare @duration int = 180000000

select [max_duration] / 1000000 MaxDurationInSeconds,
qsp.plan_id,
qsq.query_id,
qsqt.query_sql_text,
qsrs.*
from sys.query_store_runtime_stats qsrs
inner join sys.query_store_plan qsp on qsrs.plan_id = qsp.plan_id
inner join sys.query_store_query qsq on qsp.query_id = qsq.query_id
inner join sys.query_store_query_text qsqt on qsq.query_text_id = qsqt.query_text_id
where max_duration >= @duration
order by max_duration desc

Please suggest a solution so that after command timeout sql server cancels the query execution.
Regards
Anil

Answer 1

Hi @Mangalore, Anil ,
What's your version of the Entity Framework? Is it Entity Framework 6? Do you write the Default Command Timeout in the connection string?It conflicts with CommandTimeout.
More details you could refer to below article:
https://stackoverflow.com/questions/6232633/entity-framework-timeouts

Best regards,
Yijing Sun

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

That is expected behavior. Sqlserver can only detect query cancellation at certain points in the query processing (typically when returning results). After detecting the cancellation it must do a rollback.

the sql server kill command has been improved so that it can kill a query in progress (but still requires a rollback). you might want to catch the timeout and kill the process.