September 14, 2020 at 12:28 pm
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
September 14, 2020 at 12:32 pm
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
September 14, 2020 at 1:10 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 14, 2020 at 1:20 pm
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 ?
September 14, 2020 at 1:46 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 14, 2020 at 2:09 pm
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?
September 14, 2020 at 2:37 pm
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.
Larnu.uk
September 14, 2020 at 3:22 pm
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
September 14, 2020 at 3:58 pm
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)
September 14, 2020 at 5:02 pm
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 OVERRIDEaccording 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.
September 14, 2020 at 5:47 pm
Thank you I might go into perfom to find out what is going on they do reboot the server
September 14, 2020 at 5:48 pm
And the SSIS packages ran to success they haven’t done any debug and the packages ran through MSDB
September 14, 2020 at 6:37 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 14, 2020 at 7:14 pm
Hi yes it is set to 2048 I will check about awe and lock pages in memory I have proposed to upgrade
September 14, 2020 at 8:32 pm
Thank you for all the suggestions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply