You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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 "More information" 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 "Applies to" section.
MORE INFORMATION

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.
  1. On the ServerA instance of SQL Server 2005, run the following statements to create a database in SQL Server Management Studio:
    USE MASTERGOIF EXISTS (SELECT * FROM sysdatabases where name = 'LinktestTarget')	DROP DATABASE LinktestTargetGOCREATE DATABASE LinktestTargetGOALTER DATABASE LinktestTarget MODIFY FILE(NAME = 'LinktestTarget', SIZE = 200MB)GOALTER DATABASE LinktestTarget MODIFY FILE(NAME = 'LinktestTarget_log', SIZE = 100MB)GO
  2. 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 = @@serverNameIF 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'
  3. 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 LinktestTargetGOCREATE 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]GOSET NOCOUNT ONGODECLARE @counter intSET @counter = 1WHILE @counter < 50000BEGIN	INSERT INTO T1 (c1, c2, c3) VALUES (@counter, @counter,'')	SET @counter = @counter + 1ENDGOUPDATE STATISTICS T1 (PK_t1) WITH FULLSCAN
  4. 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 ASET A.c2 = B.c2FROM LS.LinktestTarget.dbo.T1 AS A 	JOIN @t2 as B on B.c1 = A.c1
Properties

Article ID: 942982 - Last Review: 10/23/2007 23:07:31 - Revision: 1.1

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

  • kbexpertiseadvanced kbtshoot kbprb KB942982
Feedback
e='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> /html>>50&did=1&t=">