IMPORTANT: This article contains information about modifying the registry. Before you
modify the registry, make sure to back it up and make sure that you understand how to restore
the registry if a problem occurs. For information about how to back up, restore, and edit the
registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows Registry
SYMPTOMS
A simple distributed SELECT query with a WHERE clause that references a remote table on a remote server runs normally and it does not experience performance degradation; however, under the following conditions, you may notice significant performance degradation:
- You use the same distributed query.
- You wrap it inside a stored procedure that takes an input parameter.
- In the WHERE clause of the SELECT statement in the distributed query, you compare a column against the input parameter.
For example, the following code is a sample distributed SELECT statement that is not wrapped inside a stored procedure and the resultant query plan:
set statistics profile on
go
Select * FROM IMCLTSQLT01.HRMART.dbo.TEmployee_Info a
WHERE a.EMPLID='131682'
go
set statistics profile off
go
Select * FROM IMCLTSQLT01.HRMART.dbo.TEmployee_Info a
WHERE a.EMPLID='131682'
|--Remote Query(SOURCE:(IMCLTSQLT01), QUERY:(SELECT a."EMPLID" Col1002,a."EMPL_RCD#" Col1003,a."SSN" Col1004,a."EMPLOYEE_SS#" Col1005 ....
The following example demonstrates the same distributed SELECT statement that is wrapped inside a stored procedure and in which the SELECT statement WHERE clause compares a column against the input parameter (note the additional
Filter step in the query plan):
CREATE PROCEDURE dbo.xtest
(
@EMPLID char(11)
)
AS
Select * FROM IMCLTSQLT01.HRMART.dbo.TEmployee_Info a
WHERE a.EMPLID=@EMPLID
go
set statistics profile on
go
exec xtest '131682'
go
set statistics profile off
go
Select * FROM IMCLTSQLT01.HRMART.dbo.TEmployee_Info a
WHERE a.EMPLID=@EMPLID
|--Filter(WHERE:([IMCLTSQLT01].[HRMART].[dbo].[TEmployee_Info].[EMPLID]=[@EMPLID])) WHERE:([IMCLTSQLT01].[HRMART].[dbo].[TEmployee_Info].[EMPLID]=[@EMPLID])
|--Remote Query(SOURCE:(IMCLTSQLT01), QUERY:(SELECT a."COUNTRY_CDE" Col1055,a."MGR_NAME" Col1054,a."MANAGER_ID" Col1053,a."REHIRE_DT" Col1052,a."COMPANY_DESCR" Col1051,a."BEN_STATUS" Col1050,a."HIGHEST_EDUC_LVL" Col1049,a."TAX_LOC_STATE" Col1048,a."
The stored procedure in the preceding example experiences performance degradation because the
Filter is not remoted to the remote server.
WORKAROUND
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may
require you to reinstall your operating system. Microsoft cannot guarantee that you can solve
problems that result from using Registry Editor incorrectly. Use Registry Editor at your own
risk.
To optimize the performance of the stored procedure, set the
Dynamic Paramters Provider option to
ON. Use either of the following methods to do so:
- Change the Dynamic Paramters Provider option for the provider to 1 in the following registry key:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/instancename/Providers/Providername/Dynamic Paramters
- Drop and re-create the linked server. When you re-create the linked server, select the Microsoft OLE DB Provider for SQL Server, click Provider options, and then click to select the Dynamic Parameters check box.
When you execute the stored procedure, the performance improves significantly because when you turn Dynamic Parameters on, SQL Server delegates the parameterized query to the OLE DB provider.