9

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
Improve this question
0

1 Answer 1

Reset to default
10

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.

Improve this answer
1
  • 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. Commented Jun 9, 2014 at 8:29

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.