We currently have an SSIS installation on the same server as SQL Server. This SQL Server installation is strictly to run SQL Server Agent to schedule the jobs that execute the SSIS packages deployed to this server. No user DBs, just MSDB and SSISDB, and then a small DB (<100mb) for the DBAs to store certain stored procs and tools to check status, etc.
We have 128GB of memory for the server, and half (64GB) of the memory is dedicated to SQL Server. The EDW database that we are pushing the data to in SSIS is located on a different server entirely.
Is this set-up correct? Is it correct to have Integration Services run on the same server that you are scheduling the packages through SQL Server agent on? I ask because we are running into memory issues, and the DBAs are suggesting that we increase the memory allotted to SQL Server. Should we give more memory to SQL Server?
Best Answer
I concur with @dan Guzman's comment. You're likely overallocated on memory for SQL Server (and probably for the box itself). Unless you just have continuous package execution and/or suboptimal design patterns in play, that smells like the box will be happy with 1/4 of the resources - just make sure it has the best network speed (assuming data comes from server 1, ssis packages run on server 2 and push data to server 3, you'll likely be network bound). Profile and test, of course.
SSIS is an in-memory run-time engine. When you launch jobs from SQL Agent, they are going to run in their own address space so your 64GB of free memory is going to allocated to the Operating System, dtexec (the process that actually runs packages), etc. Getting into the weeds of an SSIS package, you're only really burning memory when you have data flow tasks and even then, the typical culprits are blocking transformations (aggregations, merge joints, etc) and fat data types n/varchar(max) or varbinary data in the pipeline. Better design can reduce memory contention. And of course, depending on your data source/destinations, you might have to deal with 32 bit drivers so the maximum memory per package execution would be capped under 4GB.
SQL Server is going to need memory to keep the system databases running + the SSISDB for project deployment model. That's likely a trivial amount of resources (disk + memory) but yes, good job on setting a maximum capacity on SQL Server.