question

tomgough79 avatar image
tomgough79 asked

Is it a good idea to use the "remote" join hint?

Generally speaking, I try to avoid using join hints if at all possible in order not to lock the optimiser into a bad query plan. Unfortunately, in this case, the performance of my query is completely unacceptable and so I've added the "remote" join hint into an inner join between a local and remote table. This has resolved the issue but I'm concerned that whilst this may be the correct approach as the system stands currently, that may not always be the case. Is there any reason to think this may come back to haunt me? Thanks
joinsjoin hints
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
tanglesoft avatar image
tanglesoft answered
The 'remote' join hint is one definitely one of the hints whose performance seems to vary considerably overtime and across versions of SQL Server, and has been known to do the opposite and make queries slower. As a general practice I use the DMV tables to track the worse performing queries and then look to review/resolve why they are there. If this query comes to the top of the list address it then but in the mean time I don't see a quicker alternative to resolving your issue without changing the architecture. Probably also worth tracking the number of rows on each side of the join and understanding how this will change over time.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tomgough79 avatar image tomgough79 commented ·
Sadly, that rather confirms what I thought...oh well! Hopefully, we'll eventually be able to not to have to run queries between servers
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.