When creating a distributed partitioned view in SQL Server 2000 or 2005, set the "lazy schema validation" option true for each linked server participating in your distributed partitioned views. This acts to optimize performance by ensuring that the query processor does not request meta data for any of the linked tables until the data is actually needed from the remote member table, reducing overhead. For example, to set this option to true, run this command for at each linked server:

sp_serveroption 'server_name', 'lazy schema validation', true

where 'server_name' is the name of the linked server.

[2000, 2005] Updated 1-14-2005

*****

When creating a distributed partitioned view in SQL Server 2000 or 2005, set the "collation compatible" option true for each linked server participating in your distributed partitioned views. This option helps to optimize performance by telling SQL Server to assume that all character sets and collation sequences in all the linked servers are compatible with the local server. This, in effect, allows SQL Server to send comparisons on character columns to the provider, instead of performing this task locally. This helps to better distribute the workload among all of the federated servers, boosting performance.

To set this option, run this command:

sp_serveroption 'server_name', 'collation compatible', true

Of course, don't make this setting if all the character sets and collation sequences for all of the federated servers are not identical. [2000, 2005] Updated 1-14-2005

*****

To help boost the speed of queries that run against federated databases, consider doing the following, if possible, in your queries:

·         Try not to use any data conversion functions.

·         Try not to use the TOP clause in the SELECT statement.