I was building out a blog post about cardinality today and noticed that sys.dm_exec_query_stats was getting cleared my my Surface Pro 3 dev instance about every 30 to 60 seconds.

I opened object explorer to check if any jobs were running in SQL Agent that may be running DBCC FREEPROCCACHE

I know that sounds odd but I often demo random things in the name of performance and this sounds like a “hands on troubleshooting” demo I may have setup.

SQL Agent was actually disabled so this was not the issue.

*Note: Don’t run DBCC FREEPROCCACHE in production it does exactly what it sounds like it will do.

Next stop: SQL Server Logs.  There were no messages about the cache being cleared. If you’re not sure what to look for in the logs here’s an example:

Again, this was not found in today’s log. What I did notice is plenty of messages reading “A significant part of sql server process memory has been paged out. This may result in a performance degradation.”

Could that cause the proc cache to clear?

Since the cache was getting cleared so quickly it was easiest to repeat the following query over and over until the cache cleared, which I’d know happened because the query would return 0 rows.

select plan_handle from sys.dm_exec_query_stats

I wouldn’t query sys.dm_exec_query_stats without a where clause on a production server since the results would probably be too large. This method worked out for this case since this is my dev box and I’m the only one using it.

As soon as the query stats dmv was cleared, I checked the log and sure enough; the message was there at the exact time the dmv cleared.

How do I resolve those messages?

Lock Pages in Memory will resolve the “A significant part of sql server process memory has been paged out. This may result in a performance degradation.” messages.

If you’re using SQL 2016 you can use the following query to determine if Lock Pages is enabled:

SELECT sql_memory_model_desc
FROM sys.dm_os_sys_info

Enable Lock Pages in Memory by doing the following:

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
7. In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.

Why is paging out SQL Server’s process memory a bad thing?

Well, to start with, you can see that the proc cache got flushed and that’s probably not good for performance. To dig any deeper we need to understand how memory works in Windows.

Let’s say you have Notepad.EXE open and Windows orders a trim of Notepad’s working set (RAM).

Which of the following can be paged out?

1. The associated DLL files
2. The EXE file
3. The unsaved text you’ve written
4. All text in the open document
5. All of the above

Take a moment and answer.

Did you say 5. All of the above?

If you did, I’m sorry that is not correct.

The answer is 3. The unsaved text. This is because the Page File only holds data that has been modified. If you’re going to free up RAM by swapping out used RAM pages for pages in the Page File on disk, why would you move the EXE or DLLs into the Page File? They’re already unmodified and on disk.

When we see the “A significant part of sql server process memory has been paged out. This may result in a performance degradation.” message, this indicates that memory has been swapped out to disk and cannot be used again until it is swapped back into RAM. This process can be time consuming and expensive.

My Surface was reporting 58% memory consumed. With so little RAM used you wouldn’t think that Windows would trigger a trim. After all SQL Server was only set to a maximum memory of 1024 MB. Also, increasing the maximum memory to 4096 MB did not help this problem. This is because we’re not talking about memory pressure in SQL, we’re talking about memory pressure in Windows.

Yet; after configuring Locked Pages In Memory for the SQL Server service account, the warning messages stopped appearing and my machine is still performing the same as before.

It’s usually a best practice to enable this policy but I never thought I’d need it on my dev/demo instance.

If you liked this post be sure to follow me on Twitter and LinkedIn. I always welcome endorsements and new followers.