SQL Server 2022 performance degradation (2x slower) with increased Context Switches/sec under Compatibility Level 110 after migration

k.a 0 Reputation points
2026-09-30T14:42:05.9633333+00:00

Hi,

Please note that this message was translated using Generative AI. I apologize in advance for any unnatural phrasing or translation errors.

We recently migrated our database from SQL Server 2017 to SQL Server 2022 on a new virtual machine. After the migration, a specific query takes about twice as long to execute on the new server compared to the old server.

We have conducted extensive troubleshooting but have not been able to pinpoint the root cause. We would highly appreciate your insights.

[Environment & Configuration (Identical on both Old and New)]

  • Platform: VMware vSphere VM
  • CPU: 4 vCPUs (CPU model, clock speed [2.0 GHz], and vNUMA/Cores per Socket configuration are completely identical)
  • OS: Windows Server (Power plan set to "High Performance", Processor scheduling set to "Background services")
  • SQL Server Configuration: Compatibility Level is set to 110 (SQL Server 2012) on both servers. MAXDOP is identical. Soft-NUMA is confirmed OFF (softnuma_configuration = 0) and the online node count is identical.

[Symptoms & Troubleshooting Results]

  • Symptoms: The query execution time is 2x slower on SQL Server 2022. While running the query, total CPU utilization is low, but the CPU load is unevenly distributed, with a single core handling most of the load (though not reaching 100% utilization). We are also observing the SOS_SCHEDULER_YIELD wait type during execution.
  • PerfMon Metrics: We captured Performance Monitor logs on both servers. System\Context Switches/sec on the new server has approximately doubled compared to the old server.
  • Other Metrics:
    • Processor\% Privileged Time is NOT high (remains low, so it's not an OS/driver-level issue). 
          - `sys.dm_os_spinlock_stats` shows no significant spinlock contention (e.g., `LOCK_HASH` backoffs are only around a few thousands, not millions).
    
    
             - CPU Ready Time on the VMware host is 0%, and the host has plenty of hardware resource headroom.
    
             
                      - **Tested Actions (No Improvement):**
    
                      
                                  - Ran `UPDATE STATISTICS ... WITH FULLSCAN` on all relevant tables.
    
                                  
                                                 - Tested query hints: `OPTION (MAXDOP 1)`, `OPTION (QUERYTRACEON 9481)`, `OPTION (QUERYTRACEON 4199)`, `OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'))`, and `OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110'))`. None of them resolved the increased context switches or the execution time.
    ```Since all hardware specs, OS settings, and database configurations are identical, we suspect this might be related to a behavioral change in the SQL Server 2022 thread scheduler engine when running under an old compatibility level (110).

Has anyone experienced a similar issue where Context Switches/sec increases by about 2x only on SQL Server 2022 under Compatibility Level 110? Any suggestions on what to check next would be greatly appreciated.

Thank you!Hi,

Please note that this message was translated using Generative AI. I apologize in advance for any unnatural phrasing or translation errors.

We recently migrated our database from SQL Server 2017 to SQL Server 2022 on a new virtual machine. After the migration, a specific query takes about twice as long to execute on the new server compared to the old server.

We have conducted extensive troubleshooting but have not been able to pinpoint the root cause. We would highly appreciate your insights.

[Environment & Configuration (Identical on both Old and New)]

  • Platform: VMware vSphere VM
  • CPU: 4 vCPUs (CPU model, clock speed [2.0 GHz], and vNUMA/Cores per Socket configuration are completely identical)
  • OS: Windows Server (Power plan set to "High Performance", Processor scheduling set to "Background services")
  • SQL Server Configuration: Compatibility Level is set to 110 (SQL Server 2012) on both servers. MAXDOP is identical. Soft-NUMA is confirmed OFF (softnuma_configuration = 0) and the online node count is identical.

[Symptoms & Troubleshooting Results]

  • Symptoms: The query execution time is 2x slower on SQL Server 2022. While running the query, total CPU utilization is low, but the CPU load is unevenly distributed, with a single core handling most of the load (though not reaching 100% utilization). We are also observing the SOS_SCHEDULER_YIELD wait type during execution.
  • PerfMon Metrics: We captured Performance Monitor logs on both servers. System\Context Switches/sec on the new server has approximately doubled compared to the old server.
  • Other Metrics:
    • Processor\% Privileged Time is NOT high (remains low, so it's not an OS/driver-level issue). 
          - `sys.dm_os_spinlock_stats` shows no significant spinlock contention (e.g., `LOCK_HASH` backoffs are only around a few thousands, not millions).
    
    
             - CPU Ready Time on the VMware host is 0%, and the host has plenty of hardware resource headroom.
    
             
                      - **Tested Actions (No Improvement):**
    
                      
                                  - Ran `UPDATE STATISTICS ... WITH FULLSCAN` on all relevant tables.
    
                                  
                                                 - Tested query hints: `OPTION (MAXDOP 1)`, `OPTION (QUERYTRACEON 9481)`, `OPTION (QUERYTRACEON 4199)`, `OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'))`, and `OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110'))`. None of them resolved the increased context switches or the execution time.
    ```Since all hardware specs, OS settings, and database configurations are identical, we suspect this might be related to a behavioral change in the SQL Server 2022 thread scheduler engine when running under an old compatibility level (110).

