Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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 '13 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 '13 at 17:20
    
Using OPENQUERY didn't make any difference unfortunately. –  stanleykylee Jan 31 '13 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 '13 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 '13 at 19:04

1 Answer 1

up vote 4 down vote accepted

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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