Link to home
Start Free TrialLog in
Avatar of Dustin Saunders
Dustin SaundersFlag for United States of America

asked on

SQL Server CPU usage (Resource Monitor)

I have a few instances of SQL on 2014 SP2 and they are using CPU consistently.  There doesn't appear to be any memory pressure, and all instances have this same issue.

Is there a known bug or reason why this CPU usage would be so high?
sqlResourceManager.png
Avatar of lcohan
lcohan
Flag of Canada image

There could be various reasons why CPU utilization is high in SQL (missing indexes/outdated stats being one of the most common as far as I'm aware) and I recommend you use SQL own Performance Dashboard reports for deeper in detail analysis why the CPU is so high - down to the query level.

https://www.microsoft.com/en-ca/download/details.aspx?id=29063
http://www.sqlshack.com/performance-dashboard-reports-sql-server-2014/

Anti-virus software can also do this in some occasions if SQL own database files mfd, ldf, ndf, aren't excluding from scanning.
However, seeing just the summary of the wait type doesn't help too much to pinpoint the particular query(ies) that is(are) causing it. For that I recommend you to install the Adam Mechanic's sp_whoisactive stored procedure and execute it to see what exactly processes/queries have that wait type:

http://sqlblog.com/files/folders/beta/entry42453.aspx

Just install it in master database and then execute it with:

EXEC sp_whoisactive

and look for queries with a lot of CPU in CPU column and their wait type in wait_info column. here are article how you can use the procedure for more complex monitoring:

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
1. Restart SQL Server services first and then see the shoot up of memory and CPU.

2. If the utilization is still high then check the session which is consuming it.

3. Check whats going on.. if any query is taking too much of time the update the stats and rebuild indexes on the tables used.

Please let me know if you still face any issues.
I have a few instances of SQL on 2014 SP2 and they are using CPU consistently.
Are all those instances in the same box or each machine has their own SQL Server instance?
Can you provide the machine configuration (CPU & RAM)?
How the CPU usage looks like in the Task Manager?
I have a few instances of SQL on 2014 SP2 and they are using CPU consistently.
Are all the SQL Server instances in a single server or each machine has their own SQL Server instance?
How the CPU usage looks like in the Task Manager?
Can you provide the machine configuration (CPU & RAM)?
1. Restart SQL Server services first and then see the shoot up of memory and CPU.
This should be your last resort. First you have to check if you can identify the cause of this symptom and remove it. If it will still persist then you can try a SQL service restart.
1. Restart SQL Server services first and then see the shoot up of memory and CPU.
This is the typical solution usually used by developers that aren't used to work in production and in a shared environment.
Avatar of Dustin Saunders

ASKER

15 express instances, all on the same server.  1 socket, 4 core- 2014 SP2.  18GB RAM.

The problem is that something triggers the resource monitor to start using CPU and it never stops.  So each instance will use ~6% of the CPU until the server is restarted.  Restarting the instance does not restart this behavior.

We don't run AV on the SQL servers.

If I restart the SQL service and no users connect to it, it still will use CPU continually.  All instances target server memory match total server memory.
For reference, that CPU time (51,522,968) is over the course of 4 days.  I only see this usage on SQL 2014 servers (we have 2).

On a SQL 2012 R2 server after 7 days the max CPU time for resource monitor on any instance is 179,120.
15 express instances, all on the same server.  1 socket, 4 core- 2014 SP2.  18GB RAM.
This is the same server where you got the issue last week with the Restore command? If so, I thought that you had increased the memory of the server.

Anyway 15 Express Editions can only use a maximum of 15GB RAM so OS will still have 3GB for itself.
Are any of those SQL Server instances facing heavy tasks?
IMO you should provide more CPU to the server or better is to migrate at least half of the instances to a new server.
This is a different server--  but this is abnormal behavior.  None of my other servers (there are about 160) use any CPU like this, even with more load.  

I attached 2 CPU graphs since June of servers that have 30 instances on them.   I'd like to find out what is causing the problem and fix that, why is it only (seems to be) 2014 that does this?  Is there a way to see what RESOURCE MONITOR is busy doing?
sqlNormal1.png
sqlNormal2.png
All 15 instances has user databases? If not then shutdown the ones that doesn't have databases to see if it helps.
Or if you can, shutdown each instance at a time and give some minutes to check the CPU behavior. The one that you shutdown and release CPU time should be the one that has issues.
All 15 have user databases-  I did try shutting them down one by one but the problem persisted.  When I restart the instances one by one, they immediately resume using CPU and the CPU time for RESOURCE MONITOR is the only thing being used.  If the instance has already started doing this, when the service is restarted the CPU resumes abnormal usage.

Only a server reboot resolves the issue.
Only a server reboot resolves the issue.
But temporary, right?
What's the behavior if only one of the 15 instances is up and running?
Yes, the reboot clears the issue for anywhere from 12 hours - 36 hours.
With only 1 instance using ~6-8% CPU constantly.
With only 1 instance using ~6-8% CPU constantly.
Can we say that 2 instances uses 12-16% of CPU or isn't that linear?
Yeah it's fairly linear.  With 2 instances it is about 10-12%.

However, there are no connections on the database after restart while the CPU is being used.
there are no connections on the database after restart while the CPU is being used.
This is the weird part. I guess you already tried to run a trace to capture the SQL Server instances activities after they restart, right?
Nothing on the trace after an instance restart, but CPU going.  

If I then log in and run my query to see CPU time, I can see it just continually going up into the millions.

I'm using:
;with cte ([totalCPU]) as (select sum(cpu) from   master.dbo.sysprocesses)
select 
	  tblSysprocess.spid
	, tblSysprocess.cpu
	, CONVERT(BIGINT,(tblSysprocess.cpu * CONVERT(BIGINT,100))) / CONVERT(BIGINT, cte.totalCPU) as [percentileCPU]
	, tblSysprocess.physical_io
	, tblSysprocess.memusage
	, tblSysprocess.cmd
	, tblSysProcess.lastwaittype
from   master.dbo.sysprocesses tblSysprocess
         cross apply cte
order by tblSysprocess.cpu desc

Open in new window


to generate that table I submitted in original post.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland 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
Only seemed to be an issue with the 2014 environment.  DB's diff'd back to 2012 don't have the issue.