5

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 enter image description here
  • The query itself is a select statement with a where clause which selects transactions this month. enter image description here

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!

Improve this question
7
  • Are you using four-part notation or OPENQUERY()? With OPENQUERY, SQL Server does less work locally and pushes more of the effort to the remote server. Are you getting a parallel plan when there are more than 1 vCores available and the query allows it (ie, not using MAXDOP)?
    – Darin Strait
    Jan 31, 2013 at 15:42
  • I've a screenshot of the query and the execution plan without using MAXDOP. Parallelism isn't used in the plan and as I expected, MAXDOP doesn't help. I haven't tried using OPENQUERY as I am not aware of this, but I will look into it now.
    – stanleykylee
    Jan 31, 2013 at 17:20
  • Using OPENQUERY didn't make any difference unfortunately.
    – stanleykylee
    Jan 31, 2013 at 17:58
  • If you affinitize SQL Server to only use 1 processor out of the 2 or 4 available, does that also fix the problem?
    – Jon Seigel
    Jan 31, 2013 at 18:14
  • Since the plan is not parallel and SQLS will only run it on one core, I'm thinking that the problem is more likely on the virtualisation side than the SQL side. I don't know much at all about Xen, but my normal virtualisation troubleshooting questions are: Is your VM host busy? Is there memory or cpu pressure on the host that the VM doesn't see? Is your SQL VM moving between processors or between hosts? Is there a problem with NUMA?
    – Darin Strait
    Jan 31, 2013 at 19:04

1 Answer 1

Reset to default
4

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):

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

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

sql server affinity GUI

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.

Improve this answer
3
  • You're welcome. I'm glad it was an easy fix!
    – Jon Seigel
    Jan 31, 2013 at 23:06
  • You should mark this the answer.
    – Chris Aldrich
    Feb 1, 2013 at 13:56
  • I need to wait 24 hours before I can mark my own answer. Will do so later today. :)
    – stanleykylee
    Feb 1, 2013 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.