Solved

SQL Express 2008 R2 on SBS 2008 slow/max CPU

Posted on 2013-06-14
25
770 Views
Last Modified: 2013-06-20
Hi there

I have a software called iCare running on my SBS 2008. It is a healthcare software.
Runs on IIS and SQL Express 2008 R2 for database.
The process sqlserv.exe occupies around 50% of the CPU on most occasions and keeps going upto 100% CPU every few minutes. I think this happens when a query is run by the users.
In particular there is a module called Add Activity in the software which freezes 8 times out of 10 and comes back with an error SQL Exception. This is when the Silverlight goes round in circles and circles and throws this exception.
I think its not right for the process to occupy that much CPU. The database goes very slow  and its very frustrating. where do I start? I am not great at SQL.
0
Comment
Question by:amitkhera
  • 14
  • 10
25 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 39247048
Open SSMS, connect to the iCare server, right-click on the server entry in the left pane, and select Properties. Under the General tab you'll find a Memory entry - this should be the amount of memory that the server has to offer. Then go to the Memory tab and see what SQL Server has been given as limits.

If the Maximum Server Memory is really high ( > 7/8 of physical memory) then you should probably reduce it (see here) to maybe 3/4 of system physical memory, as you may be getting SQL Server and other programs fighting for memory.

When that happens the OS will be doing lots of paging to try to get programs back into memory to run, and everything slows down. iCare may have timeouts built in that abort after waiting more than a certain period, eventually causing SilverLight to die - I can't tell because (a) I'm not in iCare support and (b) you didn't include the error info !    :)

hth

Mike
0
 

Author Comment

by:amitkhera
ID: 39247068
Thanks Mike

I have attached screenshots of both the screens for you to have a look.
Please let me know what I need to do next.

screenshot 1


screenshot 2


Thanks

Amit
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39247293
Go back to that second screen and make the "Max Server Memory" about 3/4 of the value for "Memory" in the first screen.

Refer to this article If nothing else, leagve a gB or two for the OS, even if you're running absolutely nothing else.  

hth

Mike
0
 

Author Comment

by:amitkhera
ID: 39247389
Hi Mike

I have made the value for Max Server Memory to 10744 MB now.
That is about 3/4th of the total memory.
Do I need to restart the SQL service or the SBS server?
Please let me know and then I can start monitoring iCare and see how it goes.

Thanks

Amit
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 39247396
I would not mess with the memory setting on a SBS server. More than likely your monitoring database is full. This is tactical in SBS. If it is this works very well. Remember if you replace the monitoring database you have to recreate any reports.

http://blogs.technet.com/b/sbs/archive/2011/08/22/how-to-recreate-the-sbsmonitoring-database.aspx
0
 

Author Comment

by:amitkhera
ID: 39247468
Hi RickEpnet

My understanding is that SBS 2008 comes with SQL Server 2005 on which SBSMonitoring database works.
I think SBS 2008 does not come with SQL Server 2008 Express by default.
I think the iCare support team installed it to be able to host the iCare database on our SBS.
Am I right in thinking this? I am only trying to confirm because I am not an expert on SQL.

I have changed the memory setting on the SQL 2008 express R2 as suggested by the user Dcpking above which hosts iCare and I have not changed anything on the SQL 2005 as it hosts SBSMonitoring.

I looked up an article on MS TechNet a few weeks ago which suggested adding some indexes to SBSmonitoring database to make it work faster. I did that and the SBSmonitoring database does not eat up much CPU now.
Link to the article
http://social.technet.microsoft.com/Forums/en-US/smallbusinessserver/thread/d195baac-da8b-4387-9079-c55d5e1879b4

I am trying my best to find a way to get the database to be more responsive. The latest I have done is the solution from Dcpking above. Lets see what happens

Thanks

Amit
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39247658
You should be ok to leave it for now and try the "usual suspects" to see if they still have the same effect. If they do you could try stopping and restarting the SQL Server, but I don't think that will make any difference.

Mike
0
 

Author Comment

by:amitkhera
ID: 39248648
Thanks Mike. The CPU usage has gone down tremendously. It's only being used 30% max and other times it sits quietly at 2-3%
iCare worked fine all day after changing the max server memory.
Thank you very much mate. You are a star. I will observe the server performance over the next few days and will let you know if it happens again.
What I don't understand is that if we have fixed 10GB of RAM for SQL server then only 4GB is left for the OS and everything else. But now the usage of RAM has also come down to 50%. Does SQL server use the 10GB all the time or does it use as much as it needs out of the 10GB ad leaves the rest free?

Thanks Mike

Amit
0
 

Author Closing Comment

by:amitkhera
ID: 39248658
What a star this chap is. I have been looking for a solution for such a long time.

Thanks Mike
0
 

Author Comment

by:amitkhera
ID: 39248666
Hi Mike
Where did the value 264856745 MB come from in my sql server max memory variable?
Is that the default value that SQL server gives on a default installation?
Does this value always need changing?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39248890
Yes it's the default and yes that's what needs changing. Unless you have a machine with 256 petabytes of RAM (and if you have then please employ me<grin>!).

Mike
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39248903
Amit,

