Per-Database CPU Usage in SQL Server

 

As database administrators, it's crucial to monitor and analyze CPU usage across different databases in SQL Server. By understanding which databases consume the most CPU resources, we can better optimize performance and make informed decisions about resource allocation. In this post, I'll share a T-SQL query that helps you identify the CPU usage distribution among your databases.

The following query aggregates CPU time across all databases and ranks them by their total CPU consumption. It also calculates the percentage of total CPU time each database uses.

Copied!

WITH DB_CPU_Stats 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
  WHERE DatabaseID > 4 -- comment to exclude system databases
--AND DatabaseID <> 32767 -- Uncomment to exclude ResourceDB
ORDER BY row_num 
OPTION (RECOMPILE);

This query can be a valuable script in your SQL Server performance monitoring toolkit. By regularly running it, you can keep an eye on how CPU resources are being utilized and take proactive steps to optimize your database environment.