SQL Server completely slows down after occupying full memory

Question

On our server with 32 GB RAM we have an instance of SQL Server running that is capped with max Memory at 80%.

Everything works fine when the memory utilization is low. See screenshot below

But as time passes, 3-4 days hence SQL will utilize the full RAM (80% of total).

During this 3-4 days we make no changes to the server, but day by day it keeps eating more RAM.

When it reaches the Max Limit the entire performance goes for a toss, and we face query timeouts on our website. It takes many seconds to execute the same query that was executing within milliseconds.

At this point we have no choice but the restart the entire server and things return to normal. (Restarting the service only does not work)

This will work for a week or so, after which we have to restart it again

I have read online, that SQL server does not release memory. But they have also mentioned that it is how SQL functions, but does not affect performance. In my case it does and performance suffers.

Is there a memory leak? Or a stored proc consuming lots of memory and never releasing it? If so how do I debug it?

Answer 1

By default SQL server consumes entire memory if uncapped and uses all memory available when capped.This is Normal.You will also have to ensure SQLSERVER is the only application on the box(which is recommended) and also try to cap memory as per best practices.

I would start troubleshooting using below approach

1.start finding top memory consuming queries and see if I can reduce the memory usage of the query ..memory consuming queries can be found by below query.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

now that you found queries causing high memory, you need to fine tune them to see if you can reduce the memory usage.

EX: query may be doing many reads due to an unsuitable index or may be your IO device has an issue due to which buffer pool is getting flushed many times

2.You can also find top components that use memory, which gives you an understanding of how RAM is spent

SELECT TOP(20) [type], [name], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type], [name]  
ORDER BY SUM(single_pages_kb) DESC; 

if buffer pool uses more memory consistently, I would not worry about that, but if it is cachestore_obcp.then you might be having many ad-hoc queries filling up cache store which is bad

One part of investigation leads to another, so you will have to troubleshoot based on the leads as there is no one click solution

Side Note: Not Recommended:
one of our dev instances used to face the same issue, so instead of doing all the tuning stuff, we used to run below command which effectively releases memory immediately..but this is not at all recommended for a production instance, as this flushes plans stored in the cache and you may face slight CPU pressure

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 

References:

• sql-server-memory-related-queries
• sql-server-find-most-expensive-queries-using-dmv

Answer 2

You need to find out Most expensive queries and need to review query code, many times the query code is not effectively optimized. Performance tuning you need to do.