patternsqlModerate
sql server max memory includes SSIS?
Viewed 0 times
includessqlssismaxmemoryserver
Problem
I have installed 2 instances of sql server plus SSIS on the following server.
Note the amount of RAM is nearly 384 GB
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?
Note the amount of RAM is nearly 384 GB
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?
Solution
(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
If you feel that there are SSIS packages that does run for hours, then I would suggest you to monitor below SSIS Performance Counters:
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
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 writtenand 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
Code Snippets
Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows writtenContext
StackExchange Database Administrators Q#108266, answer score: 10
Revisions (0)
No revisions yet.