question

MangaloreAnil-8831 avatar image
0 Votes"
MangaloreAnil-8831 asked Bruce-SqlWork commented

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

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

dotnet-aspnet-generaldotnet-entity-framework
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered MangaloreAnil-8831 commented

Hi @MangaloreAnil-8831 ,
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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Yijing Sun,
We are using Entity Framework 6, Default timeout is not been set in connection string.
Timeout is been set while creating the DB Context Object. Command timeout is working in the Web API and it is returning the timeout error, but in the sql server the query is still running, need help in solving this issue

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered Bruce-SqlWork commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much for your quick response on this! That explains why the long running queries are not getting terminated in SQL Server though we have set the command timeout in EF6 while executing the LINQ query. Now I would like to know if there is a way to terminate / abandon the query originated from specific source (or application) which is being executed in SQL Server for a long time. I know there is a global query timeout setting for the entire database but how to set the execution timeout for the query originated from specific source?

0 Votes 0 ·
YijingSun-MSFT avatar image YijingSun-MSFT MangaloreAnil-8831 ·

Hi @MangaloreAnil-8831 ,
If have a possible,you could write block time using sql query.Just like this:

 BEGIN TRANSACTION
 SELECT * FROM yourTable WITH (TABLOCKX, HOLDLOCK)
 WHERE 0 = 1
 WAITFOR DELAY '00:05'
 ROLLBACK TRANSACTION

Best regards,
Yijing Sun

0 Votes 0 ·
Bruce-SqlWork avatar image Bruce-SqlWork MangaloreAnil-8831 ·

You use the transact sql kill statement. This will require determining the spid of the query, which is tied to the connection. Ef connection pooling makes this a little more difficult, as normally you can just get the spid before the query, and on timeout use a different connection to kill the spid.

You probably can find the spid, by doing a process query for the same machine and query running for over the timeout. See system views

sys.dm_exec_sessions
sys.dm_exec_requests

0 Votes 0 ·