SQL Server Performance

SQL Server Memory

Discussion in 'Performance Tuning for Hardware Configurations' started by dasari, Apr 8, 2005.

  1. dasari New Member

    My Database is a SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server, with 3.5G RAM.
    The /3gb switch is enabled. When I check the Available Bytes it shows 600 MB of Memory as available.
    My Questions:
    1.)Will the OS take 1GB and limit SQL to 2.5GB ?

    2.)Or will SQL grow to 3GB on need basis ?

    3.)Or will SQL just remain below 2GB because OS needs 1 gig and so wouldn't
    let SQL use the /3gb ?

    Thanks for your answers.

  2. bradmcgehee New Member

    If you have 3.5GB of RAM, and use the /3GB switch, then the OS will only take 1GB, leaving 2.5GB to SQL Server. SQL Server will only take as much RAM as it needs, if it is set to use memory dynamically. If you manually set SQL Server to use all the available RAM, it will. If there is 2.5GB of memory available, as in your case, SQL Server can use it all, when it needs it.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. dasari New Member

    Can I now safely increase the number of threads in SQL Server ?
    Since I have about 600 MB available , there would be memory for the
    thread stacks too since memory to leave area comes from the OS memory.
    What could be the negative impact of doing this ?
  4. satya Moderator

    Chekc the process and queries that are affecting the performance, PROFILER for slow runnign queries and PERFMON to identify the resources usage will help to assess the situation.

    Increase of threads can be considered if the system is dedicated to SQL Server and disk I/O activity tends to be heavy, then you should enable this option to get substantial performance gains.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. derrickleggett New Member

    I wouldn't normally consider enabling it on a system that only has 3.5GB of RAM though. Be careful when doing this. Do you have a test system that mirrors this? Why do you think that you need to increase the threads?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page