locked
Plans getting removed from sys.dm_exec_cached_plans RRS feed

  • Question

  • We are seeing Query plans getting cleared from sys.dm_exec_cached_plans within seconds. Upon further investigating we found even though there is almost nothing in sys.dm_exec_cached_plans DMV CACHESTORE_SQLCP has more than 18 GB allocated. We tried to run DBCC FreeProcCache but that didn't changed anything.

    SQL error log doesn’t show anything and we don’t have any databases set to auto_close_on. 

    Not sure what else to check. Any help here would be appreciated.

    Below are other details regarding our environment

    Vesrion - Microsoft SQL Server 2016 Standard (SP1) (KB3182545) - 13.0.4001.0 (X64)
    OS - Windows Server 2012
    Total Memory - 128
    Max Memory - 110

    Thanks

    Prashant

    Thursday, October 18, 2018 11:39 PM

Answers

  • Hello Prashant,

    CACHESTORE_SQLCP is close to 19 GB. This stores the Adhoc query plan.

    1) Is Optimize for Adhoc workload enabled? If not, I will strongly advise you to test this.

    2) LPIM - you can definitely test. I usually enable on most database server after checking the below,

          -> Dedicated database server. Also SSIS, SSRS, SSAS or other SQL components not using much memory.

          -> We dont have any other process on the database server that uses much memory. 

    Above things can be tetsed to see if there is any releif.

    But the actual solution would be to go for the latest SP + CU.

    Regards;
    Vivek Janakiraman


    Tuesday, October 23, 2018 1:18 AM

