Impact of linked servers on queries

End-of-year optimization, collect 12-hour Profiler trace files, and use RML analysis to view the pre-consumption statements:

is a production environment-specific loginname that consumes the former n (in descending order of the total CPU). Blue shading is a number of frequent calls to the process, you can see the process average CPU at more than 1000 milliseconds, the average execution time of about 1.5 seconds, pay attention to their average logical reading is very low! Look at the stored procedure Code and discover that there is a common denominator with the linked server. Database. Schema. Table name the left JOIN Association query.
See example of the current server using a linked server: the stored procedure script is as follows:

The statement is simple, in the same window to execute the body part of the procedure statement, a linked server (linked to the local), a non-linked server, view the actual execution plan:

There is a linked server with a query cost of 100%, a local direct query of 0%, to see the plan information for a large operation, the data of the entire table is returned by the remote query, and the corresponding score is returned by using the SEEK predicate with the associated field on the local query:

Actually this process average execution time 1.5s, mainly consumes in the remote query, the remote query finally manifests in the first graph the red box statement, Inside has the Tbl1003, the Col1013, the Col1014 alias, the sentence average logical reads relatively big, for executes frequently the statement to have the question obviously. Before using linked server because the database is stored on a different server, has been migrated to the same server, so you can consider removing the linked server, if you need to migrate out later, it is necessary to consider the remote query return record number, as well as network conditions. Try to use the linked server associated query as shown in the example to synchronize remote data locally and then query directly locally.
If we turn on profiler tracking, we can see that the linked server in event rpc:completed will alias the table to tbl**, the column alias is col**, we can use a query with a linked server, with the Where condition (convenient filtering tracking):

If there are many similar statements found in the trace data, it is essential to know that the linked server is generating it.

Impact of linked servers on queries