1

I have an agent job that runs a very simple "select max(date) from table" against a linked sql server. The linked server is part of an AG and the link from remote uses a listener to get to primary. Most occasions, the job runs without issue but, with increased frequency, it times out at the 10min mark (default remote connection timeout). If it fails, I can run the underlying sql directly on the linked server and it returns within 5 seconds. Here is a screen grab of my activity monitor showing what the process looks like on the linked server when executing from remote and hanging; PAGEIOLATCH_SH and CXPACKET. While this is occurring, running the "select" locally produces results as expected. I cannot find anything, for the life of me, that gives a viable solution or a reason as to why this fails to begin with. Any help would be greatly appreciated.

So far, I have looked in logs but did not find anything. Increasing the default remote cnxn timeout to 20min decreased the fail frequency but has not removed it entirely. There are not other significant loads against the table, database, or server as can be proven when running locally produces sub-minute results as expected.

The last execution was successful, albeit longer than anticipated... Adding the Results, Stats, etc. images from Server1 (execution source).

ActivityMonitor on Linked Server Live Query Stats Execution Plan Client Stats Results

Improve this question
3
  • 2
    Guessing that the query plan went parallel for whatever reason and that plan is slow. Try use Query Store or the plan cache to find the query plan it used (not the one you got in SSMS which might be different).
    – Charlieface
    Commented Feb 15 at 22:13
  • 1
    The query from the "remote" side can't be parallel in the case of a parallel query. However, the remoted query can go parallel. on the remote server. Please review the plan there and see if it is parallel - please post the plan here for more if you have that situation and need help.
    – Conor Cunningham MSFT
    Commented Feb 16 at 2:33
  • It is hanging right now but Activity Monitor (linked server) fails to return a plan.
    – M Bradley
    Commented Feb 16 at 18:03

1 Answer 1

Reset to default
0

I have had trouble some times with queries across Linked Servers not using the index. Maybe if you executed the SQL locally it would do a better job of that?

SELECT MAX(date) FROM LINKED_SERVER.DatabaseName.dbo.Table

vs

EXEC ('SELECT MAX(date) FROM DatabaseName.dbo.Table') AT LINKED_SERVER

In order to do this, your linked server must have the property RPC Out set to true.

Improve this answer
1
  • The above solution timed out just the same
    – M Bradley
    Commented Feb 16 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.