I have two SQL Server instances and I do a lot of remote querying of a database on one server from another server. Like this query on server1.database1:
select T1.id from server1.database1.dbo.table1 T1 inner join server2.datbase2.dbo.table2 T2 on T1.id = T2.id
I've inherited this code from someone else and was wondering if there's a better (faster) way of doing this? I mean, is there a way I could create an exact replica copy of server2.databse2.dbo.table2 on server1.database1.dbo that updates itself and keeps itself current in real-time?
Microsoft SQL Server Standard Edition (64-bit) Version 10.0.4000.0
EDIT: Actually, what I do now in this scenario is, if I can, I use open query and with(nolock) to grab the smallest dataset I need, and I put that in a temp table. And I set up the "id" column to be a unique clustered index, so that it can join quickly on whatever I'm joining on server 1.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX table2_idx_id ON dbo.table2 (id);
SELECT T1.id FROM server1.database1.dbo.table1 T1 INNER JOIN server2.datbase2.dbo.table2 T2 ON T1.id = T2.id