Post reply

SQL 2012 Memory question - sql memory being paged out

  • January 7, 2015 at 8:54 am

    #296415

    I have SQL 2012 SP1 64bit Standard on a Win 2012 R2 server with 96GB of ram. Its a physical box right now. If the Standard Edition is limited to 64GB, why do I see SQL Server using more in Task Manager or Resource Monitor? Right now its using 83GB? I have not set max server memory.

    This is a warehouse we are building. Overnight we run SSIS packages to update the warehouse from various sources. The SSIS packages will buffer millions of rows from the source, then look to see what rows in the destination need to be updated or inserted. I'm now seeing messages that sql server memory is being paged out during the window when several packages run. The packages run one at a time, from an Agent job, about a dozen, all with millions of rows to process. Takes about 1.5 to 2 hours.

    Is the SSIS buffering using SQL Server memory? Or is the memory required to buffer for SSIS forcing SQL Server to give up too much RAM? Should I set max server memory or will that hurt the SSIS/SQL even more?

    Not sure what to do here. We're only going to be adding more ETL feeds to this thing.

    Thanks all.

  • January 7, 2015 at 8:59 am

    #1768488

    You should always set max server memory.

    SQL does use memory outside of the buffer pool (which is what max server memory sets). Thread stacks, backup buffers, linked server drivers, CLR. SSIS has it's own separate memory, it's a separate process.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2015 at 12:07 pm

    #1768565

    SSIS has it's own separate memory, it's a separate process.

    Thanks for that. I always wondered but never knew for sure. Up until recently, all my SSIS packages were rather small and quick so I never saw an impact.

    Its funny how things change. Back in SQL 7 days it was always "Never change a default setting unless you think you're smarter than SQL Server" and I never thought I was. Now all I read is to always set max server memory.

    Anyway, set max server memory to 80GB on my 96GB server and will watch.

    Thanks for the info. I learn something every time I ask a question here!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply