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?