Microsoft KB Archive/942982

= Performance is slow when you run a query in SQL Server 2005 that updates a table on a linked server and then joins the table to a table on the local server =

Article ID: 942982

Article Last Modified on 10/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



SYMPTOMS
Consider the following scenario:
 * You have a query that updates a table on a linked server and then joins the table to a table on the local server.
 * The local server is running Microsoft SQL Server 2005.
 * On the local server, you run the query in SQL Server 2005.

In this scenario, performance is slow compared with the performance of the same query in Microsoft SQL Server 2000. For example, when you run the query in SQL Server 2000, the query takes less than 1 second to finish. However, when you run the query in SQL Server 2005, the query takes about 24 seconds to finish.



CAUSE
The query performs a Remote Scan operation on the table on the linked server. When you run the query in SQL Server 2005, the Remote Scan operation retrieves all the rows in the table. If the table contains many rows, the Remote Scan operation takes a long time to finish.

However, when you run the query in SQL Server 2000, the Remote Query operation submits the query to run on the linked server. Therefore, even if the table contains many rows, the Remote Scan operation is complete in a short time.



WORKAROUND
To work around this problem, add a UNIQUE constraint or a PRIMARY KEY constraint in the table on the local server. Add the constraint to the column that you use for the JOIN operation.

For example, you update the DECLARE statement that is mentioned in step 4 of the &quot;More information&quot; section to the following: DECLARE @t2 table (c1 int primary key, c2 int)



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce this problem
Note The following steps assume that you have two servers that are running SQL Server 2005. One instance name is ServerA\SQL2005. The other instance name is ServerB\SQL2005.   On the ServerA instance of SQL Server 2005, run the following statements to create a database in SQL Server Management Studio: USE MASTER GO IF EXISTS (SELECT * FROM sysdatabases where name = 'LinktestTarget') DROP DATABASE LinktestTarget GO CREATE DATABASE LinktestTarget GO

ALTER DATABASE LinktestTarget MODIFY FILE (NAME = 'LinktestTarget', SIZE = 200MB) GO ALTER DATABASE LinktestTarget MODIFY FILE (NAME = 'LinktestTarget_log', SIZE = 100MB) GO   On the ServerB instance of SQL Server 2005, run the following statements to create a linked server in Management Studio: DECLARE @serverName nvarchar(50) SET @serverName = @@serverName

IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'LS') EXEC master.dbo.sp_dropserver @server=N'LS', @droplogins='droplogins'

EXEC sp_addlinkedserver @server = N'LS', @srvproduct = N'SQLDB', @provider = N'SQLOLEDB', @datasrc = N'ServerA\SQL2005', @catalog = N'LinktestTarget'

EXEC sp_serveroption N'LS', N'data access', N'true' EXEC sp_serveroption N'LS', N'rpc out', N'true' EXEC sp_serveroption N'LS', N'rpc', N'true' EXEC sp_serveroption N'LS', N'use remote collation', N'true' EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LS', @useself = N'true', @locallogin = N'sa'   On the ServerA instance of SQL Server 2005, run the following statement to create a table, and then populate the table with 50,000 rows of data: USE LinktestTarget GO CREATE TABLE [T1] (   [c1] [int]  NOT NULL,    [c2] [int] NOT NULL,    [c3] char (4000) NOT NULL,    CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED ([c1]) WITH FILLFACTOR = 80 ON [PRIMARY] ) ON [PRIMARY] GO SET NOCOUNT ON GO DECLARE @counter int SET @counter = 1

WHILE @counter < 50000 BEGIN INSERT INTO T1 (c1, c2, c3) VALUES (@counter, @counter,'') SET @counter = @counter + 1 END GO UPDATE STATISTICS T1 (PK_t1) WITH FULLSCAN   On the ServerB instance of SQL Server 2005, run the following statements. Notice that the performance is slow. DECLARE @t2 table (c1 int, c2 int) INSERT INTO @t2 (c1, c2) VALUES (399990, 399990) UPDATE A SET A.c2 = B.c2 FROM LS.LinktestTarget.dbo.T1 AS A    JOIN @t2 as B on B.c1 = A.c1 

Keywords: kbexpertiseadvanced kbtshoot kbprb KB942982

-

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

© Microsoft Corporation. All rights reserved.