1

I have two databases D1 and D2. I have created a synonym T1 in D2 which refer to a table T1 from the D1 database.

Now if I query using the T1 synonym in the D2 table, will it use the index of the T1 table from the D1 database?

Currently, I am in a situation where my tempdb is growing, and I found that it is because of the query on the synonym, I can't view the query plan from the D2 database as SHOWPLAN permission is denied in the D1 database, however, if I run the same query on the D1 database, it goes well, and use a proper index.

Or is there a way to create a synonym with indexes?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sujeet Chaurasia
  • 97
  • 8
  • Good question: I just assumed the index would go with the object. Perhaps this will help https://stackoverflow.com/questions/43574638/do-indexes-need-a-synonym-to-be-used – John Cappelletti Feb 02 '23 at 05:49

1 Answers1

0

There should be no difference in index usage between using the synonym versus the table directly. However, the database context can make a difference in the execution plan and affect performance as well as tempdb usage of the same query.

Review the database compatibility level and database-scoped configuration settings for differences between the two databases.

Also consider the plans could be different due to other factors, such as parameter sniffing.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71