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.
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