14,494 questions
Most of your spikes appear to coincide with page faults. 4GBs is not enough for the OS and other services to run. I would suggest you change your max server memory to 110GB and reevaluate.
SQL Server 2019 High Number of Context Switches
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 33%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJM%3C/text%3E%3C/svg%3E)
Johannes Maly
101
Reputation points
I'm observing a quite high number of context switches on a SQL Server 2019 Enterprise. The SQL Server instance is part of an always on group with 3 nodes (one of them is a read only node). The instance where I'm observing the high number of context switches acts the the primary node. The secondary replica runs in synchronous commit mode while the read only replica runs in asynchronous commit mode.
The server version reports as:
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
The SQL Server runs as a Windows Server 2019 standard addition hosted in a virtual machine in VMWare with 18 cores and has 124 GB out of 128 GB memory assigned.
Max degree of parallelism is set to 8 while cost threshold for parallelism is set to 100.
SQL Sentry is used for monitoring and shows a high number of context switches while CPU load is moderate and number of waits is low.
Can anybody explain how I can find out what exactly is causing this "strange" behaviour?
SQL Server Other
{count} votes
-
Johannes Maly 101 Reputation points
2022-03-14T15:16:55.817+00:00 Perfmon
-
Johannes Maly 101 Reputation points
2022-03-14T15:19:11.33+00:00 SQL Sentry One
-
Johannes Maly 101 Reputation points
2022-03-14T15:19:53.377+00:00 SQL Sentry One
Sign in to comment
Accepted answer
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,771 Reputation points2022-03-14T16:13:53.113+00:00 -
Johannes Maly 101 Reputation points
2022-03-14T17:00:37.95+00:00 Thank you for your fast response.
But to be honest I can't see a direct coincidence with page faults.
The node in question is using about 122 GB out of the 128 GB of available memory.
But I will give it a try and reduce the memory that is assigned to the SQL Server for your suggested limit of 110 GB.Best Regards
Johannes
Sign in to comment -
1 additional answer
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff2022-03-15T06:00:22.893+00:00 Hi JohannesMaly-3567,
Constantly high values of Context Switches/sec and Processor: % Processor Time are a clear indication of processor bottlenecks. Please refer to Troubleshoot high-CPU-usage issues in SQL Server which might be helpful.
And for Max Degree of Parallelism, please refer to the recommended value in the doc and you can do some tests to see if higher or lower settings are better for your workload.
Please also check this article about tuning cost threshold for parallelism.Best Regards,
Amelia
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.-
Johannes Maly 101 Reputation points
2022-03-15T07:59:16.17+00:00 Thank you for your answer.
I think CPU load is moderate and tolerable. It is far from constant 100%.
The configuration for max degree of parallelism could maybe improve the overall workload. But this is a bit tricky on a production server.
But for sure I will follow the links you provided for deeper understanding of how to configure this.Best Regards
Johannes
Sign in to comment -