Slow SQL Server query using a DB2 linked server in virtual environment
Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.

We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):


IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server


Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.


Affinity Mask Option


sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO



We have updated our image to include this option. Thanks everyone for the help in this issue.



Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.


Affinity Mask Option - SQL Server 2012