8

I'm looking at the following scenario:

  • MSSQL Server 2019 Standard running on Windows Server 2016.
  • sys.dm_os_schedulers shows 12 available logical CPU cores.
  • Those 12 cores are split between 2 sockets.
  • Even though they're sitting on 2 sockets, they seem to be on the same NUMA node (the server is virtualized by VMWare) (coreinfo output):
*-----------  Physical Processor 0
-*----------  Physical Processor 1
--*---------  Physical Processor 2
---*--------  Physical Processor 3
----*-------  Physical Processor 4
-----*------  Physical Processor 5
------*-----  Physical Processor 6
-------*----  Physical Processor 7
--------*---  Physical Processor 8
---------*--  Physical Processor 9
----------*-  Physical Processor 10
-----------*  Physical Processor 11

Logical Processor to Socket Map:
******------  Socket 0
------******  Socket 1

Logical Processor to NUMA Node Map:
************  NUMA Node 0
  • There are no affinities configured.
  • Server is hosting multiple instances.
  • MAXDOP is set to 6 for all instances.

But still a symptom that I'm seeing is, that when instance 1 is running an intensive set of queries, the CPU is running on 50%, single cores are using 100% / very little CPU, alternating: diagram

While this in itself is alright, at that time there is instance 2 which suddenly has terrible performance and is even losing connection to the database.

Does anyone have an idea or a hint where to look at to further narrow down this issue? Feel free to ask for any information you need. :-)


edits: vSphere config for @ErikDarling:

vsphere config

errorlog output:

2023-09-05 23:15:51.95 Server      Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64) 
    Jul 27 2023 18:11:00 
    Copyright (C) 2019 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

2023-09-05 23:15:51.95 Server      UTC adjustment: 2:00
2023-09-05 23:15:51.95 Server      (c) Microsoft Corporation.
2023-09-05 23:15:51.95 Server      All rights reserved.
2023-09-05 23:15:51.95 Server      Server process ID is 4852.
2023-09-05 23:15:51.95 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware7,1'.
2023-09-05 23:15:51.95 Server      Authentication mode is MIXED.
2023-09-05 23:15:51.95 Server      Logging SQL Server messages in file 'D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2023-09-05 23:15:51.95 Server      The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2023-09-05 23:15:51.95 Server      Registry startup parameters: 
     -d D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2023-09-05 23:15:51.95 Server      Command Line Startup Parameters:
     -s "MSSQLSERVER"
2023-09-05 23:15:53.75 Server      SQL Server detected 2 sockets with 6 cores per socket and 6 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2023-09-05 23:15:53.75 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2023-09-05 23:15:53.76 Server      Detected 92159 MB of RAM. This is an informational message; no user action is required.
2023-09-05 23:15:53.76 Server      Using conventional memory in the memory manager.
2023-09-05 23:15:53.76 Server      Page exclusion bitmap is enabled.
2023-09-05 23:15:53.87 Server      Buffer Pool: Allocating 4194304 bytes for 2621440 hashPages.
2023-09-05 23:15:54.03 Server      Default collation: Latin1_General_CI_AS (us_english 1033)
2023-09-05 23:15:54.03 Server      Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.
2023-09-05 23:15:54.15 Server      Buffer pool extension is already disabled. No action is necessary.
2023-09-05 23:15:54.32 Server      Query Store settings initialized with enabled = 1, 
2023-09-05 23:15:54.33 Server      The maximum number of dedicated administrator connections for this instance is '1'
2023-09-05 23:15:54.33 Server      This instance of SQL Server last reported using a process ID of 4524 at 05.09.2023 23:15:29 (local) 05.09.2023 21:15:29 (UTC). This is an informational message only; no user action is required.
2023-09-05 23:15:54.34 Server      Node configuration: node 0: CPU mask: 0x000000000000003f:0 Active CPU mask: 0x000000000000003f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023-09-05 23:15:54.34 Server      Node configuration: node 1: CPU mask: 0x0000000000000fc0:0 Active CPU mask: 0x0000000000000fc0:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023-09-05 23:15:54.37 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2023-09-05 23:15:54.38 Server      In-Memory OLTP initialized on standard machine.
2023-09-05 23:15:54.40 Server      [INFO] Created Extended Events session 'hkenginexesession'
2023-09-05 23:15:54.40 Server      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2023-09-05 23:15:54.40 Server      Total Log Writer threads: 3. This is an informational message; no user action is required.
2023-09-05 23:15:54.43 Server      clwb is selected for pmem flush operation.
2023-09-05 23:15:54.45 Server      CLR version v4.0.30319 loaded.

