Memory getting full

Question

Hi,

The company that I work they did an implementation of a system they sale, and problems are ocurring in the database.

The instance is not releasing the memory that it uses and is using all the memory of the server.

We have to release the memory manualy and in like about 15 minutes sometimes less all the memory gets full again.

The database is set to awe enable 1, min memory 1g and max memory 6g, and the service account have the permission to lock pages in memory.

What can be causing this?

Answer 1

what is your SQL instance 2000 or 2005?

Answer 2

SQL Server 2005

Answer 3

2005

Answer 4

As a default SQL Server uses ALL memory, including virtual memory, and will not release memory unless it gets to a point of "extreme" memory pressure.  You need to set the max memory setting to limit the memory usage.  A good starting place is to set the max to the physical RAM size minus 2gb for the OS and other applications.  Then monitor performance and make sure the system is not paging and change the setting appropriately.

Answer 5

Hi,

The company that I work they did an implementation of a system they sale, and problems are ocurring in the database.

The instance is not releasing the memory that it uses and is using all the memory of the server.

We have to release the memory manualy and in like about 15 minutes sometimes less all the memory gets full again.

The database is set to awe enable 1, min memory 1g and max memory 6g, and the service account have the permission to lock pages in memory.

What can be causing this?

Kindly post your servers hardware configuration and the information returned by SELECT @@VERSION so that we can offer better ideas how to troubleshoot this.  As Tom points out, if you set Max Server Memory to 6GB and there is 8GB RAM on the server, SQL will use up to 6GB for the buffer pool, and then a small additional calculated amount of VAS (generally around 384MB but could be more) for the SQLOS and multipage allocators.  SQL Server is a memory dependant application, it will use everything you throw at it in most cases, and you should force it to free memory, that's not how it is designed to work.

Ideally, SQL Server is running on a dedicated server that has no other applications installed on it.  In this configuration, you can properly adjust your MaxServerMemory settings so that when under normal load the Memory\Available MBytes counter never falls below 100-150MB which means that the OS has additional memory to manage itself while SQL has commited the maximum amount of memory for its cache use.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Select @@version
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

OS Windows 2003 Server SP2
Memory 8 GB
DataBase size 57 GB

SQL Memory
AWE = true
Minimum of server memory in (MB)  = 0
Maximum of server memory in (MB) = 6656

Answer 7

Please see this KB article:  http://support.microsoft.com/default.aspx/kb/321363

Answer 8

You actually are set at 6.5GB of memory for SQL which could be problemattic.  What specifically is the problem that you are trying to address.  If it is that SQL uses all the memory, then there is no fix for that, it is how it is supposed to work, unless you back the MaxServerMemory settings down.  It's not going to release memory after commiting it either in most cases which is the way it should work.

What performance problem are you experiencing inside the database that caused you to look at this to begin with?  A 57GB database isn't that big, but it isn't small either.  What is the rest of the hardware configuration like?  #Disks, Raid Level, #CPU's?  What is the outcome of the following query:

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP', 
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
SELECT W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold


-- Signal Waits for instance
SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
       '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats;


-- Page Life Expectancy value
SELECT cntr_value AS 'Page Life Expectancy'
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy';


-- Buffer Pool Usage 
SELECT TOP (10) [type], sum(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type  
ORDER BY SUM(single_pages_kb) DESC;

This script comes from the following link:

http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!1340.entry

there are also other really good scripts to get more information on the system on the above link, but the quoted portion in this post will tell the most about where exactly your problems on that server lie.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 9

Intel(R) Xeon (R) CPU

5430@ 2.00 GHz 8.00GB RAM

ftServer 4400RIs 5.0.1372.0

4 processor

 

All response from the server are to slow

Answer 10

My guess would be your server is page swapping.  What is your pages per sec?

Answer 11

queries result:

wait_type	wait_time_s	pct	running_pct
CXPACKET	59055.75	24.09	24.09
WAITFOR	41653.47	16.99	41.08
ASYNC_NETWORK_IO	29141.73	11.89	52.96
PAGEIOLATCH_SH	24913.63	10.16	63.12
SOS_SCHEDULER_YIELD	13433.23	5.48	68.6
OLEDB	13410.91	5.47	74.07
MSQL_XP	10202.8	4.16	78.23
LATCH_EX	8736.33	3.56	81.8
LCK_M_S	8032.2	3.28	85.07
PAGEIOLATCH_EX	7503.28	3.06	88.13
SLEEP_BPOOL_FLUSH	5971.63	2.44	90.57
WRITELOG	4923.81	2.01	92.58
ASYNC_IO_COMPLETION	3983.89	1.62	94.2
BACKUPBUFFER	3932.61	1.6	95.81

%signal (cpu) waits	%resource waits
1.2	98.8

Page Life Expectancy

7418

type	SPA Mem, Kb
CACHESTORE_OBJCP	52128
CACHESTORE_SQLCP	27248
OBJECTSTORE_SNI_PACKET	7544
CACHESTORE_PHDR	6352
MEMORYCLERK_SOSNODE	5504
MEMORYCLERK_SQLGENERAL	5288
USERSTORE_SCHEMAMGR	3656
MEMORYCLERK_SQLSTORENG	2984
MEMORYCLERK_SQLCONNECTIONPOOL	2888
OBJECTSTORE_LOCK_MANAGER	2584

Answer 12

With a PLE of 7418, SQL Server does not appear to be under memory pressure. Your CXPACKET waits are your top wait type at 24%. I would try changing MAXDOP to 1, especially if this is an OLTP workload. The code block below shows you how to do this.

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current values are
EXEC sp_configure

-- Set MAXDOP = 1 for the instance
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO

I would also strongly consider consider installing at least SQL Server 2005 SP3, since you are on a pretty old build of SQL Server 2005 (Build 3042). There were a lot of fixes for memory related issues in newer builds of SQL Server 2005.

---

http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.

Answer 13

 +1 to what Glenn recommended about MAXDOP = 1 and thank you Glenn for responding to this post.  Another thing to keep in mind is that if you recently restarted the SQL Services on this server, these wait types need to be checked again after the server has been running for a period of normal usage.  You have some IO related waits in your top waits, so IO bottlenecks could be contributing to the problem as well.  I'd also recommend checking if the server has HyperThreading enabled and if so, look at disabling that.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 14

thks, glen, jonathan and tom for the recomendations (all the help) I will keep inform how everithing goes.

Answer 15

Hi Amendez

This is Mark, Microsoft SQL Support Engineer. I'm glad to assist you with the problem.

After reviewing the public reply to you, I agree with glen, jonathan and tom. after taking their suggestion, please keep monitor the issue to see how things will go. If you need us further assistance, please don't hesitate to tell us.

Regards
Mark Han 

Answer 16

Hi,

The problem got solved setting the 'max degree of parallelism' to 3.

Thks really for all the help.