SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 performance issue


SQL Server 2008 performance issue

Author
Message
Lansend1
Lansend1
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89197 Visits: 45284
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


Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16872 Visits: 19122
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
Lansend1
Lansend1
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16872 Visits: 19122
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89197 Visits: 45284
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


Lansend1
Lansend1
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Lansend1
Lansend1
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search