sys.dm_os_schedulers:

scheduler_address   parent_node_id  scheduler_id    cpu_id  status  is_online   is_idle preemptive_switches_count   context_switches_count  idle_switches_count current_tasks_count runnable_tasks_count    current_workers_count   active_workers_count    work_queue_count    pending_disk_io_count   load_factor yield_count last_timer_activity failed_to_create_worker active_worker_address   memory_object_address   task_memory_object_address  quantum_length_us   total_cpu_usage_ms  total_cpu_idle_capped_ms    total_scheduler_delay_ms    ideal_workers_limit
0x0000025918220040  0   0   0   VISIBLE ONLINE  1   1   1153043 16864086    133496581   4   0   9   4   0   0   4   285671940   10880995620 0   0x00000259186BC160  0x000002591804E040  0x0000025918050040  4000    0   0   7283681 57
0x0000025918240040  0   1   1   VISIBLE ONLINE  1   1   1447172 19240663    140078602   5   0   10  4   0   0   4   301808773   10880994760 0   0x00000259183A8160  0x0000025918058040  0x000002591805A040  4000    0   0   7212441 58
0x0000025918260040  0   2   2   VISIBLE ONLINE  1   0   214859549   566109938   1285209060  4   1   9   4   0   0   6   -1417541559 10880995669 0   0x0000026F4AD94160  0x0000025918060040  0x0000025918062040  4000    0   0   32962149    55
0x0000025918480040  0   3   3   VISIBLE ONLINE  1   1   183703151   539137658   1240154119  3   0   9   3   0   0   3   -1502852373 10880995416 0   0x000002591850E160  0x0000025918068040  0x000002591806A040  4000    0   0   37429531    56
0x00000259184A0040  0   4   4   VISIBLE ONLINE  1   1   9690121 55468504    372230585   5   0   10  5   0   0   4   807572752   10880995541 0   0x0000025918580160  0x0000025918070040  0x0000025918072040  4000    0   0   14392226    56
0x00000259184C0040  0   5   5   VISIBLE ONLINE  1   1   86901162    272690595   959567522   4   0   11  3   0   0   2   2129958437  10880995650 0   0x0000026BFFCBE160  0x0000025918078040  0x000002591807A040  4000    0   0   24265549    57
0x00000259184E0040  0   1048578 0   HIDDEN ONLINE   1   0   10806   0   2405    1   0   1   1   0   0   1   2078088 10880994401 0   0x00000259186B6160  0x00000259186B0040  0x00000259186B2040  4000    0   0   0   2
0x0000025918C20040  1   6   6   VISIBLE ONLINE  1   1   14364691    48529713    395946180   4   0   9   3   0   0   2   862720484   10880993363 0   0x00000268144D2160  0x0000025918620040  0x0000025918622040  4000    0   0   14308350    57
0x0000025918C40040  1   7   7   VISIBLE ONLINE  1   1   981117  15276400    106523681   4   0   8   3   0   0   3   225092127   10880995322 0   0x0000025918E02160  0x0000025918628040  0x000002591862A040  4000    0   0   6992313 57
0x0000025918C60040  1   8   8   VISIBLE ONLINE  1   1   278403126   713103290   1467283639  2   0   8   2   0   0   1   -1025444988 10880995657 0   0x00000267DA07C160  0x0000025918630040  0x0000025918632040  4000    0   0   37662853    55
0x0000025918F00040  1   9   9   VISIBLE ONLINE  1   1   12156863    43687653    376792761   3   0   8   3   0   0   2   821501532   10880993419 0   0x0000026882FAC160  0x0000025918638040  0x000002591863A040  4000    0   0   12790071    56
0x0000025918F20040  1   10  10  VISIBLE ONLINE  1   1   183639496   503573475   1376124782  2   0   7   2   0   0   1   -1243539994 10880995250 0   0x00000268A8E1C160  0x0000025918640040  0x0000025918642040  4000    0   0   30034933    55
0x0000025918F40040  1   11  11  VISIBLE ONLINE  1   1   10686428    37944165    326803213   5   0   9   4   0   0   3   712439435   10880986711 0   0x0000025B1CADA160  0x0000025918648040  0x000002591864A040  4000    0   0   12708950    57
0x0000025918F60040  1   1048579 6   HIDDEN ONLINE   1   0   0   0   2215    1   0   1   1   0   0   1   2048291 10880993266 0   0x0000025919104160  0x000002591867E040  0x0000025919100040  4000    0   0   0   2
0x00000259191A0040  64  1048576 0   VISIBLE ONLINE (DAC)    1   1   4   9   12095   2   0   3   1   0   0   0   12102   10880209924 0   0x00000259186BA160  0x0000025919122040  0x0000025919124040  4000    0   0   0   5
0x000002591F9A0040  0   1048580 1   HIDDEN ONLINE   1   1   0   0   100614190   1   0   1   1   0   0   1   200151374   10880995666 0   0x000002591F96E160  0x000002591F968040  0x000002591F96A040  4000    0   0   0   2
0x000002591F980040  1   1048581 7   HIDDEN ONLINE   1   1   0   0   761882074   1   0   1   1   0   0   1   1210023305  10880995572 0   0x000002591F976160  0x000002591F970040  0x000002591F972040  4000    0   0   0   2
0x000002591F9C0040  1   1048582 8   HIDDEN ONLINE   1   1   0   0   755215122   1   0   1   1   0   0   1   1199890707  10880995666 0   0x000002591F97E160  0x000002591F978040  0x000002591F97A040  4000    0   0   0   2
0x000002591F9E0040  1   1048583 9   HIDDEN ONLINE   1   1   0   0   749447107   1   0   1   1   0   0   1   1191192769  10880995666 0   0x000002591FB80160  0x000002591F67C040  0x000002591F67A040  4000    0   0   0   2
0x0000025920320040  0   1048584 2   HIDDEN ONLINE   1   1   6   0   12128   1   0   1   1   0   0   1   12189   10880901314 0   0x0000025921A1E160  0x0000025921A18040  0x0000025921A1A040  4000    0   0   0   2
0x0000025920340040  0   1048585 3   HIDDEN ONLINE   1   1   2   0   12123   1   0   1   1   0   0   1   12179   10880901330 0   0x0000025921A32160  0x0000025921A2E040  0x0000025921A2C040  4000    0   0   0   2
0x0000025920240040  0   1048586 4   HIDDEN ONLINE   1   1   565500  0   2766233 1   0   1   1   0   0   1   5482068 10880994369 0   0x0000025924210160  0x000002592420A040  0x000002592420C040  4000    0   0   0   2
0x00000259285C0040  0   1048587 5   HIDDEN ONLINE   1   1   2972    0   3393964 0   0   1   0   0   0   0   6777584 10880866350 0   0x000002594F878160  0x000002594F860040  0x000002594F868040  4000    0   0   0   1
0x00000259285E0040  0   1048588 0   HIDDEN ONLINE   1   1   985 0   881850  0   0   1   0   0   0   0   1751250 10880866350 0   0x000002596012A160  0x000002594F87A040  0x000002594F87C040  4000    0   0   0   1
0x0000025960420040  1   1048589 10  HIDDEN ONLINE   1   1   1883    0   1831914 0   0   1   0   0   0   0   3652749 10880866350 0   0x0000025A85804160  0x0000025A85850040  0x0000025A85876040  4000    0   0   0   1
0x0000025960400040  1   1048590 11  HIDDEN ONLINE   1   1   2129    0   1854492 0   0   1   0   0   0   0   3698022 10880566076 0   0x0000025A85812160  0x0000025A85808040  0x0000025A85810040  4000    0   0   0   1
0x0000025A715A0040  0   1048591 1   HIDDEN ONLINE   1   1   2858    0   2369960 0   0   1   0   0   0   0   4730473 10880866319 0   0x00000259D154A160  0x0000025A46240040  0x0000025A46242040  4000    0   0   0   1
0x00000259283C0040  0   1048592 2   HIDDEN ONLINE   1   1   1431    0   1262788 0   0   1   0   0   0   0   2514230 10880176213 0   0x0000025A84230160  0x0000025A84264040  0x0000025A84244040  4000    0   0   0   1
0x000002596A680040  0   1048593 3   HIDDEN ONLINE   1   1   2599    0   2576020 0   0   1   0   0   0   0   5141377 10880866319 0   0x0000025A7982C160  0x0000025A79826040  0x0000025A79828040  4000    0   0   0   1
0x00000259283E0040  0   1048594 4   HIDDEN ONLINE   1   1   2634    0   2455946 0   0   1   0   0   0   0   4901224 10880176229 0   0x0000025A8426C160  0x0000025A8424A040  0x0000025A8426A040  4000    0   0   0   1
0x000002596A6A0040  0   1048595 5   HIDDEN ONLINE   1   1   2801    0   2731176 0   0   1   0   0   0   0   5451858 10880866335 0   0x0000025A79834160  0x0000025A7982E040  0x0000025A79830040  4000    0   0   0   1
0x000002592E8C0040  1   1048596 6   HIDDEN ONLINE   1   1   2416    0   2279872 0   0   1   0   0   0   0   4549033 10880176213 0   0x0000025A8298C160  0x0000025A829AA040  0x0000025A829A8040  4000    0   0   0   1
0x000002592E8A0040  1   1048597 7   HIDDEN ONLINE   1   1   3045    0   2940803 0   0   1   0   0   0   0   5871356 10880866319 0   0x00000259D13D6160  0x0000025A864FE040  0x00000259D13D2040  4000    0   0   0   1
0x000002595F9A0040  0   1048598 0   HIDDEN ONLINE   1   1   1173    0   1000858 0   0   1   0   0   0   0   1990374 10880866319 0   0x00000259D0E64160  0x0000025A84BFE040  0x00000259D0E52040  4000    0   0   0   1
0x00000259C0E80040  1   1048599 8   HIDDEN ONLINE   1   1   2070    0   2225588 0   0   1   0   0   0   0   4440225 10880814028 0   0x0000025A830BA160  0x0000025A830F4040  0x0000025A830B6040  4000    0   0   0   1
0x00000259C0EA0040  1   1048600 9   HIDDEN ONLINE   1   1   2337    0   2287844 0   0   1   0   0   0   0   4564136 10880866335 0   0x0000025A830D2160  0x0000025A830C2040  0x0000025A830C4040  4000    0   0   0   1
0x0000025A715C0040  0   1048601 1   HIDDEN ONLINE   1   1   2939    0   3071534 0   0   1   0   0   0   0   6134000 10880866319 0   0x0000025A47534160  0x00000259D1544040  0x00000259D1548040  4000    0   0   0   1
0x0000025A715E0040  0   1048602 2   HIDDEN ONLINE   1   1   2093    0   2161732 0   0   1   0   0   0   0   4312590 10880176229 0   0x0000025A4751E160  0x0000025A4751A040  0x0000025A47500040  4000    0   0   0   1
0x00000259240E0040  0   1048603 3   HIDDEN ONLINE   1   1   1518    0   1618964 0   0   1   0   0   0   0   3226729 10880866319 0   0x0000025A79FC2160  0x0000025A47508040  0x0000025A4752A040  4000    0   0   0   1
0x000002592E8E0040  1   1048604 10  HIDDEN ONLINE   1   1   1897    0   1558224 0   0   1   0   0   0   0   3105359 10880866319 0   0x00000259D13F6160  0x00000259D13E0040  0x00000259D13EA040  4000    0   0   0   1
0x0000025926DC0040  0   1048605 4   HIDDEN ONLINE   1   1   1875    0   2196012 0   0   1   0   0   0   0   4380991 10880176213 0   0x0000025A74E1C160  0x0000025A74E2E040  0x0000025A74E0A040  4000    0   0   0   1
0x0000025A86580040  1   1048606 11  HIDDEN ONLINE   1   1   1189    0   984507  0   0   1   0   0   0   0   1957588 10880566076 0   0x00000259D14C6160  0x00000259D14E2040  0x00000259D14F6040  4000    0   0   0   1
0x000002592A640040  1   1048607 6   HIDDEN ONLINE   1   1   1882    0   1544327 0   0   1   0   0   0   0   3077654 10880866319 0   0x0000025A82A42160  0x0000025A82A2C040  0x0000025A82A3A040  4000    0   0   0   1
0x0000025926DE0040  0   1048608 5   HIDDEN ONLINE   1   1   1308    0   1039508 0   0   1   0   0   0   0   2067713 10880814028 0   0x0000025A74E7E160  0x0000025A74E04040  0x0000025A74E02040  4000    0   0   0   1
0x000002595F9C0040  0   1048609 0   HIDDEN ONLINE   1   1   2829    0   2412064 0   0   1   0   0   0   0   4813658 10880866319 0   0x00000259D0E7C160  0x00000259D0E3A040  0x00000259D0E2E040  4000    0   0   0   1
0x000002595F9E0040  0   1048610 1   HIDDEN ONLINE   1   1   2379    0   2192673 0   0   1   0   0   0   0   4375512 10880866335 0   0x0000025A856EE160  0x00000259D0E42040  0x0000025A85690040  4000    0   0   0   1
0x0000025960440040  1   1048611 7   HIDDEN ONLINE   1   1   1386    0   1352646 0   0   1   0   0   0   0   2693439 10880866319 0   0x0000025A85842160  0x0000025A85854040  0x0000025A85858040  4000    0   0   0   1
0x0000025A86600040  0   1048612 2   HIDDEN ONLINE   1   1   3015    0   2869906 0   0   1   0   0   0   0   5729329 10880176229 0   0x0000025A6D2CE160  0x0000025A6D2F4040  0x0000025A6D2A8040  4000    0   0   0   1
0x0000025960460040  1   1048613 8   HIDDEN ONLINE   1   1   1610    0   1571447 0   0   1   0   0   0   0   3131717 10880866335 0   0x0000025A85862160  0x0000025A85864040  0x0000025A8584A040  4000    0   0   0   1
0x000002592A620040  1   1048614 9   HIDDEN ONLINE   1   1   3448    0   3469913 0   0   1   0   0   0   0   6929661 10880176213 0   0x0000025A82A4E160  0x0000025A82A46040  0x0000025A82A48040  4000    0   0   0   1
0x0000025A86620040  0   1048615 3   HIDDEN ONLINE   1   1   3332    0   3070622 0   0   1   0   0   0   0   6130958 10880176213 0   0x0000025A6D298160  0x0000025A6D2BC040  0x0000025A6D2E6040  4000    0   0   0   1
0x0000025A86700040  0   1048616 4   HIDDEN ONLINE   1   1   2185    0   1890908 0   0   1   0   0   0   0   3770898 10880866335 0   0x0000025A856E4160  0x0000025A856F0040  0x0000025A85686040  4000    0   0   0   1
0x0000025A86680040  1   1048617 10  HIDDEN ONLINE   1   1   2   0   15639   0   0   1   0   0   0   0   19194   10880884265 0   0x0000025A861F4160  0x0000025A861B8040  0x0000025A861EC040  4000    0   0   0   1
0x0000025A866A0040  1   1048618 11  HIDDEN ONLINE   1   1   3   0   14081   0   0   1   0   0   0   0   16078   10880884202 0   0x00000259D07F2160  0x0000025A861FE040  0x00000259D0668040  4000    0   0   0   1

sys.dm_os_nodes:

node_id node_state_desc memory_object_address   memory_clerk_address    io_completion_worker_address    memory_node_id  cpu_affinity_mask   online_scheduler_count  idle_scheduler_count    active_worker_count avg_load_balance    timer_task_affinity_mask    permanent_task_affinity_mask    resource_monitor_state  online_scheduler_mask   processor_group cpu_count
0   ONLINE  0x0000025918032040  0x000002591802A640  0x0000025918600160  0   63  6   6   23  3   45  60  1   63  0   6
1   ONLINE  0x0000025918604040  0x000002591802AD90  0x0000025919080160  0   4032    6   6   16  1   64  0   1   4032    0   6
64  ONLINE DAC  0x0000025919106040  0x000002591802B4E0  0x00000259186B8160  64  0   1   1   1   0   0   0   0   63  0   6

sys.configurations:

configuration_id    name    value   minimum maximum value_in_use    description is_dynamic  is_advanced
1535    affinity mask   0   -2147483648 2147483647  0   affinity mask   1   1

DBCC tracestatus: No table is printed, so I assume this is prove that no trace is running?

0

1 Answer 1

10

But still a symptom that I'm seeing is, that when instance 1 is running an intensive set of queries, the CPU is running on 50%, single cores are using 100% / very little CPU, alternating

