SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lock Pages in Memory setting for 64-bit systems


Lock Pages in Memory setting for 64-bit systems

Author
Message
fanzhouqi
fanzhouqi
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 140
read this
http://msdn.microsoft.com/en-us/library/ms190730.aspx
fanzhouqi
fanzhouqi
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 140
the detail for
AWE,lock page in 64bit and 32 bit
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx
shaun.stuart
shaun.stuart
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2920 Visits: 878
I just went through all of this two days ago setting up a new 64 bit server. I think what fanzhouqi is referring to is that when the SQL Server service account has the lock pages in memory permission on a 64-bit system, SQL Server by default uses the AWE APIs for memory management, which results in slightly better performance than the normal memory management APIs. Checking the AWE box on the server configuration screen does nothing - it's a NOP. His second link says this, although it's not super clear.

On other thing the OP may want to consider is enabling trace flag 834. See http://support.microsoft.com/kb/920093. This flag requires Enterprise edition and the Lock Pages In Memory permission. The flag lets SQL use large memory pages instead of Windows normal 4k pages.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
shaun.stuart (12/7/2011)
On other thing the OP may want to consider is enabling trace flag 834. See http://support.microsoft.com/kb/920093. This flag requires Enterprise edition and the Lock Pages In Memory permission. The flag lets SQL use large memory pages instead of Windows normal 4k pages.


I would strongly recommend not doing that. It's something that was documented because it was used in a TPC benchmark. It prevents dynamic sizing of the buffer pool and it requires that the memory for the buffer pool be physically contiguous, if it's not then the amount of memory allocated could be much lower than max server memory. It can also significantly slow down SQL's startup

http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
Large page for the buffer pool is definitely not for everyone. You should only do this for a machine dedicated to SQL Server (and I mean dedicated) and only with careful consideration of settings like ‘‘max server memory’. Furthermore, you should test out the usage of this functionality to see if you get any measureable performance gains before using it in production.


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


FisherDad
FisherDad
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 248
In our experience on a Hyper-v Guest running Win Server 08 64 bit the cpu usage grew slowly/steadily over time from page swapping until a reboot. As we added some monitoring software and got busier it grew much quicker. Our baseline usage is fairly low. The baseline would get up to 70%, we would reboot and it would go back to 15%. With lock pages in memory enabled the growth slowed substantially and with a tweek or 2 to better accomodate the monitoring usage, baseline cpu usage stays between 15 and 30%. We have 5 instances of SQL running on the guest with 70-80 (250GB tot) mostly low usage databases. This guest runs on about 30gb of memory mostly allocated to the SQL instances.

FisherDad
David Moutray
David Moutray
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1152 Visits: 895
Now this is one case where I have heard that Lock Pages in Memory can get you in trouble. If you have Virtual Servers with overcommit enabled on the RAM.

I'm not a virtualization expert (and perhaps you are not using overcommit RAM on your virtual SQL Servers). Has anyone else heard the same thing? What are the pros and cons of using Lock Pages in Memory with Virtual Servers? (For that matter, how does it work when you have virtual servers?)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
Virtual servers with overcommit and SQL Server on them is going to get you in trouble anyway, locked pages or not.

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


surilds
surilds
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 183
Server Configuration
----------------------
Windows 2003 R2 Enterprise Edition 64 bit SP2
RAM - 96 GB

SQL version - SQL Server 2008 R2 SP2 Enterprise Edition 64 bit

Memory allocated to SQL Server -- min = 0, Max = 86 GB

Checked in logs found Locked pages are enabled.

When I check in the Task Manager--PF Usage it is 92.3 GB (consistently) due to it the applications are running slow.

Checked in Task Manager- Processes--sqlservr.exe is using 0.7 GB of memory & calculated all the processes it is not taking more than 15 GB of memory.

I still cant figure out which application\program is using the rest of the memory.

Please suggest.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230284 Visits: 46344
Don't use Task Manager to check SQL's memory usage. It can report a completely incorrect figure. Use perfmon.

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


surilds
surilds
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 183
Please let me know what counters need to monitored to find the physical memory usage?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search