sql server max memory includes SSIS?

Question

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?

• billinkc: Will you ever have more than one SSIS package running at the same time? If so, they'll both be contending for the free OS memory so keep that in mind.

Answer 1

(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

Answer 2

How much memory does SSIS need? (The real answer is: It depends.)

However, Jonathan Kehayias has a formula that is pretty good, or so I think, since it works great for me.

https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

The simple formula for reserving space for needs other than SQL Server in Windows is:

Start with 1 GB, plus 1 GB for every 4 GB in the machine between 4 and 16 GB, plus 1 GB for every 8 GB in the machine above 16 GB.

After that you should do some monitoring to determine if you need more or less memory for your SSIS packages. That is the it depends portion of your planning for memory use. If you see problem with your SSIS processes then you need to determine if more memory is needed.

Because you have two instances on the same server this will likely require further tuning.

Answer 3

Just wanted to add some thoughts. If you are executing SSIS packages as a job agent and running them on the SQL server, then you should consider the amount of "free" memory available on the server that is not already allocated to the database. SQL server by default will reserve most of the memory over time depending on what you are doing. When you restart services, this resets to your MIN LIMIT (magically somethings work again, but your execution plans are also blown away). If you have not set a LIMIT MAX, then you may not have allowed for enough server-side memory to run certain SSIS packages that use "Blocking transformations" where the data is read into memory before moving. These do NOT use DB reserved memory. If you are running SSIS packages that use "Blocking Transformations" like Lookups and Sort (or a Script), it is likely that you will cause the SSIS job to look for server available memory (non-SQL DB allocated memory) and if your server hits about 95% utilization you will start paging (job will never end or run for hours). You can see the package paging if you look at the default buffer folder of the package. Anyway, setting a limit allows you to ensure SSIS packages have enough memory to run certain transformations that technically do not occur on the DB (SSIS is an ETL tool). All of this is not as big of an issue if you execute your packages on a server other than the DB server itself.