While this in itself is alright, at that time there is instance 2 which suddenly has terrible performance and is even losing connection to the database.

The crux of the issue has to do with cpu scheduling and thread affinity in Windows and SQL Server.

  • There are two instances of SQL Server, neither knows about the other.
  • There are two sockets, SQL will set thread level affinity per socket (since no other trace flags are present).
  • SQL Server only presents threads to the OS to be scheduled, it does not control scheduling of them.
  • Windows schedules as it sees fit.
  • Virtualization skew due to cpu scheduling and processing abilities can come into play, such as cpu ready and co-stop in VMWare.
  • MAXDOP is set to 6, which is an entire socket. SQL Server will not typically span sockets or NUMA nodes to place parallel threads.

Without getting into the murky waters of parallel thread placement, it's a good assumption that it'll be placed on the set of threads local to the connection. If the connection originally came in on cpu 3, then it would be local to socket 0, which would set the thread placement to socket 0 affinitized threads (in this case 0 - 5). That's essentially where your 50% cpu usage comes from... SQL server is running the parallel query, the threads are set in SQL Server Instance 1 as available to run to the OS, the OS schedules them at some point to run.

This happens many times per second, any hits on a wait will cause the thread to not be scheduled as it'll be in an indefinite wait state according to the OS. Since these aren't the only threads to be scheduled as other items need to also run (such as Instance 2, services, etc.) and that may be on threads affinitized to the same set of processors. They may not get time to run or may not run to completion due to other factors (such as ISRs, DPCs, APCs, etc.).

