Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
There are indexes on tables involved into the query? –  lg. Jul 15 '10 at 14:59
    
Yes. Every table has clustered indexes and primary keys. –  Registered User Jul 15 '10 at 20:53
add comment

1 Answer

Do your table have partitions? Do you have nonaligned indexes on partitioned tables? This is a shot in the dark, but try a OPTION (MAXDOP 1) query hint to your problem ETL query.

share|improve this answer
    
This is happening on non-partitioned tables. I'll try limiting the degrees of parallelism. I've already limited it to 3 at the server level, but I'll force 1 for some of the ETL processes. –  Registered User Jul 15 '10 at 20:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.