I have a production SQL Server 2008 R2 database server that had 8GB of RAM allocated to it for the query engine (set by the minimum memory in the memory management screen for the database server). I noticed I was regularly running into memory bottlenecks whenever several of my ETL processes ran in parallel because each stored procedure call, no matter how simple, was reserving 2GB of RAM for the first 3 incoming processes and then reserving fractions of 2GB for each subsequent query. And yet none of the first 3 incoming processes even used half of the reserved RAM. I added another 16GB of RAM to the server and allocated all the new additional RAM to the database server in the memory management screen. Now the first 5 incoming processes reserve 4GB of RAM and each subsequent query reserves fractions of 4GB. What's going on?
I don't see any settings that can be altered, so I am assuming the solution will require coding. I am going to test separating some of the long-running stored procedures that have many steps into chains of smaller stored procedures to see if provides any relief for now. Any advice would be greatly appreciated!