locked
Procedure stats getting cleared very often RRS feed

  • Question

  • Recent days, in our organisation, we are facing with a strange issue.

    Our SQL Server has enough memory(256 GB) and CPU cores. There is no memory pressure(outcome of dm_os_sys_memory is "Available physical memory is high").

    Only problem is, the procedure stats sys.dm_exec_procedure_stats is getting cleared very often. Stats doesn't stay even for an hour.

    The reason why we need the stats is, it is being collected on daily basis with the help of the DMV dm_exec_procedure_stats. And, our SQL Developers used to do optimization on stored procedures, and once it is done, to know the effectiveness of the tuning activity, a comparison is being done between the collected historical stats and the current one.

    Since, the procedure stats is not staying on the cache, we are not able to set any benchmark.

    Kindly suggest some solutions.

    Version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Enterprise Edition: (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

    Tuesday, November 27, 2018 3:39 PM

Answers

  • I note that you are on the RTM version of SQL 2017. You should slap on the latest Cumulative Update - there will be no service pack for SQL 2017.

    Whether the CU will resolve your problem, I don't know. But Josh is right on the money that Query Store is better for the particular purpose you mention.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 27, 2018 10:12 PM
  • Hi senthil Kumar K P,

     

    As mentioned by Erland, please install the latest Cumulative Update for SQL Server 2017.

     

    Cumulative Update 12 for SQL Server 2017

     

    By the way, Could you please tell us whether databases autoclose property are enabled. If so, please disable them, because this will cause cache flush.

     

    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

    Wednesday, November 28, 2018 8:06 AM

All replies

  • Use the Query Store instead?

    Yes, the procedure cache will clear itself whenever it wants to.

    Josh

    Tuesday, November 27, 2018 7:43 PM
  • I note that you are on the RTM version of SQL 2017. You should slap on the latest Cumulative Update - there will be no service pack for SQL 2017.

    Whether the CU will resolve your problem, I don't know. But Josh is right on the money that Query Store is better for the particular purpose you mention.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 27, 2018 10:12 PM
  • Hi senthil Kumar K P,

     

    As mentioned by Erland, please install the latest Cumulative Update for SQL Server 2017.

     

    Cumulative Update 12 for SQL Server 2017

     

    By the way, Could you please tell us whether databases autoclose property are enabled. If so, please disable them, because this will cause cache flush.

     

    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

    Wednesday, November 28, 2018 8:06 AM
  • Hi Emily,

    No, we haven't enabled the Autoclose property.

    Wednesday, November 28, 2018 9:51 AM
  • Use the Query Store instead?

    Yes, the procedure cache will clear itself whenever it wants to.

    Josh

    Hi Josh,

    We haven't explored Query Store yet. Can you suggest any easily understandable article of it.


    Wednesday, November 28, 2018 10:40 AM
  • We haven't explored Query Store yet. Can you suggest any easily understandable article of it.

    There is a UI for a Query Store which is decently intuitive to get you started.

    But here are two reading items:



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, November 28, 2018 11:46 AM