What keeps clearing my query stats?

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:

image

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.

image

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

image

 

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.

image

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.

4 thoughts on “What keeps clearing my query stats?

  1. Thank you for a very clear explanation. I wonder if Windows is commonly configured with Lock Pages In Memory = FALSE for SQL Server. What is the default value? Please tell me it is TRUE for all reasonable combinations of SQL Server and Windows installation versions. If not then we need to be aware of which combinations have the wrong default and some of us might need to add a step to our install procedure.

    Like

    • The default is False. You need to explicitly enable the group policy item for the SQL Server service account to enable Lock Pages In Memory. You can easily validate an account via powershell.

      Like

  2. Thank you for the reply. If the default is for LPIM to be disabled, then I guessed the problem must be widespread. As it turns-out the problem is at least fairly old and somewhat well-known. This article by Kehayias is a few years old but seems especially clear and comprehensive through 2011: https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/. There are many other articles. The consensus of course in the dozens of articles I read or skimmed is what you said: current best practice is to enable LPIM. Good that you brought this up and that SQL Server Central published a link to your blog, since I think many “accidental” DBAs (nice way of saying limited formal training) like myself have still never heard of Lock Pages In Memory. You will probably help save somebody from the hours of work you must have spent chasing down the cause and solution to the problem.

    Like

    • I wouldn’t necessarily say the “default is disabled” I think a more accurate saying is that the permission must be explicitly granted. Since this isn’t a SQL configuration, there is no real “default”. I hope that helps.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s