SQL: Why are your linked servers so slow?

SQL: Why are your linked servers so slow?

I’ve recently been reading a few articles and discussions around linked servers, and I’ve noticed two things:

  • People consider them easy to configure
  • They have a bad reputation for introducing performance issues

I do think they’re pretty easy to configure, but they’re also easy to configure badly.

And that leads us to the main cause of the second issue: performance.

I review the configuration of a lot of systems, and the primary issue I see with how linked servers are configured, is that the Server Option Collation Compatible has been left at the default value.

It’s the #1 cause of performance problems with linked servers. I mentioned it recently in a forum and a friend noted “Here in the US, we just leave them all configured as Latin”. But that’s not the issue at all.

If you read the documentation page , you’ll see that option described like this:

Affects Distributed Query execution against linked servers. If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option isn’t set, SQL Server always evaluates comparisons on character columns locally. This option should be set only if it’s certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

Why this is such a performance issue

What this option is saying is Can I trust the collation on the remote server?. This seemingly simple question hides a bunch of potential pain.

For example, imagine I have a simple query like:

SELECT CustomerID, SalesTransactionID, SalesDate
FROM SomeServer.dbo.SalesTransactions
WHERE CustomerID = 'FRED';

The issue is where the WHERE clause is implemented. If I trust the collation on that remote server, I can trust it to just find the transactions for CustomerID FRED and return them to me.

But if I don’t know that I can trust the collation on the remote server, I need to retrieve the entire SalesTransactions table back from the remote server and do the filtering of the transactions locally.

The performance hit on doing this could be enormous.

Now imagine the impact that can have on joins, complex queries, etc. I can be a real mess performance-wise, if you need to bring everything back locally just to implement filtering that you can trust.

Default value

The insidious part of this, is that the default value for the linked server is to have this set to False. It doesn’t matter whether your collation is the same as the remote one; the issue is whether SQL Server knows if it can trust it or not.

And of course this only affects filtering for string values. It already knows it can trust integers, etc.

In so many systems that I see, the character set and collation details are identical on the local and remote servers, yet the code is having to do the work the hard way.

It’s little wonder why linked servers get such a bad reputation for performance issues.

Learning SQL Server Administration

It’s worth your while becoming proficient in SQL Server administration. We’ve recently released an online, on-demand and low-cost course that’s been very popular. You can find it here: SQL Server Administration for Developers and DBAs

2025-05-18