Has anyone experienced a similar issue where Context Switches/sec increases by about 2x only on SQL Server 2022 under Compatibility Level 110? Any suggestions on what to check next would be greatly appreciated.

Thank you!

SQL Server Database Engine

1 answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-09-30T14:42:37.2933333+00:00

    The next checks should focus on proving whether this is truly a SQL Server 2022 scheduler change, or whether the regression is caused by environment differences or a changed execution plan despite the same compatibility level.

    1. Confirm the environments are comparable beyond the items already checked.
      • Compare physical memory available to SQL Server.
      • Compare CPU count, socket/core topology, and reported processor clock speed.
      • Reconfirm the Windows power plan is High performance on both servers.
      • Compare VMware configuration and host contention characteristics.
      • Compare storage layout for data, log, and tempdb, plus IOPS, throughput, and latency under a comparable workload.
      • Compare SQL Server version/build, database compatibility level, server configuration, and database-scoped configuration.
      To compare reported CPU clock speed on both servers: 
         Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
      If clock speed or benchmark behavior is not comparable, investigate power plan, CPU allocation, virtualization layer, and host configuration first.
    2. Verify the exact source and target engine details. Run this on SQL Server 2022 and compare with the old SQL Server 2017 instance: 
         SELECT
       SERVERPROPERTY('Edition') AS edition,
       SERVERPROPERTY('EngineEdition') AS engine_edition,
       SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('ProductLevel') AS product_level;
   
   SELECT
       name,
       compatibility_level
   FROM sys.databases
   WHERE name = DB_NAME();
      If the SQL Server version/build or compatibility level differs in any way, the regression should not be attributed to edition or scheduler behavior alone.
    3. Treat SOS_SCHEDULER_YIELD as evidence of CPU contention or worker pressure, not as proof of a scheduler defect. Check whether waits are accumulating significantly during the regression: 
         SELECT
       wait_type,
       waiting_tasks_count,
       wait_time_ms
   FROM sys.dm_os_wait_stats
   WHERE wait_type IN
   (
       'SOS_SCHEDULER_YIELD',
       'THREADPOOL'
   )
   ORDER BY wait_time_ms DESC;
      • Large or rapidly increasing SOS_SCHEDULER_YIELD waits indicate CPU contention.
      • THREADPOOL indicates worker exhaustion.
    4. Compare actual execution plans for the affected query between SQL Server 2017 and SQL Server 2022. Even when the compatibility level remains 110, an engine change can still result in different execution plans. Inspect for:
      • additional scans
      • different join types
      • increased or reduced parallelism
      • increased estimated cost
      • large cardinality estimation errors
      Since only one specific query is slower, plan comparison is the highest-value next step.
    5. If the regression is limited to a few queries, test whether reverting the database to the previous compatibility behavior helps isolate optimizer-related regression. A documented approach after upgrade is to keep the old compatibility level first, enable Query Store, then move to the newer compatibility level and use Query Store to identify regressed queries or force the older plan as a short-term mitigation. In this case, compatibility level 110 is already being used, so the practical implication is: use Query Store and compare the regressed query’s plan and runtime behavior rather than assuming the scheduler is the root cause.
    6. If considering legacy CE testing, use only the supported scope and note the risk. For SQL Server 2016 SP1 and later, query-level legacy CE can be tested with: 
         SELECT * FROM Table1
   WHERE Col1 = 10
   OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
      Database-level legacy CE can be tested with: 
         ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
   
   SELECT name, value
   FROM sys.database_scoped_configurations
   WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
      Risk: database-level or server-level CE changes affect all queries in scope, and queries that perform better with the default CE can regress.
    7. If the query is repeatedly executed and spills or estimate errors are present, inspect memory grant behavior and Query Store feedback records. For SQL Server 2022, compare estimated versus actual rows, join order, join types, and DOP. If Query Store is enabled, check sys.query_store_plan_feedback for CE Feedback or DOP Feedback. A plan-shape difference alone does not prove that a feedback feature caused the regression.

    The strongest supported conclusion is that the doubled Context Switches/sec does not, by itself, establish a SQL Server 2022 scheduler regression. The next supported path is:

    • validate environment comparability again, especially storage and reported CPU characteristics
    • compare exact engine/build details
    • compare actual execution plans for the affected query
    • use Query Store to identify whether the query regressed because of plan differences or feedback behavior

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.