Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL server and RAM memory

Author  Topic 

ilimax
Posting Yak Master

164 Posts

Posted - 2011-03-25 : 11:17:59
Yesterday we had situation when SQL server took 28 GB of RAM memory.
It actually took whole server memory.
After I stopped SQL Server service, used RAM goes to 2.5 GB.
I restarted service again and everything was fine.

Does anybody have idea why this happened?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-25 : 11:28:35
This is normal. SQL Server is a memory hog and won't release the memory unless it's no longer using AND some other process is requesting it.

So why is this a problem on your server? It is being used by other things?

You can limit the amount of memory SQL can consume via sp_configure, but keep in mind that it'll hinder performance if SQL needs the larger set.

For 28GB of memory, I'd probably set the max memory setting to around 24GB.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ekelmans
Starting Member

7 Posts

Posted - 2011-03-27 : 07:25:34
This is no bug, it's a feature! (i always wanted to say that)

Your SQL server is actually using all the resources you've given it

-- Get PLE from sys.dm_os_performance_counters
SELECT cntr_value AS 'Page Life Expectancy'
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND counter_name = 'Page life expectancy'

This query tells you how many seconds a 8KB page from your database file is kept in SQL buffer memory, the Higher the better. Since SQL does not have to go to disk to get the data needed for a query.

By Restarting the server and forcing it to rebuild its cache in memory, you actually hurt SQL's performance!

Now go forth and be merry, all's well with your system!

grtz, T :)
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 15:39:38
Restrict the SQL Server to use RAM with AWE option,how much RAM you have allocated for SQL Server

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-04-16 : 04:37:13
1)Are you on a 32 bit or 64 bit system?This will impact decisions about using the PAE switch in boot.ini file.
2)Consider using Lock Pages in memory .


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -