SQL 2005 sp3 Cluster out of memory #701

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

SQL Server 2005 Forums

High Availability (2005)

SQL 2005 sp3 Cluster out of memory #701

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-15 : 20:54:27

I have a SQL 2005 sp3 32bit in win2003 R2 sp2 cluster environment.
During 'stress test' of an application developed with NHybernate I can see that almost no work is being put on the SQL server yet it manages to start depleting system ram at 30min into the test (from 3.3GB down to 350KB) until about 2.5hrs when the 300kb goes down to 80kb and then error #701 starts filling the errorlog.

I have already added '/3GB' switch in boot.ini , 'lock pages in ram' to the service account and this [last] time config. SQL min ram=1GB and max=3GB

All of my monitoring/optimizing of the server tells me the app. is doing something I do not yet understand but it is triggering SQL to eat the memory.
Out of small 10MB trace (40secs capture) profile files I can see [DTA] an average of 94 selects, 3 inserts and 2 updates. Most of the "select" queries I see are extremely long and there NO stord procs in the app. by 'design'! They tell is 'too late' to change the app.

I also ran another test on the 2nd node with [borrowed] 6GB also config boot.ini with /PAE and the result was about the same only more RAM more time but same error #701.

Anybody have any idea what's going on? is it NHybernate a good developing tool? any idea how could I "see" what's coming into the SQL server?
The trace files after being run in DTA are reported with 53% sintax errors so there are no recomendations!

Any idea/help/comment is greatly appreciated!
lec

Sr. SQL server & Oracle

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-15 : 22:23:08

