CODEWITHSUNDEEP
sql-server
Ponraj
Ponraj

Reputation: 29

High CPU utlization on sqlserver2012

I am facing high cpu usage on specific time. 4 processor,16GB RAM, 29 Databases total datafile size 60 GB. i often got high thread count is too high. can anyone help me on this?

Upvotes: 2

Views: 175

Answers (2)

Dan Stef
Dan Stef

Reputation: 773

Use the following statement to check which database used the most CPU:

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [Database Name],     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 [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5,2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

Or this statement to find which SQL statement used the most CPU

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
    (
        (
            CASE qs.statement_end_offset
                WHEN -1 THEN DATALENGTH(qt.[text])
                ELSE qs.statement_end_offset
            END - qs.statement_start_offset
        ) / 2
    ) + 1
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5    --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

The 2nd statement was found here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33c7521f-7ed4-4574-82dd-aeb2f32111c5/find-high-cpu-query?forum=sqldatabaseengine

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28920

There is no immediate solution.You will have to find out queries, which are causing high CPU and fine tune them.

Below DMV ,gives you queries which are consuming more CPU

SELECT TOP 50 qs.creation_time, qs.execution_count, qs.total_worker_time as total_cpu_time, qs.max_worker_time as max_cpu_time, 
qs.total_elapsed_time, qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads, qs.max_physical_reads,t.[text], qp.query_plan, t.dbid, t.objectid, t.encrypted, qs.plan_handle, qs.plan_generation_num FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
ORDER BY qs.total_worker_time DESC

further ,you are saying,thread count is to high.It may be due to parallelism as well..so ,you may have to find out,queries that are running in parallel.

After finding them,find out reasons for their parallel execution.In Most cases,it may be due to lack of indexes as well.

This query will help you in this:Find Query Plans That May Utilize Parallelism

SELECT
p.*,
q.*,
cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) q
WHERE cp.cacheobjtype = 'Compiled Plan'
AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

Upvotes: 3

Related Questions