6

We have SQL Server 2008 and 8GB RAM in a customer, hosted in a Windows 2008 R2 SP1, and consider the following amount of free memory:

enter image description here

and consider the following running programs:

enter image description here

Scenario: Users use a website which saves data in this server in a SQL Server database. No more than 5 users.

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use? Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

SQL Server version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Windows version: Microsoft Windows [Version 6.1.7601]

  • SQL is going to take what it can unless you configured an upper limit on what it can use. With only 8gb running on the server and it's an option to move IIS somewhere else I'd run with that. Depending on activity of the server you may want to leave a min of 2gb for the OS. – Jeff A Nov 25 '14 at 19:35
7

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use?

The answer to the question "how much memory SQL Server would use" is, as much as it can. For that reason its always advisable to put appropriate value for max server memory so that buffer pool is restricted.

Please don't use task manager to get SQL Server memory utilization it wont give correct value in case when SQL Server service account has locked pages in memory(LPIM) privilege. To find how much RAM SQL Server is using please use below query

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

You can do that in Sp_configure using below query.

sp_configure 'show advanced options',1
go
reconfigure 
go
sp_configure 'max server memory (MB)' , xxx --value in MB which you need to set
go
reconfigure.

You can fix max server memory using the query but remember, SQL Server can still use memory outside buffer pool for third party DLL's and extended stored procs. This can lead SQL Server to use memory more that what is restricted in buffer pool

Please read this article. It would help you in setting starting value for max server memory. You should use below counters to set correct value for max server memory and monitor SQL Server memory usage.

SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE. But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB. So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool, due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

1

SQL server will use as much memory as possible. You can and you should allocate limited memory in the SQL server settings (properties) to leave enough for OS and IIS. How much memory foe each depends on what each does, load and expectations.

This article explains this in depth: http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

0

The answer is It Depends on exactly how much load your are putting on SQL Server, IIS, and so forth. (SSIS? SSRS?) All of these things add up.

Jonathan Keheyias has a general formula that works pretty good for me: "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."

https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/

So this formula would say to limit SQL Server to about 5 GB and let the other code on the server also have some memory to use. (Or add more memory and recalculate.)

You will need to experiment with the memory settings until you find your balance.

Note: If you get your memory use balanced correctly, most of the memory will still be in use, but better shared across your applications.

-1

Identify the Cache memory required for each database in your server and set Max. Server Memory higher than its cumulative value(not necessary to configure the value greater than twice of cumulative cache memory).

By doing this, you can allocate the memory needed for MSSQL server and free up the RAM unnecessarily occupied by the buffer cache.
Note: Max. Server Memory configuration parameter throttles only the buffer pool usage NOT the MSSQL server memory usage;

--space used by each database in the buffer pool

select 
  count(*)*8/1024 AS 'Cached Size (MB)'
 ,case database_id
    when 32767 then 'ResourceDB'
    else db_name(database_id)
  end as 'Database'
from sys.dm_os_buffer_descriptors
group by db_name(database_id), database_id
order by 'Cached Size (MB)' desc;

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.