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:
![clip_image002[4]](https://mostafaelmasry.files.wordpress.com/2015/10/clip_image0024.jpg "clip_image002[4]")
- 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.
DB Cloud TECH 