If SQL Server Instance 1 runs an intense workload with many threads on the same cores that Instance 2 also wants, they both can't run at the same time. Additionally, the OS doesn't know anything about SQL, any possible timeouts, errors, or anything else, so it schedules threads as it sees fit. Assume Instance 1 runs a query that uses parallelism where the underlying implementation is a tight loop that does not internally yield to SQL OS (SQL Server implements a cooperative scheduling environment, vs Windows which is not). Assume that there are no other waits (disk, memory, network, etc.) on these threads. The threads may be scheduled for multiple runs on a cpu before threads from Instance 2 are run, again that's up to Windows. Assume instance 2 is running a thread that requires memory allocation, that thread may ask for an allocation from the Windows memory manager which in turn may need to wait for concurrency lock before continuing further. This would place that thread in a waiting state and be context switched off the cpu. Additionally assume that said thread had a timeout of 15 seconds. The timeout might be hit before the lock could be granted to that thread, which would only put the thread as alerted (according to Windows) and allow it to be scheduled which might be behind a bunch of other threads, such as the hidden schedulers in the instance, the other instance, incoming network packets, video rendering, disk completions, etc.

Is this a contrived example? Yep, for the fact of giving an easy to follow example.

This doesn't even bring in hypervisors, how they schedule, cpu scheduling drift allowed, etc., such as is possible in VMware. Nor does it bring into account any issues from the administrators of said virtualization platform who may be oversubscribing the host.

