Ssis memory Advice

  • zouzou

    SSCrazy

    Points: 2290

    I have a 32bit server with 32Gb of ram the sql server 2008r2 server doesn’t have any dbs and the only thing that it runs is SSIS packages what would the ideal memory allocation for the sql server ? It seems that when we restart the server the packages start miraculously and succeeding.  At the moment sql max is set to 2 GB

  • ktflash

    Ten Centuries

    Points: 1221

    Maybe take a look into this:

    https://www.brentozar.com/blitz/max-memory/

    I want to be the very best
    Like no one ever was

  • Phil Parkin

    SSC Guru

    Points: 244807

    Since SSIS packages do not share memory with SQL Server and you are not using the DB engine, it seems like it would make sense to reduce that allocation, not that I have had experience of doing this.

    If it were me, I'd start reducing it in chunks & assessing any impact along the way.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • zouzou

    SSCrazy

    Points: 2290

    It’s only 2 GB I don’t think I should lower it more than that do you think can affect SSIS operations if it’s set to too low ?

  • Phil Parkin

    SSC Guru

    Points: 244807

    OK, I've had a coffee now, so brain is out of first gear!

    Are you suggesting that SSIS packages run OK after a reboot of the SSIS server, but then at some point stop executing?

    If so, we need to know more about what errors you are seeing.

    If there is a massive slowdown, is it memory-related?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • frederico_fonseca

    SSChampion

    Points: 14785

    and is SSIS running packages through SSIS Catalog, filesystem or msdb? 32 bit or 64 bit - and are you access databases from other vendors using their drivers (Oracle, DB2 ...)?

    and have you done some kind of investigation onto perfmon /task manager to see what maybe causing issues - this could well be rouge processes not releasing memory (e.g. c# scripts on ssis, powershell steps and a miriad of other aspects)

    and when you mention restart the server - is that a full windows restart or just a SQL Server instance restart?

  • Thom A

    SSC Guru

    Points: 98790

    frederico_fonseca wrote:

    and is SSIS running packages through SSIS Catalog, filesystem or msdb? 32 bit or 64 bit?

    Certainly won't be SSISDB, it wasn't available back in SQL Server 2008, and it's 32bit according to the fact they are using a 32bit server. Though, as they are running 2008R2, I would also strongly recommend that look at upgrade paths, considering that it hasn't been supported for over a year now.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jonathan AC Roberts

    SSCoach

    Points: 17344

    It doesn't sound like a memory issue, but if you are sure it is you could reduce the memory SQL Server uses just before the SSIS packages run and increase it again afterwards. There is no need to restart SQL Server to do this.

    This SQL script will change the memory usage of SQL Server.

    EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'max server memory (MB)', N'4000' RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

     

  • frederico_fonseca

    SSChampion

    Points: 14785

    Jonathan AC Roberts wrote:

    It doesn't sound like a memory issue, but if you are sure it is you could reduce the memory SQL Server uses just before the SSIS packages run and increase it again afterwards. There is no need to restart SQL Server to do this.

    This SQL script will change the memory usage of SQL Server.

    EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'max server memory (MB)', N'4000' RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

    according to OP memory is already set to 2GB out of 32GB so this would not matter either. if nothing else if it is memory it could make it worst as it would take away 2GB of memory (or nothing as SQL won't be using it anyway)

  • Jonathan AC Roberts

    SSCoach

    Points: 17344

    frederico_fonseca wrote:

    Jonathan AC Roberts wrote:

    It doesn't sound like a memory issue, but if you are sure it is you could reduce the memory SQL Server uses just before the SSIS packages run and increase it again afterwards. There is no need to restart SQL Server to do this.

    This SQL script will change the memory usage of SQL Server.

    EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'max server memory (MB)', N'4000' RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

    according to OP memory is already set to 2GB out of 32GB so this would not matter either. if nothing else if it is memory it could make it worst as it would take away 2GB of memory (or nothing as SQL won't be using it anyway)

    Yes, I did say I didn't think it was a memory issue. I put the script in just so the OP could put it in their SSIS job if they thought it was a memory issue they could use that script to test it.  The 4000 was just in there as a "sample" so they could see what script could change the memory setting in SQL Server, the value (4000) was never meant to be used verbatim. I thought it was far too obvious to even state that they would need to set that value to a lower value just before the SSIS package ran and a higher one after it finished.

  • zouzou

    SSCrazy

    Points: 2290

    Thank you I might go into perfom to find out what is going on they do reboot the server

  • zouzou

    SSCrazy

    Points: 2290

    And the SSIS packages ran to success they haven’t done any debug and the packages ran through MSDB

  • Jeffrey Williams

    SSC Guru

    Points: 88712

    In your original post - you stated that the database engine was not used.  However, it seems it is being used - probably by SQL Server Agent and the MSDB database (to host the SSIS packages).

    Since this is a 32-bit system - please confirm the 2GB memory is actually set as 2048 and not some other value.  You should also review and insure that AWE is not enabled on this system as well a making sure that lock pages in memory is not set.  You want to make sure the database engine cannot take more than 2GB of memory.

    I would recommend upgrading to the latest available OS and SQL Server instance - but at a minimum you should be on Windows Server 2012 R2 and SQL Server 2016.  With this you can implement the Integration Services Catalog and get much better management and maintenance of your SSIS packages.  Especially if you take the time to implement full projects for your estate - there are a lot of advantages to using projects and deploying the projects to the catalog.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • zouzou

    SSCrazy

    Points: 2290

    Hi yes it is set to 2048 I will check about awe and lock pages in memory I have proposed to upgrade

  • zouzou

    SSCrazy

    Points: 2290

    Thank you for all the suggestions

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply