Sql-server – sql server max memory includes SSIS

memorysql serversql server 2014ssisssis-2014

I have installed 2 instances of sql server plus SSIS on the following server.

Note the amount of RAM is nearly 384 GB

enter image description here

enter image description here

And this is the max and min memory settings that I have applied to my 2 instances.
I assume that both instances will use equal amount of resources, if that would really be the case, 184,320 MB which is 180 GB be a good starting number to set up my memory?

How much memory would I allocate to SSIS?

Best Answer

(From the screenshot) I see an unusual configuration where you have SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory.

To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration

We have SSIS running packages on our production servers and they hardly cause any memory issues. I just leave enough memory for OS, since DTExec.exe is an external process to sqlserver.exe and so its memory requirements wont be included as part of MAX Memory. Also, SSIS is designed to process large amounts of data row by row in memory with high speed.

If you feel that there are SSIS packages that does run for hours, then I would suggest you to monitor below SSIS Performance Counters:

Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written

and from Top 10 SQL Server Integration Services Best Practices - SQL CAT Team

Process / Private Bytes (DTEXEC.exe): The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.

Process / Working Set (DTEXEC.exe): The total amount of allocated memory by Integration Services.

Old but still relevant : Integration Services: Performance Tuning Techniques