You could do a Profiler (Or preferably, Server side) trace, then import that file into SQLNexus (http://sqlnexus.codeplex.com) or RML Utilities.

You could also look into the
sys.dm_exec_query_memory_grants DMV to see which queries are consuming the most memory, and waiting on memory.

You really should consider moving to a x64 environment, 32 bit is really dead technology, and any new development with any load should really be 64 bit.

Does the App use CLR? Are you running out of RESERVED_MEMORY?

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-16 : 16:13:06

Chad, thanks for the reply. I'll give a test to the utilities in another test run.
dm_exec_query_memory_grants DMV is empty, SQL hasn't been modified since the last error #701 (24hrs ago). I'm still looking into how to release the bulk of the memory (see below) without bouncing the instance.
I don´t see how the developers would've use CLR feature since the design was to avoid even use of stored procs because of transportability concerns.
32bit vs 64bit...well no need to even comment!
I also ran:
SELECT type
,SUM(virtual_memory_reserved_kb + awe_allocated_kb
+ shared_memory_reserved_kb + multi_pages_kb + single_pages_kb)
/ 1024 AS [Total Memory Reserved by Clerk (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY [Total Memory Reserved by Clerk (MB)] DESC

and got:

type Total
Memory
Reserved
by Clerk
(MB)
-------------------------------------- ---------
MEMORYCLERK_SQLBUFFERPOOL 4848
CACHESTORE_SQLCP 1247
MEMORYCLERK_SQLCLR 99
MEMORYCLERK_SQLGENERAL 38
MEMORYCLERK_SQLSTORENG 18
MEMORYCLERK_SOSNODE 14
OBJECTSTORE_LOCK_MANAGER 9
CACHESTORE_PHDR 7
CACHESTORE_OBJCP 4
USERSTORE_SCHEMAMGR 2
USERSTORE_DBMETADATA 2
MEMORYCLERK_SQLOPTIMIZER 1
USERSTORE_OBJPERM 0
USERSTORE_TOKENPERM 0
CACHESTORE_XMLDBTYPE 0
CACHESTORE_EVENTS 0
CACHESTORE_XPROC 0
OBJECTSTORE_SNI_PACKET 0
CACHESTORE_BROKERRSB 0
OBJECTSTORE_SERVICE_BROKER 0
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT 0
CACHESTORE_XMLDBATTRIBUTE 0
MEMORYCLERK_SQLHTTP 0
CACHESTORE_NOTIF 0
CACHESTORE_XMLDBELEMENT 0
MEMORYCLERK_SQLSOAP 0
MEMORYCLERK_QSRANGEPREFETCH 0
CACHESTORE_BROKERREADONLY 0
MEMORYCLERK_SQLCLRASSEMBLY 0
MEMORYCLERK_SQLQUERYCOMPILE 0
CACHESTORE_BROKERTO 0
CACHESTORE_BROKERKEK 0
MEMORYCLERK_SNI 0
MEMORYCLERK_FULLTEXT_SHMEM 0
CACHESTORE_BROKERUSERCERTLOOKUP 0
CACHESTORE_STACKFRAMES 0
MEMORYCLERK_SQLCONNECTIONPOOL 0
MEMORYCLERK_SQLSERVICEBROKER 0
MEMORYCLERK_SQLQUERYPLAN 0
OBJECTSTORE_LBSS 0
MEMORYCLERK_FULLTEXT 0
CACHESTORE_TEMPTABLES 0
CACHESTORE_BROKERTBLACS 0
MEMORYCLERK_SQLXML 0
USERSTORE_SXC 0
MEMORYCLERK_BHF 0
CACHESTORE_VIEWDEFINITIONS 0
MEMORYCLERK_SQLQUERYEXEC 0
CACHESTORE_BROKERDSH 0
MEMORYCLERK_SQLSOAPSESSIONSTORE 0
MEMORYCLERK_SQLQERESERVATIONS 0
MEMORYCLERK_HOST 0
MEMORYCLERK_SQLXP 0
MEMORYCLERK_SQLUTILITIES 0
CACHESTORE_SYSTEMROWSET 0

-lec

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 16:21:13

So, just to be clear...SQL's Errorlog has the 701 errors? How are you determining:

quote:
start depleting system ram at 30min into the test (from 3.3GB down to 350KB) until about 2.5hrs when the 300kb goes down to 80kb

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-16 : 16:57:00

Yes, errorlog has the 701 errors in it; I´ve seen 3 different "initial" msgs. right at the time when the 701 msgs. begin (i.e. A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1064964, committed (KB): 2454912, memory utilization: 43%.)

The timings I mentioned are, so far, consistent every time a 'stress_test' is run and I'm looking at Task Manager's physical memory and also at perfmon. I also have performance dashboard and DBA dashboard monitors loaded.

Any comments on the memory pools, how do tell about the Reserved_Memory ?

-lec

Sr. SQL server & Oracle

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 17:21:27

So the way SQL Server works is it will consume memory as it needs it up until it reachs the max server memory setting, or the Total Physical Memory - 2GB if the default setting is still in place. SQL does not release that memory unless windows is under memory pressure. What you are seeing above with 4.8 GB being Buffer and 1.2GB being Proc Cache looks pretty normal to me.

What is your setting for Max Server Memory, and Min Memory per Query? How much physical memory is on the server?

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-16 : 17:31:44

When I set the /3GB switch I also set the: min server=1.5GB and max server=3GB, min Qry=1.5MB
The box has 4GB but OS reports 3.25GB in system windows properties

- lec

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 17:44:36

Min memory per query should be 1024. Try turning off /3GB and turn on AWE.

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-16 : 18:37:17

I thought /3GB was required to enable AWE [in SQL srvr] ?
Could you elaborate on this pls.?

- lec

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-16 : 20:52:11

No, /3gb takes 1GB of memory from the kernel address space and gives it to user address space. With 4GB of memory, you don't need AWE if you have /3gb because with /3GB you have 3GB directly addresable in your user address space, no need for the AWE.

If you want to address > 2GB without using /3gb, you can enable AWE and address the 3rd GB through AWE. I would cap Max Server Memory at like 2.5GB though. See if that helps get rid of the 701 errors.

1. Remove /3GB
2. Enable AWE (or not.. only if 2GB is insufficient for your buffer pool)
3. Set Min Query Mem to 1024
4. Set Max Server Memory to 2560

What is the nature of the queries that are running? You are sure there are not using CLR (disable CLR and you will find out).

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-17 : 07:02:11

This makes sense, it seemed I was "patching" SQL to support a poorly designed app.
Last night we've [developers and team] agreed to disable 'some' option in the app. and although some workstations timeout SQL Server has no error #701.

How can I tell 'usage' of the allocated buffer pool?

- lec

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-17 : 07:28:02

You might advise the "designers" that their comment "They tell is 'too late' to change the app. will be transposed by you into "It's 'too late' coming to me now with this sh*t app without a change".

re the queries themselves....is the databse tuned for each item? poor index (and code) design will be brought to the fore during stress-testing. do you have any sample execution plans?
they may also not be closing connections to the server properly.

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-17 : 11:16:24

What do you mean by usage? The Memory_Clerks query you used above will tell you the sizes of the various pools. Use perfmon and track SQL:BufferManager:Page Life Expectancy. You want the average to stay above 300. If it is consistently below 300, then SQL needs more buffer memory (Or more efficient queries).

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-18 : 09:19:27

Meant I've seen comments refering to MS's document explaining those results/memory allocations but haven't got a link [that works] to it.

- lec

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-18 : 15:36:25

You might find this useful:

http://technet.microsoft.com/en-us/library/cc966540.aspx

-Chad

leoc50
Yak Posting Veteran

54 Posts

Posted - 2010-11-18 : 17:44:32

Thanks it sure is!

- lec