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

Improve this question
1
  • Never perform cross server joins. I have seen SQL server decide to copy an entire table locally in order to perform the join. Huge performance problem that can also add stress to a busy I/O system. Grab only the data you need, store locally, process further. Iterate through your various links to get more data.
    – datagod
    Oct 27, 2016 at 15:00

2 Answers 2

Reset to default
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.

Improve this answer
1
  • This is, actually, the final solution I'm aiming at. But it has been discarded for the time being. We have implemented Database Mirroring in HP mode, so Transactional replication - which we already tried - will reduce performance. Looking at other solutions (that's why I'm mentioning reporting server and will host all the databases in question (method TBD - maybe replication maybe sth else) in the question... For the time being linked servers are the only solution.
    – AcePL
    Oct 27, 2016 at 13:43
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.

Improve this answer
4
  • Is SQL Server's behavior when working on data from linked servers so unpredictable that literally same query (down to spaces), can and will be executed differently by different server\instances with the execution time varied by in excess of 3000%?? I'm not questioning your statement, just reality... SO please don't take it personally... :)
    – AcePL
    Oct 28, 2016 at 9:00
  • Apologies I must have misunderstood your question. So SQL1 is serving the data and SQL2 and SQL0 are running the same query against it? With SQL2 being through VPN, this could have some impact but also network settings such as Packet Size. I do not however understand why these execution plans are different? The bottom one SQL0 is doing expensive lazy spool which would be slowing down your query.
    – Marcin Gminski
    Oct 28, 2016 at 9:29
  • @AcePL: Yes, SQL Server's behavior with linked servers is upredictable, You can never be 100% what get's computed remotely and what gets transferred and computed locally. Some links that might help You a bit debugging what's the problem in Your case: thomaslarock.com/2013/05/… , dba.stackexchange.com/questions/10873/… , dba.stackexchange.com/questions/43754/…
    – Marcin S.
    Oct 28, 2016 at 9:53
  • @MarcinGminski - SQL1 and SQL2 both serve the data (they host different dbs, both being queried against). SQL0 links to both SQL1 and SQL2 to execute the query while scenario 2 links to just one server (but still majority of the joins with the tables on SQL1). So One could say that SQL0 query links to 2 different servers, while SQL2 links to just one, but this being that big of a difference??
    – AcePL
    Oct 28, 2016 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.