Remote Query performance inconsistent

  • Leo.Miller

    SSChampion

    Points: 12820

    I'm running 2 SQL Servers with SQL 2016, each returning data from the same remote DB2 database.

    DEV - Version 13.0.1601, so RTM, Enterprise Edition

    PROD - Version 13.0.5026 - So SP2, Standard Edition

    Both servers are 4 core, 16GB RAM, on Windows 2016, Standard.

    The SQL database uses Synonyms, that point to a linked DB2 server, and there is no data in the SQL Database, no user tables or views. Both databases and linked server definitions are identical on the two servers.

    I run the same query on both DEV and PROD, and the execution plans are the same, but on DEV it takes 2 - 3 Sec, and on PROD it takes 3 - 4 MINUTES! Both return tho same 84 rows of data, and the actual execution plans indicate not more than 2000 rows being returned from any of the 4 remote tables.

    The only wait counter I've seen that increases significantly on the PROD server while the query is running is "PREEMPTIVE_OLEDBOPS" which climbs 190+ sec on PROD, but <1 sec on DEV.

    If I've understood the description correctly this tells me SQL is waiting for a response from the DB2 server. What doesn't make sense is why the same DB2 server seems to perform so differently depending on which SQL Server submits the request. Any ideas where along the chain the issue could be?

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • rVadim

    Hall of Fame

    Points: 3803

    From what I learnt in this post it could be waiting for OLE DB info while parsing query during compilation. So, maybe (just guessing here) there is a difference in configuration of OLE DB Providers between Dev and Prod?

    https://www.sqlskills.com/help/waits/preemptive_oledbops/

  • Sue_H

    SSC Guru

    Points: 89729

    You might also want to try a tracert from the two different servers to the DB2 server and see if you can find anything in the network paths that could be slowing things down.

    Sue

  • Leo.Miller

    SSChampion

    Points: 12820

    Thanks, we've looked at both these and found nothing. I have found the NICs are set to AutoDetect, and an FTP on the problem server is only doing 33Kb per sec. I'm asking the system Admin to set them to 100Mb Full Duplex to see if this fixes it.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

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