Remote Query Timeout Setting

What this check looks for

The remote query timeout (s) setting in sys.configurations, reported when it is below the default of 600 seconds. The check is skipped on Amazon RDS.

Why it matters

This setting controls how long SQL Server waits for a remote server to answer before giving up. Set it too low and legitimate distributed queries fail partway through.

The default is 600 seconds, which is ten minutes. A value below that means:

• Linked server queries against a large result set get cancelled, and a query that used to work stops working as the data grows.
• Queries over a slow or congested network fail, intermittently, depending on the link.
• Long running remote operations, such as a distributed report or an ETL pull, get truncated.
• The error does not clearly say what happened. You get a timeout or a provider error, and since the query is correct and works when run on the remote server directly, the investigation goes to the network, to the remote instance, and to the query itself before it reaches the configuration.

Partial results are the worst outcome. A distributed query that is cancelled mid stream can leave a process having consumed some of the data and not the rest, and depending on how the application handles the error, that may not be noticed. A scheduled data load that silently imports 80 percent of the rows is harder to detect than one that fails outright.

What this setting does not do is worth being clear about, because it is frequently confused with three other things:

The single most common confusion: somebody reduces remote query timeout believing it will stop long running local queries. It does nothing of the kind. Local query duration is controlled by the client’s command timeout, and on the server side there is no equivalent setting at all.

Zero means no timeout, which is infinite waiting. That is its own risk: a remote server that has stopped responding but not disconnected can leave a session waiting indefinitely, holding locks and a worker thread.

And the per linked server setting overrides this one, which is where the real control belongs. sp_serveroption sets a query timeout on an individual linked server, so a slow reporting link can have a long timeout without every other distributed query inheriting it.

How to confirm it yourself

The setting:

SELECT [name], [value], [value_in_use], [minimum], [maximum], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('remote query timeout (s)', 'remote login timeout (s)',
                  'query wait (s)', 'remote access', 'remote proc trans');

600 is the default for remote query timeout (s). 0 means wait forever.

The per linked server overrides, which take precedence:

SELECT s.[name]            AS [linked_server],
       s.[product],
       s.[provider],
       s.[data_source],
       s.[query_timeout],
       s.[connect_timeout],
       s.[is_remote_login_enabled],
       s.[is_data_access_enabled],
       s.[is_rpc_out_enabled]
  FROM sys.servers AS s WITH (NOLOCK)
 WHERE s.[is_linked] = 1
 ORDER BY s.[name];

query_timeout of 0 on a linked server means “use the instance setting”. Any other value overrides it for that server only.

Whether anything is actually timing out, which tells you if this is theoretical:

EXEC xp_readerrorlog 0, 1, N'timeout';
EXEC xp_readerrorlog 0, 1, N'Query timeout expired';

And from job history, since scheduled distributed work is where this usually bites:

SELECT TOP (50)
       j.[name] AS [job_name],
       msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) AS [ran],
       h.[run_status],
       h.[message]
  FROM msdb.dbo.sysjobhistory AS h
 INNER JOIN msdb.dbo.sysjobs  AS j ON j.[job_id] = h.[job_id]
 WHERE h.[run_status] = 0
   AND (h.[message] LIKE '%timeout%' OR h.[message] LIKE '%linked server%')
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

What distributed queries are running right now, and how long they have been waiting:

SELECT r.[session_id],
       r.[wait_type],
       r.[wait_time] / 1000 AS [wait_seconds],
       r.[total_elapsed_time] / 1000 AS [elapsed_seconds],
       DB_NAME(r.[database_id]) AS [database_name],
       t.
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.[wait_type] LIKE 'OLEDB%'
    OR t. LIKE '%OPENQUERY%'
    OR t. LIKE '%OPENROWSET%';

OLEDB waits are time spent waiting on a remote provider, which is exactly what this timeout governs.

How to fix it

Put the instance setting back to 600 and control individual links per server.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote query timeout (s)', 600;
RECONFIGURE;

Immediate, no restart, and it affects new queries from that point.

Then set the timeout where it belongs, on the linked server itself:

-- a slow reporting link that legitimately needs longer
EXEC sp_serveroption @server = N'ReportingServer',
                     @optname = N'query timeout',
                     @optvalue = N'1800';

-- a link that should fail fast if the far end is unresponsive
EXEC sp_serveroption @server = N'FlakyVendorServer',
                     @optname = N'query timeout',
                     @optvalue = N'120';

-- and the connection timeout, separately
EXEC sp_serveroption @server = N'FlakyVendorServer',
                     @optname = N'connect timeout',
                     @optvalue = N'15';

That is the right structure: a sensible instance default, and a deliberate value per link based on what that link actually does.

Avoid 0 on the instance setting. Infinite waiting means a session can hang indefinitely on an unresponsive remote server, holding its locks and its worker thread, and those accumulate.

Then look at why the remote query is slow, because raising a timeout to accommodate a bad query is treating the symptom:

• Check that filtering happens remotely. A four part name query such as SELECT * FROM [Remote].[Db].[dbo].[BigTable] WHERE Col = 1 may pull the whole table across and filter locally. OPENQUERY forces the predicate to run on the remote server:

SELECT * FROM OPENQUERY([RemoteServer],
       'SELECT col1, col2 FROM dbo.BigTable WHERE col3 = 1');

That single change routinely turns a ten minute distributed query into a two second one.

• Select only the columns you need, since every byte crosses the network.
• Check the remote server has appropriate indexes for the predicate you are pushing to it.
• Consider whether a distributed query is the right design at all. For anything large and repeated, replication, a scheduled extract or an availability group readable secondary is usually better than pulling across a link on demand.

And set the client command timeout deliberately too. That is the setting most often blamed on this one, and it lives in the application’s connection code rather than in SQL Server.

How long it takes

About half an hour to correct the setting and review the linked server options.

Related reports

Related checks

Frequently asked questions

Does this control how long a local query can run? No. It only applies to waiting on a remote server. Local query duration is governed by the client’s command timeout, which is set in the application.

What does 0 mean? Wait indefinitely. That risks a session hanging forever on an unresponsive remote server while holding locks and a worker thread, so it is not a good instance default.

Should I set it per linked server instead? Yes. Leave the instance setting at 600 and use sp_serveroption to give each link a value that suits what it does.

My linked server query is slow, so should I raise the timeout? Check first whether the predicate is being evaluated remotely. A four part name query can pull an entire table across the network; OPENQUERY pushes the filter to the remote server and usually removes the need for a longer timeout entirely.