DBCC MemoryStatus dump and help 'Insufficient memory'

Hey guys. Today morning at about 9:58am I had an insufficient memory exception in SQL. I've reserved 12gb memory for my SQL server. I've pasted below the dbcc MemoryStatus dump gotten from SQL log. It also gave me a BPool::Map: No remappable address found

My SQL Server version is 8.00.2148. Enterprise Ed. Can you see any problems in the below pasted log?

2006-08-09 09:58:11.63 spid175 BPool::Map: no remappable address found.
2006-08-09 09:58:11.69 spid101 Buffer Distribution: Stolen=156843 Free=1634 Procedures=17623
Inram=0 Dirty=108669 Kept=0
I/O=0, Latched=1220, Other=1286875
2006-08-09 09:58:11.69 spid101 Buffer Counts: Commited=1572864 Target=1572864 Hashed=1396751
InternalReservation=510 ExternalReservation=130 Min Free=1552 Visible= 185824
2006-08-09 09:58:11.69 spid101 Procedure Cache: TotalProcs=3811 TotalPages=17623 InUsePages=16353
2006-08-09 09:58:11.69 spid101 Dynamic Memory Manager: Stolen=151680 OS Reserved=1432
OS Committed=1397
OS In Use=1387
Query Plan=90752 Optimizer=21
General=17603
Utilities=3220 Connection=10805
2006-08-09 09:58:11.69 spid101 Global Memory Objects: Resource=6560 Locks=31195
SQLCache=651 Replication=2
LockBytes=2 ServerGlobal=22
Xact=1021
2006-08-09 09:58:11.69 spid101 Query Memory Manager: Grants=2 Waiting=0 Maximum=26799 Available=3906

Thank you.

Are you running a server app on this SQL Server machine?|||I don't quite understand your question but I think this is what you are asking. There are 4 machines which could acces the db server. Two of these are the web servers. The front app is written in vb.net. The other two are backend application servers which have windows services installed on them. What they do is, they read data from a file and depending on the data, do some ins,upd,del etc. I hope I answered your question...|||Hi Tej, I experienced a similar problem a week back. The performance

counters were normal but it ran out of memory for some reason. A

restart fixed it but I don't know the cause.|||

Tej,

I'm having the same issue on build 2187. I'm working with PSS on resolution (SLOWLY). It's my theory that the optimizer is the root issue as dbcc memorystatus is bringing back huge swings within a minute or two timeframe (from 0 to 125,000 buffers) just before the 701 error pops into the applog. Have you been able to reproduce this issue or find a 701 error message in your application log? It doesn't show up in the sql server error log... but there is a good command for getting a mini-dump during the 701 errors. Simply use dbcc dumptrigger ('set',701) . I wouldn't recommend using this "just because" but it will help MS PSS further diagnose your problem.

|||

Determining the root cause of a memory issue is sometimes difficult.

From the output in the original log it seems there are a number of contributing factors.

Here's the breakdown on that system.

There are 185824 (Visible= 185824) buffers in virtual address space that can be mapped in at any one time. This is a normal value on 32 bit systems.

Of those buffers 156843 are "stolen" (Stolen=156843) - or in use by a component other than the buffer pool.

Stolen buffers can further be broken down:

- Procedure cache 17623
- Query Plan=90752
- Connection= 10805
- Locks=31195

Procedure cache and query plan could well be driven up by a large number of adhoc queries that are not parameterized.

The connection size indicates there are a fair amount of client connected.

The memory stolen for locks is large, but not abnormal.

Resolving the insufficient memory issues here will most likely involve some tuning of the apps to use better parameterized queries, and improving query plans to reduce the number of locks taken.

|||

Hi, I have a similar question in that our server only shows 100MB of memory being used by SQL when AWE is enabled and we have 8GB in the box with a max setting of 7GB. We have PAE enabled and the boot.ini is below as well. Does this dump mean that SQL is only using 104MB of memory at the moment?

Thanks in advance,

Scott

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /noexecute=OptIn /PAE

Here is our dump.

Memory Manager KB

--

VM Reserved 1674140

VM Committed 104384

AWE Allocated 6747704

Reserved Memory 1024

Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

--

VM Reserved 3456

VM Committed 34976

AWE Allocated 2540400

MultiPage Allocator 3264

SinglePage Allocator 183128

(5 row(s) affected)

Memory node Id = 1 KB

--

VM Reserved 1666588

VM Committed 65460

AWE Allocated 4207304

MultiPage Allocator 9632

SinglePage Allocator 183128

|||

I presume you see the 100MB value through task manager or perfmon. This is normal when AWE is enabled as these tools do not account for physical page allocations done by applications.

Have a look at the AWE Allocated entry. It indicates that over 6GB has been allocated.

AWE Allocated 6747704

Another way to confirm is to look at the SQL Server:Memory Manager\Total Server memory(KB) counter from performance monitor.