SQL Server : Memory pressure,Spiked user connections and CPU usage I have the below sql server environment.


SQL Server 2016 standard edition.
128 GB  RAM
96 GB alllocated to SQL Server
8 tempdb's with 1 GB each.
Have a major database with 2.5 TB size.
Reporting also done from the same sql instance.Reporting is minimum


There is memory pressure as i see that my plan cache is getting created periodically.Also i have noticed the flag which shows memory pressure when i queries ring buffer.
Also i see tempdb memory spill,huge memory grant issues when i see the execution plan of slow query at times.
Most of the read operations coming to the database has complex queries and to help with performance,the read operations are run in parallel with the below settings.


MAX DOP : 8
Cost Threshold for Parallelism = 20
40 Logical CPUs


I have been trying to improve performance and i was able to achieve it to some extend.But some days server becomes slow and the transactions(read mostly) take like 30 sec or even 1 minute to finish.Since most of the read transactions are running in parallel,i see the CXCONSUMER wait time and for the transactions and also this is making other queries also to wait for the resources. 

I am confused where i should start to get things under control.
Do i need more memory to support to 20 times bigger data here.?

Additional info:
I use the monitoring tool grafana from TICK stack,to see the CPU usage and other performance counters.Sometimes,i see the COU usage going really high and along with it number of user connections going really high.The fact is those are not real users(when i checked with the team) and some other process is causing this.
I am not sure,if some locking mechanism can cause this kind of issues were user connections spike up,also CPU usage.
The threshold for parallelism should be higher than 20 for very large database. 

Memory pressure can be avoided by tuning query/index, rebuilding fragmented index, enabling TF 2371 at database level to fire auto update statistics frequently.

As a DBA you can add multiple file group to your very large database, each containing separate mdf file (located on different disk drive) and move larger table on newly added file group to get higher disk IO.