question

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

sql-server-2017cacheprocedure-cache
5 comments
10 |1200 characters needed characters left characters exceeded

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

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 ·

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

0 Likes 0 ·

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 ·

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 Likes 0 ·
KenJ avatar image KenJ kpsenthilkumar86 ·

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 Likes 0 ·

0 Answers

· Write an Answer

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.