Post reply

memory allocation on SQL server 2005

  • June 11, 2007 at 8:33 pm

    #120356

    Hi,

    We have a sql server 2005 standard edition 32 bit installed on windows server 2003 R2 SP2. it has 8GB Ram and 8 processors. Full text indexing is enabled, Reporting services is enabled.

    How much memory can sql use in this configuration? Does Full text/Reproting services/Linked servers use memory other than resereved for SQL Server?

    SQL Agent log says "8 processors, 4096 MB RAM Detected". That means SQL server is not using 8GB ram.

    Thanks

  • June 11, 2007 at 11:33 pm

    #712168

    http://msdn2.microsoft.com/en-us/library/ms179301(SQL.90).aspx

  • June 12, 2007 at 12:40 am

    #712185

    Thanks Micheal.

    This msdn site has Latest sql help.

    It may sound naive but cannot find the answer if report services, linked servers, full text using rest of the 4gb memory(total 8gb) which is not reserved by SQL Server though.

    Any Help !!

     

     

  • June 14, 2007 at 10:14 pm

    #713122

    first of all, you'll have to make sure your sql is configured to access memory more than 3GB first. since this is a 32-bit sql, you'll have to grant sql service account to lock pages in memory, enable /3GB switch in your boot.ini and set AWE to 1. then fixed the sql memory usage to 5.7gb, left 1gb memory for your iis because report services will run on that memory. 1gb should be enough for OS if you're not running any other job on the server.

    linked servers, full text, etc should be within the sql memory. if its in the memtoleave area, your fixed 5.7gb would left 300mb for sql to run any out of process jobs, such as sp_OAcreate, etc.

    hope this helps.

    Simon

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • June 18, 2007 at 6:05 am

    #713533

    Hi Simone,

    Thanks for your reply. If I use AWE enable, and lock pages in memory option on , do I need to enable /3gb switch as well? Just checked that my server has PAE enabled processors.

    Thanks

    Reena

  • June 18, 2007 at 10:49 pm

    #713721

    http://www.sql-server-performance.com/awe_memory.asp

    You can read about AWE options in the link given above.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 20, 2007 at 2:46 am

    #714034

    Hi everyone,

    I'm afraid that I have not been able to find a clever answer to the setup of our server by reading what the linked sites say. Hopefully someone here can give me some tips!

    We have a Datawarehouse with the following specs:

    Dual Intel Xeon Processor 3.2 Ghz (HT turned off)

    8 GB RAM

    Windows 2003 Server SP2 (Enterprise Edition) - 32 Bit

    SQL Server 2005 Enterprise Edition - 32 Bit

    The server performs the following tasks:

    - Replication Distributor (collects Data from production system) and Subscriber (writes collected data into ODS) : Reduces load on the production system.

    - ETL Process

    - Analysis Services - Cube processing

    - Reporting Services

    We recently upgraded the RAM to 8GB and changed the server boot.ini to active PAE. I read on the linked sites that we should make some changes to the SQL Server so that it grabs itself a nice chunk of RAM and uses it correctly.

    If I understand it correctly we should do the following:

    1. Set AWE to on

    2. Set minimum RAM use for SQL to a nice high value - 4gb???

    3. Set "Lock Pages in Memory" - This forces SQL to work in RAM, right??

    Are these the right things to do?

    Condidering the tasks the server performs are there maybe some other things to consider for this environment?

    Any suggestions would be greatfully accepted!

    Regards

    GermanDBA

    Regards,

    WilliamD

  • June 20, 2007 at 7:45 am

    #714103

    unless you are running 64 bit you aren't really using any RAM over 4GB

  • June 20, 2007 at 7:54 am

    #714106

    Hi SQL_Noob,

    As far as I was aware the system can only assign a maximum of 4GB to a single process. The OS can see the full 8GB.

    Does this mean that the 8GB would be a waste of time? Or is my idea of supplying SQL Server with 4GB and the other processes (OS / Reporting / Analysis) the rest) totally wrong?

    regards

    GermanDBA

    Regards,

    WilliamD

  • June 20, 2007 at 8:10 am

    #714118

    from what i remember it's still 4GB per process and the rest is just for a buffer cache. Pretty much anything memory related is still in the 4GB limit.

    We had servers with 8GB for years and now we are hitting a brick wall with the amount of data we process. If you have queries that return 2 million plus rows with order by and other sorting it's going to bring 32 bit to it's knees simply because it's limited to 4GB

    we had a query take 10 minutes on a 32 bit system due to the amount of data it handles. on 64 bit with 10GB or 12GB of RAM it's around 30 seconds.

    another server we had a huge I/O conflict with the amount of data and we replaced with with a PCI Express system and bought 64 bit just because it's a huge improvement. all the hardware supports it, so it's a waste not to buy the 64 bit software.

    64 bit is still young on Wintel but it's not a bad investment to think about simply because win 2003 x64 supports something like 1 TB of RAM. you can theoratically buy a win2003 license and use it for years to come with new hardware upgrades until there is no more driver support.

  • June 20, 2007 at 8:16 am

    #714120

    just so I understand you correctly.

    At the moment, the system we have has 4GB of RAM that cannot be used?

    To make it use more, we need an upgrade to 64-BIT?

    Thanks

    GermanDBA

    Regards,

    WilliamD

  • June 20, 2007 at 9:40 am

    #714154

    the other 4GB is used, but only as a cache from what i remember. most memory functions are still in the first 4GB.

    to really use it you have to go 64 bit. this way the entire memory footprint is available to any 64 bit app for anything. just make sure you install 64 bit windows and 64 bit SQL

  • June 20, 2007 at 12:19 pm

    #714194

    AWE is used for memory above 4GB. Yes it does gests used mostly for data cache but that *helps*. It will never be as much as in a 64 bit server but it should not be minimized either the importance of the data cache.


    * Noel

  • June 21, 2007 at 1:02 am

    #714268

    Hi,

    so there are two possibilities:

    1. Go through the steps that I listed above (Turn AWE on etc.)

    2. Swap out the OS and SQL Server for 64 Bit versions.

    Well then.... off to my boss to see what he thinks.

    P.S. If I move the entire Datawarehouse onto 64 Bit, would a database restore be enough or is it better to start from scratch? (We have the whole kit and kaboodle written as a setup script)

    Regards,

    WilliamD

  • June 21, 2007 at 8:46 am

    #714379

    restore should be enough

    yesterday we put one half of our new 64 bit cluster in production. storage is EMC SAN. We attached the disk volumes to the new server, mounted the db and done. just had to transfer jobs, logins, etc.

    and part of nightly job we copy the db's to 32 bit servers for backup and R/O access and you can mount the db on 64/32 bit servers at will

    64 bit is not the whole answer, but it's a nice jump in performance. new servers with PCI Express are very fast since the I/O is blazing fast as well. PCI-X servers are essentially 15 year old I/O technology

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

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