Address High CPU on SQL Server

Following are the guidelines to troubleshoot High CPU scenario in MSSQL Server and fix the problem.

1. Identify running SQL statements taking more CPU
2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and creation and last execution time
3. Find the SQL Stagement, check if this is a procedure
3.A: If this is SQL Procedure, try to recompile the procedure and see if this can fix the problem:
4. Remove Plan Cache for specific Plan
5 A. Remove Plan Cache for Database
5. Remove Cache for entire server/Pool
6. Find Query Execution Plan and identify if any indexes can be added:


-- 1. Identify running SQL statements taking more CPU

-- Quick capture of session, host, login, sql text, blocking text
-- reads, writes, cpu, and writes in tempdb
SELECT  [r].[session_id],
        [s].[host_name],
        [s].[login_name],
        [r].[start_time],
        [r].[sql_handle],
		[st].[text],
        [r].[wait_type],
        [r].[blocking_session_id],
        [r].[reads],
        [r].[writes],
        [r].[cpu_time],
        [t].[user_objects_alloc_page_count],
        [t].[internal_objects_alloc_page_count],
		[r].database_id
FROM    [sys].[dm_exec_requests] AS [r]
JOIN    [sys].[dm_exec_sessions] AS [s]
        ON [s].[session_id] = [r].[session_id]
JOIN    [sys].[dm_db_task_space_usage] AS [t]
        ON [s].[session_id] = [t].[session_id] AND
           [r].[request_id] = [t].[request_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[sql_handle]) AS [st]
WHERE   [r].[status] IN ('running', 'runnable', 'suspended')
--AND r.database_id = 23
GO



-- 2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and creation and last execution time
select top 100 ((total_elapsed_time/execution_count)/1000) as AverageExectime_milliSecondas ,plan_handle, creation_time, last_execution_time, execution_count, qt.text, * 
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
   WHERE sql_handle =  0x030094859475487654857485......



-- 3. Find the SQL Stagement, check if this is a procedure 
-- 3.A: If this is SQL Procedure, try to recompile the procedure and see if this can fix the problem: 
sp_recompile [clk.OutboundQueue_ListItemsByItemTypeIdItemId]


-- 4. Remove Plan Cache for specific Plan (Ref: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017)
DBCC FREEPROCCACHE (0x050985493434340304000....)

-- 5.A. Remove Cache for database (Ref: https://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/)
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = 'AdventureWorks');
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);


-- 5.B. Remove Cache for entire server/Pool (Ref: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017)
DBCC FREEPROCCACHE 


-- 6. Find Query Execution Plan and identify if any indexes can be added:
SELECT  query_plan
FROM    sys.dm_exec_query_plan(0x050098759845400000......);
GO


--
Advertisements
This entry was posted in Execution Plan, Performance, SQL, SQL Server, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s