Linked Server Performance

  • On our production system, we are inserting records into a local table on a SQL Server from another SQL server using a linked server. The server with the local table is "pulling" the records via a linked server from the other server.

    The INSERT takes around 3 hours to run in production (we are inserting over 600,000 very long records). Both servers are on the same network and probably on the same switch (they are in a data center and I have very limited knowledge of their physical setup). Both servers are running SQL Server 2005 SP3. (They are both going to be upgraded to SP4 in about a week.)

    We have almost the same identical server setup for our testing system. In testing, the same INSERT takes only 5 minutes to insert the records. The testing servers are both running SQL Server 2005 SP4.

    The same identical TSQL code is running in both environments.

    Any suggestions of where to look for why production is so much slower than testing for the INSERT?

    I looked at MSDTC as a possibility because it is mentioned often in connection with transactions and INSERT. In both environments, MSDTC is running on all 4 servers but in testing, I am able to configure MSDTC in Component services on the server with the local table whereas in production Component Services hangs when trying to look at the configuration. Also, the testing servers are both configured for Network DTC whereas the only producrtion server that will bring up MSDTC in Component Services isn't. To see if MSDTC could have been the problem, I stopped the MSDTC service on the server with the local table in testing and tried the INSERT again. Still only 5 minutes. Then I turned off Network DTC on the other server in testing. Still only 5 minutes. So, I don't think MSDTC has anything to do with it.

    Could database compatibility effect the time? In testing, both databases involved are 90 whereas in production they are both 80.

    I am really grasping at straws with this. Any ideas on what else could cause the time difference between production and testing would be appreciated.

  • First off ensure that the plans are the same , are you joining from/to the incoming data ?

    Linked servers can behave in 'odd' ways

    Maybe a SSIS task would be a better option to ensure a more robust solution ?



    Clear Sky SQL
    My Blog[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply