SQL Procedure Cache getting wiped out every 60 seconds

All we need is an easy explanation of the problem, so here it is.

I have an Azure VM running SQL 2017, deployed around August 2020. Not yet taking a production load. Several databases restored, but no users active yet. Running sp_Blitz I was getting this: "The oldest query in the cache was created at "

So I ran this query every second for several minutes:

select 
datediff(second, min(creation_time), getdate() ) AS [Age seconds], 
count(*) as [Row Count] 
from sys.dm_exec_query_stats
[Age Seconds] would get up to 60, and [Row Count] would get up to
about 30 or 40 max, then it would reset to 0’s EVERY MINUTE.

This was going on for several days.
I disabled just about every login except mine, stopped ancillary services (Agent, SSIS, SSRS, etc.) and took user databases off-line. Nothing stopped it.

I see in the log several entries of

"A significant part of sql server process memory has been paged
out…"

Finally a re-start of the service did the trick.

SOMETHING SOMEWHERE was running DBCC FREEPROCCACHE every MINUTE like clockwork.

Has anyone ever encountered this, and if so, what was the resolution?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

"A significant part of sql server process memory has been paged out…"

I would start with above message. This message means SQL Server working set memory is paging out process to disk. A widely used workaround is, and since you have SQL Server running on Azure VM, to provide SQL Server service account Locked pages in memory (LPIM) privilege. Please refer to this MS support article . To assign LPIM

To assign the Lock pages in memory user right, follow these steps:

  1. Click Start, click Run, type gpedit.msc, and then click OK.

  2. Note The Group Policy dialog box appears.

  3. Expand Computer Configuration, and then expand Windows Settings.

  4. Expand Security Settings, and then expand Local Policies.

  5. Click User Rights Assignment, and then double-click Lock pages in memory.

  6. In the Local Security Policy Settingdialog box, click Add User or Group.

  7. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.

  8. Close the Group Policy dialog box.
    9.Restart the SQL Server service.

Method 2

Yes, MAX SQL memory is set to about 75% of total memory. As stated, there is no appreciable load on this server. I did find this article which exactly described my issue: 60 seconds, then 5 seconds of nothing, then repeat.
https://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/

I did end up granting LPIM to the service account before reading the responses here. Thanks for the confirmation.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply