SQL statement causes CPU consumption to be so high

In general, we can use SQL Server's own profiling tracking tool SQL Profiler to analyze the source of problems generated by database design and to conduct targeted processing. However, we can also write SQL statements to target performance queries. Typically, the following three system views are used: sys.sysprocesses, dm_exec_sessions, dm_exec_requests

--one, to see how many current database user connections are
Use master

GO
SELECT *
From Sys. [sysprocesses]
WHERE [spid] > 50
--and db_name ([dbid]) = ' Gposdb '

SELECT COUNT (*)
from [SYS]. [Dm_exec_sessions]
WHERE [session_id] > 50

--second, select the top 10 CPU-consuming sessions
SELECT TOP 10
[session_id],
[REQUEST_ID],
[Start_time] As ' Start time ',
[Status] As ' state ',
[Command] As ' command ',
Dest. [Text] As ' SQL statement ',
Db_name ([database_id]) as ' database name ',
[BLOCKING_SESSION_ID] As ' is blocking the session ID of the other session ',
[Wait_type] As ' Wait for resource type ',
[Wait_time] As ' Waiting time ',
[Wait_resource] As ' waiting for resources ',
[Reads] As ' physical read Times ',
[writes] As ' write Times ',
[Logical_reads] As ' logical read Times ',
[Row_count] As ' returns the number of result rows '
From Sys. [Dm_exec_requests] As Der
Cross APPLY sys. [Dm_exec_sql_text] (Der.[sql_handle]) As Dest
WHERE [session_id] > 50
and Db_name (der.[database_id]) = ' Gposdb '
ORDER by [Cpu_time] DESC

---third, query the first 10 CPU-consuming SQL statements
SELECT TOP 10
Dest. [Text] As ' SQL statement '
From Sys. [Dm_exec_requests] As Der
Cross APPLY sys. [Dm_exec_sql_text] (Der.[sql_handle]) As Dest
WHERE [session_id] > 50
ORDER by [Cpu_time] DESC

--Iv. How many workers are waiting in a query session
SELECT TOP 10
[session_id],
[REQUEST_ID],
[Start_time] As ' Start time ',
[Status] As ' state ',
[Command] As ' command ',
Dest. [Text] As ' SQL statement ',
Db_name ([database_id]) as ' database name ',
[BLOCKING_SESSION_ID] As ' is blocking the session ID of the other session ',
Der.[wait_type] as ' wait for resource type ',
[Wait_time] As ' Waiting time ',
[Wait_resource] As ' waiting for resources ',
[Dows]. [Waiting_tasks_count] As ' number of tasks currently in waiting ',
[Reads] As ' physical read Times ',
[writes] As ' write Times ',
[Logical_reads] As ' logical read Times ',
[Row_count] As ' returns the number of result rows '
From Sys. [Dm_exec_requests] As Der
INNER JOIN [SYS]. [Dm_os_wait_stats] As dows on der.[wait_type] = [dows]. [Wait_type]
Cross APPLY sys. [Dm_exec_sql_text] (Der.[sql_handle]) As Dest
WHERE [session_id] > 50
ORDER by [Cpu_time] DESC

--Five, query the high CPU usage statement
SELECT TOP 10
Total_worker_time/execution_count as Avg_cpu_cost,
Plan_handle,
Execution_count,
(SELECT SUBSTRING (text, STATEMENT_START_OFFSET/2 + 1,
(case when statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), text))
* 2
ELSE Statement_end_offset
End-statement_start_offset)/2)
From Sys.dm_exec_sql_text (sql_handle)
) as Query_text
From Sys.dm_exec_query_stats
ORDER by [Avg_cpu_cost] DESC

SQL statement causes CPU consumption to be so high