https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/
Email::: Microsoft findings
Below queries were used on siebeldb database high CPU issue to findout which query being used, these queries given by Microsoft
We can find what is the exact query behind fetch cursor quey.
======== For transaction creation time
SELECT c.session_id, es.program_name, es.login_name, es.host_name, DB_NAME(es.database_id) AS DatabaseName, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (0) c
LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
================= Plan handle with SQL statement
SELECT er.sql_handle, ec.sql_handle,
SUBSTRING(ers.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(ers.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text_er,
SUBSTRING(ecs.text, (ec.statement_start_offset/2)+1,
((CASE ec.statement_end_offset
WHEN -1 THEN DATALENGTH(ecs.text)
ELSE ec.statement_end_offset
END - ec.statement_start_offset)/2) + 1) AS statement_text_ec
FROM sys.dm_exec_requests er cross apply sys.dm_exec_cursors(er.session_id) ec
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) ers
CROSS APPLY sys.dm_exec_sql_text(ec.sql_handle) ecs
======================================Creation time =====
SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
( CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO
========================================================================================================
Below queries were used during siebeldb CPU full issue by Microsoft
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
==================================================
Top CPU
SELECT s.session_id
,r.STATUS
,r.blocking_session_id 'blocked by'
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'Wait Time (in Sec)'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
=========================================
select case
when cpu_count / hyperthread_ratio > 8 then 8
else cpu_count / hyperthread_ratio
end as optimal_maxdop_setting
from sys.dm_os_sys_info;
===========================================
TOP CPU
SELECT
r.session_id,
se.host_name,
se.login_name,
Db_name(r.database_id) AS dbname,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time as duration_ms,
r.reads,
r.logical_reads,
r.writes,t.pending_io_count as "Physical_IO_Performed",
t.pending_io_byte_count as "Physical_IO_Bytes",
r.row_count as rows,r.granted_query_memory*8 as Granted_query_memory_KB ,
t.task_state as Task_Status,
t.scheduler_id,
r.blocking_session_id, r.wait_type,r.wait_time,r.wait_resource,r.lock_timeout,r.open_transaction_count,r.transaction_isolation_level,r.executing_managed_code,
tsu.database_id,tsu.internal_objects_alloc_page_count,tsu.user_objects_alloc_page_count,tsu.internal_objects_dealloc_page_count,tsu.user_objects_dealloc_page_count,
s.TEXT sql_text,
p.query_plan query_plan,
sql_CURSORSQL.text as Cursor_Text,
SQL_CURSORPLAN.query_plan as Cursor_Plan,
CURRENT_TIMESTAMP as currenttime
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions se
ON r.session_id = se.session_id
INNER JOIN sys.dm_os_tasks t on t.request_id=r.request_id and t.session_id=se.session_id and r.scheduler_id=t.scheduler_id
Inner join sys.dm_db_task_space_usage tsu on tsu.request_id = r.request_id and tsu.session_id=r.session_id and tsu.exec_context_id = t.exec_context_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE -- r.session_id <> @@SPID
se.is_user_process = 1
order by cpu_time desc
======================================================
MISSING INDEX
-------------
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
=====================================
Select * from sys.dm_exec_requests where wait_time > 0 and wait_resource like '2:%'
ORder by wait_time desc
========================================
FIND INDEX FRAGMET
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30