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.