LONG RUNNING CPU QUERY

---This query is useful to get the currently running CPU related long running queries.

SELECT  getdate() as runtime,
qs.last_execution_time,
qs.Execution_count as Executions,
qs.total_worker_time as TotalCPU,
qs.total_physical_reads as PhysicalReads,
qs.total_logical_reads as LogicalReads,
qs.total_logical_writes as LogicalWrites,
qs.total_elapsed_time as Duration,
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
substring (qt.text,qs.statement_start_offset/2,(case when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text,
qt.dbid as DBID,
qt.objectid as OBJECT_ID,
cast ( query_plan as xml) as XMLPlan
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where
CONVERT(VARCHAR(5),qs.last_execution_time,108)>'01:00'--Give Service now ticket TIME here.
AND CONVERT(VARCHAR(5),qs.last_execution_time,108)<'01:40'
and convert(varchar(50),qs.last_execution_time,102)='2019.06.17'

ORDER BY TotalCPU DESC

-- RUNNING_PCT column will tell you about the percentage of CPU spike if the wait type is SOS_SCHEDULER_YIELD

WITH Waits AS

 (

 SELECT 

   wait_type, 

   wait_time_ms / 1000. AS wait_time_s,

   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

 FROM sys.dm_os_wait_stats

 WHERE wait_type 

   NOT IN

     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',

   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',

   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')

   ) -- filter out additional irrelevant waits

   

SELECT W1.wait_type,

 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, 

 W1.wait_type, 

 W1.wait_time_s, 

 W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

go

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms)

AS NUMERIC(20,2)) AS signal_cpu_waits

FROM sys.dm_os_wait_stats


-------The below query will tell you about Long running queries running currently in the database. Run this command in master database

SELECT

       r.session_id

,      r.start_time

,      TotalElapsedTime_ms = r.total_elapsed_time

,      r.[status]

,      r.command

,      DatabaseName = DB_Name(r.database_id)

,      r.wait_type

,      r.last_wait_type

,      r.wait_resource

,      r.cpu_time

,      r.reads

,      r.writes

,      r.logical_reads

,      t.[text] AS [executing batch]

,      SUBSTRING(

                           t.[text], r.statement_start_offset / 2,

                           (      CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])

                                          ELSE r.statement_end_offset

                                  END - r.statement_start_offset ) / 2

                      ) AS [executing statement]

,      p.query_plan

FROM

       sys.dm_exec_requests r

CROSS APPLY

       sys.dm_exec_sql_text(r.sql_handle) AS t

CROSS APPLY  

       sys.dm_exec_query_plan(r.plan_handle) AS p

ORDER BY

       r.total_elapsed_time DESC;

with s as (
select top(100)
creation_time,
last_execution_time,
execution_count,
total_worker_time/1000 as CPU,
convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
qs.total_elapsed_time/1000 as TotDuration,
convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
total_logical_reads as [Reads],
total_logical_writes as [Writes],
total_logical_reads+total_logical_writes as [AggIO],
convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
total_dop,
convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
total_grant_kb,
convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
total_used_grant_kb,
convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
total_ideal_grant_kb,
convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
total_reserved_threads,
convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
total_used_threads,
convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads]
from sys.dm_exec_query_stats as qs with(readuncommitted)
order by convert(money, (qs.total_elapsed_time))/(execution_count*1000) desc
)
select
s.creation_time,
s.last_execution_time,
s.execution_count,
s.CPU,
s.[AvgCPUTime],
s.TotDuration,
s.[AvgDur],
s.[AvgIOLogicalReads],
s.[AvgIOLogicalWrites],
s.[AggIO],
s.[AvgIO],
s.[AvgIOPhysicalReads],
s.plan_generation_num,
s.[AvgRows],
s.[AvgDop],
s.[AvgGrantKb],
s.[AvgUsedGrantKb],
s.[AvgIdealGrantKb],
s.[AvgReservedThreads],
s.[AvgUsedThreads],
--st.text as query_text,
case
when sql_handle IS NULL then ' '
else(substring(st.text,(s.statement_start_offset+2)/2,(
case
when s.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else s.statement_end_offset
end - s.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid) as database_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
sp.[query_plan],
s.[sql_handle],
s.plan_handle,
s.query_hash,
s.query_plan_hash
from s
cross apply sys.dm_exec_sql_text(s.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(s.[plan_handle]) as sp
--where db_name(st.dbid)='AdventureWorks2014'

Identifying top CPU consuming Queries.

SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_time, 
    min_worker_time, max_worker_time, 
    min_elapsed_time, max_elapsed_time, 
    max_logical_reads, max_logical_writes, 
    execution_count, total_elapsed_time, 
    total_logical_reads, total_logical_writes, 
    total_physical_reads, query_plan_hash, 
    statement_start_offset, statement_end_offset, 
    query_hash, query_plan_hash, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_cpu_time DESC;

Identifying top I/0 Consuming Queries

SELECT TOP 10 total_logical_reads, total_logical_writes, 
    execution_count, total_elapsed_time, 
    query_plan_hash, statement_start_offset, 
    statement_end_offset, query_hash, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY total_logical_reads + total_logical_writes DESC;

LongRunningQueries:

SELECT TOP 10 qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time, 
    qs.execution_count, st.text, qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_elapsed_time DESC;