[MSSQL] Find out CPU usages of all different SQL Server Objects

In SQL Server Management Studio run the "Performance - Top Queries By Total CPU Time" report.


In this post, I provide you one script to find out CPU usages of all different SQL Server Objects.
This script is very important for DBA when DBA monitoring CPU usages.
Below is a script:

http://www.dbrnd.com/2015/06/sql-server-cpu-usage-per-different-objects/

Measure CPU Usage Per Database :

 WITH cte as ( SELECT SDEPA.DatabaseID , SUM (SDEQS.total_worker_time) AS CPUTimeInMiliSeconds FROM sys.dm_exec_query_stats SDEQS CROSS APPLY (SELECT CAST(value as int) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(SDEQS.plan_handle) WHERE attribute = N'dbid') SDEPA GROUP BY SDEPA.DatabaseID) SELECT COALESCE(DB_NAME(DatabaseID), '{Overhead Processes}') as [Database] , CAST ([CPUTimeInMiliSeconds] * 1.0 / SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0 as DECIMAL(6, 3)) AS [CPUTime%] FROM cte ORDER BY CAST ([CPUTimeInMiliSeconds] * 1.0 / SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0 as DECIMAL(6, 3)) DESC;

Thống kê top query trên database cụ thể:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan,

[DB] = DB_NAME(qt.[dbid])

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

--ORDER BY qs.total_logical_reads DESC -- logical reads

--ORDER BY qs.total_logical_writes DESC -- logical writes

where DB_NAME(qt.[dbid]) = 'db_name'

ORDER BY qs.total_worker_time DESC -- CPU time