SQL Server Performance

memory usage performance issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by aemee123, Apr 11, 2008.

  1. aemee123 New Member

    Hi All - I'd really appreciate some advice on what to do here. Not sure if I need more ram.
    I am having the following performance issue. Periodically the sql server instance slows down significantly and even simple queries take a long time to complete. I have allocated 24gb to the instance as the maximum server memory (the box has 32). I am using "target server memory" and "total server memory" counters on performance manager as I read that this is a good indicator of what is being used. The problem is, 24 hours a day they are equal to each other. Does this indicate that we are short on physical ram allocated to sql server?
    The other counters I am looking at is pages/sec which spike quite high periodically, available bytes (just over 2gb most of the time) and processor time (quite low - around 25%). Disk I/O levels are fairly stable but I think when the server slows down it is paging to disk and hence I/O is affected.

    Spec - 16 way server with 32gb ram. 64 bit windows server 2003, 64 bit sql server 2005 enterprise.
    Thanks!
    T
  2. techbabu303 New Member

    Hi Amee,
    If 64 bit OS is dedicated only to SQL server , I dont see the reason why you should not allow the SQL server to handle the memory dynamically.
    Cheers
    Sat

  3. jagblue New Member

    Hi ,
    You have to tuen on AWE for this perticular Instance
    and to the sql service account you have to give permisson to Lock page In mamory
    Check this Link for more detail
    http://support.microsoft.com/kb/918483
  4. melvinlusk Member

    If it's a 64-bit server, I don't think you shouldn't turn on AWE for a 64-bit instance. Also, if you look at the KB article you refer to, it states that you should enable AWE for a 32-bit SQL Server process....
    "If you use a Windows user account globally for all SQL Server processes in a domain, determine the user rights that are assigned by using a Group Policy configuration. A 32-bit SQL Server process may use this account as the startup account. However, this account requires the Lock pages in memory user right to enable the Address Windowing Extensions (AWE) feature."
  5. jagblue New Member

    Hi
    It is correct On 64 BIT SQL Server their is no need to Enable AWE
    But As Per this Blog

    "on 64 bit platform, locked pages, awe mechanism, enable better application's scalability and performance both during ramp up time and over long period of time"
    http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
  6. satya Moderator

  7. aemee123 New Member

    Thanks for the advice so far.. Ok - lock pages in memory has been enabled and we are going to monitor this for the next few days. Still not very clear on the memory counters for sql server and how we can tell what sql server actually needs.
  8. satya Moderator

  9. techbabu303 New Member

    Hi Satya,
    I guess there is typo error on article , it should have stated " you need not"
    One advantage with 64-bit is you need toset any settings such as AWE as compared to 32 bit. But an importantmeasure is CPU utilization, and getting the best performance ROI aswell as increased throughput. The context switching is a vital to theoverall performance of your SQL Server, as it takes care the operatingsystem or applicaiton by forcing to change the executing thread on oneprocessor to another thread executed on another processor.
    http://sqlserver-qa.net/blogs/x64/archive/2007/08/11/1367.aspx

    Regards,
    Sat
  10. melvinlusk Member

    Are you saying that "you need" to turn on AWE for 64-bit and not 32-bit? It should be the other way around right?
  11. melvinlusk Member

    Ah, it looks like I should have read Slava's blog on MSDN first. Nevermind.
  12. aemee123 New Member

    Dear All - Thanks for the help so far - I've read everything i could on the subject, although I think I might have found at least one of the culprits. The network i/o waits per second are hitting 2000ms EXACTLY all the time. The network is 2gbps with dedicated gigabit switches, however it is only ever utilised about 10%. I'm assuming this must be an internal sql server setting somewhere which is imposing this limit? Can anyone help?
    Thanks so much
    T

Share This Page