4

In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher.

http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/

I have a new server with 64GB RAM. Due to licensing constraints, this server must run the SQL Server database engine, SSIS, and SSAS. SSIS ETLs happen overnight, and users hit the actual database mostly during the daytime. In this scenario, would it be best to leave the default setting so that SSIS can take the memory if needed, and then the database engine can take memory when it needs it?

Improve this question
1
  • 3
    Just a comment: If you have a server that is also running SSAS you should also adjust the amount of memory that SSAS can consume. Most all of the defaults for SSAS should be adjusted if you are running everything on one server.
    – user507
    Mar 9, 2016 at 15:50

2 Answers 2

Reset to default
7

You should always set your max memory away from default and leave some room for OS (see Jonathan's post of how much Memory to leave based on the amount of RAM installed).

Jonathan Kehayias has blogged about : How much memory does my SQL Server actually need?

reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can also refer to my answer here for more details.

would it be best to leave the default setting

NO, dont leave it as default as problems like OS unresponsiveness, Working Set trimming as well as other applications running on the server will be affected adversely. It will affect your backups as well.

Note that Memory Manager for SQL Server 2012 and up has changed.

Improve this answer
3
  • 1
    I have also seen Jonathan's recommendations, among others. The problem is that they account for only the SQL Server engine running on the server, and not SSIS, SSAS, etc.
    – SomeGuy
    Jul 3, 2014 at 14:25
  • 2
    Thats fine. You just have to allocate more to OS then. Main purpose is to balance out between OS and SQL Server, so the former one does not starve OS.
    – Kin Shah
    Jul 3, 2014 at 14:34
  • 1
    Leaving max_server_memory at default will allow SQL Server to consume all available RAM, leaving no room for SSIS or SSAS services.
    – Hannah Vernon ♦
    Jul 3, 2014 at 15:29
0

You have a complex environment with SSIS and SSAS so you have to be cautious with max server memory setting for SQL Server. In your scenario Jonathans blogs would not provide complete help. You should refer to This Microsoft link for determining memory requirement for SSIS

You should leave enough memory for OS,SSIS,SSAS to function correctly as SSIS,SSAS does not takes memory from buffer pool. You could start with giving 10-15 G to OS and rest to SQL server. Then monitor SSIS and SQL server memory usage with perfmon counters.

Regarding how much memory to keep for SQL server you should take help of Perfmon counters. This article will give details of perfmon counter to monitor for getting accurate value for max serevr memory for SQL server

  • SQLServer:Buffer Manager--Page Life Expectancy(PLE)

  • SQLServer:Buffer Manager--CheckpointPages/sec

  • SQLServer:Buffer Manager--Freepages

  • SQLServer:Memory Manager--Memory Grants Pending

  • SQLServer:memory Manager--Target Server Memory:

  • SQLServer:memory Manager--Total Server memory

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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