9

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?

  • 2
    As for heading question NO, memory for SSIS does not comes from buffer pool in any instance of SQL Server – Shanky Jul 28 '15 at 4:15
  • 1
    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. – billinkc Jul 28 '15 at 20:35
10

(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

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.