We ran into an issue at work where a SQL Server query was slow when the VM in which the SQL Server was running in had more than 1 vCore. I had posted the question on dba.se and was able to piece together an answer so I wanted to keep a record of that here as well.
- 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
- 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
The issue is in part due to the CPU affinity setting in SQL.
We deployed this VM with SQL from an image which causes a known issue. 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;
sp_configure 'affinity mask', 15;
sp_configure 'show advanced options', 0;
We have updated our image to include this option.
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 onTwitter Facebook Google+ LinkedIn
Leave a Comment
Your email address will not be published. Required fields are marked *