In linked server queries when you use filtered query (Where clause) filtering may not be remoted, entire data is fetched locally from remote server and filtering happens locally hence will cause severe performance impact. You will find “Remote Scan” operator in plan in this case. When filtering is remote you will see “Remote query” operator instead of remote scan.
How to fix it?
DynamicParameters and NestedQueries in the provider you are using should play a role here (Object exploreràServer objects à Linked servers àProviders à Choose the provider you are using in linked server (Ex: SQLOLEDB) and enable DynamicParameters and NestedQueries ). DynamicParameters setting should enable providers to support parameterized queries. If you enable it probably “remote scan” would switch to “remote query” and filtering will be happen on remote server instead of fetching the data locally and filtering.
DynamicParameters and NestedQueries in the provider you are using should play a role here (Object exploreràServer objects à Linked servers àProviders à Choose the provider you are using in linked server (Ex: SQLOLEDB) and enable DynamicParameters and NestedQueries ). DynamicParameters setting should enable providers to support parameterized queries. If you enable it probably “remote scan” would switch to “remote query” and filtering will be happen on remote server instead of fetching the data locally and filtering.
SQLServerScribbles.COM 