July 15, 2021 at 7:28 am
Hi,
I am getting the following information while running the SSIS job. Though it is not an error, I feel is making the package very slow.
My sql server memory configuration is max server memory (in MB ) - 2147483647
Total RAM - 128 GB
What can be the possible solution for this? Can I reduce the SQL server memory allocation?
July 15, 2021 at 8:00 am
MAX Server memory should never be left at the default - with 128 GB ram and with SSIS running on same server I would give SQL a max of 100-110 GB leaving the remaining for OS and SSIS.
While at it also check that both MAX DOP and Cost threshold for parallelism are not set to their defaults - MAX dop going to depend on number of cores/vcpus on your machine but normally a max of 8 or number of cores/2 if lower and CTFP should be set to 25 (or 50) until you have time to investigate correct values for your load.
if you also have SSRS then reduce the value above by another 5GB
SSIS uses a separate memory pool than SQL - so the error is likely to be related to SQL taking most of the memory causing other processes to starve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply