July 18, 2017 at 8:12 am
A vendor told my coworker recently that there was getting to be too many linked servers and that they could start to become a problem. I know that they could be a problem if you're trying to make too many hops from one to another while querying multiple linked servers, but is there any other performance issues one needs to be concerned about if there are too many linked servers? How would you handle such a situation if there were multiple vended applications, each with their own database that had data you needed to query? Would it be best to consolidate them onto fewer instances based on how often the data is shared? For example, if you find yourself querying database A, B, and C together more often, would it be more beneficial to put them all on the same instance to cut down on the linked server queries? I also believe it may be best to architect a warehouse solution to bring all the disparate data together, but if the data is not even close to being related, is it even worth trying to do that?
It's my thought that linked servers are not inherently bad and that they are quite useful if your data is quite spread out. But I don't know how to consolidate them to make things more efficient? I would love to learn more about this. Any comments or suggestions are most welcome.
Thanks!
July 18, 2017 at 10:33 am
I think the problem with linked servers isn't in how many you have setup, but the performance each time you use one. I've seen systems where the performance limitation was data gotten over a linked server.
Here are some good articles about the topic. The main focus is on ensuring you're querying on the remote server instead of writing a local query with a 4 part table name, such as using OpenQuery, executing a stored proc or ILTV Function on the remote server to get the remote data, or even copy data you need down to the local server first before your query:
https://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/
https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/06/30/linked-server-performance-heterogeneous-databases/
https://technet.microsoft.com/en-us/library/ms188313(v=sql.105).aspx
July 18, 2017 at 11:55 am
Thank you, Chris. I was not aware of the OpenQuery function. I'll have to take a closer look to see if I can get it incorporated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply