none
memoryclerk consumes a lot of memory

    Question

  • Hi  guys,

    Could you explain me why memoryclerk_sqloptimizer consumes a lot of memory and how to solve it (release memory)?

    Please have a look reports below.

    1. Before

    2. After 5 days

    So we can  see that sqloptimizer became to consume about 1.9Gb instead of 1Gb  initially (in 1 day after server restart this value was just about 700Mb).

    After some days this behavior will lead to server  to be almost unavailable for application.

    Please suggest what can be a cause and how I can change this situation.

    Max memory 8Gb (server 32Gb).

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

    • Edited by AleshaDBA Wednesday, August 22, 2012 9:02 AM
    Wednesday, August 22, 2012 8:41 AM

Answers

  • Try the below:

    /**********************************************************  
    *   top procedures memory consumption per execution  
    *   (this will show mostly reports & jobs)  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
       
    ) T  
    ORDER BY IO_Per_Execution DESC 
       
    /**********************************************************  
    *   top procedures memory consumption total  
    *   (this will show more operational procedures)  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T  
    ORDER BY Total_IO_Reads DESC 
       
    /**********************************************************  
    *   top adhoc queries memory consumption total  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,QueryText          = qt.text  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
        GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T  
    ORDER BY Total_IO_Reads DESC 
       
    /**********************************************************  
    *   top adhoc queries memory consumption per execution  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,QueryText          = qt.text  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
        GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T 


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 24, 2012 8:31 AM

All replies

  • Hi,

    Please post the version of SQL Server you are using.  How much RAM does your machine have, and what is max server memory set at?



    Thanks, Andrew

    Wednesday, August 22, 2012 8:59 AM
  • Thanks! Modified the post.
    Wednesday, August 22, 2012 9:03 AM
  • After some days this behavior will lead to server  to be almost unavailable for application.


    Memeory management is highly dynamic in SQL Server. BTB, Is there lots of adhoc queries running in your server?

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, August 22, 2012 9:28 AM
  • Hi,

    Please see:http://sqlmonitormetrics.red-gate.com/sql-server-memory-manager-optimizer-memory-kb/

    Do you have a lot of ad-hoc queries?  As you can see from Brad's post, it may not be a simple thing to fix.  You may also consider turning on 'optimize for ad hoc workloads' using sp_configure.

    I presume that there's other applications running on the server if you've capped it 8Gb out of 32GB?



    Thanks, Andrew

    Wednesday, August 22, 2012 9:32 AM
  • So, please have a look info below.

    The data from sys.dm_exec_cached_plans

    Can it help?

    Yep. This server has several instances, but the problem occurs only on a one.

    However  I think that these cached plans are kept in cachestore_cp, cachestore_obj etc. What relations exist between adhoc and memoryclerk_sqloptimizer?

    I've should note for last several hours memoryclerk_sqloptimizer wasted additional 200Mb of memory

    • Edited by AleshaDBA Wednesday, August 22, 2012 11:30 AM
    Wednesday, August 22, 2012 11:22 AM
  • It does not look like you have many adhoc queries. When did you restart the server/service last time?

    SQL server uses memory for optimizing the queries. This is apart the procedure cache and data cache. memoryclerl_sqloptimizer will not have any cached plans for the queries or procs, but the memory used for its optimization.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, August 22, 2012 11:55 AM
  • Yes, I agree with you regarding memory. The server was restarted 10 days ago after sql_optimizer wasted almost all memory and SQL server became unavailable for application (I don't have any significant info about app :) )
    Wednesday, August 22, 2012 12:05 PM
  • Any chance you can get us the error logged at the fact? DBCC MEMORYSTATUS result also plz. 

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, August 22, 2012 1:25 PM
  • Please look at this.

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                              33739256
    VM Committed                             8750764
    Locked Pages Allocated                   0
    Reserved Memory                          1024
    Reserved Memory In Use                   0
    
    (5 row(s) affected)
    
    Memory node Id = 0                       KB
    ---------------------------------------- -----------
    VM Reserved                              9536
    VM Committed                             9248
    Locked Pages Allocated                   0
    MultiPage Allocator                      7664
    SinglePage Allocator                     4894536
    
    (5 row(s) affected)
    
    Memory node Id = 1                       KB
    ---------------------------------------- -----------
    VM Reserved                              33726072
    VM Committed                             8737976
    Locked Pages Allocated                   0
    MultiPage Allocator                      62488
    SinglePage Allocator                     4894536
    
    (5 row(s) affected)
    
    Memory node Id = 64                      KB
    ---------------------------------------- -----------
    VM Reserved                              2560
    VM Committed                             2504
    Locked Pages Allocated                   0
    MultiPage Allocator                      2416
    SinglePage Allocator                     4894536
    
    (5 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1864
    MultiPage Allocator                      8152
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     120
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1984
    MultiPage Allocator                      8152
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              33603584
    VM Committed                             8623744
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      416
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     416
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2091656
    MultiPage Allocator                      912
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLOPTIMIZER (node 1)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     17816
    MultiPage Allocator                      32
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLOPTIMIZER (Total)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2109472
    MultiPage Allocator                      944
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLUTILITIES (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              120
    VM Committed                             120
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     88
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLUTILITIES (node 1)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLUTILITIES (Total)         KB
    ---------------------------------------- -----------
    VM Reserved                              120
    VM Committed                             120
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     104
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              12416
    VM Committed                             12416
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2432
    MultiPage Allocator                      29792
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     576
    MultiPage Allocator                      3608
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              12416
    VM Committed                             12416
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3008
    MultiPage Allocator                      33400
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     552
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (node 1)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     512
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1064
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCLR (node 0)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSERVICEBROKER (node 0)    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     128
    MultiPage Allocator                      544
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLHTTP (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     80
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 1)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     64
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 64)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (Total)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     144
    MultiPage Allocator                      48
    
    (7 row(s) affected)
    
    MEMORYCLERK_FULLTEXT (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     24
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLXP (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     200
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (node 1)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     128
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (Total)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     328
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLQERESERVATIONS (node 0)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     4128
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              2816
    VM Committed                             2816
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (node 1)           KB
    ---------------------------------------- -----------
    VM Reserved                              1408
    VM Committed                             1408
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (Total)            KB
    ---------------------------------------- -----------
    VM Reserved                              4224
    VM Committed                             4224
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_HOST (node 1)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     48
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     5920
    MultiPage Allocator                      12992
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 1)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     4936
    MultiPage Allocator                      3648
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 64)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      2336
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (Total)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     10864
    MultiPage Allocator                      18976
    
    (7 row(s) affected)
    
    MEMORYCLERK_FULLTEXT_SHMEM (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              192
    SM Committed                             192
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
    ---------------------------------------------- -----------
    VM Reserved                                    0
    VM Committed                                   0
    Locked Pages Allocated                         0
    SM Reserved                                    0
    SM Committed                                   0
    SinglePage Allocator                           48
    MultiPage Allocator                            0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE (node 0)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3544
    MultiPage Allocator                      1992
    
    (7 row(s) affected)
    
    CACHESTORE_OBJCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     22992
    MultiPage Allocator                      1336
    
    (7 row(s) affected)
    
    CACHESTORE_SQLCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2678624
    MultiPage Allocator                      5344
    
    (7 row(s) affected)
    
    CACHESTORE_PHDR (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     11048
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XPROC (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     48
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_TEMPTABLES (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_NOTIF (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_VIEWDEFINITIONS (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBTYPE (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBELEMENT (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBATTRIBUTE (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 64)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      24
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTBLACS (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     40
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERKEK (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERDSH (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERRSB (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERREADONLY (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTO (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_EVENTS (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     888
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (node 1)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     360
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (Total)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1248
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (node 0)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (node 1)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (Total)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_FULLTEXTSTOPLIST (node 0)     KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SCHEMAMGR (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     7144
    MultiPage Allocator                      80
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1616
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (node 1)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     96
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (Total)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1712
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8384
    MultiPage Allocator                      528
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (node 1)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     240
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (Total)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8624
    MultiPage Allocator                      528
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     440
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (node 1)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     144
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (Total)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     584
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 0)                   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     160
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 1)                   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     144
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 64)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (Total)                    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     312
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     64
    MultiPage Allocator                      128
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (node 1)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     48
    MultiPage Allocator                      112
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (Total)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     112
    MultiPage Allocator                      240
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     880
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     872
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 64)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1752
    MultiPage Allocator                      168
    
    (7 row(s) affected)
    
    OBJECTSTORE_SERVICE_BROKER (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     424
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              32768
    VM Committed                             32768
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     14864
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 1)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16072
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 64)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     24
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (Total)         KB
    ---------------------------------------- -----------
    VM Reserved                              32768
    VM Committed                             32768
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     30960
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    Buffer Pool                              Value
    ---------------------------------------- -----------
    Committed                                1048576
    Target                                   1048576
    Database                                 358127
    Dirty                                    5398
    In IO                                    0
    Latched                                  8
    Free                                     80987
    Stolen                                   609462
    Reserved                                 348
    Visible                                  1048576
    Stolen Potential                         386337
    Limiting Factor                          17
    Last OOM Factor                          0
    Page Life Expectancy                     5529
    
    (14 row(s) affected)
    
    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                520519680
    Available Virtual Memory                 8761175425024
    Available Paging File                    33099771904
    Working Set                              9043939328
    Percent of Committed Memory in WS        100
    Page Faults                              385666186
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0
    
    (10 row(s) affected)
    
    Procedure Cache                          Value
    ---------------------------------------- -----------
    TotalProcs                               9509
    TotalPages                               339423
    InUsePages                               179
    
    (3 row(s) affected)
    
    Global Memory Objects                    Pages
    ---------------------------------------- -----------
    Resource                                 303
    Locks                                    3870
    XDES                                     112
    SETLS                                    8
    SE Dataset Allocators                    16
    SubpDesc Allocators                      8
    SE SchemaManager                         649
    SE Column Metadata Cache                 253
    SQLCache                                 20217
    Replication                              2
    ServerGlobal                             50
    XP Global                                2
    SortTables                               3691
    
    (13 row(s) affected)
    
    Query Memory Objects (internal)          Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                344380
    Current Max                              344380
    Future Max                               344380
    Physical Max                             769840
    Next Request                             0
    Waiting For                              0
    Cost                                     0
    Timeout                                  0
    Wait Time                                0
    
    (11 row(s) affected)
    
    Small Query Memory Objects (internal)    Value
    ---------------------------------------- -----------
    Grants                                   1
    Waiting                                  0
    Available                                17737
    Current Max                              18125
    Future Max                               18125
    
    (5 row(s) affected)
    
    Optimization Queue (internal)            Value
    ---------------------------------------- --------------------
    Overall Memory                           6885367808
    Target Memory                            3754950656
    Last Notification                        1
    Timeout                                  6
    Early Termination Factor                 5
    
    (5 row(s) affected)
    
    Small Gateway (internal)                 Value
    ---------------------------------------- -----------
    Configured Units                         28
    Available Units                          28
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         380000
    Threshold                                380000
    
    (6 row(s) affected)
    
    Medium Gateway (internal)                Value
    ---------------------------------------- -----------
    Configured Units                         7
    Available Units                          7
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         12
    Threshold                                -1
    
    (6 row(s) affected)
    
    Big Gateway (internal)                   Value
    ---------------------------------------- -----------
    Configured Units                         1
    Available Units                          1
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         8
    Threshold                                -1
    
    (6 row(s) affected)
    
    Memory Pool Manager                      Pages
    ---------------------------------------- -----------
    Reserved Current                         388
    Reserved Limit                           386715
    
    (2 row(s) affected)
    
    Memory Pool (internal)                   Pages
    ---------------------------------------- -----------
    Allocations                              609820
    Predicted                                801948
    Private Target                           0
    Private Limit                            0
    Total Target                             996147
    Total Limit                              996147
    OOM Count                                0
    
    (7 row(s) affected)
    
    MEMORYBROKER_FOR_CACHE (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              345263
    Rate                                     31
    Target Allocations                       539493
    Future Allocations                       0
    Overall                                  840499
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_STEAL (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              264169
    Rate                                     -2885
    Target Allocations                       458368
    Future Allocations                       0
    Overall                                  840499
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_RESERVE (internal)      Pages
    ---------------------------------------- -----------
    Allocations                              388
    Rate                                     -2365
    Target Allocations                       386684
    Future Allocations                       192460
    Overall                                  840499
    Last Notification                        1
    
    (6 row(s) affected)
    
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    

    Regarding errors in log. When server  was almost unavailble  in error log the following errors were occuring:

    The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.  Error: 18056, Severity: 20, State: 29.

    No other errors were noted before and after  a restart server.

    Wednesday, August 22, 2012 1:35 PM
  • Another strange thing from Memory status:

    Memory node Id = 0 KB ---------------------------------------- ----------- VM Reserved 9536 VM Committed 9248


    Memory node Id = 1 KB ---------------------------------------- ----------- VM Reserved 33726072 VM Committed 8737976


    MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2091656
    MultiPage Allocator                      912
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLOPTIMIZER (node 1)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     17816
    MultiPage Allocator                      32

    The memory  were allocated for node Id=1! But sqloptimizer uses SinglePage Allocator allocated  2091656Kb for node 0!  What does it mean?



    • Edited by AleshaDBA Wednesday, August 22, 2012 1:46 PM
    Wednesday, August 22, 2012 1:45 PM
  • Interested to look at the below output.

    WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage HAVING SUM (W2.Percentage) - W1.Percentage < 95;



    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Thursday, August 23, 2012 7:34 AM
  • Of course! But what do you think about my previous question about nodeid and sqloptimizer memory?

    Thursday, August 23, 2012 7:54 AM
  • For NUMA enabled system, many memory clerks will be logically on node 0. You can verify this from other divisions like cachestore memoryclerk, xp servicebrokers etc. Memory related issues are always hard to find the root cause. To me, it looks like you have some application/design issues with. I do not have a stright forward answer here, but trying to find/co-relate the root cause. It looks like you have lots of Linked server/Extended procs running on your server. RESOURCE_SEMAPHORE is the wait type related for your memory issue here.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Thursday, August 23, 2012 9:00 AM
  • Ok, but why is memory allocated from node 0 for optimizer, when almost all memory was allocated for node 1. How does it work?

    RESOURCE_SEMAPHORE is another symptom of problem.

    The developers seggested me restart server once in a week as decision for now. :)

    What do you think about the following which I can see on the server when I swtich on performance monitor with counters Logins and Logouts  per second.

    When just  switched on there were just about 2 connections and this situation was stable. But after about 5 minutes I saw the following

    Every second more than 40 connections logon and logout from server.

    So after server minutes I saw again 0  logouts and logins. How do you think the such behavior can be a cause of problem?

    Thursday, August 23, 2012 9:31 AM
  • (Login and Logout) records the logins to the server. it may be from application/DBAs/Developers/Linked server connections etc... 

    What are you seeing abnormal here(You look forward no connections to your server?). Probabaly I did not get your question???


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Thursday, August 23, 2012 10:19 AM
  • The question is why a one login needs to logon and logout from server 40 times per second. Can it be  a cause of problem? Each query needs at least 1 Mb. Can I equalize a one connection to  a one query (MARS is not enabled)?
    Thursday, August 23, 2012 10:54 AM
  • Is your logouts/sec match logins/sec? 

    I suggest you should have a look at profiler and make sure that you are actually utilizing connection pooling. Grab the audit login, logout events as well as RPC:Completed and look for calls to sp_reset_connection - that will indicate at least some pooling is going on.  Look at EventSubClass for Audit Login Event Class to verify the connection is pooled or non-pooled.

    If you see login and logout events with no sp_reset_connections then you need to look at your application code to nail down the issue.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    • Edited by Latheesh NK Thursday, August 23, 2012 12:08 PM
    Thursday, August 23, 2012 12:01 PM
  • Is your logouts/sec match logins/sec?   YES

    If you see login and logout events with no sp_reset_connections then you need to look at your application code to nail down the issue.

    It looks ok,  Audit Login, Audit Logout, RPC:Completed. All three events are subsequential.


    • Edited by AleshaDBA Thursday, August 23, 2012 12:18 PM
    Thursday, August 23, 2012 12:17 PM
  • Just to confirm all together did you look at my another question:" Look at EventSubClass for Audit Login Event Class to verify the connection is pooled or non-pooled."?


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 24, 2012 4:49 AM
  • Sorry, I remember there was something like pooling-2 in last column. But could you please explain what it means or at least maybe you have some references about this topic? Thanks in advance!
    Friday, August 24, 2012 6:21 AM
  • The data will show that you have a pooling in your application implemented. If it non-pool, means that the connection to the SQL server is not pooled. I guess am clear to you. I dont have a link handy. But definitely get back to you with the one soon.

    You have many prepared plans in your application. Could you please get the highest top 10 adhoc queries that using high memory(granted) and look at those for its performance.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 24, 2012 7:31 AM
  • Unfortunately I don't know how I can do it. How I can differ adhoc and not adhoc query.  I ran the following query

    select SUM(cast (size_in_bytes as  bigint)) from  sys.dm_exec_cached_plans where usecounts=1 and cacheobjtype='Compiled Plan'

    The result was 2199420928 Bytes (about 2 Gb). 

    From 10000 plans in cache  8794  have usecounts=1 is it ok or strange?

    • Edited by AleshaDBA Friday, August 24, 2012 8:23 AM
    Friday, August 24, 2012 8:19 AM
  • Try the below:

    /**********************************************************  
    *   top procedures memory consumption per execution  
    *   (this will show mostly reports & jobs)  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
       
    ) T  
    ORDER BY IO_Per_Execution DESC 
       
    /**********************************************************  
    *   top procedures memory consumption total  
    *   (this will show more operational procedures)  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T  
    ORDER BY Total_IO_Reads DESC 
       
    /**********************************************************  
    *   top adhoc queries memory consumption total  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,QueryText          = qt.text  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
        GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T  
    ORDER BY Total_IO_Reads DESC 
       
    /**********************************************************  
    *   top adhoc queries memory consumption per execution  
    ***********************************************************/  
    SELECT TOP 100 *  
    FROM 
    (  
        SELECT 
             DatabaseName       = DB_NAME(qt.dbid)  
            ,QueryText          = qt.text  
            ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
            ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
            ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
            ,Executions         = SUM(qs.execution_count)  
            ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
            ,CPUTime            = SUM(qs.total_worker_time)  
            ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
            ,MemoryWrites       = SUM(qs.max_logical_writes)  
            ,DateLastExecuted   = MAX(qs.last_execution_time)  
       
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
        GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ) T 


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 24, 2012 8:31 AM
  • Friday, August 24, 2012 8:58 AM
  • Hi AleshaDBA, did you ever find a resolution? We are on sql server 2012 with a similar (same?) problem. Our query optimizer memory is ever-increasing, up to 50+GB until the server can no longer process simple queries or logins and we have to restart. We do have lots of ad hoc queries but this is not new --- we have had plenty of ad hoc queries for several years and this problem has started happening in the last few weeks.  Thanks 


    Friday, May 24, 2013 4:53 PM
  • Sorry but I don't have an answer. We just moved this server  on the separate server with enough CPU and Memory resources and the problem went away.
    Monday, May 27, 2013 6:40 AM
  • hi,Latheesh NK

       did you have solven the problem of memoryclerk_sqloptimizer consumes a lot of memory ,and how did you done?

    Tuesday, August 27, 2013 7:44 AM