SQL Server will eat whatever it can lay its hot sticky little fingers on, in the name of having space "just in case"! On top of the amount you limit it to it'll also grab some more (you can't stop this bit, but it usually isn't much) for the procedure cache. So, you should always give the OS what it needs, and as you're also running IIS and a load of ASPX stuff (which might be needing a chunk of RAM for its in-memory bits, I suggested 3/4. Many would have suggested a higher fraction, but this seems to work for you. Keep an eye on it and adjust it up or down accordingly.

hth

Mike
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:amitkhera
ID: 39252714
Hi Mike

The SQL server has been working fine with the 3/4th max server memory setting for 2 days.
Today the CPU is again in use 100% a few times. I have reduced the max server memory to 1/2 of total server memory. But even then sqlserv.exe is using too much CPU.
Shall I increase the fraction to say 7/8th and see how it goes.

Thanks

Amit
0
 

Author Comment

by:amitkhera
ID: 39252724
Hi Mike

I have made the max server memory to 7/8th of total memory and see how it goes.

Thanks

Amit
0
 

Author Comment

by:amitkhera
ID: 39252756
Hi Mike

After adjusting the max memory usage to 7/8th of server RAM, the CPU usage has gone down.
It is varying between 0 to 50% of max. its not going above 50% of max.
So iCare is working fine again.
I will keep you updated. If you think of anything else in the meantime please let me know

Amit
0
 

Author Comment

by:amitkhera
ID: 39252803
Hi Mike

I am afraid the memory adjustment has not helped us permanently.
What I have noticed is that the CPU is more or less constantly hogged around 50% constantly and goes to 80-90 % sometimes. But then all of a sudden it drops to 2-5 %.
The culprit is sqlserv.exe on all occasions. When the CPU is 2-5% in use, iCare works blazingly fast. When the CPU is 50-80-100% in use, iCare reels to a death. The error I get on the user interface is Server Exception call.
What I can't understand is as to why does SQL Server hog the server CPU and why does it fall to 0-2% CPU suddenly. Why is it varying so much?
I think I am going to get the sack soon !!

Thanks

Amit
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39252805
Getting a very high cpu spike isn't incredibly worrying - what is worrying is when it stays there long enough to impact other programs, which is what I think you were experiencing before. It sounds like you need to talk to the tech support of iCare and see just what Add Activity is doing. They will probably have seen this behaviour before (you surely can't be unique!) and have hopefully cured it before.

Aside from that, are you still getting web page problems, or are you just worried about the cpu spikes?

Mike
0
 

Author Comment

by:amitkhera
ID: 39252823
Hi Mike

I have had a word with the iCare support this morning and they are blaming it to something wrong on our server. As I said, I have been adding activities on iCare over the weekend and its been fine.

I have been trying to monitor the CPU usage this morning while adding activities.
My observation is - when the CPU is spiked upto anything over 20%, adding activities on iCare fails. When the CPU is around 0-5 %, adding activities is fine.

Also there have been instances where the Silverlight is going around in a circle while trying to  add an activity (CPU pegged at 50%) and then all of a sudden the CPU will come down to 1-2% and the activity will go in fine.

iCare support have offered to move iCare away from my SBS onto a spare server I have got.
This should resolve all the problems according to them. They could be right.
But it would be great if I could find a solution to this problem.

Thanks

Amit
0
 

Author Comment

by:amitkhera
ID: 39252980
Hi Mike
another update. iCare support told me that on their server they have limited max server memory for SQL to 1GB. This is the server they use to host iCare for lots of companies.
I have limited our server accordingly to 2GB of RAM for SQL and again the CPU usage has gone down to single digits in %age. iCare is working fine now.
But I am not holding my breath. Lets see how long it lasts.

Ta
Amit
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39253551
How curious. Normally SQL Server works better with more memory (hence its built-in desire to get as much as possible!) so I'm wondering just what iCare are doing that it behaves like this. Maybe their non-SQL code is taking up a lot of your RAM If so, they may be causing a lot of swapping (you can add columns to Task Manager to see this), so that all the other iCare processes are sitting around idly waiting for their code to swap back in from disk! Ask iCare what they'd recommend for total RAM - they may say "as much as possible, but still keep SQL Server starved".

Mike
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39258263
How're you getting on, Amit
0
 

Author Comment

by:amitkhera
ID: 39259773
Hi Mike

I was running out of time and the management want me to sort this out asap. So I am moving the iCare to a new VM which will run just iCare and nothing else
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39259841
Not a bad idea. We look forward to your next problems :)  

Good luck


Mike
0
 

Author Comment

by:amitkhera
ID: 39261959
Hi Mike

Just a quickie. This is probably going to be a very easy question for a man of your knowledge.

I have a new VM on my network. It has a static IP of 192.168.1.4
I couldn't access the IIS Homepage of this machine over my LAN. So I did a bit of research over the internet and enabled a rule in the Firewall of this machine which was something like HTTP-In (Branchcache etc etc etc). Now I can access the IIS homepage over my LAN.

Now I need to be able to access the IIS Homepage of this machine over the Internet. I went into my router settings. We have a static IP for our router(192.168.1.1). The static IP is forwarded to the SBS machine on the network (192.168.1.2) for things like RWW to work. So HTTP and HTTPS are already forwarded to 192.168.1.2 which is fine.

I created a new forwarding rule on the router to forward any requests to port 8080 to be forwarded to 192.168.1.4 (my VM) but it still doesn't work. It tells me page cannot be displayed. Do I need to enable a firewall rule or something on the VM? Please help.

http://192.168.1.4 over LAN works fine.
http://192.168.1.4:8080 over LAN says Page Cannot be displayed.
http://my public IP:8080 over internet says page cannot be displayed
http://my public IP over Internet brings me to IIS homepage of SBS machine.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39264836
I'm assuming that you're trying to access something like SSRS with this ...

You'll need to make sure that the .1.4 machine is actually looking for info on port 8080! You'll also have to configure the .1.4's IIS to serve stuff (like its home page) on 8080 and not 80!

Your middle two examples don't work because your .1.4 machine isn't looking on 8080 for any incoming traffic. I know because ... I used to work for a web startup during the .com bubble last millennium  -- {grin}

hth

Mike
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now