Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    8

    Unanswered: Linked Server woes

    HELPPPPPPPPP!!!!!!!

    Have a process which inserts data into a table on another server, but it takes FOREVER. The table is pretty small at 86000 rows.
    A simple
    insert into SERVER-B.DatabaseX.dbo.TableY select * From DatabaseY.dbo.TableX

    Wanted to know if you had any ideas I’ve not thought of.

    I’ve rebuilt the linked servers, ensured the systems are configured the same for SQL server...Kind of stumped.

    There’s no blocking happening as I am the only person using the servers at the time.
    But from the data below, the 2.5 hours is ridiculous

    Here is the scenario:
    Inserting data from SERVER-A (SQL2008R2/Windows Server 2008R2) into SERVER-B (SQL 2014 SP1, Windows Server 2012R2) across the network. Below is my testing.

    on SERVER-A (originating server)
    • Selecting 86000 rows works fine in seconds
    • Selecting 86000 rows from SERVER-B works fine in seconds
    • Inserting 86000 rows into a temp table works fine in seconds
    • Inserting 86000 rows into SERVER-C from SERVER-A - 1 minute 30 (used to compare using a different server - SERVER-C is SQL 2012, Windows Server 2012R2)
    • Inserting 86000 rows into SERVER-B from SERVER-A - 2.5 hours!!!!

    On SERVER-B (destination server)
    • Selecting 86000 rows works fine in seconds
    • Selecting 86000 rows from SERVER-A works fine in seconds
    • Inserting 86000 rows into a temp table works fine in seconds
    • Inserting 86000 rows into SERVER-A from SERVER-B - 1 minute

    Now leads me to believe something is misconfigured on SERVER-B or the connectivity between SERVER-A/SERVER-B

    HELP?!?!

    **Disclaimer - I know pushing data is slower and I accept that, but the test into SERVER-C from SERVER-A proves it shouldn't be so bad!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Borrowing from a tweet from Buck Woody on 2016-01-21, a Linked Server is something like:

    Click image for larger version. 

Name:	Linked Server.jpg 
Views:	1 
Size:	112.5 KB 
ID:	16726

    This is OK for doing very simple, low volume tasks. It is not suitable to move volumes of data!

    I would strongly suggest an SSIS package. If that doesn't work for you, I'd consider a PowerShell script.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2005
    Posts
    8
    Yes, but this is the option that is currently in use.

    It works great on some servers, but not on others.

    Am curious to see whether there are configuration things people can think of which may be able to speed this up on the one server it is not consistently working on.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which of the three options I mentioned (Linked Server, SSIS, or PowerShell) is the one that is currently in use?

    SSIS should be the fastest of the bunch.
    PowerShell has the ability to match SSIS if coded properly.
    Linked Servers are vulnerable to many kinds of configuration and infrastructure problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2005
    Posts
    8
    Linked server option.

    That process is ingrained right now and can't be changed.

    Like I said, it works faster on some servers but not all - where should I check for issues after checking the linked server is created correctly, the system configurations are correct within SQL server - should I pass it off to the network admins? This is like 2 rows per second here. There's a problem, and I don't mean with the methodology in which it is being completed by - just the slowness of the data.

    If it helps, it's going from HEAP to HEAP - so no indexes, keys etc to bog anything down or worry about, and all statistics are up to date with full scan etc.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If they are committed to staying with Linked Servers, then you have a considerable problem. There are many problems, and you can fix all the problems that exist at a given point in time and have things work nicely for hours or even months and end up back in the same position you are at right now... If whoever is clinging to using the Linked Servers gets serious about fixing these problems, then your life will become a lot easier.

    The computer/networking model for Linked Servers works well for servers in a simple, fast environment. For virtual servers on the same virtual host, Linked Servers are usually pretty trouble free. For servers in a single rack, with only an Ethernet switch between them, Linked Servers work well most of the time. As the cables get longer and more technology gets between the Linked Servers, things get progressively more interesting.

    Google the phrase "linked server troubleshooting" which will produce thousands of articles. In general, you are more likely to have luck with the newer and the more popular articles. With that said, you may still need to visit all of them and even that may not fix the problems with your specific Linked Servers and you'll have to work with them down to the Operating System or even Hardware level to resolve the problem.

    The biggest single problem is that even when you get the Linked Servers working well, you don't know how long they will stay that way. In general, I can keep the Linked Servers that I support performing in the 1k to 10k rows per second range in the same rack, and somewhat faster on a virtual host. In the same environment for much less effort I can maintain 200k rows per second for SSIS.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •