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

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.

Answer 2

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".