asked
SSIS Server Hardware requirements for the best perfomance??
I creating a SSIS server, this server will consume a database, execute the package one or two times per hour, on each execute will process 100 000 rows, It's better use SSIS server in the same SQL Server or use a SSIS standalone server?? How many ram can support this architecture?? I will execete 2 different packages at the same time, both with 2 or 3 loops, i trying to reduce this loops. Best regards!!
There are a lot of questions being asked here. The simple answer is: 100,000 rows is actually a small number of rows for SSIS. I have run multiple million row packages from the same server as host database server. The key is to find the balance in the defaultrowbuffer setting under the dataflow properties. This will limit the number of rows in memory, thus limiting the amount of RAM needed to run the package. As far as how much RAM can support this architecture is set by the OS and SQL Server edition. How this helps.
It's hard to say any direct recommendation for apropriate memory settings without knowin details. As @corygs mentioned, you can fine tune the memory consumption and also performance of the package by properly setting the `DefaultBufferMaxSize` and `DefaultBufferMaxRows` properties, of the data flow, but this is only one part. As those limits are pro single execution tree in the data flow so it depends how many execution trees thee are insid the data flow. Other thing is usage of lookups and any of the partial or fully blocking aggregations as those have own bufferes which are not part of the `DefaultBufferMaxSize`. But in general you can observe the package memory consumption during runtime and than align SQL Server Max Memory appropriately so there is no swapping occuring etc. Also take in mind other proceses possibly running on the server.
Thanks for your answer.. I was checking scalability and in two or three months the numbers of rows will be over a million.. Another question! Its better to use a StandAlone SSIS and other Server for SQL or use one for both data storage and businness integelligence!? (Its better use only one for licensing) but for perfomance??
even a few millions of rows is not an issue... SSIS is capable of processing of a few millions of rows in a minute. The only thing to remember are appropriate settings of the memory.. On SQL Server side and on SSIS side.
13 People are following this question.
