Solved

SQL Server CPU usage (Resource Monitor)

Posted on 2016-09-13
22
63 Views
Last Modified: 2016-09-28
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
0
Comment
Question by:Dustin Saunders
  • 9
  • 8
  • 3
  • +2
22 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41796503
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41796662
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41796671
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/
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797365
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41797412
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?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41797414
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)?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41797816
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41797830
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.
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41797876
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.
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41797886
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41797918
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41798010
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
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41798022
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.
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41798047
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41798051
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?
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41798055
Yes, the reboot clears the issue for anywhere from 12 hours - 36 hours.
With only 1 instance using ~6-8% CPU constantly.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41798067
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?
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41798073
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41798085
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?
0
 
LVL 12

Author Comment

by:Dustin Saunders
ID: 41798223
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.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41799446
I wouldn't monitor the CPU like that. I usually follow this MSDN article.
You can also query sys.dm_os_ring_buffers for resources usage, including CPU.
0
 
LVL 12

Author Closing Comment

by:Dustin Saunders
ID: 41820382
Only seemed to be an issue with the 2014 environment.  DB's diff'd back to 2012 don't have the issue.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

23 Experts available now in Live!

Get 1:1 Help Now