Linked server performance might be affected because of Remote Scan

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s