Solved

Windows 2008 R2 | SQL 2008 R2 Express 64 bit - Info required about AWE, Locked pages to understand Memory usage

Posted on 2013-12-27
11
94 Views
Last Modified: 2015-06-23
Hi,

I was setting up a test lab to understand SQL server 2008 64 bit AWE/Lock pages feature and how it would effect Windows 2008 R2 server

My server config : I have 2 CPUS & 8 GB RAM

setup  SQL 2008 R2 Express 64 bit with instance name sqlexpress
enabled AWE, Set 5 GB of RAM as Minimum & Maximum
When i checked in taskmanger the memory usage is around 200 MB for sqlservr.exe process
And overall Physical Memory usgae is 600 MB
restarted my test server no luck
AM I missing something how to make SQL lock 5 GB which was allocated to it using AWE, Lock memory
Any help is highly appreciated

I tried using below command and also tried GUI
USE master
go
sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'min server memory', 680 -- 2 GB min, replace with your desired amount here
EXEC sp_configure 'max server memory', 680 -- 6 GB min, replace with your desired amount here
RECONFIGURE WITH OVERRIDE

USE master -- database name
go
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Thanks in Advance
Techiee
0
Comment
Question by:techieeguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39741938
It looks like you only changed the comment, try changing 680 to 2048 for example:
EXEC sp_configure 'min server memory', 2048 -- 2 GB min, replace with your desired amount here

Open in new window

(same goes for max)
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39741968
BTW, max with express may be 2 (or even 1) GB, and AWE not applicable, not sure though...

I was reading this: http://msdn.microsoft.com/en-us/library/ms143685(v=sql.105).aspx
0
 

Author Comment

by:techieeguy
ID: 39742891
Thanks for your quick response

sorry to post wrong command i did change them to 5 GB when i tried pls refer attached screenshots sql02.jpg & sql03.jpg  sql instance sqlexpress server properties -> Memory and from my task manager
current physical usage is 1 gb
SQL03.jpg
SQL02.JPG
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:techieeguy
ID: 39742898
Hi Robert Schutt
I saw your 2nd comment and the blog pls disregard my above comment the blog provided by you does makes sense seems SQL express will not cross 1GB

Could you confirm if i did the same with a sql server this command will work and i can lock pages i will try with sql if it requires
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39742986
If you are trying to lock pages in memory, you are not going about it the right way.  This is how you do it: How to: Enable the Lock Pages in Memory Option (Windows)
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39743202
Yes, with a full version it should work. Still, depending on the SQL Server version, you may hit the old restrictions (2 or 3 GB).

I must admit I haven't worked with this option myself, only responded to the obvious mistake you seemed to have made...

By the way, in the page linked by @acperkins it says:
Locking pages in memory is not required on 64-bit operating systems.
And in a link from that page Memory Architecture it says:
Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems
Maybe you need to elaborate on why you think you need to lock pages and use AWE.

Here's my simplistic view: if you have a dedicated database server that has little else to do and you have enough memory to fit most of your regularly used data in it, SQL Server will already keep the data needed to work most efficiently in memory.

A good database design and application architecture (with smart use of SQL queries) will go a long way to support that and you should not have to resort to any 'low-level' trickery to make SQL Server faster.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39744768
Locking pages in memory is not required on 64-bit operating systems.
This is not as cut and dried as MS would like us to think.  There is a lot of debate on this among the leading SQL Server MVPs.  Let me know if you need blogs on this subject and I can search for them.
0
 

Author Comment

by:techieeguy
ID: 39746944
Sorry for delayed response
just download sql 2012 iso which was 3.6 GB size and installed 64 bit, yeah AWE option is not there but lock pages can still be enabled as per "Anthony Perkins" blog by using group policy.

To enable the lock pages in memory option using group policy

    On the Start menu, click Run. In the Open box, type gpedit.msc.

    On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.

    Expand Security Settings, and then expand Local Policies.

    Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

    In the pane, double-click Lock pages in memory.

    In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.

    In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.

    Log out and then log back in for this change to take effect.


I have 2 days off from tomorrow, i am trying from some script or way to grow my database above 4 gb and check if lock pages are visible using Rammap and Vmmap.

What i am basically trying to achieve is to understand this feature as i could see some times we are unable to track/monitor memory usage of windows server due to this,

I tried reading lot of material and spent lot of time still unable to understand how this works. Any blog with steps to achive above is good enough. I don't want to waste your time so any good material will do and i can close this question :)
SQL-2012-memory.JPG
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39761326
one of my favorite sql websites is blog.sqlauthority.com, I searched there and found links to pages mentioned in earlier posts, but also to http://support.microsoft.com/kb/918483 where, under the heading "Improvements in Windows Server 2008 and in Windows Server 2008 R2" (near the bottom), you find some more (hopefully interesting) info about why it shouldn't be necessary although the goal of the page seems to be explain how to do it anyway.
0
 
LVL 35

Expert Comment

by:Seth Simmons
ID: 40845817
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question