Query Exceeding Query Timeout on Linked Server and Working

Question

Currently we have a SQL Server Agent job that executes a stored procedure that reads data over a linked server.

Initially on our test server this was timing out at 10m:17s (617 seconds). We changed the Query Timeout in Linked Server properties->Server Options to 3600 (1 hour) and the SQL Server agent job now works fine with the Agent job taking about 30 minutes.

When this was made live on Production, the Agent job continued to succeed however with the difference in data quantity the job now takes 1h:15m. This is beyond the timeout setting. My theory is that the stored procedure is 'finished' with the linked server within 3600 seconds and the extra time is spent processing locally.

My concern is why is this working? Or more to the point preventing it from ceasing to work if the data changes much.

I have researched a number of topics on this, but I haven't encountered any similar posts.

Answer 1

Query Timeout setting affects only when the query is kept in waiting state for resources. This option doesn't affect the maximum duration a query can run and as per my understanding without custom logic, there is no setting which times out a query if it exceeds specified value.

MSDN also states the same. If the value is ZERO, SQL Server takes the value from Global query wait Option setting which can be configured through Sp_configure

Below is an excerpt from MSDN:

In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait