kpsenthilkumar86 avatar image
kpsenthilkumar86 asked

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

10 |1200

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

JohnM avatar image JohnM commented ·

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

0 Likes 0 ·
Show more comments
KenJ avatar image KenJ commented ·

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 Likes 0 ·
Show more comments

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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