May 4, 2013 at 9:34 pm
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,
May 5, 2013 at 12:36 am
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
May 5, 2013 at 6:05 am
Identify the largest CPU users, tune the queries, repeat until performance is acceptable
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
May 5, 2013 at 7:38 pm
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.
May 6, 2013 at 2:27 am
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
May 6, 2013 at 11:25 am
From SQL Server 2008 R2 Diagnostic Information Queries by Glenn Berry[/url] 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply