Sign in
 

none
memoryclerk consumes a lot of memory

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks! Modified the post.
    Wednesday, August 22, 2012 9:03 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Of course! But what do you think about my previous question about nodeid and sqloptimizer memory?

    Thursday, August 23, 2012 7:54 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    (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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 SQLZealots Thursday, August 23, 2012 12:08 PM
    Thursday, August 23, 2012 12:01 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Friday, August 24, 2012 8:58 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |