Integration service on dedicated SQL instance

Alen Cappelletti 951 Reputation points
2024-05-06T09:26:30.6333333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,923 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 33,781 Reputation points
    2024-05-07T06:55:47.53+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.4K Reputation points
    2024-05-06T21:00:28.7566667+00:00

    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.

    0 comments No comments