Post reply

Should be Azure VM cpu scale be more uniform?

  • February 12, 2018 at 7:53 am

    #336095

    Client has an Azure VM with SQL Server 2016 Standard with one default instance and 4 named instances. VM has 112GB RAM and 16 cores.  I was looking at the performance monitor for cpu and I can see anomalies (if they are exactly that) with the workload on the cpu's in that I would expect to see the same workload over each cpu? (See image)

    Or am I wrong?  I have had this issue before in a VMWare VM where the OS/SQL was only using half the CPU's, if had allocated. Oone tweak later and it was resolved.  I ran a script here: 

    DECLARE @OnlineCpuCount int
    DECLARE @LogicalCpuCount int

    SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
    SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

    SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',
     CASE
      WHEN @OnlineCpuCount < @LogicalCpuCount
      THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
      ELSE 'You are using all CPUs assigned to O/S. GOOD!'
     END as 'CPU Usage Desc'

    And the output as correct, the VM is using all CPUs assigned to OS.  Should I be setting the same MAXDOP across all instances?  These are small apps that hardly use any CPU/RAM.

    Thanks

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • February 12, 2018 at 10:33 am

    #1979467

    If, for some reason, you want to see the workload to be forced to be distributed across all of the instances, you could set the CPU affinity.  But this would be for very specific cases.  I have had environments such as you describe and have affinitized the CPUs, but actually incurred a performance hit.  It is best to let SQL scheduler assign CPUs their tasks.  Lookup SQL CPU affinity best practices.  If the workload is light and the payload of each task is light, as you describe, then it is not unusual to have scheduler use the same CPU over and over.  It is not neglecting the others, it just doesn't need them.  Under a light load, it is not assigning tasks in a 'round-robin' method.  

    It's always a good idea to monitor how your parallel plans allocate your CPUs so you can set a proper MAXDOP , if needed.  At my current job, on a 16 proc box with a MAXDOP setting of zero, there were several queries which would take up all 16 CPUs.  As you can guess, SQL ground to a halt.  I changed the setting to 2 and the world was right again.

  • February 13, 2018 at 6:25 am

    #1979583

    Thanks Davis! 

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply