SQL Server : query using synonym in remote server

Question

We just migrated our database environment from server 1 to server 2. We are in SQL Server 2014 (old and new servers).

In the previous environment, we had a DATABASE_1 with a Table_a (in which there is an clustered index) and a DATABASE_2 that contains a synonym_a referencing the DATABASE_1.dbo.table_a. The query using this synonym (SELECT with JOIN) was running fine (select top 10000 in 1s).

Now, we have one server with DATABASE_1 with the Table_a, and another server (linked server) with DATABASE_2 with synonym_a. The same query is running very slow. I can see the execution plan is different between the 2 environment. The index in table_a is not used in the new environment.

We tried to add WITH INDEX but it is not possible to specify an index hint for a remote data source. We need the synonym (because the same code is deployed automatically in different site and can't have the name of server/database in the code of our queries, stored procedure). And we can't replace the view by a stored procedure.

Does anyone have a solution for this problem?

Answer 1

when you use link server execute plan couldn't see the index of another server that you have link.So when use query by join server A send the request to another server by itself plan and ignore the index by the way try the chance by use this query

select * from (
    select * from Table_a order by (column that index in server 2)
              )a inner join (
    select * from server2.db.owner.table_b order by column)b
    on a.id=b.fkid