SQL Server 2008 performance issue

  • We have a dedicated Windows Server 2008 R2 (64bit) running SQL Server 2008 SP3. The server has sufficient resources or at least we think so

    ProLiant ML350 G6 , Intel Xeon E5606 @ 2.13GHz, , 4 Cores , 24GB ( recently upgraded from 8Gb) , yes it has SATA drives , I know, not recommended for SQL servers , but I do not believe that is my issue at present, please do read on.

    The server is primarily dedicated for one application that runs on all client workstation and uses an SQL database. Additionally QuickBooks 2012 is also installed on the server. Other than that it only has ancillary software to back up the database, HP & APC management agents and such.

    Database mdf size is about 1.8GB and the LDF 25MB

    We have not configured any Min /MAX memory everything is set to default and from what I can see the SQL server is set to use all available memory and l processors. The only performance thing we have not done, is checked the box “Boost SQL Server priority”. Simply because it is not checked by default.

    Server was installed in Feb 2012, but client actually started using the application to its full potential in late 2013. At which time they began complaining that the application is either frozen or running as slow as molasses. Whenever they complained we saw that all server memory was consumed & the SQL server was consuming a little less than 6.5GB. We would reboot the server and that would instantly resolve the problem until such time the SQL server once again came close to a little less than 6.5GB. Processor utilization is absolutely negligible, as a matter of fact CPU time for the SQL server is about 1.5% of total time.

    Spoke to the client and we decided to give the server enough memory to choke on it and we upgraded it to 24GB Ram. We thought problem solved , we thought wrong.

    We continue to have the same problem albeit less frequently. But, the SQL server maximum consumption still does not exceed a little less than 6.5GB at which point the client once again complains about slow response from the application, we reboot the server the SQL server memory usage falls down dramatically & all is well until the next time it approaches 6.5GB. IT NEVER EVER exceeds that 6.5 mark. We have run a maintenance plan on the database to rebuild, reindex, etc. Plan never reports an error.

    We are IT guys and have enough knowledge to install and do rudimentary maintenance on the SQL server. Not SQL GURU’s, not by a long stretch. But we do maintain about 10 -12 SQL servers for various clients. None of them BTW have this issue.

    Question:

    1.If rebooting the server or simply restarting the SQL server & thereby releasing the memory it is holding solves the issue, then why does the SQL server take more memory if it wants?

    2.Am I missing anything, is there something else I could check, set etc.?

    Thank you for attention

    Sincerely

    Ashwin Pai

    Ashwin
    Lansend.com

  • DO NOT check boost priority. It should not be enabled and can lead to OS instability if it is enabled.

    Max server memory should be set. See chapter 4 of https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ for a sensible starting point. That book may also give you avenues of troubleshooting when the problem occurs. I can't tell from the description what might be wrong.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Could it be possible that you have that limit established on the "max server memory" configuration options?

    SELECT *

    FROM master.sys.configurations

    WHERE name = 'max server memory (MB)'

    There are many things that can be affecting your performance, a forum post won't give you a complete solution and you might need a qualified professional.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Gail will check the book out.

    Luis Thanks for the response, please see below for output of query, but note we did not set it , the server set it & we let it be.

    configuration_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced

    1544max server memory (MB)21474836471621474836472147483647Maximum size of server memory (MB)11

    With regards to " you might need a qualified professional" do you mean an SQL professional ?

    Ashwin
    Lansend.com

  • Lansend1 (7/2/2014)


    With regards to " you might need a qualified professional" do you mean an SQL professional ?

    Yes, even if you have highly qualified professionals helping on this site, sometimes this kind of problems require a deep analysis. I hope that you get a good solution in here, but think on taking the next step and search for options.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lansend1 (7/2/2014)


    Thanks Gail will check the book out.

    Luis Thanks for the response, please see below for output of query, but note we did not set it , the server set it & we let it be.

    You need to set max server memory to a sensible value. The default (2048 TB) is not a sensible value.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail ,

    The server has a total of 24GB RAM

    I set it to 14GB /14336 MB

    Will report back after the holidays.

    Thanks.

    PS: I am going through your book , and will post anything I find.

    Ashwin
    Lansend.com

  • Setting the max server memory to a sensible value as Gail put it worked.

    The SQL server memory utilization is now going beyond the previous 6.5GB mark.

    Thank you one and all

    Ashwin
    Lansend.com

Viewing 8 posts - 1 through 7 (of 7 total)

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