SQL Server Performance Forum – Threads Archive
64-bit SQL and Insufficent memory msg!
Hi, All: I have just installed a brand new 64-bit system, with 64-bit SQL Server 2005 sp1, and 12GB of RAM. The same queries and processes that worked on SQL Server 2000 Std Edition successfully, are failing on SQL2k5 64-bit [B)] After migrating the databases and jobs, I am frequently getting this msg: Insufficent memory to run this query First, I thought that SQL 2005 can take all the memory especially on 64-bit. And, please tell me, do I need to enable AWE, PAE on this architecture? I thought that is dynamically handled. Please help me resolve this asap! Thank you.* is NUMA enabled on server if so how many nodes.
* Check if Lock pages in memory is enabled for server.(Windows account).
* AWE is also going to do the same it enables lock pages in memory.
* For database enable forced parameterization.
* Run DBCC memorystatus output and paste it here may be cached plans are more.
* are there more sort and hash operations going on.
* For memory in MSDN there is a blog by Slava that talks about MEMORY.
Memory Manager KB
—————————— ——————–
VM Reserved 12678456
VM Committed 525432
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0 (5 row(s) affected) Memory node Id = 0 KB
—————————— ——————–
VM Reserved 12672824
VM Committed 519888
AWE Allocated 0
MultiPage Allocator 19856
SinglePage Allocator 93096 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2424
MultiPage Allocator 4568 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total) KB
—————————————————————- ——————–
VM Reserved 12615680
VM Committed 463360
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 184
MultiPage Allocator 128 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB
—————————————————————- ——————–
VM Reserved 120
VM Committed 120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB
—————————————————————- ——————–
VM Reserved 3456
VM Committed 3456
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1216
MultiPage Allocator 432 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCLR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 304 (7 row(s) affected) MEMORYCLERK_SQLHTTP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SNI (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_FULLTEXT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLXP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_HOST (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3520
MultiPage Allocator 13952 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_OBJCP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1752
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SQLCP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2200
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_PHDR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1648
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XPROC (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_TEMPTABLES (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_NOTIF (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_VIEWDEFINITIONS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBTYPE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBELEMENT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_STACKFRAMES (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_BROKERTBLACS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERKEK (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERDSH (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERRSB (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERREADONLY (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERTO (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_EVENTS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1536
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SCHEMAMGR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2392
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 504
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 176
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LBSS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 208 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 216
MultiPage Allocator 48 (7 row(s) affected) OBJECTSTORE_SERVICE_BROKER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (Total) KB
—————————————————————- ——————–
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 73632
MultiPage Allocator 0 (7 row(s) affected) Buffer Distribution Buffers
—————————— ———–
Stolen 1003
Free 379
Cached 10634
Database (clean) 16382
Database (dirty) 26578
I/O 0
Latched 0 (7 row(s) affected) Buffer Counts Buffers
—————————— ——————–
Committed 54976
Target 1282824
Hashed 42960
Stolen Potential 1482360
External Reservation 0
Min Free 512
Visible 1282824
Available Paging File 3608501 (8 row(s) affected) Procedure Cache Value
—————————— ———–
TotalProcs 32
TotalPages 703
InUsePages 0 (3 row(s) affected)
Global Memory Objects Buffers
—————————— ——————–
Resource 250
Locks 9207
XDES 44
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 295
SQLCache 212
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2 (12 row(s) affected)
Query Memory Objects Value
—————————— ———–
Grants 0
Waiting 0
Available (Buffers) 951220
Maximum (Buffers) 951220
Limit 951175
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 963975 (11 row(s) affected) Small Query Memory Objects Value
—————————— ———–
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800 (5 row(s) affected) Optimization Queue Value
—————————— ——————–
Overall Memory 8423350272
Target Memory 7948312576
Last Notification 1
Timeout 6
Early Termination Factor 5 (5 row(s) affected) Small Gateway Value
—————————— ——————–
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000 (6 row(s) affected) Medium Gateway Value
—————————— ——————–
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12 (5 row(s) affected) Big Gateway Value
—————————— ——————–
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8 (5 row(s) affected)
Thanks for reply. Please stick w/me here. How can I tell if NUMA is enabled? Should Lock Page in memory be granted, I assume?
Does AWE need to be manually enabled here YES/NO?
Also, please see output below: I await your reply. Apprecate it!
MEMORYBROKER_FOR_CACHE Value
——————————– ——————–
Allocations 10636
Rate 0
Target Allocations 979892
Future Allocations 0
Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_STEAL Value
——————————– ——————–
Allocations 997
Rate 0
Target Allocations 970253
Future Allocations 0
Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_RESERVE Value
——————————– ——————–
Allocations 0
Rate 0
Target Allocations 1028241
Future Allocations 237793
Last Notification 1
Here’s the link for Slava Oks’s blog https://blogs.msdn.com/slavao/default.aspx
Thanks! Should have looked there first <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br />Yes NUMA enabled.<br /><br />Seem like for 2005 there’s quite a bit to know and configure about Memory configuration…It’s harking back to the day of 6.5 – why so complicated?
There seems to be issue with some locks on the database
If we look below Min Free > Free that means my take (may be wrong) is sql server is gasping for memory?? Also Lock manager seems to consuming most of memory is there any blocking going on the server and is lock escalation disabled.
Check in startup if -T1211 is enabled or dbcc tracestatus(-1). It is not a NUMA enabled machine.. Look for perfmon counters and look for Memory
Available MBytes
Pages / second PRocessor:
Priv time
User Time
Process:
Working set size SQL Server.
SQL Server Buffer Manager:
Cache hit ratio. SQL Server memory manager:
Min server memory and max server memory. SQL Server Access Methods:
Full scans / seconds
Also check for Sp_lock and if there is blocking on server
Min Free 512
Free 379
OBJECTSTORE_LOCK_MANAGER (Total) KB
—————————————————————- ——————–
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 73632
MultiPage Allocator 0
Well I ran this query, and base on that it said NUMA enabled:
select CASE count( DISTINCT parent_node_id) WHEN 1 THEN ‘NUMA disabled’ ELSE ‘NUMA enabled’ END from sys.dm_os_schedulers where parent_node_id <> 32 I don’t see any blocking, but this is output from sp_lock: 51700DB SGRANT
52500DB SGRANT
53700DB SGRANT
58111151510180TAB ISGRANT
The obj_id = spt_values
if so why is Lock manager is taking so much of memory it is actually consuming 590 megs of memory If there are not lock why should it so.. May be my hypothesis is wrong about locks. Is the performance still slow and can you run dbcc memorystatus again and compare with one posted. Also after dbcc memorystatus run DBCC freesystemcache and see what happens Details:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4b5c460b-e4ad-404a-b4ca-d65aba38ebbb.htm
I did need to grant the SQL Service account Lock Pages in Memory – Should this be of help?
if sql sever 2005 is enterprise edition it should help.. also look for hp ilo driver if it is hp system.
Gogo msinf32 and search for cpqcidrv they have some issues about paging occuring and disable it..
Can anyone tell me, after Lock Pages in Memory and reboot, if this output looks better? Sorry for so much space usage: Memory Manager KB
—————————— ——————–
VM Reserved 12677584
VM Committed 793048
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0 (5 row(s) affected) Memory node Id = 0 KB
—————————— ——————–
VM Reserved 12671952
VM Committed 787504
AWE Allocated 0
MultiPage Allocator 20664
SinglePage Allocator 213824 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3416
MultiPage Allocator 4568 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total) KB
—————————————————————- ——————–
VM Reserved 12615680
VM Committed 731904
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 360
MultiPage Allocator 128 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB
—————————————————————- ——————–
VM Reserved 720
VM Committed 720
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB
—————————————————————- ——————–
VM Reserved 1088
VM Committed 1088
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1288
MultiPage Allocator 520 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 376
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCLR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 304 (7 row(s) affected) MEMORYCLERK_SQLHTTP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SNI (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_FULLTEXT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLXP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_HOST (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4400
MultiPage Allocator 13824 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_OBJCP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 47400
MultiPage Allocator 32 (7 row(s) affected) CACHESTORE_SQLCP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 68376
MultiPage Allocator 32 (7 row(s) affected) CACHESTORE_PHDR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6328
MultiPage Allocator 48 (7 row(s) affected) CACHESTORE_XPROC (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_TEMPTABLES (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_NOTIF (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_VIEWDEFINITIONS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBTYPE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBELEMENT (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_STACKFRAMES (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_BROKERTBLACS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERKEK (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERDSH (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERRSB (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERREADONLY (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERTO (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_EVENTS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1768
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SCHEMAMGR (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2072
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1936
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 176
MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LBSS (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 192
MultiPage Allocator 944 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 432
MultiPage Allocator 48 (7 row(s) affected) OBJECTSTORE_SERVICE_BROKER (Total) KB
—————————————————————- ——————–
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (Total) KB
—————————————————————- ——————–
VM Reserved 32768
VM Committed 32768
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 73824
MultiPage Allocator 0 (7 row(s) affected) Buffer Distribution Buffers
—————————— ———–
Stolen 1285
Free 11230
Cached 25443
Database (clean) 27070
Database (dirty) 23004
I/O 0
Latched 0 (7 row(s) affected) Buffer Counts Buffers
—————————— ——————–
Committed 88032
Target 1328650
Hashed 50074
Stolen Potential 1467269
External Reservation 0
Min Free 512
Visible 1328650
Available Paging File 3600146 (8 row(s) affected) Procedure Cache Value
—————————— ———–
TotalProcs 831
TotalPages 15287
InUsePages 50 (3 row(s) affected)
Global Memory Objects Buffers
—————————— ——————–
Resource 255
Locks 9231
XDES 50
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 258
SQLCache 318
Replication 2
ServerGlobal 60
XP Global 2
SortTables 2 (12 row(s) affected)
Query Memory Objects Value
—————————— ———–
Grants 0
Waiting 0
Available (Buffers) 985600
Maximum (Buffers) 985600
Limit 985615
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 998415 (11 row(s) affected) Small Query Memory Objects Value
—————————— ———–
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800 (5 row(s) affected) Optimization Queue Value
—————————— ——————–
Overall Memory 8724381696
Target Memory 8113102848
Last Notification 1
Timeout 6
Early Termination Factor 5 (5 row(s) affected) Small Gateway Value
—————————— ——————–
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000 (6 row(s) affected) Medium Gateway Value
—————————— ——————–
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12 (5 row(s) affected) Big Gateway Value
—————————— ——————–
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8 (5 row(s) affected) MEMORYBROKER_FOR_CACHE Value
——————————– ——————–
Allocations 25445
Rate 0
Target Allocations 1014535
Future Allocations 0
Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_STEAL Value
——————————– ——————–
Allocations 1279
Rate 0
Target Allocations 990369
Future Allocations 0
Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_RESERVE Value
——————————– ——————–
Allocations 0
Rate 0
Target Allocations 1064988
Future Allocations 246403
Last Notification 1 (5 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Could this be an issue: http://support.microsoft.com/kb/923624/en-us?spid=2855
Here’s some critical info: The server has 12GB, max/min are default Total Mem Usage shows only 940 MB – yes megabytes, while target is 10000 MB (10GB) ??????????????
To be honest I would not think so.. If you look at KB TOKENPERM value is high but not in your case.. Infact that was the first thing I checked with earlier snapshot of dbcc memorystatus output. even in second snapshot it is less..
Other than locks I do not seee anything high. What about query that you using are you using lot of order by clause.. if so can you disable order by clause and check..
I think our time is sychronized perfectly after I post I see you have just updated new information… OK is it always 940 MB or does grow gradually and then fall down drastically.. In error log are you seeing any informational message like lock page in memory privilege not available. Also It is a HP System if so can you check for HP Ilo driver in MSINFO32 search for cpqcidrv.sys and if so can you disable it. There are couple of issue from HP ILo driver on a 64 bit edition. What else look for paging on server. Something is not making sense for me atleast
Thanks a lot for helping – I still need to work toward resolution. I will look into the HP issue. As for total mem usage, it seems consistently low. No other errors than the 701 Insufficient Memory in the log. Ideas???
Ok, checked MSINFO32 and have that driver running. Now, SHOULD, I stop that, and how? I’m not sure what to look for in the device manager. Doesn’t seem I can stop it directly from MSINFO32 – please advise?
I think you can do it from Device manager under system services or something like that I do not completely recall. And also from BIOS.. Wait let me check for a link. Did not get any but I think if we go to device manager under system services we will see HP ILO and that can be disabled and restarted.
Hi, gurucb: Before I disable anything, do you have any tech articles or links on this type of behavior that may cause this msg? Any ideas from the forum folks out there? Calling all 2K5 experts, please advise.
you should probably just file a case with MS PSS they should be able to trace anything that generates an actual error message
Well, I was trying to avoid that, hoping someone would have had this experience or had any ideas to resolve.
That would be my next move……
See this: http://h20000.www2.hp.com/bizsuppor…c=us&objectID=c00688313&jumpid=reg_R1002_USEN
Hey, gurucb: That was a great link! I really thought I might be onto something, but the version 1.9.3790.0 of the driver which states fixes the error, is the same one active on the box 🙁 Am I reading this right?
OK then may be we hit road block on that end. but there are still other avenues to look for.. Run profiler and select all events / columns with out filter (if it is not production)(under that select under performance all parallelism events) and then reproduce the problem copule of times by running query. Also in SP_configure change max degree of parallelism to 1 and run same query to check if we still get errors. Best would be run PSSDiag and see the data.
PSSdiag on SQL 64 bit I’m not sure it will run properly as we had problems earlier. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Thanks!! Great IDEA MAXDOP- I think I’m onto something! I seem to remember this an issue w/earlier versions of SQL and MAXDOP with 8 processors or more. More info on our HW – it is a 16-way box, but its 4 duo-core with Hyperthreading – that’s 4x2x2 (16) The original setting was MAXDOP=8 (what is default?) However, going forward how can I ensure that the server is maximizing use of its processing power?? PLease advise! So, far, no error (fingers crossed) but is this a good long-term solution???
Many thanks!
maxdop 8 or rather max degree of parallelism
is a good starting point in your case,
that is 1 for each core, but not 1 for each logical proc for transaction processing app, consider maxdop 1 or 2 during business hours, 8 during maintenance if 8, then for each large or problem query, test maxdop 1, 2, 4, 8 with STATISTICS TIME ON to determine the best balance between duration and CPU
So, if it’s 4-duo core, that would be 8, right? Thanks, again!
Hi what is the status… did you try with different max dop settings to see if this works.
Thanks for asking…The concern is MS is unable to reproduce this issue, and we’re still investigating. Maybe a HW issue? Although I forwarded the HP tech article for reveiew. Will post back here.
for lack of better idea, and hard indicator,
i would suggest
1. disabling HT
2. boot the OS to 4 proc (/NUMPROC=4) if that works, drop the numproc boot.ini setting
After various affinity tests, and physical swapping, we believe it might be a processor issue…. We tried all combinations. Any combination of CPU’s from 1-7 processors works and never fails, however under the following condition we get it to fail: <br /><br /><br />>3 processors and using the CPU designated as CPU2. – IT WILL ALWAYS FAIL. As soon as we uncheck this CPU, with any #of processors, it WORKS!!! <br /><br /><br />Edit: Looks like we nailed it! BAD PROCESSOR!! We’re contacting our HW vendor to get a new processor – may be disappointing for future people who find this thread – but HW is not invincible.<br /><br />Thanks ALL for your help! [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]
Great to hear that.. I had been a learning and challenging issue… to say the least
Is the machine a multi-CPU AMD-based server? If so then the memory for these is arranged in banks, one bank per CPU (as in physical CPU, not per core in a CPU). I’ve seen strange "out of memory" messages on dual/multi-CPU Opteron workstations and servers which have all the memory in one bank instead of spread equally across both/all banks.
Hi, Niall The machine is a multi-CPU, but Intel Xeon based server with 4 dual-core processors. Perhaps the same thing here (?) But replacing the processor solved the issue convincingly. THoughts?
]]>