none
MEMORYCLERK_SQLOPTIMIZER taking lot of memory

    Question

  •  

    I have  sys.dm_os_memory_clerks output as follows . What is this  SQLOPTIMIZER for? Why this is consuming so much memory?   And also LOCK MANAGER .   The server is facing slowness since yesterday. I never saw SQLOptimizer in top memory consumer before.

     

    type

    SPA Mem, KB

    OBJECTSTORE_LOCK_MANAGER

    3308352

    MEMORYCLERK_SQLOPTIMIZER

    2100056

    CACHESTORE_OBJCP

    1402304

    CACHESTORE_SQLCP

    745056

    MEMORYCLERK_SOSNODE

    620176

    USERSTORE_TOKENPERM

    444024

    USERSTORE_SCHEMAMGR

    263968

    CACHESTORE_PHDR

    61352

    USERSTORE_DBMETADATA

    47696

    MEMORYCLERK_SQLGENERAL

    34320

     

    SQL Server 2008 R2, 64 Bit

     48 Logical CPU/4 Socket

    There server is having 128 GB RAM

     Max Memory is set to 110 GB

    NO other apps run on this .

    Page Life Expectancy is above 25000

    Free Pages are getting down drastically. But there is no memory related error logged in sql server log


    MCITP, MCTS, MCDBA,MCP
    Friday, June 24, 2011 10:41 AM
    Moderator

All replies

  • Hi Madhu 

     

    Please share Min Server Memory and MAX Server Memory in sql server configuration. you can run "execute sp_configure"  to see configuration. also share pagefile info.


    -Giri
    Friday, June 24, 2011 1:46 PM
  • Please check the DMV: - sys.dm_exec_query_optimizer_info


    Harsh Chawla Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Friday, June 24, 2011 2:27 PM