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.