How to Know When Memory Constraints are Negatively Impacting CPU in SQL Server? – Interview Question of the Week #216

Question: How can memory constraints interfere with SQL Server workers? A scheduler with failed_to_create_worker = 1 could not create a new worker. Memory constraints are a common cause, but this one flag does not diagnose every memory problem or prove the cause of high CPU.

A prospective performance-health-check client asked me how memory problems could affect CPU work. It was a loaded question, and the practical diagnostic I shared was this short query:

SELECT current_workers_count, scheduler_id, cpu_id,
       status, is_online, failed_to_create_worker
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY scheduler_id;

Look at failed_to_create_worker for the visible online schedulers. A value of 1 is evidence of a worker-creation failure on that scheduler. Microsoft describes memory constraints as the usual cause. That can restrict SQL Server’s ability to get work running even when processors exist.

current_workers_count is the number of workers associated with the scheduler, including workers with no assigned task. It isn’t a CPU percentage or a direct measurement of free memory.

My original conclusion was too certain: the flag does not tell us “exactly when memory pressure negatively impacts CPU.” It tells us about this particular failure. A value of 0 does not exclude other memory pressure, and a value of 1 does not establish that the server’s overall CPU utilization must be high.

During a health check, correlate the failure with the SQL Server error log, process and system memory observations, task queues and the workload at the same time. The result is a starting clue, not a reason to change memory or worker limits blindly.

The query requires the appropriate server-state permission; SQL Server 2022 and later uses VIEW SERVER PERFORMANCE STATE. Scheduler rows and counts change with activity, so no fixed result values are promised here.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.