Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

High CPU Issue Expand / Collapse
Author
Message
Posted Saturday, May 4, 2013 9:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Hi,
We have a high CPU issue, it's almost 80 - 90% and sometimes close to 100%.
I will be trying to Rebuild the indexes as lots of indexes having fragmentation issue, I will be also updating the statistics and Recompiling the objects to improve Stored Procedure.
Do you suggest anything which can help to identify the CPU issue and resolve it?

Thanks,
Post #1449489
Posted Sunday, May 5, 2013 12:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Sounds like you are doing a lot of guess work to identify the issue.


Run a server side trace capturing RCP:Completed and SQLBatch:Completed events with ( at very least the cpu column). With this run the output through ClearTrace http://www.scalesql.com/cleartrace/download.aspx

You will now know *which* actions are having the highest impact.

Once you have done that feel free to post back with your finding for better targeted advice




Clear Sky SQL
My Blog
Kent user group
Post #1449498
Posted Sunday, May 5, 2013 6:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,207, Visits: 36,616
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Identify the largest CPU users, tune the queries, repeat until performance is acceptable



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449509
Posted Sunday, May 5, 2013 7:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
Thanks Gail.
Thanks Dave. I have down loaded ClearTrace Trace tool on my local m/c and I will run Trace tomorrow on my production server and import that trace file into ClearTrace Tool, it has a nice graphically analysis.

Gail,
I will be also looking part1 and part2 solution.
Post #1449561
Posted Monday, May 6, 2013 2:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 27, 2013 8:19 AM
Points: 2, Visits: 21
This has worked well for us time and again (we use sql server 2005). This query gives the currently running queries, run it when the cpu is high and review and optimize the slow queries that show up.

Ravi Periasamy

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
Post #1449624
Posted Monday, May 6, 2013 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
From SQL Server 2008 R2 Diagnostic Information Queries by Glenn Berry you can grab a quick view of your highest CPU procs:

Find highest CPU use by DB:

-- Get CPU utilization by database (adapted from Robert Pearl)  (Query 17)
WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

-- Helps determine which database is using the most CPU resources on the instance

PS Forgot the one that shows the highest procs

USE TheDBFromLastCheck;
GO

-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost (Query 38)
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1449812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse