July 1, 2014 at 3:32 pm
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
July 1, 2014 at 3:43 pm
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
July 1, 2014 at 3:46 pm
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.
July 2, 2014 at 4:42 pm
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
July 2, 2014 at 5:09 pm
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.
July 3, 2014 at 1:00 am
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
July 3, 2014 at 3:52 pm
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
August 6, 2014 at 8:20 am
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