Hi All,

I have set my Max server memory to 28 GB and Min server memory to 8 GB, the total memory on the windows 2008 server is 64 GB (only 17.7 GB is being currently used).

I do not see anything other than SQL server running dedicatedly on the box but still SQL server is only using around 197,740 KB ( Task manager).

I can see the no of pages in sys.dm_os_buffer_descriptors as 1012518. 

My main concern is why SQL server is not using all the specified memory on the server and I am also getting low page life expectancy errors through SCOM.

SQL Server 2008 and windows serve

Hi 

When SQL server starts it will only take as much memory as it needs, it will continue to request more memory only if it needs more until it reaches the Max server memory, If the OS comes under memory pressure then it can request SQL to release memory which it may do until it reaches the MIN server memory but as a rule SQL will try to hold onto as much memory as it can.

If you have not run any large queries then SQL would not have needed to access large amounts of memory and therefor not request it from the OS. The best way to test this would be to run a query over a large table to bring that table into memory. After that you should see the memory usage climb.

 

Is it virtual or physical server?

Physical server

Hi,

Have you checked "Total Server Memory" & "Target Server Memory" counters (under SQLServer:MemoryManager object) from Performance Monitor? If not, please take a look at them, write them down and share with us please. So that we will have an idea about the memory usage of your SQL Server Instance.

Please note that, if you have more than one SQL Server instance on the same box, them you should be careful about which objects you are using.

Target Server Memory (KB) - 29360128

Total Server Memory (KB)  - 13865616

OK. These figures say that your SQL Server Instance is configured to use 28GB of RAM as you said in your first post, so your configuration is correct and your SQL Server Instance currently consuming 13.22GB of RAM.

When investigating SQL Server Instances' RAM consumption, take advantage of these counters rather than Task Manager.

Thanks,

What may be the reason behind the alerts of PLE as the server still has lot's of memory (17.7 GB used out of 64 GB of RAM)?

I can see the PLE is sometimes dropping well below 300 MB and not utilising the whole 28 GB.

Is this Buffer Manager: Page Life Expectancy counter?

P.S. The unit of PLE is not MB, it's second.

Sorry, mistakenly wrote MB. It is 300

Paul Randal has written an article for this issue, he explains almost everything you are looking for, so it's unnecessary to write the same stuff again and probably I wouldn't be able to explain things better than him. So please check it out:

http://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy

Can you post the output from this query?

--sql server uses the memory
select 
CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
bpool_committed,bpool_commit_target 
  from 
sys.dm_os_sys_info

SIZE                           bpool_committed bpool_commit_target
------------------------------ --------------- -------------------
14,008,208.00                  1751026         3670016

Thanks, will check it.

Hi All,

I have set my Max server memory to 28 GB and Min server memory to 8 GB, the total memory on the windows 2008 server is 64 GB (only 17.7 GB is being currently used).

Hi

If total memory on windows server is 64G why have you just given 28 G to SQl server and what is reason to keep min server memory 8 G. What is output of

Select @@Version

I do not see anything other than SQL server running dedicatedly on the box but still SQL server is only using around 197,740 KB ( Task manager).

Never use task manager to view SQl server memory consumption it always does not gives correct information. You can use below DMV since you are using SQl server 2008

select * from sys.dm_os_process_memory

please post output of this query as well( please make sure you post it clearly)

My main concern is why SQL server is not using all the specified memory on the server and I am also getting low page life expectancy errors through SCOM.

PLE  reflects amount of I/O activity on your server as well as how volatile is buffer cache. I assume you are runing either index rebuild, reorganize, stats update or checkdb or Some ETL process when PLE alert is coming. Can you increase SQL server max server memory setting to 55-57 G because SQL Server is only one residing on the system.

Target Server Memory (KB) - 29360128

Total Server Memory (KB)  - 13865616

Target is much higger than total it would be good to provide more RAM to system and that can be done via increasing max server memory value