Locked Page Allocations

SQL Server Administration
1

I have SQL Server 2012 64bit installed on Windows 2008 R2 x64. 32GB RAM on the server. SQL set to use max of 28GB. This was a new build, not an upgrade.

SQL is reporting the majority of it's memory represented in Locked Page Allocations.

This article supplies me the query I'm running (from sys.dm_os_memory_nodes) which reports the locked_page_allocations, and tells me that Lock Pages in Memory is only set via a Local Security Policy with SQL 2012, by assigning the right to the applicable startup account. I have nothing set with that right.

Task Manager reports a relatively small amount of memory used by sqlserver.exe, however the server is utilizing all of the memory that I provide to SQL.

Others I've talked to with similar setups tell me that they do not have locked page allocations, and their task manager reports the full amount of memory utilized by the sqlserver.exe process.

I have no experience with this side of SQL. Can anyone tell me why I have memory showing in this locked_page_allocations, given that I do not have the Lock Pages in Memory policy assigned to anyone/anything?

Thanks!

2

When you installed SQL Server - what account did you use for SQL Server? If that account is a member of the local administrators group then locked pages in memory will be granted.

There is no problem having locked pages on a dedicated SQL Server. The fact that SQL Server is taking up the memory is normal and nothing to be concerned about - that is how it is supposed to work.

1 Like
3

It is setup with "Local System" (which is also something I'm looking to change). So... this explains it. This is the first I've heard of Locked Pages being automatically assigned for Administrators. Good to know. I hear you about it not being a problem. Just doing some troubleshooting and wanted to know what was going on there. I know SQL will use all of the memory. Just wasn't understanding the Locked Pages situation.

Thanks very much!

4

See this article: Stop using Task Manager to check SQL’s memory usage!