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

Question: How to Know When Memory Constraints are Negatively Impacting CPU in SQL Server?

Answer: the other day, I was asked this question when someone wanted to sign up for my
Comprehensive Database Performance Health Check. I indeed thought it was a very much loaded question as I have been never asked this question before.

Let me just tell you what essentially user is asking that when Memory Pressure or Memory issue is actually affecting CPU so bad that it is together negatively affecting the SQL Server Performance.

So before I answer this question, let me ask you back, do you know the answer to this question? If not, please bookmark this blog post as you are going to learn something new and you can use this script for your business in the future.

Now run the following query:

SELECT current_workers_count, scheduler_id, cpu_id,
status, is_online, failed_to_create_worker
FROM sys.dm_os_schedulers
WHERE status='visible online'
GO

Now look at the results of the above query. If you see the column failed_to_create_worker as 1, that indicates that when there is a memory constraint, SQL Server failed to create a new worker thread for that particular schedule. There you go, you now know exactly when Memory pressure negatively impacts CPU performance, which eventually leads to poor performance of your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)