SQL Server – Fix 100% CPU Usage on NUMA Node 0

numasql servervmwarewindows-server

The previous Senior DBA left the company and I was made aware of a server which suffers from multiple problems, largely slowness (all the way from SSMS taking a long time to open, long running queries, and failing SSIS jobs (Data Warehouse), to difficulties connecting to linked servers (150+ of them)).

There is most likely way too much happening on this server which contains 5 stacked instances. A new Senior DBA will be joining the team soon but it would be nice to have this all straightened out for him or her by the time they join.

So, the nitty-gritty:

When I was made aware of the issue I found that 94% of the server's memory was allocated to SQL Server. I went ahead and brought that down to 85% by deallocating memory from two instances which were overprovisioned.

I then noticed that MAXDOP on our default instance was set to 4 (possibly 6, I don't recall) along with a CPU Affinity setting. Those CPUs were pegged while there was minimal activity on the others. I went ahead and removed the Affinity setting (since these settings were in place before additional CPUs were added). I set MAXDOP to 20 across all 5 instances.

Currently, I'm still seeing 4 of the CPUs pegged but with an overall avg. usage (across all CPUs) of roughly only 25%.

I've used SysInternal's ProcExp, the Resource Monitor, and the Windows Performance Toolkit to observe the issue but don't really know how to isolate what process(es), specifically, is(are) the root cause. Any recommendations / guidance on how to truly isolate what's happening here? (i.e. specific counters / traces / other programs.)

UPDATE, per requests:

System Info:
Windows Server 2012 R2 Standard
64 GB memory total
20 CPUs

Configuration:
24 GB memory allocated to this instance
26.5 GB memory allocated to other instances (total of 50.5 GB – 78.9%)
Cost Threshold for Parallelism = 50 (across all instances)

I disabled an unused SSAS process.

Best Answer

So you've got 5 'stacked' instances on a single Windows server. You haven't said exactly how many sockets/CPUs are available and how much memory though. I like setting affinity for each instance in such cases, even if I decide to have CPUs overlapping in the struggle to balance the overall CPU load (depends on each instance's load).

Any instance with more than 4 CPUs could use an explicit DOP setting in my experience - rarely over '4' in stacked cases like yours. Don't forget to set 'Cost threshold of Parallelism' for each instance to something reasonable (50?) to avoid excessive parallelism - in your case this is even more important.

Remember that the memory left "for the OS" should be more now, since you have to account for the footprint of each instance (on top of SSIS etc). Check in SQL Config Mgr if SSAS is also running and adjust its 'max memory' accordingly, by default it goes for 80% of the whole server memory (!)

Also maybe worth taking away and 'Lock pages in memory' rights of the SQL Service account(s) so that the OS can breath and do its job better (if it pages, everyone suffers!). Also good practice is to set some reasonable 'min memory' for each instance.

I think running sp_blitz and sp_blitz_first on each instance would give you some quick pointers on more pressing issues.

You may also want to monitor some windows permon counters like 'available memory' and 'working set' for each of the processes running there in case you find particular times of the day/night when the server is suffering.

Related Solutions

Sql-server – Low PLE on NUMA node 000, high on 001

If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.

SQL Server – NUMA Nodes, MAXDOP, and PLE Explained

SQL SERVER 2005 SP3

Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO: 9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014.

You can use my script to help you suggest a good MAXDOP value.

Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

SQL server is NUMA aware. This means that SQL server knows what NUMA node the processors are in and what NUMA node the memory is in. Due to this, SQL server will assign worker thread on correct NUMA node for the data that you want to access.

Refer to :

Below are some queries that will help you find out if there is an imbalance of schedulers between the NUMA nodes - which can lead to significant performance problems under load :

SELECT 
  parent_node_id,
  scheduler_id, 
  [cpu_id], 
  is_idle, 
  current_tasks_count, 
  runnable_tasks_count, 
  active_workers_count, 
  load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE';

--- check the current tasks, runnable tasks, active workers and avg load factor COUNT ...
SELECT 
  parent_node_id, 
  SUM(current_tasks_count) AS current_tasks_count, 
  SUM(runnable_tasks_count) AS runnable_tasks_count, 
  SUM(active_workers_count) AS active_workers_count, 
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;

You should monitor SQL Server:Buffer Node and SQL Server, Memory Node as opposed to just PLE - monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node).

exhausting worker threads

IF you set the MAXDOP setting as default (which is 0) then it could lead to worker thread starvation.

Best Answer

Related Solutions

Sql-server – Low PLE on NUMA node 000, high on 001

SQL Server – NUMA Nodes, MAXDOP, and PLE Explained

Related Question