October 9, 2015 at 1:07 pm
I'm trying to enable "lock pages in memory" on this server:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
I have a domain user running the SQL Server service, and have added that user to the "lock pages in memory" user right as described here:
https://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx
I've also added trace flag 845 and restarted SQL Server (although technically it's not necessary for Developer 64-bit edition of SQL Server), and verified that it appears in ERRORLOG:
Registry startup parameters:
-d D:\MSSQL_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e D:\MSSQL_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l D:\MSSQL_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-T 845
(Note that I didn't place that space between the "T" and the "845" - that's just how it appears in ERRORLOG. Go figure. I also didn't put a space before the semicolon when I entered it. Also note the "T" is uppercase.)
I also verified Trace Flag 845 is enabled by executing:
DBCC TRACESTATUS(845)
... which returned:
TraceFlagStatusGlobalSession
845110
I can tell "lock pages in memory" is not enabled in four ways.
First, running this returns no rows:
exec xp_readerrorlog 0, 1, 'Using locked pages for buffer pool'
Second, running this:
exec xp_readerrorlog 0, 1, 'lock memory privilege was not granted'
... returns:
"Cannot use Large Page Extensions: lock memory privilege was not granted."
Third, this query, I believe, should return a non-zero value after a day of running, but it doesn't:
select @@SERVERNAME
, sum(omn.locked_page_allocations_kb / 1024) as Total_locked_pages
from sys.dm_os_memory_nodes omn
join sys.dm_os_nodes osn
on omn.memory_node_id = osn.memory_node_id
where osn.node_state_desc <> 'ONLINE DAC'
having sum(omn.locked_page_allocations_kb) = 0
union
select @@SERVERNAME
, locked_page_allocations_kb / 1024 as Total_locked_pages
from sys.dm_os_process_memory
where locked_page_allocations_kb = 0
And fourth, this query should return "ENABLED", but it doesn't:
DECLARE @LockPagesInMemory VARCHAR(255)
SET @LockPagesInMemory = 'UNKNOWN'
DECLARE @Res TABLE ([output] NVARCHAR(255) NULL)
IF (SELECT value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell') = 1
BEGIN
INSERT @Res
EXEC xp_cmdshell 'WHOAMI /PRIV'
IF EXISTS (SELECT * FROM @Res WHERE [output] LIKE 'SeLockMemoryPrivilege%')
SET @LockPagesInMemory = 'ENABLED'
ELSE
SET @LockPagesInMemory = 'DISABLED'
END
SELECT @LockPagesInMemory AS LockPagesInMemoryEnabled
Is there something I've forgotten, something I've misunderstood, or something else I can check? I've gotten this to work on lots of servers in the past. Thanks.
October 11, 2015 at 10:00 am
Larry Leonard-399461 (10/9/2015)
I have a domain user running the SQL Server service, and have added that user to the "lock pages in memory" user right
have you rebooted the server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2015 at 1:29 pm
Privileges are gained by Windows accounts when the account logs in. A Windows service logs in before users are able to logon to the OS. If you want a service to gain privileges set by a user who is logged onto the same OS, the service must be restarted by that user, which causes the service to again logon and thus gain the privileges just granted. You can and should use SSCM to restart just SQL Server, after granting LPIM to the SQL Server startup account.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply