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.

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s