SQL 2012 using excessive CPU usage

Ask Question Forum:

Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page

Show

ASK RECENT

SQL 2012 using excessive CPU usage

Asked by isdd2000

at 2024-07-30 01:17:20

Point:500 Replies:3 POST_ID:829161USER_ID:12054

Topic:

MS SQL Server;IT Administration;Windows Server 2012

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.

Author: isdd2000 replied at 2024-09-02 21:28:31

Issue ended up related to debug logging

Accepted Solution

Expert: Barry Cunney replied at 2024-07-30 01:40:10

500 points GOOD

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

SELECTHostname,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 = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE er.statement_end_offsetEND - er.statement_start_offset)/2) + 1) AS [Individual Query],nt_domainFROM sys.dm_exec_requests er --#2INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qtWHERE session_Id > 50AND session_Id NOT IN (@@SPID)ORDER BY sp.cpu DESC

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:

Select All Code

Expert: duncanb7 replied at 2024-07-30 01:32:55

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

新聞財經娛樂♬資訊 News Economy

About Us|Service|Pricing

Stock Index全球即時指數期貨:

&nbps;

News即時新聞:↓

News即時新聞:↑

On Balance Volume (OBV):

Youtube Video♬ Sharing by members:↓

Youtube Video♬ Sharing by members:↑

Ask Question Forum:

SQL 2012 using excessive CPU usage

Your Information

Members/Profolio