1

I'm using SQL server on a server with 48 GB of RAM.

When I start the SQL Server service, it's using 12% of the RAM at first, but after 24 hours it's reaching 90% and query executions begin slowing down. We have to restart the service so we can serve users.

What could be the problem and how we can fix it?

Example images:

enter image description here

enter image description here

and (select value_in_use from sys.configurations where name = 'max degree of parallelism')

enter image description here

and my server cores are 47.

Improve this question
8
  • SQL Server will use all the ram available. Just limit the max in the settings. For the performances you need to investicate furter. What is consuming resources in your database? How many running sessions? Activity monitor adn query store are good starting point.
    – user_0
    Commented Oct 16, 2019 at 9:04
  • 1
    Please update your question with the result of the query from this link: sqlskills.com/blogs/paul/…
    – sepupic
    Commented Oct 16, 2019 at 9:52
  • 1
    Here is the script to see what types of LATCHes do you have: sqlskills.com/blogs/paul/… please add the result of this too
    – sepupic
    Commented Oct 16, 2019 at 12:11
  • 1
    Your instance has enough memory and your problem is not there. Maybe you have wrong parallelism settings, but please first show us the result of detailed latches wait stats
    – sepupic
    Commented Oct 16, 2019 at 12:32
  • 1
    Yes your latches waits has to do with parallelism. What is yout full server version (select @@version), max dop of parallelism on server level (select value_in_use from sys.configurations where name = 'max degree of parallelism') and number of processors(cores) on your server?
    – sepupic
    Commented Oct 17, 2019 at 7:11

2 Answers 2

Reset to default
3

Based on the waits statistics in your system you have incorrect settings for parallelism.

Maxdop in your system should not be more than 16, you can find the correct value based on this MS article: Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Here are some T-SQL scripts to do it (I don't know your exact NUMA configuration but the scripts alilyze it): MAXDOP setting algorithm for SQL Server

Here is the explanation given by Max Vernon on which I agree. He explains why incorrect setting of maxdop slows down the execution.

If you set MAXDOP higher than the number of cores/numa node, you end up with calls into far memory which are many many times slower than calling near memory. This is because each numa node has its own memory; having a query use more threads than are present in a single numa mode will spread the CPU load over multiple cores, and therefore multiple memory nodes.

Improve this answer
2

SQL will always use up the RAM it has to cache data. Setting a min/max RAM for your instance is always a good idea (especially MAX, so you keep some RAM for your OS) See this good answer on dba.SE:Why is SQL Server consuming more server memory?

Right click your instance in SSMS -> Memory -> Fill in min/max memory. It's a good idea to leave at least 4 GB for your OS on a SQL dedicated machine. If you have other services running, keep those in mind as well.

Now, you're saying your queries are slowing down, which should not happen. Perhaps you should make a different question asking for performance advice on specific queries.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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