Query Optimization with Linked Server

Varinder Sandhu SQL, SQL-Linked Server, Varinder Sandhu

Hello Friends,

I have faced a problem in SQL queries with Linked server; I just want to share with you all.

Let me explain with example as below

Say, we have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.

Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

Now the problem is the some triggers and stored procedures which need to insert the values from SQL 2008 to SQL 2000 databases using Linked server. Now stored procedures and triggers are taking very long time to execute in this scenario.

But the same stored procedures and triggers are working fine when all the databases are on same SQL server i.e. SQL server 2000.

So the problem comes into the picture when we introduce the Linked Server.

So the solution of this problem, we optimized our stored procedures and triggers like remove the cursors, optimize the joins etc.

Note: As per above scenario query optimization is required with linked server. Whether both SQL server 2000 and SQL 2008 on same physical machine or on different machine.

If anybody has any other experiences about this, please share as comment.