.::: Top 50 CPU, Disk IO Consuming Queries, Find Slow SQL Queries & History Query :::.

  -- >> query 11 sdh di coba . Top 50 CPU Consuming Queries in SQL Server

Select
     @@SERVERNAME as ServerName,
     @@servicename as ServiceName,
     DB_NAME(st.dbid) AS [Database Name],
     st.[text] AS [Query Text],          
     wt.last_execution_time AS [Last Execution Time],
     wt.execution_count AS [Execution Count],
     wt.total_worker_time/1000000 AS [Total CPU Time(second)],
     wt.total_worker_time/wt.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan

 
from
    (select top 50
          qs.last_execution_time,
          qs.execution_count,
   qs.plan_handle,
          qs.total_worker_time
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) wt
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
 where  DB_NAME(st.dbid) is not null and not DB_NAME(st.dbid) = 'master'
  -- and DB_NAME(st.dbid) = 'msdb'
 --or not DB_NAME(st.dbid) = 'msdb' or not DB_NAME(st.dbid) = 'master' and or DB_NAME(st.dbid) = 'model' and or DB_NAME(st.dbid) = 'tempdb'
order by wt.total_worker_time desc


-- >> query 12 sdh di coba Top 50 Disk IO Consuming Queries in SQL Server

select
     @@SERVERNAME as ServerName,
     @@servicename as ServiceName,
     DB_NAME(st.dbid) AS [Database Name],
     st.[text] AS [Query Text],      
     qs.last_execution_time AS [Last Execution Time],
     qs.execution_count AS [Execution Count],
     qs.total_logical_reads AS [Total Logical Read],
     qs.total_logical_reads/execution_count AS [Average Logical Read],
     qs.total_worker_time/1000000 AS [Total CPU Time(second)],
     qs.total_worker_time/qs.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan AS [Execution Plan]
from
    (select top 50
          qs.last_execution_time,
          qs.execution_count,
   qs.plan_handle,
          qs.total_worker_time,
          qs.total_logical_reads
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
where  DB_NAME(st.dbid) is not null and not DB_NAME(st.dbid) = 'master'
  -- and DB_NAME(st.dbid) = 'msdb'
 --or not DB_NAME(st.dbid) = 'msdb' or not DB_NAME(st.dbid) = 'master' and or DB_NAME(st.dbid) = 'model' and or DB_NAME(st.dbid) = 'tempdb'

order by qs.total_logical_reads desc

 
-- >> query 13 Find Slow SQL Queries With sys.dm_exec_query_stats  sdh dicoba  https://voltwu.github.io/blog/mssql/2021/08/24/sql-statements-with-sys-dmexecquerystats-view/
 

SELECT TOP 10000
      qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
      qs.total_elapsed_time / 1000000.0 AS total_seconds,
      qs.execution_count,
      qs.total_rows,
      qs.last_rows,
      qs.min_rows,
      qs.max_rows,
      SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
                  (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+1
                  ) AS individual_query,
      o.name AS object_name,
      @@SERVERNAME as ServerName,
      @@servicename as ServiceName,
      DB_NAME(qt.dbid) AS database_name
FROM
      sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
      LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
-- WHERE
  --    qt.dbid = DB_ID('AdventureWorks2019')
  where  DB_NAME(qt.dbid) is not null
  -- and DB_NAME(qt.dbid) = 'msdb'
-- and not DB_NAME(qt.dbid) = 'msdb' and not DB_NAME(qt.dbid) = 'master' and not DB_NAME(qt.dbid) = 'model' and not DB_NAME(qt.dbid) = 'tempdb'
ORDER BY
      average_seconds DESC;


 

-- query 14 How to see a history of queries ran on a SQL server >> sdh dicoba


with s as (
select top(1000)
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,     @@SERVERNAME as ServerName,
     @@servicename as ServiceName,
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,
      @@SERVERNAME as ServerName,
      @@servicename as ServiceName,
      DB_NAME(st.dbid) AS database_name
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) is not null
  -- and db_name(st.dbid) = 'msdb'
 not db_name(st.dbid) = 'msdb' and not db_name(st.dbid) = 'master' and not db_name(st.dbid) = 'model' and not db_name(st.dbid) = 'tempdb'