SQL Server Performance

Performance issues/questions

Discussion in 'Performance Tuning for Hardware Configurations' started by jtfox12, Sep 25, 2007.

  1. jtfox12 New Member

    Hey, I have a W2k3 Ent. SP1 / SQL 2k SP4 server running on a Dual 3.0GHZ Xeon with 9 GB of RAM. I have made some performance tuning changes to enable SQL to utilize the memory in the server. In the boot.ini file i added the /PAE & /3GB switches. I also enabled AWE memory and set SQL max memory (initialy I set it to 6GB but reduced it to 4GB.)
    Task manager used to show SQL using 2GB of RAM but now it only shows it using 90MB. Performance on the server itself seems to be fast (i.e. it restarts very quickly and Windows is very responsive, but queries and such are not.) Why is SQL only showing 90MB in task manager?
    And on AWE memory and max memory settings...if I set the /3GB swtich for the first 4GB of memory (essentially allocating 3GB to SQL) does that mean I need to set the max server memoy in SQL based on the available memory after the initial 4GB (in my case 5GB would be left after the initial 4GB) or the total physical memory in the system?
  2. bradmcgehee New Member

    If I was faced with your problem, one of the first things I would do is to use Performance Monitor to find out what the Buffer Hit Cache Ratio is for your SQL Server. If it is 99% or higher, this would indicate that SQL Server has all the RAM it thinks it needs. SQL Server will only take RAM if it needs it. If the Ratio is less than 99%, then perhaps one or more of your settings has a misconfiguration. From what you described, the setting seem OK. Also, check out the AWE and memory tips on this website for more help.
  3. satya Moderator

Share This Page