Hello and welcome! SQL server performance management is an important task in the life of a DBA.
You just cant get away with it. There are some queries you can run to check the SQL server health.
First in the series is to check the databases CPU resources usage.
Here is your query:
WITH DB_CPU_STATS_ON_INSTANCE
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_STATS_ON_INSTANCE
WHERE DatabaseID > 4
AND DatabaseID <> 32767
ORDER BY row_num OPTION (RECOMPILE);
It will provide you database wise CPU usage. The output will look like this.
Hope you find it useful.
In the next article we will see how to find top CPU queries in SQL Server database that are consuming more CPU.
TOP CPU queries in SQL Server database
Happy Learning!!
SQL Automation Guru 
[…] the previous article we have seen how to identify databases CPU resource usages. Please check Databases CPU resources usage article for more details.Now if you want to check what all top CPU queries in SQL Server database […]
LikeLike