SQL Server 占用完整内存后速度完全变慢

Question

在具有 32 GB RAM 的服务器上，我们运行一个 SQL Server 实例，最大内存上限为 80%。

当内存利用率较低时，一切正常。请参阅下面的屏幕截图

但随着时间的推移，3-4 天后 SQL 将利用完整的 RAM(总数的 80%)。

在这 3-4 天内，我们没有对服务器进行任何更改，但它每天都在消耗更多的 RAM。

当达到最大限制时，整个性能都会下降，我们的网站将面临查询超时。执行在几毫秒内执行的同一查询需要花费很多秒的时间。

此时我们别无选择，只能重新启动整个服务器，一切恢复正常。 (仅重启服务是不行的)

这将工作一周左右，之后我们必须重新启动它

我在网上看到，SQL Server不释放内存。但他们也提到，这就是 SQL 的运行方式，但并不影响性能。就我而言，确实如此，并且性能受到影响。

是否存在内存泄漏？或者一个存储过程消耗大量内存并且从不释放它？如果是这样我该如何调试它？

Answer 1

默认情况下，如果未设置上限，SQL Server 会消耗整个内存，如果未设置上限，则使用所有可用内存。这是正常的。您还必须确保 SQLSERVER 是包装盒上唯一的应用程序(推荐)和 also try to cap memory as per best practices .

我将使用以下方法开始故障排除

1.开始查找内存消耗最高的查询，看看是否可以减少查询的内存使用量。内存消耗查询可以通过下面的查询找到。

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

现在您发现查询导致内存占用过高，您需要对其进行微调，看看是否可以减少内存使用量。

EX:由于索引不合适，查询可能会执行多次读取，或者您的 IO 设备可能由于缓冲池多次刷新而出现问题

2.您还可以找到使用内存的顶级组件，这让您了解 RAM 的使用方式

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; 

如果缓冲池始终使用更多内存，我不会担心这一点，但如果它是cachestore_obcp。那么您可能会有许多临时查询填充缓存存储，这很糟糕

调查的一个部分会导致另一部分，因此您必须根据线索进行故障排除，因为没有一键解决方案

旁注:不推荐:
我们的一个开发实例曾经面临同样的问题，因此我们没有进行所有调整，而是运行以下命令，该命令可以立即有效地释放内存。但是对于生产实例，根本不建议这样做，因为这会刷新计划存储在缓存中，您可能会面临轻微的CPU压力

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 

引用文献:

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