5

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?

SomeGuy
  • 2,053
  • 8
  • 35
  • 48

2 Answers2

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.

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
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

Shanky
  • 19,148
  • 4
  • 37
  • 58