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.

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

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)

, , , ,
Previous Post
What is Clean Buffer in DBCC DROPCLEANBUFFERS? – Interview Question of the Week #215
Next Post
Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216

Related Posts

4 Comments. Leave new

  • Hi Dave- The query is missing a comma after cpu_id. Had me confused for a minute. It’s valid syntax but it’s causing unintended aliasing. Thanks for the article.

    Reply
  • Tim Cartwright
    March 18, 2019 8:31 pm

    Pinal, I believe you are missing a comma after cpu_id in your query. Should it no be:

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

    Reply

Leave a Reply

Menu