Tips & Tricks: Using VMware & SSIS – Best Practices

By Oscar Li

Virtualization is a powerful tool to effectively allocate resources and maximize value. One of the popular virtualization tools that Melissa clients use is VMware, which offers many configurable settings for virtual machines.

However, these various configurations may lead to unexpected issues or complications when working with different versions of SQL Server. Each year, and each version, of SQL Server has its own intricacies and can behave inconsistently. One example is with SQL Server 2016 utilizing VMware.

VMware in the Hot Swap

Melissa discovered an issue when utilizing the option Hot Swap CPU while hosting SQL Server 2016 with VMware.

We confirmed with VMware that this is against the best practices (3.3.6 CPU Hot Plug). Engineers from both VMware and Microsoft have verified that this will lead to issues with thread contention. The setting MAXDOP if set to too high will also have an effect on the contention and SSIS processes, we have noticed that some processes or packages may freeze and never complete.

As reported from a Melissa client, reports show that when querying SQL Server Numa in DMV (sys.dm_os_memory_nodes) only one node will be allocated 100% memory. After disabling Hot Swap CPU, SQL Server now correctly detects a single numa. Disabling Hot Swap CPU will increase performance and should prevent hanging issues from occurring in SSIS.

This Hot Swap CPU issue is only reported for SQL Server 2016 edition, which seems to be caused by the new cardinality estimator and automatic soft numa default features. The combination of new changes and incorrect setup in VMware may cause threads to wait past a certain threshold.