October 26, 2009 at 7:57 am
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
October 26, 2009 at 9:04 am
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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2009 at 9:33 am
lock pages in memory is not available with sql2005 Ent edition 32 bit?
October 26, 2009 at 10:29 am
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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2009 at 11:26 am
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?
October 26, 2009 at 11:40 am
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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2009 at 7:05 am
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?
October 27, 2009 at 10:24 am
I know it is on x64, I haven't enabled it on 32bit.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2009 at 12:23 pm
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
October 27, 2009 at 2:39 pm
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.
October 28, 2009 at 1:31 am
repent_kog_is_near (10/27/2009)
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
No, this notification is only given at startup time of the sqlserver instance !
As Eddie stated, the "lock pages in memory" is being granted at windows server level !
For 32-bit, you will also need to enable awe !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2009 at 8:14 am
Eddie
AWE is enabled, and I just wanted to check through t-SQL code if it is possible to determine 'LPIM' option.
How much max physical memory can be added in 32bit vs 64 bit environment for SQL Server?
Thanks
Dan
October 28, 2009 at 8:42 am
What Windows version and edition are you on ?
(x86 / x64 / ia64)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2009 at 9:28 am
x86-32 is where we are now.
What are the factors that should influence going for 64bit environment.?
October 29, 2009 at 1:32 am
repent_kog_is_near (10/28/2009)
x86-32 is where we are now.What are the factors that should influence going for 64bit environment.?
Search SSC for "64-bit"
www.sqlservercentral.com/articles/News/abitabout64bit/1360/
www.sqlservercentral.com/articles/Miscellaneous/overviewof64bitsql/1166/
and off course at MS.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply