Linked servers are a simple way to make remote data sources appear to SQL Server as a native table from a query perspective. You can link to the same instance of SQL Server, other instances of SQL Server, or even non-SQL Server data sources such as Oracle, Active Directory, text files, etc.
I have been attempting to optimize queries written against linked server resources with different degrees of success. The primary factor for good performance is the size of the remote object (number of columns and rows).
Even if the remote object is a SQL Server table, trying to join the table through a linked server results in a table scan of the linked server table. If there are better indexes supporting the performance of the query they are not available through a linked server. Therefore, all activities on the linked table are performed using a table scan. If the remote table is large, this can be horrible when it comes to performance.
IMHO we use linked servers because they are convenient. They just get the job done. When we want the whole contents of a linked table it doesn’t matter regarding the size. But, when we want a small subset it can be quite painful.
Are we using linked servers in place of other tools? What do you do when you are not able to use a linked server and require remote data? Share your thoughts below, or drop an Email to btaylor@sswug.org.
Cheers,
Ben