November 17, 2015 at 3:36 am
I am going through a consolidation exercise where I want to put databases from one instance into another existing instance. Currently the instances are on separate hardware.
So SERVER1\INSTANCE_A has some procs that query SERVER2\INSTANCE_A. I am going to move all databases from Server2 onto Server1 and change the linked server on SERVER1 to reference itself. I'm wondering if the optimizer is smart enough to "realize" that the linked server refers to itself and not have to wade through layers of linked server code to access the data.
So, my question is what is the performance difference of doing
Select * from linkedserver.database.schema.table
as opposed to
Select * from database.schema.table
if the databases are on the same server? We have a lot of references to the linked server in our stored procs and views and I'm wondering if I must make the code change during the move of the datbases, or if I can wait and do it in a separate code release a few weeks later.
November 17, 2015 at 5:24 am
Functionally, so long as the linked server exists, you shouldn't have too much of a problem. But remember that it takes a little bit extra CPU to process those linked servers. So the more references you have to them, the longer the query execution will take. Each little bit of "extra" added to each other little bit of "extra" adds up.
Also, in my experience, changing a linked server isn't quite so easy as opening it up and changing the server name. In my situation, I've had to delete the previous linked server and create a new one with the same name but a different connection to get it to work.
The self-referencing linked server is very useful in non-prod environments where one is testing code that will go up to production where there actually IS a linked server (non-self-referencing). I've not noticed much of a difference with my code. But then I don't have a lot of nested views / procs / functions. Just joined tables and a couple of subqueries using the linked server. So I guess the answer really depends on your setup.
November 17, 2015 at 5:19 pm
keymoo (11/17/2015)
I am going through a consolidation exercise where I want to put databases from one instance into another existing instance. Currently the instances are on separate hardware.So SERVER1\INSTANCE_A has some procs that query SERVER2\INSTANCE_A. I am going to move all databases from Server2 onto Server1 and change the linked server on SERVER1 to reference itself. I'm wondering if the optimizer is smart enough to "realize" that the linked server refers to itself and not have to wade through layers of linked server code to access the data.
So, my question is what is the performance difference of doing
Select * from linkedserver.database.schema.tableas opposed to
Select * from database.schema.tableif the databases are on the same server? We have a lot of references to the linked server in our stored procs and views and I'm wondering if I must make the code change during the move of the datbases, or if I can wait and do it in a separate code release a few weeks later.
If the two servers are up and running, you could simply do a test.
As a bit of a side bar, I strongly recommend NOT using 3 and 4 part naming anywhere except within a synonym or passthrough view. I do recommend using just 2 part naming that references a synonym. Think of it as an "alias".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2015 at 4:33 am
Jeff Moden (11/17/2015)
As a bit of a side bar, I strongly recommend NOT using 3 and 4 part naming anywhere except within a synonym or passthrough view. I do recommend using just 2 part naming that references a synonym. Think of it as an "alias".
Jeff, could you go into more detail about this, please? As in the reasons behind this recommendation?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply