How to check which query or user caused SQL Server 100% CPU Utilization-CodePudding

My SQL server 2016 database had 100% CPU Utilization issue at 06:30. Now I want to check which query or user caused this issue. I searched some articles online. They are all showing how to check it when the query is running. But what I want is to check history at 06:30. The database is fine now. So if I check the performance now, I can't find any query/job are running now. How do I check the history to identify the cause?

CodePudding user response：

You could try to investigate the top expensive parallel execution queries from sys.dm_exec_query_stat dmv, where max_worker_time is maximum CPU time.

Select Top 100 O.type_desc, O.name, S.creation_time, S.last_execution_time, S.min_elapsed_time, S.max_elapsed_time,
       S.Last_elapsed_time, S.Min_worker_time, S.Max_worker_time, S.Last_worker_time, S.execution_count,
       SUBSTRING (T.text, (S.statement_start_offset/2) 1, ((CASE WHEN S.statement_end_offset = -1 THEN DATALENGTH(T.text) ELSE S.statement_end_offset END - S.statement_start_offset)/2) 1)
From sys.dm_exec_query_stats As S Cross Apply sys.dm_exec_sql_text(S.sql_handle) as T
                                  Cross Apply (Select type_desc, name From sys.objects Where object_id=T.objectid) as O
WHERE T.dbid = DB_ID() And
      S.Max_worker_time > S.Max_elapsed_time
Order by S.Max_worker_time Desc

CodePudding user response：

from ssms, run the activity monitor for the server and look at recent expensive queries.