.::: Top 10 Query SQL Server base on Top 10 Total CPU, Average CPU, Expensive Query, Execution count :::.

TOTAL_WORKER_TIME = in mocrosecond https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16
-- Query 1 : Top 10 total CPU consuming queries >> sdh dicoba

SELECT TOP 10

       @@SERVERNAME as ServerName,

       @@servicename as ServiceName,

       DB_NAME(qp.dbid) AS database_name,

       QT.OBJECTID AS OBJECT_ID, 

       object_name(QT.OBJECTID) as ObjectName,

       QS.TOTAL_WORKER_TIME AS CPU_TIME,

       QT.TEXT AS STATEMENT_TEXT,

       QP.QUERY_PLAN

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 (QS.PLAN_HANDLE) AS QP

-- where not DB_NAME(qp.dbid) = 'msdb' and not DB_NAME(qp.dbid) = 'master' and not DB_NAME(qp.dbid) = 'model' and not DB_NAME(qp.dbid) = 'tempdb'

-- where  OBJECT_ID is not  = 'null'

-- where DB_NAME(qp.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.TEXT ='select * from pembelian'

where  QT.OBJECTID is not null

ORDER BY TOTAL_WORKER_TIME DESC

-- Query 2 : Top 10 average CPU consuming queries  >> sdh dicoba

SELECT TOP 10

    @@SERVERNAME as ServerName,

    @@servicename as ServiceName,

    DB_NAME(qt.dbid) AS database_name,

    QT.OBJECTID AS OBJECT_ID,

    object_name(QT.OBJECTID) as ObjectName,

    TOTAL_WORKER_TIME ,

    EXECUTION_COUNT ,

    TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,

    QT.TEXT AS QUERYTEXT

FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT

-- where 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'

-- where DB_NAME(qt.dbid) = 'teguhth'

-- where DB_NAME(qt.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.TEXT ='select * from pembelian'

-- where  QT.OBJECTID is not null

where  QT.OBJECTID is not null and object_name(QT.OBJECTID) is not null and DB_NAME(qt.dbid) is not null

ORDER BY [AVG CPU TIME] DESC ;

-- ORDER BY QS.TOTAL_WORKER_TIME DESC ; 

-- Query 3 : Top 10 I/O intensive queries >> sdh dicoba

SELECT TOP 10

       @@SERVERNAME as ServerName,

       @@servicename as ServiceName, 

       DB_NAME(QT.DBID) AS DBNAME,

       QT.OBJECTID AS OBJECT_ID,

       object_name(QT.OBJECTID) as ObjectName,

       TOTAL_LOGICAL_READS,

       TOTAL_LOGICAL_WRITES,

       EXECUTION_COUNT,

       TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

       QT.TEXT AS QUERY_TEXT

FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0 

-- and QT.TEXT ='select * from pembelian'

-- and QT.OBJECTID is not null and DB_NAME(qt.dbid) = 'teguhth' -- >> for lab

-- and 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'

-- where DB_NAME(qt.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.OBJECTID is not null

ORDER BY [IO_TOTAL] DESC

 

-- Query 4 : Execution count of each query >> sdh dicoba

SELECT top 10

       @@SERVERNAME as ServerName,

       @@servicename as ServiceName,

       QT.DBID,

       DBNAME= DB_NAME (QT.DBID),

       QT.OBJECTID,

       object_name(QT.OBJECTID) as ObjectName,

       QS.EXECUTION_COUNT,

       QT.TEXT AS QUERY_TEXT,

       QS.TOTAL_ROWS,

       QS.LAST_ROWS,

       QS.MIN_ROWS,

       QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

where QT.OBJECTID is not null

-- where  QT.TEXT ='select * from pembelian'

-- where  DB_NAME(qt.dbid) is not null

-- where DB_NAME(qt.dbid) = 'teguhth' and QT.OBJECTID is not null

-- where  QT.OBJECTID is not null

-- where  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 QS.EXECUTION_COUNT DESC