SQL Server Performance

Question about SQL Server memory configuration

Discussion in 'Performance Tuning for DBAs' started by jrdevdba, Oct 5, 2007.

  1. jrdevdba New Member

    Hello,
    I ran the audit steps named Identify SQL Server Hardware Bottlenecks and found that the server in question is averaging about 97 in Memory: Pages/sec. The section on how to interpret the counters says that this value should not average more than 20 and that a higher value is a possible symptom of a "memory bottleneck due to a lack of needed RAM."
    As far as I can tell, the server has 8 GB of RAM. The Buffer Cache Hit Ratio is averaging over 99%, so that seems OK according to the audit steps. Also, I could not find any other significant process or application that is installed on the server that would be consuming memory, so that appears to rule out that possibility as suggested by the audit steps (although if anyone can suggest how to look for anything that is not obvious, please let me know).
    So the final suggestion is regarding the SQL Server memory settings:
    SQL Server should be configured so thatit is set to the "Dynamically configure SQL Server memory" option, andthe "Maximum Memory" setting should be set to the highest level. Foroptimum performance, SQL Server should be allowed to take as much RAMas it wants for its own use without having to compete for RAM withother applications.
    Below are the values on the server I am looking at, transcribed from the SQL Server Memory Properties tab. The Maximum is currently set to 3107 out of 8187.
    So my questions are:
    1. Does the quoted advice above mean I should set the Maximum to 8187? (In case it makes a difference, the "Memory: Available Bytes" value is averaging more than 5,000,000,000 - I'm not sure how that number is determined by the performance monitor.)
    2. Does this memory change require downtime (a restart of the SQL Server service or of the whole server itself)?

    3. I became the DBA less than a year ago, so I am not sure how this valueof 3107 was entered. Is this a default value when a SQL Server has 8 GBof RAM?
    Thanks in advance for any help. If you need any more information to help give an answer, please ask me.
    Sincerely,
    jrdevdba
    Dynamically configure SQL Server memory (this option is selected)
    Minimum (MB): 0
    0 MB ..... 8187 MB
    Maximum (MB): 3107
    4 MB .... 8187 MB
    ...
    Minimum query memory (KB): 1024


  2. bradmcgehee New Member

    Is your SQL Server database attached to a SAN? If so, then you might see numbers higher than 20, depending on the SAN. If the SQL Server is not attached to a SAN, I would expect to see the number below 20, on average, over time.
    Have you used Task Manager to try and identify any processes that are doing a lot of paging? Add the columns Page Faults and PF Delta to help identify what process(es) are causing the paging.
    Is the boot.ini file set to use the /3GB switch?
    Has AWE memory been turned on for the SQL Server instance?
    If you have not done so already, check out the tips on AWE memory on the website.
    The maximum setting of 3107 seems low to me if you have 8GB in your server. I would try a figure around 6GB or so. Once you change memory, you don't have to restart the SQL Server service.
    Also, ensure you have a version of the OS and SQL Server that can make use of 8GB of RAM. Not all versions can.
  3. jrdevdba New Member

    [quote user="bradmcgehee"]
    Is your SQL Server database attached to a SAN? If so, then you might see numbers higher than 20, depending on the SAN. If the SQL Server is not attached to a SAN, I would expect to see the number below 20, on average, over time.
    Have you used Task Manager to try and identify any processes that are doing a lot of paging? Add the columns Page Faults and PF Delta to help identify what process(es) are causing the paging.
    Is the boot.ini file set to use the /3GB switch?
    Has AWE memory been turned on for the SQL Server instance?
    If you have not done so already, check out the tips on AWE memory on the website.
    The maximum setting of 3107 seems low to me if you have 8GB in your server. I would try a figure around 6GB or so. Once you change memory, you don't have to restart the SQL Server service.
    Also, ensure you have a version of the OS and SQL Server that can make use of 8GB of RAM. Not all versions can.[/quote]
    Hello, and thanks for your detailed reply.
    This particular server is not attached to a SAN.
    In terms of the memory details, we are running SQL Server Standard Edition on Windows 2003 Advanced Server. I did increase the maximum memory from 3107 to 6005 (that'swhat the slider allowed me to set). The pages/sec has gone down from anaverage of 97 during the first audit to about 24 when I ran it over thepast day. But I've noticed discussions to the effect that Standard Edition can't utilize as much RAM as Enterprise Edition.
    I took a look at the Task Manager and saw this value for SQL Server:
    sqlservr.exe Mem Usage: 1,791,552 K
    It seems to be around 1.5 to 1.8 GB whenever I check. Does this mean that the maximum memory setting doesn't make any real difference in this case due to the SQL version, as you suggested above? I think I will run another couple of 24-hour counter audits to see whether this difference is real or just an artifact of the days I chose to run the audits (Thursday-Friday the first time and Monday-Tuesday the second time, Monday being yesterday, Columbus Day).
    I will add the columns Page Faults and PF Delta as you suggest.
    Thanks again!
    jrdevdba


  4. jrdevdba New Member

    [quote user="bradmcgehee"]
    Add the columns Page Faults and PF Delta to help identify what process(es) are causing the paging.
    [/quote]
    One additional question - it looks like Page Faults Delta is available in Task Manager but not as a counter when running performance monitoring. Is that correct?
    Also, could you provide a brief description of how to interpret these counters, or direct me to a link that explains them and what values are OK versus what values are too high?
    Thanks again!
    jrdevdba


  5. jrdevdba New Member

    [quote user="bradmcgehee"]
    Have you used Task Manager to try and identify any processes that are doing a lot of paging? Add the columns Page Faults and PF Delta to help identify what process(es) are causing the paging.
    [/quote]
    I checked Task Manager, and I see these processes causing a lot of paging:
    mssearch.exe - Mem Usage 52,888K - Page Faults 324,902,295 - PF Delta 0
    svchost.exe - Mem Usage 36,156 K - Page Faults 80,949,151 - PF Delta 20
    sqlservr.exe - Mem Usage 1,773,108 K - Page Faults 16,966,482 - PF Delta 13
    I think mssearch.exe is the SQL Full-Text search service. Is the paging too high for that process? If so, are there steps to troubleshoot, or tell if it is causing the periodic slowness and blocking in other queries?
    Thanks again, and sorry to pepper you with replies, but I want to get as much help as I can based on the data I am finding.
    jrdevdba

  6. satya Moderator

    I doubt on the operating system edition you have, also the edtion of SQL SErver as there are constraints to use maximum available memory.

Share This Page