Confused on memory

  • hi m having a server which has 20 gb ram i hav give the 15 gb(max memory) to sqlserver but wen i check in wth permon total memory show on 3.5 GB...

  • waiting for the suggestions..........

  • What version of SQL / Windoze are you running, 32-bit or 64-bit?

    What are your memory settings for AWE, boot.ini and so on?

  • Dear

    what is AWE?

    Plz tell ....

    Thanx

    Thanks

  • guptaajay1985 (12/2/2008)


    Dear

    what is AWE?

    Plz tell ....

    Thanx

    see the link:

    http://support.microsoft.com/default.aspx/kb/274750

    http://msdn.microsoft.com/en-us/library/ms175581.aspx

  • Thanx Dear..

    Thanks

  • SQL Server only takes memory as it is needed. Perhaps you only have (or access) a few GBs of data?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Read the previously posted articles, but a quick summary:

    1. If you have 64-bit windows, skip this step and go to step 2. If you have 32-bit Windows, right click on the server in object explorer and click Properties. Select the Memory page and make sure AWE is checked (as well as adjusting your memory to desired settings). Save and close.

    2. Go to Administrative Tools -> Local Security Policy and inside the window, select Local Policies -> User Rights Assignment.s You will see a whole list of policies on the right hand side. The one you need is Lock pages in Memory. Double click on it and add the service account(s) you are running your SQL Server instance(s) as.

    NOTE: This will involve restarting your SQL Service to become effective, so if this is a production environment, beware of that.

    This is the quick and dirty version of setting up AWE and Lock Pages in Memory, but read the articles to get a thorough introduction before proceeding.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • And, of course, if you're using a "Standard" version of Windows Server, you can't address that much RAM anyway.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Gaby Abed (12/3/2008)


    Read the previously posted articles, but a quick summary:

    1. If you have 64-bit windows, skip this step and go to step 2. If you have 32-bit Windows, right click on the server in object explorer and click Properties. Select the Memory page and make sure AWE is checked (as well as adjusting your memory to desired settings). Save and close.

    2. Go to Administrative Tools -> Local Security Policy and inside the window, select Local Policies -> User Rights Assignment.s You will see a whole list of policies on the right hand side. The one you need is Lock pages in Memory. Double click on it and add the service account(s) you are running your SQL Server instance(s) as.

    NOTE: This will involve restarting your SQL Service to become effective, so if this is a production environment, beware of that.

    This is the quick and dirty version of setting up AWE and Lock Pages in Memory, but read the articles to get a thorough introduction before proceeding.

    1) if 32 bit you would also need /PAE in the boot.ini file, right?

    2) IIRC Lock pages in memory is only useful for SQL Server Enterprise Edition.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • 1) if 32 bit you would also need /PAE in the boot.ini file, right?

    2) IIRC Lock pages in memory is only useful for SQL Server Enterprise Edition.

    not sure about the boot.ini file and yes, for the enterprise edition, that's what most of our production servers are so it's how I'm used to it.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • TheSQLGuru (12/3/2008)


    2) IIRC Lock pages in memory is only useful for SQL Server Enterprise Edition.

    this only applies to sql 64bit, its ok under 32bit

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • quick follow up question..

    I have SQL Server 2005 Enterprise edition on a Windows 32bit Advanced Server 2003.

    I recently added 12 GB to the Server bringing the memory up to 16GB. I did this mainly so that I can add a couple fo new Instances to the Server. If I don't set up AWE and Lock Pages in Memory - each Instance should be able to grab up to 2GB.. isn't that correct??

  • That is my take on it.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jpotucek (11/25/2009)


    quick follow up question..

    I have SQL Server 2005 Enterprise edition on a Windows 32bit Advanced Server 2003.

    I recently added 12 GB to the Server bringing the memory up to 16GB. I did this mainly so that I can add a couple fo new Instances to the Server. If I don't set up AWE and Lock Pages in Memory - each Instance should be able to grab up to 2GB.. isn't that correct??

    just to note

    Server 2003 does not have an advanced edition, it has

    Standard

    Enterprise

    Web

    Datacenter.

    You are correct, thats my understanding of it too. With that much memory ensure you don't have the /3GB switch in the boot.ini

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply