4

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?

7
  • 2
    What does your SSIS package look like?
    – Zane
    Jan 9, 2013 at 16:08
  • I can only assume the simplification of the question has lead to us missing something. In the provided query, why not simply use an Execute SQL Task with a Connection Manager pointed at the CRM database and skip the linked server?
    – billinkc
    Jan 9, 2013 at 17:00
  • @billinkc I assume the update is actually based on a join between INT...table and CRM...table. Jan 9, 2013 at 17:11
  • @billinkc - yes, i've (over)simplified the query. There are lots of queries that join tables on both servers. The example I gave is a valid example - it's part of some processing in a cursor loop, where the rest of the processing includes tables on both servers. I included this specific example because it's one of the simplest queries - I would have expected it to run entirely on the remote server. Instead, it seems it gets ALL the ids & secondary_ids from the remote server, then filters on the local server, then runs the update. That seems to create the network traffic & delay.
    – SeanW
    Jan 9, 2013 at 18:01
  • In the link I provided above, it says the reason is just a permission issue. So I'm quite keen to explore that - if I can get the permissions right, it should work without any code changes. But I've given the linked server login sysadmin & ddladmin roles, but it's not helping. Has anyone actually fixed this issue with the right permissions?
    – SeanW
    Jan 9, 2013 at 18:05

3 Answers 3

3

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.

2
  • Thanks Brian. The queries themselves aren't too complex. I really wanted to sort the privileges out - seemed like the easiest answer. But even with sysadmin rights (the remote user), it wouldn't work - even for simple join queries. I am starting to doubt that it's a valid fix. I think I'm going to try move all the data across, then move it back, etc. Like you say, having the data on the same machine seems the best option.
    – SeanW
    Jan 11, 2013 at 8:11
  • I'm actually going to mark this as the answer, even though it didn't get what I wanted (a fix to the query plan / permissions issue). But having the data on the same machine seems to be the only proper solution.
    – SeanW
    Jan 11, 2013 at 8:13
5

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 .

DataFlowTask

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

Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs. SourceSelect 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. enter image description here

Connect your OLE Connection to You OLE Command. enter image description here

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. enter image description here

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

enter image description here

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 stored procedure will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.

enter image description here

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.

3
  • So you're suggesting a RBAR update? Using OLE DB command for UPDATE = RBAR
    – brian
    Jan 10, 2013 at 3:44
  • @brian Well if you don't want to do that you can dump the information into a staging table with a bulk insert on the other database and run the update from there. Either way will be faster than running a linked query. By just having an execute SQL task that runs in SSIS none of the power of that program is being leveraged. Depending on the number of records he has row by row through OLE command could be adequate for what he is doing.
    – Zane
    Jan 10, 2013 at 16:06
  • That's an amazingly detailed answer, thanks very much. I don't have time to test/implement it, unfortunately, because of deadlines and because I'm not sure it's appropriate: the job does a LOT more than the above example query, using cursors, bulk updates, individual inserts & updates, all between the two servers, so to rewrite all that into this approach, seems like too big a task. Thanks for taking the time to answer, though - one day when I'm big I'll have a look back at what this actually does!
    – SeanW
    Jan 11, 2013 at 8:05
0

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

1
  • 1
    Thanks @edq. The query is just a simple, single example. There are lots more nested selects & updates - some with joins to multiple tables on both servers. So I don't think OPENQUERY would work.
    – SeanW
    Jan 9, 2013 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.