SQL Server Databases CPU Usage Stats

Hi,

Generally There are many databases on SQL Server Instance. Running databases into same instance is very important for SQL Server license and consolidation.

When many databases run in same SQL Server Instance then Resource usage of databases are very criticial.

If any database uses lots of CPU resource then we should monitor database and why that database consume more CPU resource than others.

We can learn that all databases CPU resources usage with following script.

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);

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/