SQL Server Performance

64-bit SQL and Insufficent memory msg!

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sql_jr, Nov 3, 2006.

  1. sql_jr New Member

    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.
  2. gurucb New Member

    * 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.
  3. sql_jr New Member

    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
  4. thomas New Member

  5. sql_jr New Member

    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?
  6. gurucb New Member

    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
  7. sql_jr New Member

    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
  8. gurucb New Member

    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
  9. sql_jr New Member

    I did need to grant the SQL Service account Lock Pages in Memory - Should this be of help?
  10. gurucb New Member

    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..
  11. sql_jr New Member

    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.
  12. sql_jr New Member

  13. sql_jr New Member

    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)

    ??????????????
  14. gurucb New Member

    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..
  15. gurucb New Member

    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
  16. sql_jr New Member

    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???
  17. sql_jr New Member

    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?
  18. gurucb New Member

    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.
  19. sql_jr New Member

    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.
  20. joechang New Member

    you should probably just file a case with MS PSS

    they should be able to trace anything that generates an actual error message
  21. sql_jr New Member

    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......
  22. gurucb New Member

  23. sql_jr New Member

    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?
  24. gurucb New Member

    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.
  25. satya Moderator

    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.
  26. sql_jr New Member

    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!
  27. joechang New Member

    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
  28. sql_jr New Member

    So, if it's 4-duo core, that would be 8, right?

    Thanks, again!
  29. gurucb New Member

    Hi what is the status... did you try with different max dop settings to see if this works.
  30. sql_jr New Member

    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.
  31. joechang New Member

    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
  32. sql_jr New Member

    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 />&gt;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=':D' />]
  33. gurucb New Member

    Great to hear that.. I had been a learning and challenging issue... to say the least
  34. niall.porter New Member

    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.
  35. sql_jr New Member

    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?

Share This Page