SQL: Linked Servers: It’s a matter of being compatible

The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.

Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables soon.

With Linked Servers though, I often hear people describing performance problems and yet there’s a configuration setting that commonly causes this. In Object Explorer below, you can see I have a Linked Server called PARTNER.

Let’s right-click this server, and choose Properties, then go to the Server Options page:

The critical setting that causes lots of Linked Server performance issues is this one:

When you are connecting from server to server, and running queries that use data from both servers, this tells SQL Server if it can trust the collation at the other end. If so, it can often push predicates, etc. across to the other server to be resolved there. If not, the local server often has to pull all the data (often enormous amounts of it) across first, before applying the predicates, joins, etc.

This can have a major performance impact on queries that involve objects on the linked servers.

2017-11-02