SQL 2012 using excessive CPU usage

Hi Experts,

I have noticed my SQL 2012 server is running between 87-95% CPU usage. The environment is a Windows Server 2012 with SQL 2012 standard installed, the server is not used for anything else other than VPN occasionally. We only have one instance running and the CPU is a 3.4GHz Quad Core Xeon and task manager is reporting SQLSERVER using all the CPU.

I would like to identify where this is being used, weather there is a leak or it is legitimate and i need a secondary CPU.

Look forward to reading your solutions.
isdd2000Asked:
Who is Participating?
 
Barry CunneyConnect With a Mentor Commented:
Hi
You can use the following SQL to identify what exactly is running in SQL Server - this will give a picture of the workload being carried out in SQL server and may give some ideas on the CPU usage
SELECT 
Hostname
,nt_username
,program_name
,sp.login_time 
,qt.text AS [Parent Query]
,sp.cpu
,sp.memusage 
,sp.physical_io 
,er.total_elapsed_time
,session_Id AS [Spid]
,er.blocking_session_id 
,er.granted_query_memory 
,er.start_time 
,ecid
,DB_NAME(sp.dbid) AS [Database]
,er.status
,er.wait_resource 
,wait_type
,SUBSTRING (qt.text, (er.statement_start_offset/2) + 1, --#1
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
,nt_domain
FROM sys.dm_exec_requests er --#2
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY sp.cpu DESC

Open in new window

0
 
duncanb7Commented:
You check the CPU usage  from  command of "taskmgr.exe" and click "resource monitor"
at the performance tab.

You could download the MS ProcessExplorer to look into more in detail at this
site to find which process is getting high cpu usage, http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Duncan
0
 
isdd2000Author Commented:
Issue ended up related to debug logging
0
All Courses

From novice to tech pro — start learning today.