Example of Thread Affinity in SQL Server:

FROM sys.dm_os_threads  t
    inner join sys.dm_os_schedulers s
        on t.scheduler_address = s.scheduler_address
+----------------+--------------+----------------+--------------+-----------+
| parent_node_id | scheduler_id | status         | os_thread_id | affinity  |
+----------------+--------------+----------------+--------------+-----------+
| 0              | 0            | VISIBLE ONLINE | 6156         | 63        |
| 0              | 0            | VISIBLE ONLINE | 6160         | 63        |
| 0              | 0            | VISIBLE ONLINE | 2464         | 63        |
| 0              | 0            | VISIBLE ONLINE | 5396         | 63        |
| 0              | 0            | VISIBLE ONLINE | 2052         | 63        |
| 0              | 0            | VISIBLE ONLINE | 3468         | 63        |
| 0              | 0            | VISIBLE ONLINE | 1456         | 63        |
| 0              | 0            | VISIBLE ONLINE | 5744         | 63        |
| 0              | 0            | VISIBLE ONLINE | 1884         | 63        |
+----------------+--------------+----------------+--------------+-----------+

This is why:

  • I wouldn't have more than a single instance on a server
  • If you're going to have more than one instance on the same server, set cpu affinity properly
  • Not oversubscribe Hosts
  • Give SQL Server Guests reserved cpu, memory, and disk allocations in the hypervisor
  • Assume SMT cores aren't full execution units
2
  • Hey Sean, thank you for this insight! I do realize that this is not what you would call proper cpu affinity, but I'm thinking about just configuring one socket for this VM. That way the resources could be properly shared (and evenly used) over the instances. Reason being that at this moment, I'm not able to assign cores exclusively to instances without impacting performance. Again, I'm sure this is not what you were going for - but do you see any ciritical problems that would occur with that solution?
    – hcm
    Jan 10 at 16:44
  • I believe you're missing the main problem which is that there is no such thing as "shared and used evenly" when you have multiple applications asking for all the cpu available layered on top of each other. Making it one socket won't generically fix the issue. If there isn't enough CPU to dedicate because it'll cause perf issues means this is already broken. Make another vm, move instance2 over to it, give each VM appropriate resources. Jan 10 at 17:24

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.