We are using SQL Server 2019. On this server, several content databases (used for reporting purposes) and an active SSISDB are running. The entire system is hosted on a single server where SQL Server 2019 is installed.
Occasionally, memory usage on the server becomes quite high. Even when no SSIS ETL packages are running, memory usage does not decrease.
How can we reduce the MEMORYCLERK_SQLBUFFERPOOL
value?
Would you recommend lowering the RETENTION_WINDOW
value?
Are there any automatically started services that we can safely disable to reduce resource usage?
Sometimes, our ETL packages freeze or get stuck, and we have to restart the server to resolve the issue. After the restart, memory usage resets.
I would appreciate any recommendations or best practices you can share.
Based on the CPU and RAM information in the screenshots, how many ETL packages or jobs can I run in parallel?
-- Instead of restarting the SQL Server, can I run the following command? Would this have any negative impact on the other reporting content databases?
USE SSISDB
go
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
SELECT property_name, property_value FROM catalog.catalog_properties;

SELECT name AS [Configuration Name], value AS [Configured Value], value_in_use AS [Value In Use]FROM sys.configurationsWHERE name IN ('min server memory (MB)', 'max server memory (MB)');

SELECT
DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8 / 1024 AS [Memory Usage (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id NOT IN (1, 2, 3, 4) -- sistem veritabanlarını hariç tut
GROUP BY database_id
ORDER BY [Memory Usage (MB)] DESC;
The value in this screenshot corresponds to the previous memory limitation. Later, we increased the memory slightly, thinking it was insufficient. However, SSISDB continues to use high memory even when the ETL packages are not running.


