0

I have virtual machines on two different types of hosts (vmware).

Host type A has CPUs with a clock speed of 2.09GHz
Host type B has CPUs with a clock speed of 2.69GHz
All other resources are the same at the VM level (number of CPU sockets / virtual processors, memory, and disk).

There are Availability Groups setup between the VMs on host type A (each VM is on a different host). Same setup with VMs on host type B, but the difference is that there are are Distributed Availability Groups set up here as well (each VM is still on a different host).

We noticed some queries run faster on host type A than they do on host type B. Digging further, I found this troubleshooting resource: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-perf-between-servers

I ran the T-SQL below from that link (modifying the number of spins):

SET NOCOUNT ON 
DECLARE @spins INT = 0
DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT

WHILE (@spins < 2000000)
BEGIN
   SET @spins = @spins +1
END

SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())
SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms

Example results for host type A: Spins = 2000000, Time_ms = 135517, Spins_Per_ms = 14
Example results for host type B: Spins = 2000000, Time_ms = 553587, Spins_Per_ms = 3

The Powershell results are always much closer.

$bf = [System.DateTime]::Now
for ($i = 0; $i -le 20000000; $i++) {}
$af = [System.DateTime]::Now
Write-Host ($af - $bf).Milliseconds " milliseconds"
Write-Host ($af - $bf).Seconds " Seconds"

Example results from Host A: 18 seconds 704 milliseconds
Example results from Host B: 23 seconds 255 milliseconds

Overall CPU utilization caused by the normal workload is always lower on the VMs on host type B than the VMs on host type A before, during, and after running the query. CPU ready time is ~100-200ms higher on VM Host A than VM Host B.

Additional info:

  • The only VMs that are having the slow t-sql performance are the VMs that are participating in the DAG. Other VMs running on Host type B that are not in this particular DAG (including all those that are setup in a different DAG) are much quicker than the VMs on Host type A.

  • A VM on Host type B was moved to Host type A but performance stayed the same.

  • I failed all AGs off of one of the Host B VMs, t-sql execution was still the same (very slow).

  • With SET STATISTICS TIME, IO ON; I can see many ~15ms entries for CPU throughout the duration of the query for the VM on Host type B, and 0ms for CPU on nearly every loop for the VM on Host type A.

  • CPU-z shows the CPU clock speed holding steady, so it doesn’t appear to be a “green” setting issue.

  • I’ve asked the server team to take a look and they have found no difference in the way that the hosts or VMs are configured.

  • The only difference in terms of sys.configurations is that Filestream is enabled on the VMs on host type B.

This issue seems to be tied to SQL Server. What else can I do to further troubleshoot this?

0

1 Answer 1

2

What else can I do to further troubleshoot this?

I'd first start with checking what XE sessions on are on each server. Items such as statement completed and other heavy hitting XE items will produce these types of issues.

Then I'd collect the sqlserver.sql_batch_completed extended event along with sqlos.wait_completed and a filter on your specific SPID you're going to run your test (again). It should take longer to run with this XE on. I'd post the cpu_time and duration for each server. This information should be in the execution plan, but the plan itself is going to be kind of worthless, other than the time and the wait stats.

The above will get you started in digging in deeper.

1
  • Nailed it. There was an XE on statement completed and batch completed with several wildcard searches. Embarrassed I didn't think to check that. We were about to start rebuilding that cluster, so you saved us a ton of work. Can't thank you enough!
    – Eta_Argus
    Commented Oct 6, 2023 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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