2

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.

Query plans (actual), SQL2 and SQL0 respectively: Query Plan on SQL2 and Query Plan on SQL0

Community
  • 1
AcePL
  • 349
  • 1
  • 4
  • 15

2 Answers2

4

The poor performance is because you are using linked server. There is a constant difference of opinion between developers and database admins. Database admins know linked server connections are slow. Developers want them because they solve problems for them. I have been both a developer and a database administrator and I remember as a developer, I pushed to get linked server added. As a database admin for the past 20 years, I have trouble shot poor performance thousands of times to find a linked server connection is the cause.

Use transactional replication to get real time data to your report server. This will eliminate the 24 hour delay and it will eliminate your need to use linked server. You will also have a performance gain on your source servers because read only report queries won't be competing with live transactions that run your company.

Duane Lawrence
  • 563
  • 2
  • 11
1

What you may be experiencing is the fact that when querying remote server in order to JOIN on local tables or apply WHERE clause the entire data set is being brought back across the network and then filtered locally.

You may be better off with OPENQUERY, in which case the WHERE clause will be executed on the remote host, and only the required subset returned.

If you are in a need for cross servers joins, you should start looking at ETL tools.

Marcin Gminski
  • 415
  • 2
  • 8