I have 2 servers with SQL Server 2008 R2: INT
& CRM
.
I need to run an SSIS package on INT
, which uses data from both an INT
database and a CRM
database. It's painfully slow.
I tried this to test a simple query (on INT
):
EXEC sp_addlinkedserver @server='CRM',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='<IP of CRM>'
EXEC sp_addlinkedsrvlogin
@useself='FALSE',
@rmtsrvname='CRM',
@rmtuser='<username>',
@rmtpassword='<userpassword>'
I also gave <username>
ddl_admin role on the CRM
server, as recommended on this answer:
SQL performance issues with remote query across linked server
I then run this query:
UPDATE [CRM].DB.dbo.table1
SET field = 1
WHERE id = (SELECT id FROM [CRM].DB.dbo.table2 WHERE secondary_id = 9999)
This query takes 40 ms to run when on CRM
itself. It takes 30 seconds to run in the above example. There are no results returned, it can't be network issues, so why does it take so long?