SQL Server 2019 SSISDB's High Memory Usage

Question

SQL Server 2019 SSISDB's High Memory Usage

Oktay Bozdemir 41

We are using SQL Server 2019 and SSIS on a single server, which runs nightly ETL jobs using SSIS as the ETL tool.

Each night, when the ETL processes begin, SSISDB’s memory usage increases significantly. However, even during the day—when no ETL jobs are running—the memory usage remains around 95%.

How can we prevent SSISDB from consuming so much memory while idle? Restarting the SSIS service from the Services console does not resolve the issue; the only effective solution so far has been restarting the SQL Server itself.

SELECT DB_NAME(database_id) AS [Database Name],

COUNT(*) * 8 / 1024 AS [Memory Usage (MB)]

FROM sys.dm_os_buffer_descriptorsWHERE 1=1

--and database_id NOT IN (1, 2, 3, 4) -- sistem veritabanlarını hariç tut

GROUP BY database_idORDER BY [Memory Usage (MB)] DESC;
Query result:
SSIS_DB 66530 MB
DWH_DB 4900
REPORT_DB 4500
OTHER_DB ....

Accepted answer

0 additional answers

Your answer

Answer 1

Erland Sommarskog 124.2K MVP Volunteer Moderator

This is the expected behaviour.

When SQL Server reads data from a database, it first checks if the data is in the buffer cache, as serving the data from the buffer cache is more efficient than reading from disk. If the data is not in the cache, SQL Server reads the data from disk into the cache. The data will then stay in the cache until the space is needed for something else, and data that has not been used for a while is aged out.

So it is typical to see SQL Server acquire more and more memory and stick to it. However, if Windows signals that there is memory pressure, SQL Server will yield memory.

Nevertheless, you can force SQL Server to release the buffers with the data in SSISDB by running:

USE SSISDB
go
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE

However, it is pretty pointless. The net effect is that your ETL jobs will take a little longer time by reading the data from SSISDB into disk again.

Oktay Bozdemir 41 Reputation points

2025-07-22T10:32:38.8266667+00:00

So, does this procedure need to be executed every day? Should it run at the start of the ETL process, then again after the SRC_TO_STG step is completed, and once more after the STG_TO_DWH step is finished?

USE SSISDB

GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE
Oktay Bozdemir 41 Reputation points

2025-07-22T10:34:10.5566667+00:00

Is there a more effective solution? Apart from running the procedure, is there a configuration setting for this?

Because every time the ETL jobs run, memory usage increases, which causes SQL Server to slow down.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-22T10:55:09.03+00:00

So, does this procedure need to be executed every day?

No, it does not need to be executed at all. As I said, it is a pointless thing to do, since it will only serve to make your ETL process take a little more time.

which causes SQL Server to slow down.

I am not surprised to hear that you are seeing real issues, and I am not surprised to hear that these are performance issues. However, you have jumped to conclusion as for why things are slow. I would say that an ETL operation is expected to increase SQL Server's memory consumption if it is not already at the max, since an ETL operation usually operates on a lot of data.

Normally, when "SQL Server is slow", it is a matter of query tuning. Query Store can help you to identify slow queries. If you have not enabled Query Store for the database, do so!
Oktay Bozdemir 41 Reputation points

2025-07-22T13:36:01.55+00:00

ETL packages that perform sort operations in SSIS can run slowly and may take a long time to complete. In some cases, we are forced to restart the SQL Server. However, since the same server also hosts databases used by our reporting systems, we need to stop those systems as well.

Restarting only the SSIS service doesn’t resolve the issue, because the main memory-consuming component is the SSISDB database itself. Even when no ETL jobs are running, SSISDB maintains high memory usage throughout the day.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-22T14:42:34.1266667+00:00

Even when no ETL jobs are running, SSISDB maintains high memory usage throughout the day.

Yes, and I have explained that this is perfectly normal, and this is not th relevant to the problems you are seeing.

The one conclusion we can draw from this is that you server does not seem to be under memory pressure, because in that case the report procedures would have squeezed out the SSISDB pages when they are no longer in user.

Of course, if you still think that the SSISDB pages somehow are part of your problem, you have now learn how you can evict from the cache. So you could try that, but I predict that you will not see any difference.

And don't forget to enable Query Store!
Oktay Bozdemir 41 Reputation points

2025-07-31T07:52:13.4766667+00:00
Generally, our ETL transfers are; Source: Oracle, Destination: Oracle.

I noticed that the "Query Store" feature is disabled. Would you recommend enabling this feature for SSIS ETL transfers?

Does keeping the "Query Store" feature disabled (value set to 0) pose any disadvantages in terms of performance or monitoring?

Also, I found that the CLEAR CACHE command you previously shared does not work on the SSISDB database. Instead, it seems that DBCC FREEPROCCACHE needs to be used; however, this command affects all databases.

USE SSISDB go ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-31T09:36:05.5166667+00:00
Sorry, I missed that there was an underscore. The correct command is:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Enabling Query Store is always a good idea. It does some overhead, but it is usually tolerable. Workloads that submit tons of quick unparameterised queries can take a severe toll, though. I can't imagine that that this applies to SSISDB. Query Store is a great tool to track down slow queries.

Now, since you don't have control over the code that runs in SSISDB, this is of limited benefit. But you still can use Query Store to force a good plan when you get a plan regression.
Oktay Bozdemir 41 Reputation points

2025-08-06T07:09:52.9633333+00:00

Some days, our SSIS ETL packages get stuck and do not complete. In such cases, we have to manually kill the processes.

Are the default CPU and memory settings in SSIS sufficient? Do we need to increase the RAM? The attached screenshot shows our current CPU and RAM — do you think these are adequate? What values would you recommend for default CPU and memory settings? We are using SSIS-SQL Server 2019.

This doesn’t happen every day, but on certain days, ETL jobs hang. Some packages appear to be running, but they never finish, even after a long time. Eventually, we have to manually kill them. After that, we rerun the ETL packages.

How many ETL packages are allowed to run in parallel?
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-08-06T19:17:35.7866667+00:00

When it comes to questions about SSIS, I cannot answer, since I don't know SSIS. I only work with the Database Engine.

But if you are running SSIS and SQL Server on the same machine, there is a risk that they compete on memory. Then again, if you run them on different machines, you need one more license.

In any case, to understand what is going on, you need to get better diagnostics. Is it SSIS itself that get stuck? Or are you stuck on a query in SQL Server (my bets are on the latter).

The tool I would use to troubleshoot this is my own beta_lockinfo which gives you information about currently running processes, how long they have been running, the current query and the plan of the current query. You can also see if there is any blocking. Many other people uses Adam Machanic's sp_WhoIsActive. This tool also gives you a lot of information, but I am uncertain on much information it gives about blocking.

It goes without saying, that should run beta_lockinfo/sp_WhoIsActive before you kill the process. I should add the caveat that if SQL Server is very loaded, they can take a long time to run.

You should also check the SQL Server errorlog in such situations, since SQL Server may produces messages if it is not feeling well.

Share via

SQL Server 2019 SSISDB's High Memory Usage

0 additional answers

Your answer