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?
Nothing in life is ever so complicated that with a little work it can't be made more complicated.