Instance performance variations around SOS_SCHEDULER_YIELD behavior during single-row INSERT statements

I have a shared SQL Server 2016 dev instance in a VM which has poor INSERT performance compared to a shared SQL Server 2012 dev instance on an (supposed to be) identical VM. It also compares poorly to my local SQL Server 2016 dev instance, so I am currently proceeding on the assumption that there is some quirky issue on that instance, because most other operations on that server seem fairly steady.

They are 1000 individual insert statements of the form:

INSERT INTO tblname (columnlist) VALUES (literals);

There is no explicit transaction.

I wrapped these INSERTs in extended events so I could see a little of what the difference is between environments. I also made a version which runs a loop instead of literals. The performance of the loop is noticeably different on the affected system, I am not including the loop version code below - it performs consistently, but I do drop and recreate the DB before executing that script and the results are included.

I have the system set up to be reproducible and easily pushed to any system for comparison. I am running everything from command scripts and sqlcmd. First a setup script runs to create the database, the table, the XE session. Then a second script runs which starts the trace, executes 1000 INSERT VALUES statements, stops the trace and displays the results using https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/

I already noticed that this particular SQL Server 2016 has very different character around SOS_SCHEDULER_YIELD, and I wonder if this already points to what the underlying problem might be. In addition, I seem to have isolated the problem to the use of GETDATE and GETUTCDATE in defaults on this server.

perftest.cmd:

```
DEL *.xel
echo Running setup.sql
sqlcmd -o "setup.sql.output-1.txt" -I -b -l 120 -t 300 -S %SQLINSTANCE% -U sa -P %PASSWORD% -d master -i .\setup.sql
echo Running perftest.sql
sqlcmd -o "perftest.sql.output-1.txt" -I -b -l 120 -t 300 -S %SQLINSTANCE% -U s

Problems in performance regarding processing time can be related to your power option not being set to "HIGH PERFORMANCE", but in virtual machines, it is often related to misconfigurations in the cores per socket option.

SQL Server is numa aware and several optimizations and decisions are made on the Numa topology as presented by the bios; in high loads, misconfiguration on the alignment for the cores can lead to exacerbation of contention problems and take your server performance down.

A common misconfiguration that I have observed is 1 core per socket. Regardless of the number of sockets, this may cause latency issues when it comes to processing time and memory access.

Example:

exec sp_readerrorlog 0, 1, 'SQL Server detected'

SQL Server detected 16 sockets with 1 cores per socket and 1 logical
processors per socket, 16 total logical processors; using 16 logical
processors based on SQL Server licensing. This is an informational
message; no user action is required.

Check your cores per socket configuration and favor aligning it to underlying host. In other words: if your host has 16 cores per socket, make your VM 16 cores per socket.

For more information, see "Best Practices for Running SQL Server on vSphere":

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

3.5.4 Cores per Socket

As it’s still very common to use Cores per socket setting to ensure
that SQL Server Standard Edition will be able to consume all allocated
vCPUs and can use up to 24 cores23, care should be taken to get the
right vNUMA topology exposed to a VM, especially on the vSphere 6.0
and below while satisfying the licensing needs. As a rule of thumb,
try to reflect your hardware configuration while configuring cores per
socket ratio and revise the NUMA section (3.6) of this document for
further details

StackExchange Database Administrators Q#281310, answer score: 2