Link to home
Start Free TrialLog in
Avatar of sglee
sglee

asked on

SQLSERVR.EXE - High CPU and Memory Usage

Hi,
 
 I have SBS2011 as Virtual Machine in Windows 2012 Hyper-V Server.
 I noticed that CPU is at 100% for 24 hours. I checked the processes tab in Task Manager and discovered that sqlservr.exe is taking up 80-95% with 1,334,144K of memory and there are multiple instances of sqlservr.exe in the process tab.
 
 I googled the subject and found this article where the author reduced the memory usage of three SQL databases in SBS2011 - (1) SBSMonitoring, (2) Sharepoint and (3) Windwos Internal Database (microsoft ##SSEE) by running Windows 2008 R2 SQL Management Studio / connecting to each database / open Properties and change the Maximum server memory to 2,048 from default 2,147,483,647. My SBS2011 has 16GB of RAM.

Will the reduction of the memory to 2,048 take care of this high CPU issue or this problem comes from somewhere else?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of trawley
trawley

Yes young padawan, when  issues solving we are of memory over useage, but here talking  we are about CPU useage.

Move away from the dark side...

Get rid of hyper V...
Avatar of sglee

ASKER

Changing to VMWare is not an option at this point and this is NOT happening all the time, although I have two MS HyperV sites and both running SBS2011 as VM and both VMs do run significantly slower that SBS2011 as VM in VMWare environment.

In terms of instance of SQL server, I can not answer that because I do not know how to tell.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What OS is SBS2011?

Win8+ and compatible has it directly built-in in the task manager, just expand the node.

In earlier version add the command-line column on the process tab.
Avatar of sglee

ASKER

When I ran SQL management, I had to connect to each database three separate times. I got errors when trying to open up the properties window on two databases (1) and (2) to adjust memory size.
What errors?
Avatar of sglee

ASKER

I am not in front of the computer right now, but will be in about three hours. I will post errors then.
Avatar of sglee

ASKER

SBS2011 is Small Business Server 2011 and it is essentially Windows Server 2008 R2 with Exchange Server 2010.
Avatar of sglee

ASKER

User generated imageUser generated imageUser generated imageUser generated imageI can connect to dc1\sbsmonitoring and \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query without a problem and can open properties/memory section, but when I tried to connect to DC1\SHAREPOINT, it fails with an error.
Does this error impact the operation of the server at all?
The users on this network uses SBS2011 for Outlook/Exchange , to store files in Word, Excel, Quickbooks, Internet.
I do not believe that they use any feature related to Sharepoint.
Restart the server and get it on a weekly schedule for a restart.

Hope that helps...
Avatar of sglee

ASKER

Sherlock,

Would you explain to me as to how restarting the server once a week would address this high CPU problem?
I restarted this server a few times before posting my question on this board and it did not help.
Add the command-line column. It's in the view menu:
User generated image
Didn't see a mention of the restarts in any of the information. MS SQL servers are not perfect, and often a restart of the SQL services or the entire server will help to bring the condition within acceptable specs.

The name is not Sherlock btw... just trying to help, after a long day's work.

Best of luck solving the issue.
Restarting is only curing the symptom, not the cause. Or in other words: snake oil.

That's simply why - at least I - did not mention it.
Yes, when circumstance permits, finding the cause is always preferable. Sometime just a quick fix is needed however just to keep the users working.

Have a good one...
When you don't know what you have fixed, it's not a fix.

Especially as restarting the SQL Server instances may have severe performance effects: cold buffers again, no plan cache.

And: Depending on the instances recovery model, you may lose some data.
Avatar of sglee

ASKER

User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sglee

ASKER

@SneekCo
"The name is not Sherlock btw" ---> My apology. I was using using my iPhone to post my question to you. I spelled your name as Sneekco for sure, but apparently iPhone must have used what it thought I was trying to type in. Again sorry about that.
Avatar of sglee

ASKER

@ste5an,
"So you need to connect to that service the next time you have that load with SSMS and use either the Activity Monitor in SSMS or EXECUTE sp_who2 in a query to see the running process." -->
I don't quite follow you as I have never been to SQL Server Management Studio.
Would you be able to provide some steps that I can follow?
@sglee - no problem. Thanks for the info, phones seem to have a mind of their own :-)
You said:
I can connect to dc1\sbsmonitoring and \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
hmm, so What do I miss here?
Avatar of sglee

ASKER

User generated imageFor some reason, I am able to connect to all three databases in SQL Mgmt Studio.
CPU% is still 100%. You said reduction of maximum memory 2147483647 won't help.
Where do I go from here?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sglee

ASKER

User generated imageWhat am I looking for on this screen?
Sort the list by task state. The running tasks are the interesting ones. Right click on those running tasks and press show detail..
sort by " head blocker"

check what does it run and kill if you can this spid (starting, as per your picture, from #1)
SSMS: kill <spid#>

==
Avatar of sglee

ASKER

I replaced this server (with high CPU Util % issue) with a new server and turned off the order server onsite.
Sometime in a week or so, I am going to pick it up from the site and bring it to my office and then I will be able to try out these suggestions.
I will report back once I bring the old server to my lab.
Avatar of sglee

ASKER

I am going to pick up this server sometime next week (as the new server is working well).
Then I will be able to do whatever I wanted to do. I will keep you posted.
I don't know what the problem is, but I don't see high CPU % on a new server. Of course there is a vast difference between two servers in terms of hardware specs.
Avatar of sglee

ASKER

I have the server now, but in light of the fact that:
(1) I have already delivered a new server and all is running good
(2) the old server (SBS2011 as VM in Hyper-V) with SQL errors simply is too slow to do anything - clicking takes a few minutes to see the result ... etc, so I would not want to spend time troubleshooting this issue anymore.

Thank you everyone for your help and I appreciate it.