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

1 minute read

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.

Environment

  • 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

linksrv

Query

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

Issue

  • 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

Solution

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; RECONFIGURE; GO sp_configure 'affinity mask', 15; RECONFIGURE; GO sp_configure 'show advanced options', 0; RECONFIGURE; GO

affinity

We have updated our image to include this option.

Notes

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.

Original dba.se question

Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...