1

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?

2
  • Do you happen to have min and max server memory set to the same value? Nov 5, 2020 at 16:46
  • Does Sp_blitz not point that out specifically ?
    – Shanky
    Nov 6, 2020 at 8:42

2 Answers 2

0

"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.

2
  • 3
    It's important to mention that the OP needs to set max server memory appropriately when enabling LPIM. It would also be good to check and see if any other applications are running on that server (that are leading to memory pressure and working set trims / paging). Nov 5, 2020 at 16:16
  • This is Azure VM so chances that other applications are running are minimal. But yes setting max server memory correctly is needed. Plus LPIM is recommended by MS when SQL Server runs on Azure VM
    – Shanky
    Nov 6, 2020 at 8:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.