Lock pages in memory setting retrieval?

Question

Lock pages in memory setting retrieval?

db_bunny

Right there with Babe

Points: 763
More actions
June 17, 2008 at 5:43 pm

#129535

Hi folks,
Does anyone know of a clever way to retrieve whether the 'lock pages in memory' setting on a SQL Server is set to true in the local security policy via TSQL? I know you can probably dump it out using xpcmdshell, however i was wondering if this value might be stored somewhere within SQL Server?
Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do you want to see what users have the lock pages privilidge? If so, you may have to locate where in the registry the values are entered and use xp_regread to retrieve.

If you're just looking to see if the SQL service account has the privilidge, read the error log (xp_readerrorlog) and see if you have a line towards the beginning

2008-06-08 14:01:04.67 Server Using locked pages for buffer pool.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 2

I think this policy need to be set at the windows level for the SQL server service account.

From SQL perspective, for the service account require Lock pages in memory windwos privillege especially in 32 bit systems where AWE is enabled.

I am not sure of 64 Bit machines - whether this privillege is required or not fo rthe service account.

subban

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 3

here we go again .... to summarize previous discussions: AWE is not necessary on 64bit (completely ignored), however, lock pages in memory is recommended (and necessary if you want tot enable database mail).

If you don't enable lock pages in memory on 64bit, you'll have the risk of "the battle for memory", with errors in your SQL log like

2007-01-23 16:30:10.14 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.

see: http://support.microsoft.com/kb/918483

Wilfred
The best things in life are the simple things

db_bunny Right there with Babe Points: 763 More actions · Answer 4

Thank you everyone for your responses.

That is correct - AWE is not required in 64 bit SQL Servers, however lock pages in memory is imperative.

Kind regards

JPea

tony-1095793 SSC Journeyman Points: 79 More actions · Answer 5

Hi there!

I have just installed sql standard 64 bit 2005 SP3 plus Com updates 4 for SP3 on a server and have given the service account the Locked... setting using gpedit, but still the sql server does not show in the log that it is using locked pages when it starts. Any idea why?

The server had been running OK for a year using SP1 when it suddenly started having performance problems. I read about the problems with the 64 bit version and about the Lock settings and thought that thgis would fix it.

best regards,

Tony

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 6

GilaMonster (6/18/2008)
Do you want to see what users have the lock pages privilidge? If so, you may have to locate where in the registry the values are entered and use xp_regread to retrieve.
If you're just looking to see if the SQL service account has the privilidge, read the error log (xp_readerrorlog) and see if you have a line towards the beginning
2008-06-08 14:01:04.67 Server Using locked pages for buffer pool.

Hi,

I tried to find the path in the registry but i could not locate this path , Can you post the path or any other find the service account that has added in the lock pages in momory local security policy ?

Thanks in Advance.

Ash SSC Eights! Points: 947 More actions · Answer 7

I check it via running the below code by logging with the SQL Server service account. Hope it helps you too..FYI, this code checks the group policy value via whoami command (which Displays user, group and privileges information for the user who is currently logged on to the local system)..

CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));

-- run whoami command via xp_cmdshell

INSERT INTO #xp_cmdshell_output EXEC ('xp_cmdshell ''whoami /priv''');

IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE '%SeLockMemoryPrivilege%enabled%')

PRINT 'Lock Page in memory enabled'

ELSE

PRINT 'Lock Page in memory disabled';

DROP TABLE #xp_cmdshell_output;