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:
and
(select value_in_use from sys.configurations where name = 'max degree of parallelism')
and my server cores are 47.
Best Answer
Based on the
waits statistics
in your system you have incorrect settings forparallelism
.Maxdop
in your system should not be more than16
, you can find the correct value based on this MS article: Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL ServerHere 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 ServerHere is the explanation given by Max Vernon on which I agree. He explains why incorrect setting of
maxdop
slows down the execution.