Slow SQL Server query using a DB2 linked server in virtual environment

I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:

• Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 & ESXi 5)

• SQL Server 2005 SP3

• Linked Server created using IBM DB2 for iBMASQL OLE DB Provider

• The query itself is a select statement with a where clause which selects transactions this month.

Alright, so the issue itself:

• Virtual machine is set to 4 vCores, it takes roughly 35 seconds to complete

• Virtual machine is set to 2 vCores, it takes roughly 20 seconds to complete

• Virtual machine is set to 1 vCores, it takes roughly 5 seconds to complete

I've tried tinkering with:

• Network card settings

• MAXDOP 1 OPTION

• Looking at the execution plan

I'm at a loss as to what to do from here on. We want to use 4 vCores as the server will be heavily used. However, the queries take a lot longer when compared to using only 1 vCore.

Is there any possible way to limit the amount of vCores the linked server uses?

Is there anything else that I should take a look at?

Thanks!

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

StackExchange Database Administrators Q#33861, answer score: 4