Integration service on dedicated SQL instance

Question

Based on an assessment I did on a client's server, I have to move all the integration services to a dedicated machine. We decided to separate ETL and data on two machines.

My question is: having a dedicated instance, how do I manage the memory CAP on SQL SERVER?
Given that I assume that no operative databases will install other than SSISDB.

Would I be inclined to leave the minimum at 0 and which maximum value?
I think I'll have 4 core machine with 36/48 GB ram dedicated only to SSIS services.

Naturally I'll monitor performance counters to see how much RAM the SSIS will use, but I'm not sure of the CAP value to enter for SQL SERVER (I wouldn't go beyond 6 Gb).
I want to stay low... maybe I won't even be able to use them as there are no other real DBs.

ALEN

Answer 1

Hi @Alen Cappelletti,

You can’t set memory minimum or maximums for SSIS

Unlike SQL Server, there is no way to configure a floor or cap on the amount of memory the SSIS execution engine will use. I’ve seen SSIS fill up a server’s RAM on more than a few occasions. Again, when configuring RAM on a machine (whether physical or virtual), more is better.

Several blogs talked about this, you may take a reference to.

How Much Memory Does SSIS need

sql server max memory includes SSIS

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Yeah, it seems like a good idea to set "max server memory" low. Then again, if your SSISDB database is small, SQL Server will take much memory. And if there would be some accident where you run a wild query, so that SQL Server grabs a lot of memory, it will yield that memory when SSIS starts working.

But set it to 6000.