ODBC Linked Server – Improving Performance in SQL Server

linked-serverodbcsql-server-2008-r2

The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST.

Now the developers require this linked server object (named ServerST) to be reconfigured to point at ServerAR (even though confusingly the object would still be named ServerST). This allows them to save time rewriting a lot of code, time they do not have.

Now I can create a new linked server on ServerM of the type "Other data source" and provide a connection string that works. However a query on the original linked server that took 5s took 46s using the new linked server.

So my question is how do I provide a linked server named ServerST, that points to ServerAR and operates at the same speed.

All servers are on SQL Server 2008 R2 SP2.

The code I currently use to create the slow linked server is:

sp_addlinkedserver 'ServerST', ' ', 'MSDASQL', '', '',  
    'Driver={SQL Server};Server=ServerST;Database=theDB;Uid=link_ServerM;Pwd={P@ssw0rd};'

Best Answer

Why not just use the SQLNCLI10 provider? Are you required to use MSDASQL?

Here is a sp_addlinkedserver script adding a linked server named ServerST that points to ServerAR.

EXEC master.dbo.sp_addlinkedserver @server = N'ServerST', @srvproduct=N'ServerST', 
    @provider=N'SQLNCLI10', @datasrc=N'ServerAR'

Update 19/082014

Download SQL Query Stress Tool to compare performance of both SQL Servers, ServerST and ServerAR.

enter image description here

Scripting out your existing Linked Server

This will also include any specially defined parameters such as collation and time out configurations.

enter image description here

Related Question