February 9, 2016 at 5:52 pm
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!
February 9, 2016 at 10:24 pm
My understanding of lockpagesinmemory is that it means SQL will not yield the memory back to the OS if it needs/requests it. Setting max server memory just caps the amount of memory SQL server will use but if the OS comes under memory pressure it(SQL) will yield memory back to the OS.
I personally have never used lock pages in memory. Unless you are very experienced I'd probably leave it off.
Also if you set max memory and start up SQL it wont immediately grab that 28GB of memory it will take time and depending on your DB sizes and how they are used it might take a lot of time or might not hit the max memory at all. It will only store DB pages and execution plans etc as they are accessed/ created.
February 10, 2016 at 6:11 am
I expect based on your response that my post was not clear. Thanks for the reply regardless.
I have not set Locked Pages for this server, yet it is reporting locked pages anyway.
I was not viewing memory reported for sqlserver.exe right after a reboot. Normal operation has the task manager report that process as using about 500 MB.
For example, right now:
I have the sql server max memory set at 28000 MB.
Currently sqlserver.exe reports using 593,000 KB
When I query sys.dm_os_memory_nodes, I show locked_page_allocations_kb = 28352836 (so all of the memory I allow for SQL).
My confusion here is that I do not have anything specified in the Local Group Policy for "Lock Pages in Memory". The article I referenced in m OP, and everything else I find, seems to indicate that this is Local Security Policy is the only way to turn on Locked Pages in SQL 2012. I have not done that, yet my server reports locked_page_allocation_kb.
Others I talk to with similar setups have their task manager reporting the full amount of sql server memory showing on the sqlserver.exe process, and 0 in locked_page_allocations_kb.
February 10, 2016 at 6:20 am
In other words (short version)... SQL is reporting "locked_page_allocation" even though I have nobody/nothing set with that local security right.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply