Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 performance issue Expand / Collapse
Author
Message
Posted Tuesday, July 1, 2014 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:18 AM
Points: 4, Visits: 19
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
Post #1588206
Posted Tuesday, July 1, 2014 3:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1588208
Posted Tuesday, July 1, 2014 3:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:22 PM
Points: 3,814, Visits: 8,587
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1588209
Posted Wednesday, July 2, 2014 4:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:18 AM
Points: 4, Visits: 19
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_id name value minimum maximum value_in_use description is_dynamic is_advanced
1544 max server memory (MB) 2147483647 16 2147483647 2147483647 Maximum size of server memory (MB) 1 1

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



Ashwin
Lansend.com
Post #1588657
Posted Wednesday, July 2, 2014 5:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:22 PM
Points: 3,814, Visits: 8,587
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1588662
Posted Thursday, July 3, 2014 1:00 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1588736
Posted Thursday, July 3, 2014 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:18 AM
Points: 4, Visits: 19
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
Post #1589146
Posted Wednesday, August 6, 2014 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:18 AM
Points: 4, Visits: 19
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
Post #1600256
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse