Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Lock Pages in Memory setting for 64-bit systems Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2011 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:36 AM
Points: 9, Visits: 136
read this
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Post #1217929
Posted Wednesday, December 7, 2011 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:36 AM
Points: 9, Visits: 136
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
Post #1217931
Posted Wednesday, December 7, 2011 8:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:17 AM
Points: 1,435, Visits: 736
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.
Post #1217939
Posted Wednesday, December 7, 2011 8:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #1217960
Posted Thursday, December 8, 2011 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:08 AM
Points: 10, Visits: 228
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
Post #1218920
Posted Thursday, December 8, 2011 5:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:32 PM
Points: 219, Visits: 789
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?)
Post #1219007
Posted Friday, December 9, 2011 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #1219170
Posted Tuesday, January 22, 2013 4:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:30 AM
Points: 22, Visits: 135
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.
Post #1409941
Posted Tuesday, January 22, 2013 5:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #1409964
Posted Tuesday, January 22, 2013 5:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:30 AM
Points: 22, Visits: 135
Please let me know what counters need to monitored to find the physical memory usage?
Post #1409969
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse