9

Setup:

  • Windows Server 2008 R2
  • SQL Server 2008 R2 SP1
  • 240GB RAM
  • TempDB is 8x16GB data files w/out auto-grow (128GB total)
  • Physical/Stand-alone Server

This server is used for ETL processing. We just installed more RAM in this server for a total of 240GB RAM. SQL Server services are the only real things running.

The memory shows up fine in the BIOS, OpenManage and Windows.

If I configure SQL Server to use a Min/Max of 70/100GB memory, we have no issues. However, once I increase that to 120/150GB, I get the following error when I run one of our ETL processes:

Could not allocate space for object '<temporary system object: 422234507706368>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. (Msg 1105, State 2, Procedure Unknown, Line 1)

We have never run into this issue prior to changing the memory configuration. After re-configuring back to the original 70/100GB, we do not receive this error.

Things I've tried:

  1. Set the TempDB data files to auto-grow. This simply results in the files auto-growing until disk-capacity is reached and then fails.
  2. Add more TempDB data files. Same error as shown.
  3. Increase the TempDB size to 8x32GB (256GB total)

I'm at a loss as to what could be causing this problem.

Improve this question
13
  • 2
    Is your memory balanced across NUMA nodes? How about your processors? Does the SQL Server log show how many CPUs are in use during startup?
    – Aaron Bertrand
    Commented Jan 24, 2013 at 16:16
  • 1
    What are you using for the ETL processes? SSIS or some similar tool? If it is a tool outside of SQL Server, are you running it on the same server as your SQL Server instance?
    – Mike Fal
    Commented Jan 24, 2013 at 16:31
  • 1
    That's a good point @Mike, if the ETL process is unable to grab enough memory to do its thing, because SQL Server is using too much, then it might have to push work to tempdb.
    – Aaron Bertrand
    Commented Jan 24, 2013 at 16:32
  • 1
    Here's a good starter to monitoring tempdb usage: msdn.microsoft.com/en-us/library/ms176029(v=SQL.105).aspx. This should give you an idea of what's happening.
    – Thomas Stringer
    Commented Jan 24, 2013 at 16:44
  • 2
    Have you done any analysis of what's actually running when you're TempDB is actually expanding? A simple sp_who2/sp_whoisactive? It sounds to me like you've got some long running transactions that could be better managed, but difficult to tell. Personally, I wouldn't get to attached to the memory change but look first at the code and see if that is running properly.
    – Mike Fal
    Commented Jan 24, 2013 at 17:24

2 Answers 2

Reset to default
3

Thanks to everyone for your help.

After pouring through some execution plans, it turns out there is a JOIN that is being processed differently based on the amount of RAM available. With less RAM it evaluates it with a Hash; with more RAM, it uses a series of Merge Joins.

So basically it came down to poorly written T-SQL, which I am presently refactoring.

Improve this answer
1
  • 4
    That's quite counter intuitive because a hash join requires a memory grant whereas the merge doesn't. Is there an additional sort operation to support the merge join?
    – Martin Smith
    Commented Jan 25, 2013 at 22:17
1

This is not an answer to the question, just some code I didn't want to post in a comment. To see the balance of your schedulers and memory across NUMA nodes (and also to see if any nodes are not visible online):

SELECT 
  parent_node_id, 
  [status],
  AVG(current_tasks_count) AS avg_tasks_count, 
  AVG(load_factor) AS avg_load_factor,
  scheduler_count = COUNT(*)
FROM sys.dm_os_schedulers
GROUP BY parent_node_id, [status];

SELECT 
  memory_node_id, 
  name, 
  SUM(single_pages_kb + multi_pages_kb) AS memory_kb
FROM sys.dm_os_memory_clerks
GROUP BY memory_node_id, name;

(In SQL Server 2012, the last SUM should be SUM(pages_kb) since there are no longer separate single- and multi-page allocators.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.