Remote queries and linked servers

Question

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?

Answer 1

Several possible reasons for this.

Lack of data distribution statistics. It looks like you have this one covered in your post, but i'll leave it for completeness.

If the linked server login does not have sufficient privileges to obtain all useful statistics on CRM, a suboptimal plan will be used. *If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.* http://msdn.microsoft.com/en-us/library/ms175537%28v=sql.105%29.aspx

Mike Walsh also has a writeup on this: http://www.straightpathsql.com/archives/2010/07/linked-server-query-running-slow/

Query is too complex to perform well across the linked servers.

1. Replication: Replicate the minimum necessary columns from one server to the other. Use the local replicated data for joins.
2. Use temp tables: Insert the minimum necessary columns from one server to a table on the other. Use the local data for joins.

For both of these scenarios, all data is on the same machine. This enables the optimizer to have complete data distribution statistics. You can even index columns for further optimization. Always identify the minimum columns required to complete the necessary tasks. Sometimes it's sufficient just to replicate or insert 1 or 2 columns.

OPENQUERY is also an option, but I prefer all of the data on the same machine when possible.

If the cross server joins are INNER JOINS, you might also benefit from the use of the REMOTE hint. I've seen this work very well when the remote table has significantly more records than the local table.

Answer 2

Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. Then you need to create a dataflow task. You will find that in your You toolbox like so .

Once you have done that open the DataFlowTask and Create a new OLEDB Connection. Once again found in the toolbox.

Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs. Next Set up your OLEDB command the portion that will execute a SQL command with the result Set. First Select OLEDB Command from the toolbox.

Connect your OLE Connection to You OLE Command.

Next you create a stored procedure to handle this update. Its a fairly simple procedure to make it will ultimately look like this.

CREATE PROCEDURE UPD_CRM_TableName(
    @ID int,
    @Field int)

UPDATE TableName SET 
@Field = Field

WHERE @ID = ID

Then open you OLEDB command and select the appropriate connection in the connection tab.

Go to the Component properties tab click on SQL Command and the appropriate values to execute your sproc.

Then go to column mappings and make your parameters match you columns and you will be good to execute the package. Note that the update sproc will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.

Setting this up for any update you command will be incredibly easy to set up and will basically run as fast as your hardware and network can keep up. Make sure your integration services box has plenty of memory as that is where all of it's operations are executed. If you have any questions feel free to let me know.

Answer 3

Try using openquery over linked server. Something like this: http://social.msdn.microsoft.com/Forums/sk/transactsql/thread/436634c6-07de-44ce-b27c-cafa62d781cb