SQL Server Table Synonyms with Indexes

Question

I have multiple databases on a single instance of SQL Server 2005. I've created a synonym on one database to access a table on another database and when writing my queries, I'd like to utilize a specific index, however, when evaluating the execution plan, it doesn't appear to use it. If I write the query to access the database explicitly, it works, but I can't seem to get it to work using a synonym. For example:

select *
from testdb..testtable with (index(testindex))

|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[testtable].[id]))
     |--Index Scan(OBJECT:([testdb].[dbo].[testtable].[testindex]))
     |--Clustered Index Seek(OBJECT:([testdb].[dbo].[testtable].[PK_testtable]), SEEK:([testdb].[dbo].[testtable].[id]=[testdb].[dbo].[testtable].[id]) LOOKUP ORDERED FORWARD)

does not yield the same execution plan as

select *
from testdb_synonym with (index(testindex))

|--Clustered Index Scan(OBJECT:([testdb].[dbo].[testtable].[PK_testtable]))

Is this a limitation with Synonyms or is there something specific I need to do to get this to work?

Answer 1

I tested the same thing and it seems that the query optimizer ignores that hint when done via a synonym. The details are I did a select * against an arbitrary table with an index hint to use a non-clustered index. Without the synonym, it does a bookmark lookup/nested loop join. With it, it does a table scan. Since there are no options on the create synonym syntax, I can only assume that the index hint is ignored. No details in BOL as to why. I would chalk it up as a "feature".

Answer 2

Do you need the hint in your case? MS recommendations is to avoid index hints if it is possible due to the fact that may invalidate a more optimized plan. Even if it is optimized today it may be inefficiens tomorrow due to data load etc.

I tried to use a synonym without the hint in SQL server 2008 and got the same execution plan with the synonym as with the fully qualified name (database.schema.table).

I even tried to use the synonym with an index hint and successfully forced a non clustered index seek (and a key lookup to get the rest of the data), and i get the same execution plan with fully qualified name.

Are your statisitics updated? Do you have a selective index or does SQL server think it is more efficient to use a table scan.

Answer 3

WITH INDEX hints seems to be ignored for synonyms.

CREATE SYNONYM syn_master FOR master

SELECT  *
FROM    syn_master WITH (INDEX (wow_i_can_write_everything_here))

compiles and runs allright despite the fact I don't have an index named wow_i_can_write_everything_here in my schema.

Answer 4

This is a bug that Microsoft have fixed: see MS KB 963684

In Microsoft SQL Server 2005, you create a synonym for a table. You run a query against the synonym. The query uses the INDEX optimizer hint to force an index. If you examine the execution plan that is generated for the query, you may find the execution plan does not use the forced index.