0

I've got two database servers ServerX and ServerY, each with a linked server object to the other. I want to move a database from one to the other to increase the performance of some queries across the linked servers. Some views in the to-be-moved database use the linked server.

How bad would it be to create a linked server on ServerX called [ServerX] and have it basically loop back to itself? I know it would work but can anyone foresee a performance improvement? I'm certain that our network is not saturated to the point that it's degrading performance of the queries - it's the use of the linked servers.

Thanks in advance!

EDIT: Correct, I'm not passing the query over as a string to be executed on the remote server. An example query that I'd run would be:

SELECT * FROM [LINKED_SERVER].[Database].dbo.[Table]
hughball
  • 50
  • 7
  • Sounds like you are using 4-part names across the linked servers instead of writing pass-through queries. To confirm this (or not), please provide a short example query. – Pieter Geerkens Mar 12 '15 at 22:46
  • If the network isn't your bottleneck, then reduced latency will be your only improvement when linking a server to itself. I'd need to know more about your query(ies) to give specifics but one option would be to replicate your database to your secondary server. – jtimperley Mar 12 '15 at 22:50
  • @PieterGeerkens You're right, we're not, though we're not trying to perform a lot of logic, just simple selects, then logic in memory. Example added. – hughball Mar 12 '15 at 23:13
  • @jtimperley I didn't consider replication, though if I were to go that route, I think I'd rather just move the database and update all references so my apps know it's in the new location :D – hughball Mar 12 '15 at 23:14
  • @highball: But that means *you are driving an expensive RDBMS like a cheap disk drive*. Any non-SARG will not only mean a table scan, but a table-scan on the remote server after copying the entire table over the network; possibly repeatedly. I doubt very much that your true bottleneck is the network latency; but rather that it is the consequence of your *driving habits.* – Pieter Geerkens Mar 12 '15 at 23:17
  • @highball: ... Most of the time simple JOIN and WHERE predicates will be optimized at the correct server, but any complex predicates or queries probably will not be, because the remote server has insufficient statistics. Just like good code, good distributed database querying is meant to be highly cohesive and loosely coupled, so that pass-through queries will work. – Pieter Geerkens Mar 12 '15 at 23:20
  • @PieterGeerkens Certainly - if it's a remote query, there can be little to no query optimization. I tested this theory out by actually copying the db to the other server and running my code against it and it gave similar performance. – hughball Mar 13 '15 at 17:02

0 Answers0