SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Find "lock pages in memory" option
20 posts, Page 1 of 2
12»»

Find "lock pages in memory" option Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
repent_kog_is_near
Posted Monday, October 26, 2009 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 19, 2010 8:15 AM
Points: 211, Visits: 269
I was trying to find if "lock pages in memory" option is enabled:
I ran
exec xp_readerrorlog 0, 1, 'locked pages'
and it returns " (0 row(s) affected)"

How do you interpret it?
Thanks
Dan
Post #808705
ALZDBA
Posted Monday, October 26, 2009 9:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 4,977, Visits: 3,928
You will not find this info is the errorlog has been rolled over.
e.g. using "EXEC sp_cycle_errorlog" or "DBCC errorlog"


You should find it after a fresh start of the sqlserver instance.

Else ... it is not active.

Keep in mind, lock pages in memory is only available with sql2005 Ent edtn 64bit !

or SQL2005 Std Edtn as from SP3 64-bit

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
Post #808763
repent_kog_is_near
Posted Monday, October 26, 2009 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 19, 2010 8:15 AM
Points: 211, Visits: 269
lock pages in memory is not available with sql2005 Ent edition 32 bit?
Post #808794
ALZDBA
Posted Monday, October 26, 2009 10:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 4,977, Visits: 3,928
http://technet.microsoft.com/en-us/library/ms190730%28SQL.90%29.aspx
states:
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.

So on 32-bit, if you want to use it, you should also enable AWE.

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
Post #808838
repent_kog_is_near
Posted Monday, October 26, 2009 11:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 19, 2010 8:15 AM
Points: 211, Visits: 269
Thanks..

can you please explain your statement "You will not find this info is the errorlog has been rolled over.
e.g. using "EXEC sp_cycle_errorlog" or "DBCC errorlog"". Are you saying that I can get the info only when the Error Log is reset?
Post #808872
ALZDBA
Posted Monday, October 26, 2009 11:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 4,977, Visits: 3,928
This info is written at sqlserver startup time.

To prevent ending up with xMB of errorlog file size, many choose to recycle the errorlog file using the commands I posted.

If the errorlog has been recycled, you will see that in the first lines of the current errorlog file. (I don't have the exact notification right now, I'm at home)

By default, sqlserver will retain up to 6 errorlog files (errorlog.1 - errorlog.6)

In many cases, the number is being extended to e.g. 31 for the ones that recycle the errorlog on a daily basis.
(We use 35)
use master
go
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 35
GO

This is also being extended because a trick of hackers is to stop/start sqlserver 6 times, so leaving no trace of their actions if this setting is still left to the default.

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
Post #808879
repent_kog_is_near
Posted Tuesday, October 27, 2009 7:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 19, 2010 8:15 AM
Points: 211, Visits: 269
That is good info. But what is the relation between "lock pages in memory" option and errorlog. Are you saying the info on the option would be in the first few lines of the errorlog?
Post #809271
ALZDBA
Posted Tuesday, October 27, 2009 10:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 4,977, Visits: 3,928
I know it is on x64, I haven't enabled it on 32bit.

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
Post #809449
repent_kog_is_near
Posted Tuesday, October 27, 2009 12:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 19, 2010 8:15 AM
Points: 211, Visits: 269
just want to make sure I got it:

so if I reset the errorlog, then I should be able to see the setting/option for LPIM in the erorrlog right after that, right?

thanks
Post #809517
Eddie Hendrix
Posted Tuesday, October 27, 2009 2:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 2:12 PM
Points: 57, Visits: 71
I am assuming you are trying to enable this because your wanting to use AWE on a 32-bit system. If not you can ignore this one.
To check who has access to that extended memory go to: RUN > secpol.msc
this should start "local security policy" editor.
Drill down to LOCAL POLICY, USER RIGHTS ASSIGNMENT, then look on the right side for that property.
The account that SQL is running under will need to be included in that group that has that right.
Post #809594
« Prev Topic | Next Topic »

20 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use