April 14, 2019 at 8:48 pm
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.
April 16, 2019 at 10:23 pm
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/
--Vadim R.
April 16, 2019 at 10:58 pm
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
April 29, 2019 at 3:42 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply