We all know how easy it is to see how much CPU power is consumed by the SQL Server process, tools like Performance Monitor or Task Manager will give you this information very easy.

But have you, like I have, ever wondered which one of your many databases is consuming the most CPU power? Is it the large database, that you expect to use all the CPU or will you be surprised like I have been a few times in the last few weeks, that it actually is a smaller “unknown” database that consumes all the power?

In the Dynamic Management View (DVM) “sys.dm_exec_query_stats”, you will find a column named total_worker_time – this column contains information about how much cpu time in milliseconds has been used by a specific SQL statement. You can read the documentation to this DVM here.

By doing a fairly simple SUM and GROUP BY, you are able to get the total amount of used milliseconds per database, and by adding a little more math you can even get a percentage as well. Enough talking, here is the query:

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) AND (DatabaseID <> 32767)
ORDER BY row_num

This script was originally written by SQL Server MVP Robert Pearl, I found it on SQL Server MVP Glenn Berry’s blog – I am not trying to take any credits, I just think that this script is so nice, that I would like to share with the rest of you.

And before I finish this blog post, here is a little picture that shows how the databases on my work laptop is sharing the crazy CPU resources between them.