Linked Server Performance Problem and Collation

August 4, 2008 by Kushel
Filed under Databases, Latest

When using linked server’s within SQL Server (in our case, 2005), a very important topic to consider is Collation.

I have a SQL Server 2005 database linked to a Sybase ASA database (remote) via an SQL Server Linked Server definition under Server Objects.

When setting this up, I set remote collation to true, but I didn’t specify the collation name (incidentally the collation was the same for both databases). I then started to have some major performance issues when using SQL Server to retrieve data from Sybase.

It turns out that from my understanding, by not setting the collation name, the linked server operation was pulling back a larger set of rows (which could be whole tables), storing them in temporary tables BEFORE sorting them.

By specifically naming the collation, the linked server operation could interrogate the remote table information directly!