SQL Server 2012 NUMA Node Imbalance – Cont’d

Yesterday, I had posted an issue I been troubleshooting with memory allocation.  That was causing CPU to pin, you can read more about the issue at SQL Server 2012 NUMA Node Imbalance.  Even though I found what was causing it, I didn’t understand the internals well enough to identify the root cause.

Additional testing I discovered this issue is repro on Windows 2008 R2 Server with SQL Server 2012.  It does not seem to fixed that at 4000MB the issue will start; if server is busy, then it can start at a higher value.  In my re-pro test, I found it starting at even 6000MB.

As I said the up front memory allocation and management was contributing to this, however now I am unsure if Locked Pages in Memory is to contribute to this.

However, further insight I learned today.  If this issue exists, you might have Lazy Writer spinning continuously on the effected NUMA node.

Therefore if you execute following T-SQL:

select session_id, wait_type, wait_time, cpu_time, scheduler_id
  from sys.dm_exec_requests
where command = 'lazy writer'
order by scheduler_id

Look at the output generated, if your Lazy Writer thread is working as normal (aka, only wakes up sometimes) you should see something similar to following:

However if you have this issue the Lazy Writer might look like below:

• Notice the wait_type has gone to NULL.
• Notice wait_time has gone to 0 – signal it is not going to sleep like the rest.
• Notice cpu_time keeps climbing.

Only work around that I know for this currently is, increase the max memory until you see lazy writer stop spinning and processor utilization starts dropping.

Comments

• Anonymous
  October 03, 2015
  Most interesting case, and continuing forensics,,, keep going.

• Anonymous
  October 03, 2015
  Thank, it was rather interesting issue.  I am continuing additional investigation and going to try to setup a lab to see if I can reproduce it.  So far combination seems to be very specific AMD Processors + Windows 2008 R2 + SQL Server 2012 + Locked Pages In Memory + Low Max Memory Setting.  In further testing I have found Low Max Memory setting can be low as 6000MB and it will still cause Lazy Writer to spin, because it all depends on how busy server is.  I'll be posting a concluding post later today to summarize all my findings.