Post reply

how much RAM(memory) used by sql server 2005

  • September 10, 2009 at 5:21 am

    #133936

    I do not find enough about how much memory and processor currently used by MS SQL Server 2005

    and if any available at system which is unused.because my server has only used as database server

    so my goal is to best memory and processor utilization.

    currently i have 8 GB ram and Intel Xeon server E5335 with @2GHZ @2 GHZ.

    any help or links which i can refer for my best.

    thanks in advance.

  • September 10, 2009 at 5:39 am

    #1051667

    How much is the percentage of utilization now??

  • September 10, 2009 at 5:41 am

    #1051669

    I have used perfmon and added parameter for mssql/instancename --> memory manager --> total memory

    it shows 1620352/1024/2024 = 1.54 GB.

    when I have 8 GB available at a time.

    I am right way?

  • September 10, 2009 at 7:10 am

    #1051697

    mjarsaniya (9/10/2009)

    I have used perfmon and added parameter for mssql/instancename --> memory manager --> total memory

    it shows 1620352/1024/2024 = 1.54 GB.

    when I have 8 GB available at a time.

    I am right way?

    Hi,

    Yes u r correct.Also check

    Target Server Memory (KB)

    See the following link,

    http://qa.sqlservercentral.com/Forums/Topic783039-146-1.aspx#bm783439

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • September 10, 2009 at 12:21 pm

    #1051873

    hi mjarsaniya!

    are you running 32 bit windows or sql ?

  • September 11, 2009 at 12:26 am

    #1052085

    Hi I am using SQL Sever 2005 SP 2

    with 32 bit with windows server 2003 (data edition).

  • September 11, 2009 at 12:34 am

    #1052087

    Hi I am using SQL Sever 2005 SP 2

    with 32 bit with windows server 2003 (data edition).

  • September 11, 2009 at 1:19 am

    #1052103

    I've not had to actually setup a production server in quite some time... I've usually had the pleasure of a "System's DBA" setting them up in all the jobs I've had since 2003.

    However, I do know of a couple of "boot switches" that should help. The first switch is the AWE switch which will allow SQL Server to use up to 3GB. The other switch (which I believe is used separately), is the /PAE switch.

    While I'm not 100% sure exactly how to use either, sometimes just knowing what to look for greatly aids in your search for what to do.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 11, 2009 at 1:29 am

    #1052109

    What SQL Server 2005 Edition are you using?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • September 11, 2009 at 4:33 am

    #1052172

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    with 16 GB ram and 4 processors.

  • September 11, 2009 at 4:38 am

    #1052175

    mjarsaniya (9/11/2009)

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    with 16 GB ram and 4 processors.

    Just a small note, that looks like the RTM version, you should install the latest service pack, You are missing a hell of a lot of updates and fixes.

    http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 11, 2009 at 4:57 am

    #1052180

    Yes he is having RTM version installed.

    Please install service packs

  • September 11, 2009 at 6:13 am

    #1052200

    Jeff Moden (9/11/2009)

    I've not had to actually setup a production server in quite some time... I've usually had the pleasure of a "System's DBA" setting them up in all the jobs I've had since 2003.

    However, I do know of a couple of "boot switches" that should help. The first switch is the AWE switch which will allow SQL Server to use up to 3GB. The other switch (which I believe is used separately), is the /PAE switch.

    While I'm not 100% sure exactly how to use either, sometimes just knowing what to look for greatly aids in your search for what to do.

    (The following relates to 32-bit editions of SQL Server running on 32-bit Windows (not WOW) only).

    /PAE, /3GB, and /USERVA are Windows boot.ini switches. Only /PAE is recommended without advice from Microsoft.

    AWE is indeed a Windows facility, but you do not enable it in the same way. You can enable AWE in SQL Server server properties or via sp_configure.

    The combinations can be complex (another reason to upgrade to 64-bit), but the best comparison of the effects of enabling the various options I have found so far is this blog post by Linchi Shea.

    Spoiler: pretty much always enable /PAE and always AWE. Never /3GB...

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 11, 2009 at 6:21 am

    #1052204

    mjarsaniya (9/11/2009)

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) with 16 GB ram and 4 processors.

    I wish Microsoft would change that message.

    So many people would read that and say they had SQL Server Service Pack 2 - in fact it is Windows (NT 5.2!) which is at Service Pack 2.

    SSMS shows you the current Service Pack level of the server when you connect, as does:

    SELECT ServerProperty(N'ProductLevel')

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 11, 2009 at 6:36 am

    #1052211

    To answer the original question:

    Enable AWE. Follow the instructions in This Technet Article. Make sure you follow the instructions for your version of Windows (2003).

    There is a link in that section to help you set up the Lock Pages In Memory right for the windows user that the SQL Server process runs under (see SQL Configuration Manager to check the service name). This is essential (unless SQL is running as Local System).

    Once you have followed these steps and restarted the SQL Server service, you should see the message 'using locked pages for buffer pool' (or similar, I forget the exact wording) in your SQL Server error log. This means that it's working.

    Don't forget to set the max server memory setting appropriately. On a dedicated SQL Server (no other applications) with 8MBGB RAM, I would probably start by setting it to around 6.5 GB.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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