There are three different databases on a three separate linked servers
SQL1 (hosting 2 dbs),
SQL2 (hosting 1 db).
SQL0 acts as a reporting server and will host all the databases in question (method TBD - maybe replication maybe sth else), but as the data will be old for some audit purposes (<24H) it will still be necessary to run some queries on live data.
Localhost\SQL0 - linked to
SQL2 (each using their respective
sa credentials to connect)
SQL1 linked to
SQL2 (again, using
Query in question joins (for now) 8 tables across those three databases. In the future there will be another server, another database and several more joined tables in the query, ran regularly by agent and results emailed. It usually returns less than 100 rows, but is limited using
TOP 10 while in development.
Why this query ran on
SQL2 takes 3s or less to execute, but 105s when ran on
SQL0? As long as it's just network traffic I'm willing to disregard the 30x longer execution, but if it's impacting linked servers performance I need to find the solution stat.
All servers are on same LAN, but
SQL2 is in another building connected via VPN.
Everything is in SSMS, on a machine connected to same network as all others, no views, no linked servers to linked servers or other "complications"; I have access to all servers as sa.