The error message “Worker appears to be non-yielding on Scheduler” in SQL Server typically indicates a performance issue where a worker thread is stuck and not yielding CPU time back to the scheduler. This can lead to performance degradation, blocking, or even system instability.

Possible Causes of the Non-Yielding Scheduler Issue

Long-Running Query or Blocking Issues
Deadlocks or Latching Contention
Parallelism & CXPACKET Waits
Hardware or OS Bottlenecks
Resource Governor or Query Store Issues
Resource limits restricting execution.
Bug in SQL Server or Bad Indexing

We have not noticed any other error in OS and SQL error log. We also checked CPU and memory utilization on the server and found it was normal. But we identified there was a query which was in running stage since long time.Index and statistics were in good shape but we found CU was not up-to-date on the server so we decided to upgrade CU to N-1 level. Which is our default approach to apply in patches on production server.

We have downloaded CU30 of SQL Server 2019 from https://sqlserverupdates.com and tried to apply the patch on the server but we faced issue ” The Database Engine system data directory in the registry is not valid “.

Cause of the issue

1. Change of storage drives for database and log files and missing to update the default data and log location.
2. A misconfiguration of the default data or log path during initial installation in the Data Directories tab (see Database Engine Configuration – Data Directories page).
3. A misconfiguration of the default data or log path under Database Settings in SQL Server Management Studio (see View or Change the Default Locations for Data and Log Files).

Resolution

1. Update the correct data and log file in database settings
2. Update the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\.

In our case database team has modified data and log file path on the server at the time of server standardisation.

We need to validate data file location in instance property (Right click on the instance Go to property Database settings) and SQL server configuration manager Data Path( SQL server configuration manager > Advance Data path

Make sure data path belongs to the same location and they must exists on the server. If the data path shown in instance property is not valid then copy the valid data and log file path and update in instance property.

If data file is not SQL server configuration manager data drive then we need to change it from registry.In my case data file directory which was showing in SQL server configuration manager which was “E:\MSSQL15.MSSQLSERVER\MSSQL\DATA”. This path was not a valid path on the server. The correct path was “E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data”. We need to modify the path is sql registry.

We need to make sure that all the server path in registry must have valid path. I have modified different path in registry like SQL data path , SQL log path, Backup path,  SQL jobs Path in my case.

I also encountered an issue while opening SQL server configuration manager.

I was getting below error message at the time of opening SQL Server Configuration Manager.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. 

Open cmd with ADMIN go to C:\Program Files (x86)\Microsoft SQL Server\140\Shared

Note: my SQL Server version was SQL Server 2017.
  mofcomp sqlmgmproviderxpsp2up.mof