1

I have the below sql server environment.

  1. SQL Server 2016 standard edition.
  2. 128 GB RAM
  3. 96 GB alllocated to SQL Server
  4. 8 tempdb's with 1 GB each.
  5. Have a major database with 2.5 TB size.
  6. 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.

|improve this question|||||
  • 1
    I think 128 is maximum you can have for SQL2016 standard edition. Do you have any other services like SSRS, SSAS, SSIS also running? Because why not MAX memory be somewhere 110 if possible, did you tried tuning that up? – BeginnerDBA Sep 13 '19 at 20:52
  • I used 105 earlier,bit i didn't see much improvement. Also i worry that if i make 110 ,then if OS needs memory,SQL server has to surrender its memory and that will be causing more memory pressure.I am not sure though as i am not an expert. – user9516827 Sep 13 '19 at 20:57
  • 1
    How about the setting for Power plan? Generally that should be High performance and not default balanced. Please check that as well – BeginnerDBA Sep 13 '19 at 21:03
  • 1
    Also i would say start with metrics of various counters for CPU, memory and disk during the time of issue. May be you have opportunity in tuning up the queries where indexing needs more strategy Check the execution plans for your worst top 10 of those queries and find whats going wrong with them. – BeginnerDBA Sep 13 '19 at 21:08
  • 1
    Is this on a VM, or is it on bare metal hardware? – Adam K. Sep 15 '19 at 2:31
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.