Tips for using SQL Server 2005 Distributed Queries
Try to avoid using distribution queries or minimize it using.
Because distribution transactions incur more overhead than general transactions, avoid using
distribution queries, whenever possible.
Consider setting the DynamicParameters option of the OLE DB provider to
nonzero value.
Setting the DynamicParameters option enables SQL Server 2005 to execute parameterized queries
against the OLE DB provider. Using parameterized queries against the OLE DB provider can boost
the distributed queries performance.
Note. You should set the DynamicParameters option only if the OLE DB provider supports the
ICommandWithParameters interface and supports a '?' as the parameter marker.
Set the sp_serveroption 'collation compatible' option to true, if the character
set and sort order are the same on the linked server and on the local server.
Setting the 'collation compatible' option to true can reduce overhead and boost SQL Server 2005
performance because SQL Server will send comparisons on character columns to the remote server,
instead of evaluation the comparisons locally.
Consider setting the NestedQueries option of the OLE DB provider to nonzero value.
If the NestedQueries option has nonzero value, then SQL Server 2005 can delegate the nested
queries operations to the provider. This can increase the distributed queries performance.
You can set this option in Object Explorer, simply right-click the OLE DB provider name and
select Properties.
If the provider supports the scanning an index rowset and seek into the base
table rowset using bookmarks obtained from the index rowset, consider setting
the IndexAsAccessPath OLE DB provider option.
This can increase the distributed queries performance, when the index and table rowsets are on
the same computer as the instance of SQL Server 2005.
Consider setting the DisallowAdhocAccess option of the OLE DB provider to
nonzero value.
If the DisallowAdhocAccess option has nonzero value, then SQL Server 2005 does not allow for
ad hoc access through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider.
You can set this option in Object Explorer, simply right-click the OLE DB provider name and
select Properties.
One of the first steps to optimize distributed queries against a SQL Server 2005
linked server is rewriting queries so, that the most work will be performed
on the remote server, not the local server.
You can run the remote query from Management Studio and take a look at the query plan to find
out which parts of the remote query are performing on the remote server and which are performing
on the local server.
Consider setting the SqlServerLike option of the OLE DB provider to nonzero value.
If the SqlServerLike option has nonzero value, then the provider supports the LIKE operator
as the operator is implemented in SQL Server 2005. This can increase the distributed queries
performance. You can set this option in Object Explorer, simply right-click the OLE DB provider
name and select Properties.
Try to avoid using the bit and uniqueidentifer data types in the distributed queries.
Because distributed queries that involve the bit and uniqueidentifer data types are never
delegated to an OLE DB provider and are always evaluated locally, you should avoid using these
data types to increase the local SQL Server 2005 performance.
If you need to break the long running remote queries automatically, you can set
the linked server "query timeout" option.
By default, this option has the 0 value that means that long running remote queries will not
time out. If you suppose that the long running remote query uses many server and network resources,
you can specify the number of seconds before the query will be aborted in the linked server
"query timeout" option.
Set the sp_serveroption 'lazy schema validation' option to true, if the
distributed, partitioned views are being used against a linked SQL Server.
This option is set to false, by default. When this option is turned on, the checking of the
schema of remote tables is delayed until execution. Sometimes, deferring the schema validation
can be useful to improve performance. For example, you may want to set the sp_serveroption
'lazy schema validation' option to true, if the distributed partitioned views are being used
against a linked SQL Server.
Consider using nvarchar(max), varchar(max), and varbinary(max) data types in
distributed queries.
These data types were first introduced in SQL Server 2005. You can use them in distributed
queries as in general SQL Server statements.
If you want a distributed query be processed on the remote server instead of
the local server, use the OPENQUERY function.
By default, a distributed query is processed locally. Sometimes it is more efficient to process
a distributed query on the remote server and return to the local server only the results of the
query. Use the OPENQUERY function to specify that a distributed query will be processed on the
remote server.
