0

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!

2
  • There are indexes on tables involved into the query?
    – lg.
    Jul 15, 2010 at 14:59
  • Yes. Every table has clustered indexes and primary keys.
    – Registered User
    Jul 15, 2010 at 20:53

1 Answer 1

0

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.

1
  • 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, 2010 at 20:59

You must log in to answer this question.