Microsoft KB Archive/320208

= PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input Parameters May Experience Performance Degradation =

Article ID: 320208

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q320208



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' 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 The stored procedure in the preceding example experiences performance degradation because the Filter is not remoted to the remote server.
 * --Remote Query(SOURCE:(IMCLTSQLT01), QUERY:(SELECT a.&quot;EMPLID&quot; Col1002,a.&quot;EMPL_RCD#&quot; Col1003,a.&quot;SSN&quot; Col1004,a.&quot;EMPLOYEE_SS#&quot; Col1005 ....
 * --Filter(WHERE:([IMCLTSQLT01].[HRMART].[dbo].[TEmployee_Info].[EMPLID]=[@EMPLID]))                                                                   WHERE:([IMCLTSQLT01].[HRMART].[dbo].[TEmployee_Info].[EMPLID]=[@EMPLID])
 * --Remote Query(SOURCE:(IMCLTSQLT01), QUERY:(SELECT a.&quot;COUNTRY_CDE&quot; Col1055,a.&quot;MGR_NAME&quot; Col1054,a.&quot;MANAGER_ID&quot; Col1053,a.&quot;REHIRE_DT&quot; Col1052,a.&quot;COMPANY_DESCR&quot; Col1051,a.&quot;BEN_STATUS&quot; Col1050,a.&quot;HIGHEST_EDUC_LVL&quot; Col1049,a.&quot;TAX_LOC_STATE&quot; Col1048,a.&quot;



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/ /Providers/ /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.



MORE INFORMATION
The following excerpt is from the SQL Server Books Online topic, &quot;Optimizing Distributed Queries&quot;:

Parameter marker support

If the provider supports parameterized query execution by using the ? parameter marker in a query, then SQL Server can delegate parameterized query execution to the provider. Because nested query support cannot be automatically determined from OLE DB properties, the system administrator should set the DynamicParameters provider option to indicate to SQL Server that the provider supports nested queries.

Additional query words: Optimizing Distributed Queries Stored Procedure Performance

Keywords: kbprb kbpending KB320208

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.