Procedure stats getting cleared very often

Question

question

kpsenthilkumar86 asked Nov 20, '18

Procedure stats getting cleared very often

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: )

sql-server-2017 cache procedure-cache

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Nov 20, 2018 at 01:28 PM

How often is often? If the procedure cache is removed it's corresponding row in this DMV will be removed as well.

0 ·

kpsenthilkumar86 JohnM commented · Nov 20, 2018 at 02:34 PM

Yes John. It is removed very frequently, statistics stays for maximum an hour. Also, as you mentioned, removed from procedure cache as well.

0 ·

KenJ commented · Nov 21, 2018 at 12:46 PM

are all plans disappearing at the same time?

nothing scheduled to run dbcc freeproccache or anything similar (nobody trying to be helpful and clearing the cache ad-hoc or recompiling stored procedures)?

is 256GB how much RAM you have in the server? what do you have your SQL Server max memory set at?

0 ·

kpsenthilkumar86 KenJ commented · Nov 27, 2018 at 03:26 PM

KenJ,

We didn't schedule any DBCC freeproccache or similar. If that so, clearing would happen on a regular interval, but, for us, it is not happening on any regular interval. One day we are able to see statistics for more than 3 or 4 hours, another day at the same time we are able to see statistics for less than an hour.

Out of 256 GB - SQL Max memory is 200 GB and rest for OS and SSRS.

0 ·

KenJ kpsenthilkumar86 commented · Nov 29, 2018 at 02:42 AM

I'm guessing that dm_os_sys_memory isn't going to show pressure on your server since SQL is properly capped, so the OS won't send a mem low signal to SQL.

You might be better off looking into buffer pool usage - if you are seeing internal pressure within SQL, that will push plans out of cache even if the OS has memory to spare. Is your cache hit ratio looking good? Page life expectancy?

Some of the items in this article on SQL memory could be useful to you - https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/

0 ·

question