There are three different databases on a three separate linked servers SQL0
, 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.
Two setups:
Setup 1. Localhost\SQL0
- linked to SQL1
and SQL2
(each using their respective sa
credentials to connect)
Setup 2. SQL1
linked to SQL2
(again, using sa
credentials)
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.