All replies

  • Hello Prashant,

    When plan cache gets cleared. What is the value of PLE, Lazywrites, free list stalls, Stolen memory,database cache memory, free memory. Just wanted to check if you are having memory pressure that frees up the plan cache.

    IS LPIM enabled?

    Output of below query,

    select convert(decimal(18,2),pages_kb/1024.0) [In MB],type from sys.dm_os_memory_clerks order by pages_kb desc

    select convert(decimal(18,2),pages_in_bytes/1024.0/1024.0) [In MB],type from sys.dm_os_memory_objects order by convert(decimal(18,2),pages_in_bytes/1024.0/1024.0) desc

    Check the below article, 

    https://support.microsoft.com/en-us/help/3212523/fix-a-memory-leak-occurs-when-sql-server-procedure-cache-consumes-too

    Is Optimize for Adhoc workload enabled? You can test it, if not enabled.

    Also Upgrading to SP2 is rcommended (Not essentially a fix to your current problem without further investigation). But there are many features that will be available post SP2 on SQL 2016 Standard and also many bug fixes related to memory and other.

    Regards;
    Vivek Janakiraman

    Friday, October 19, 2018 2:28 AM
  • Hi Prashant.K.Jain,

     

    As mentioned by Vivek, if it is convenient, could you please try to install the Microsoft® SQL Server® 2016 Service Pack 2 (SP2).

     

    Here are two similar threads. You can refer to them.

     

    SQL 2016 Plan Cache Cleared Frequently

     

    Cleared cached plans on SQL Server

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 19, 2018 3:28 AM
  • Thanks for Quick response Emily/Vivek.

    Vivek,

    As mentioned plans are getting flushed every couple of seconds and we don't see any significant changes in below metrics when plans are flushed.

    PLE details is stable and >12000 S

    Memory clerks:

    In MB type
    35269.6 MEMORYCLERK_SQLBUFFERPOOL
    34081 MEMORYCLERK_SQLBUFFERPOOL
    19043.5 CACHESTORE_SQLCP
    859.26 USERSTORE_SCHEMAMGR
    796.73 MEMORYCLERK_SQLSTORENG
    477.13 CACHESTORE_COLUMNSTOREOBJECTPOOL
    412.25 MEMORYCLERK_XE
    288.34 MEMORYCLERK_SOSNODE
    255.39 MEMORYCLERK_XTP
    249.68 MEMORYCLERK_SQLSTORENG
    246.98 MEMORYCLERK_SOSNODE
    154.45 MEMORYCLERK_SQLGENERAL
    66.48 MEMORYCLERK_SQLCLR
    56.77 USERSTORE_TOKENPERM
    46.21 MEMORYCLERK_SQLCONNECTIONPOOL
    45.79 MEMORYCLERK_SQLCONNECTIONPOOL
    40.02 OBJECTSTORE_SNI_PACKET
    39.21 OBJECTSTORE_SNI_PACKET
    31.32 MEMORYCLERK_QUERYDISKSTORE
    26.47 MEMORYCLERK_SQLOPTIMIZER

    Memory objects:

    In MB type
    1954.25 MEMOBJ_SQLMGR
    1657.03 MEMOBJ_SQLMGR
    1468.5 MEMOBJ_SQLMGR
    1309.18 MEMOBJ_SQLMGR
    1127.11 MEMOBJ_SQLMGR
    1097.43 MEMOBJ_SQLMGR
    1009.42 MEMOBJ_SQLMGR
    986.83 MEMOBJ_SQLMGR
    786.12 MEMOBJ_SQLMGR
    769.03 MEMOBJ_SQLMGR
    739.57 MEMOBJ_SQLMGR
    737.28 MEMOBJ_SQLMGR
    716.3 MEMOBJ_SQLMGR
    710.61 MEMOBJ_SQLMGR
    697.03 MEMOBJ_SQLMGR
    695.42 MEMOBJ_SQLMGR
    649.26 MEMOBJ_SQLMGR
    627.39 MEMOBJ_SQLMGR
    516.52 MEMOBJ_SECOLMETACACHE
    412.13 MEMOBJ_XE_ENGINE


    LPIM is not enabled

    Lazywrites - <1 per minute

    free list stalls - <1 per minute

    Stolen memory - 31 GB

    database cache memory - 67 GB

    free memory - 12 GB

    We have "Query store" enabled on one of the databases and this seems to be related to memory leak issue you mentioned

    We have 7+ database servers and 1000+ databases running on SP1 and upgrading them to SP2 won't happen soon. Do you think enable LPIM will fix the issue?

    Thanks

    Prashant

    Tuesday, October 23, 2018 12:30 AM
  • Hello Prashant,

    CACHESTORE_SQLCP is close to 19 GB. This stores the Adhoc query plan.

    1) Is Optimize for Adhoc workload enabled? If not, I will strongly advise you to test this.

    2) LPIM - you can definitely test. I usually enable on most database server after checking the below,

          -> Dedicated database server. Also SSIS, SSRS, SSAS or other SQL components not using much memory.

          -> We dont have any other process on the database server that uses much memory. 

    Above things can be tetsed to see if there is any releif.

    But the actual solution would be to go for the latest SP + CU.

    Regards;
    Vivek Janakiraman


    Tuesday, October 23, 2018 1:18 AM
  • Thanks Vivek,

    We have 7 SQL servers (Physical machines) hosting around 1000 customer databases with exactly same schema and similar workload. CACHESTORE_SQLCP on other servers is around 1 GB and plans do not get flushed from sys.dm_exec_cached_plans on other servers. I believe high value for CACHESTORE_SQLCP and plans getting flushed from dm_exec_cached_plans dmv are related and happening due to SP1 BUG you refereed.

    Based on links Emily posted seems like people got it fixed after enabling LPIM and we are looking for a confirmation on this workaround.

    Thanks

    Prashant 

    Tuesday, October 23, 2018 4:32 PM
  • Hello Prashant,

    Sounds like a plan.
    Optimize for adhoc workload can also be tested. I have not encountered any issues on any servers after enabling it. But I have heard off issues related to plan stub lookup when this option is enabled, but I have not encountered it and seems little rare. Tetsing it will prove this in your servers.

    Regards;
    Vivek Janakiraman

    Tuesday, October 23, 2018 10:53 PM
  • Quick update,

    We enabled LPIM and restarted SQL services which the problem. Query plan cache is using much less memory and plans are no more getting flushed.

    Thanks for your help.

    Thanks

    Prashant

    Thursday, February 21, 2019 4:59 AM