6

Screen grab of resource monitor

I've got two SQL Server 2012 instances running on a server with 32GB of RAM and running little else.

One instance is behaving normally. Its working set memory is running close to its commit which is also close to the maximum server memory setting in SQL Server.

The other instance (highlighted in the image) is not behaving as I would expect. No matter how I configure its minimum and maximum memory settings, I cannot get its working set memory over a few hundred MB, even though there is plenty of available RAM on the server.

This is having a very detrimental effect on its performance especially under heavy load. The database is well used and when this instance was working normally* it would quickly fill up to the maximum server memory setting of 20GB.

The server has been restarted. The instances have been restarted. I've tried changing to various min/max settings and stopping the instance which is working fine - just to check it's not interfering somehow. The problem remains the same, while one instance seems to respond normally to the minimum and maximum memory settings, the other does not.

* I have no idea what's changed otherwise I'd change it back.

  • Both OS and SQL Server are 64 bit.
  • The contents of sys.configurations is identical on both instances.
  • The databases on the 'low' server total in the 10s of GBs.
  • I'm confident enough load is being put on the server that it should like to use more memory. In the past it used to fill its ram allocation quite quickly.
  • I've done some large queries on tables which should have pulled data into memory.
  • This is exclusively a database server, nothing else is running on it and it's running noticeably slower since this problem began.
  • sys.dm_os_sys_memory and perfmon agree with resource monitor.
  • Target Server Memory (KB) = 10240008; Total Server Memory (KB) = 5237744
6

The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.

There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:

Using locked pages in the memory manager

Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:

Locked pages

Whereas Resource Monitor shows:

Resource Monitor

Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.

There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.

It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.

Generally speaking, locking pages in memory is a good thing.

  • You were right that this instance's account had been changed to LocalSystem. We've changed the sql server start-up user to an account without the locked pages privilege. This has dramatically improved our databases' performance. – 5uperdan Jun 9 '14 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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