January 25, 2013 at 11:45 pm
HI,
SQL SERVER CPU Usage shoots up to 100 % even at a time when user is not accessing application or any other background application. CPU usage keep flaunting very quickly it vary from 3 % to 100 %. If user start accessing application it stick at 100%.
It is SQL Server 2012.
Server Configuration.
Windows Server 2008 R2
RAM :- 32 GB
Physical Memory:- 500 GB
All other services are disable like SQL Reporting Services, Full text Search, SQL Agent and SQL analysis Services.
I have run the profile when cpu usage was close to 100%. And i saw there were Audit Login, Audit Logout, RPC completed and in text data sp_reset_connections.
I found very less query being executed by application. And "Audit Logout" was most time consuming process, some time was taking more than 2 seconds. I am not sure why Audit Logout taking so much time.
January 26, 2013 at 9:12 pm
Sure... open task manager and see what's using all of that CPU. If it IS actually SQL Server, then you probably have a "busted spid" trying to do a "forever rollback". If not, then you'll have to figure out what the other programs are doing.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2013 at 10:32 pm
I have checked the CPU usage and found all the CPU are usage by SQLSERVER. I ran the query to see the CPU utilization summary and found in 100% 95-98 % CPU is consumed by SQL and rest by other process.
NOt able to understand what to do next?
January 28, 2013 at 1:30 am
hi,
can you please send the query to find the cpu utiliZation
Thanks
Naga.Rohitkumar
January 28, 2013 at 1:59 am
Next step would be to identify what in SQL Server is using the CPU.
See chapter 3 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2013 at 2:52 am
run sp_whoisactive or sp_who2 to find out what that sessions are doing.
What procedure is on event "RPC Completed" and how much cpu it is using?
Try to execute that procedure yourself.
January 28, 2013 at 3:34 am
try these queries
-- Find queries that take the most CPU overall
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC
-- Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 28, 2013 at 3:35 am
Also verify blocked processes if any
SELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(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
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 28, 2013 at 3:40 am
CPU start consuming 100% in this case i run even a simple select command, it also take time..i have checked the query that are consuming most CPU time. I need to track the reason that shoots up CPU usage. If it is 100 % consuming, every query run very slow.
January 28, 2013 at 3:42 am
purushottam2 (1/28/2013)
CPU start consuming 100% in this case i run even a simple select command, it also take time..i have checked the query that are consuming most CPU time. I need to track the reason that shoots up CPU usage. If it is 100 % consuming, every query run very slow.
have you checked the Sp_who2
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 28, 2013 at 3:48 am
Yes i checked, .Net SqlClient Data Provider as program name was consuming most.
January 28, 2013 at 4:00 am
purushottam2 (1/28/2013)
Yes i checked, .Net SqlClient Data Provider as program name was consuming most.
You need to include additional fields in profiler to see what ObjectName and TextData (exact command) that application is executing in RPC Start event. Program name is not enough.
January 28, 2013 at 4:05 am
I ran SQL Profiler and saw many sp_resetConnection, AuditLogin, AuditLogOut.
I have 1 doubt i saw continue Audit Login and Logout. There were not actions between login and logout.
They were frequent at every seconds 1 Audit Login and Logout had been called.
Is this normal behavioral?
January 28, 2013 at 4:10 am
No, it's not normal. You probably have filters defined that hide real info - check them.
Remove audit login, logout, and reset connection events and run the trace again.
January 28, 2013 at 4:56 am
Did you read the book chapter I recommended?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply