I've multiple databases on one demonstration of SQL Server 2005. I have produced a synonym on a single database to gain access to a table on another database so when writing my queries, Let me use a specific index, however, when looking for the execution plan, it does not appear for doing things. Basically write the query to gain access to the database clearly, it really works, however i can't appear to have it to operate utilizing a synonym. For instance:
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)
doesn't yield exactly the same execution plan as
select * from testdb_synonym with (index(testindex)) |--Clustered Index Scan(OBJECT:([testdb].[dbo].[testtable].[PK_testtable]))
Is a limitation with Synonyms or perhaps is there something specific I have to do in order to get this work?
This can be a bug that Microsoft have fixed: see MS KB 963684
In Microsoft SQL Server 2005, you produce a synonym for any table. You take a question from the synonym. The query uses the INDEX optimizer hint to pressure a catalog. Should you examine the execution plan that's produced for that query, you might find the execution plan does not make use of the forced index.
I examined exactly the same factor also it appears the query optimizer ignores that hint when done using a synonym. The particulars are Used to do a choose * against a random table by having an index hint to utilize a non-clustered index. With no synonym, it will a bookmark research/nested loop join. By using it, it will a table scan. Since you will find no choices on the create synonym syntax, I'm able to only think that the index hint is overlooked. No particulars in BOL why. I'd chalk up like a "feature".
WITH INDEX hints appears to become overlooked for synonyms.
CREATE SYNONYM syn_master FOR master SELECT * FROM syn_master WITH (INDEX (wow_i_can_write_everything_here))
compiles and runs allright even though I do not come with an index named
wow_i_can_write_everything_here during my schema.
Do you want the hint inside your situation? MS recommendations would be to avoid index hints if it's possible because of the truth that may invalidate a far more enhanced plan. Even when it's enhanced today it might be inefficiens tomorrow because of data load etc.
I attempted to utilize a synonym with no hint in SQL server 2008 and also got exactly the same execution plan using the synonym just like the properly accredited title (database.schema.table).
I even attempted to make use of the synonym by having an index hint and effectively forced a non clustered index seek (along with a key research to obtain the relaxation from the data), and that i obtain the same execution plan with properly accredited title.
Are the statisitics up-to-date? Have you got a selective index or does SQL server believe it is more effective to utilize a table scan.