[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

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
0
Dustin Saunders
Asked:
Dustin Saunders
  • 9
  • 8
  • 3
  • +2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
ZberteocCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ZberteocCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
Yes, the reboot clears the issue for anywhere from 12 hours - 36 hours.
With only 1 instance using ~6-8% CPU constantly.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Dustin SaundersDirector of OperationsAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now