How to get CPU usage by database for particular instance?

Question

I have found the following queries to detect the CPU usage by database, but they are showing different results:

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 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

The above query tells that the issue is with one of my database (almost 96%).

And the query below tells that the issue is with the master and the distribution databases (about 90%):

DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

I have checked thet the sys.sysprocesses is decpreated. Does this mean that the results from the second query are wrong?

Answer 1

While I, like @Thomas, completely agree with @Aaron in the comments on the question regarding the concerns of "per-database CPU usage" being either accurate or useful, I can at least answer the question as to why those two queries are so different. And the reason why they are different will indicate which one is more accurate, though that higher level of accuracy is still relative to the one that is specifically inaccurate, hence still not truly accurate ;-).

The first query uses sys.dm_exec_query_stats to get CPU info (i.e. total_worker_time). If you go to the linked page that is the MSDN documentation for that DMV you will see a short, 3 sentence intro and 2 of those sentences give us most of what we need to understand the context of this info ("how reliable is it" and "how does it compare to sys.sysprocesses"). Those two sentences are:

Returns aggregate performance statistics for cached query plans in SQL Server. ... When a plan is removed from the cache, the corresponding rows are eliminated from this view

The first sentence, "Returns aggregate performance statistics", tells us that the information in this DMV (just like several others) is cumulative and not specific to only queries that are currently running. This is also indicated by a field in that DMV that is not part of the query in the Question, execution_count, which again shows that this is cumulative data. And it is quite handy to have this data be cumulative as you can get averages, etc by dividing some of the metrics by the execution_count.

The second sentence, "plans being removed from the cache are also removed from this DMV", indicate that it is not a complete picture at all, especially if the server has a pretty full plan cache already and is under load and hence is expiring plans somewhat frequently. Also, most DMVs are reset when the server resets so they are not a true history even if these rows weren't removed when the plans expire.

Now let's contrast the above with sys.sysprocesses. This system view is only showing what is currently running, just like the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (which is stated on the linked page for sys.dm_exec_sessions). This is an entirely different view of the server as compared to the sys.dm_exec_query_stats DMV which holds the data even after the process completes. Meaning, in relation to the "are the results from the second query wrong?" question, they are not wrong, they just pertain to a different aspect (i.e. time-frame) of performance stats.

So, the query using sys.sysprocesses is only looking at "right now". And the query using sys.dm_exec_query_stats is looking at mostly (maybe) what has happened since the last restart of the SQL Server service (or obviously system reboot). For general performance analysis it seems that sys.dm_exec_query_stats is far better, but again, it drops useful info all the time. And, in both cases, you also need to consider the points made by @Aaron in the question comments (since removed) regarding the accuracy of the "database_id" value in the first place (i.e. it only reflects the active DB that initiated the code, not necessarily where the "issue" is occurring).

But, if you just need/want to get a sense of what is happening right now across all Databases, possibly because things are slowing down right now, you are better off using the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (and not the deprecated sys.sysprocesses). Just keep in mind that you are looking at/for queries, not databases, because queries can join across multiple databases, include UDFs from one or more databases, etc.

---

EDIT:
If the overall concern is reducing high CPU consumers, then look for the queries that are taking up the most CPU, because databases don't actually take up CPU (looking per database might work at a hosting company where each database is isolated and owned by a different customer).

The following query will help identify queries with high average CPU usage. It condenses the data in the query_stats DMV since those records can show the same query (yes, the same subset of the query batch) multiple times, each with a different execution plan.

;WITH cte AS
(
  SELECT stat.[sql_handle],
         stat.statement_start_offset,
         stat.statement_end_offset,
         COUNT(*) AS [NumExecutionPlans],
         SUM(stat.execution_count) AS [TotalExecutions],
         ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],
         ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [AvgCPU]
  FROM sys.dm_exec_query_stats stat
  GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset
)
SELECT CONVERT(DECIMAL(15, 5), cte.AvgCPU) AS [AvgCPU],
       CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS [AvgLogicalReads],
       cte.NumExecutionPlans,
       cte.TotalExecutions,
       DB_NAME(txt.[dbid]) AS [DatabaseName],
       OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
       SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
       (
         (CASE cte.statement_end_offset 
           WHEN -1 THEN DATALENGTH(txt.[text])
           ELSE cte.statement_end_offset
          END - cte.statement_start_offset) / 2
         ) + 1
       )
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPU DESC;

Answer 2

I have adjusted the query for division by 0 errors and optimized column names for copy/paste to Excel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName], 
      SUM(total_worker_time) AS [CPU_Time_Ms],
      SUM(total_logical_reads)  AS [Logical_Reads],
      SUM(total_logical_writes)  AS [Logical_Writes],
      SUM(total_logical_reads+total_logical_writes)  AS [Logical_IO],
      SUM(total_physical_reads)  AS [Physical_Reads],
      SUM(total_elapsed_time)  AS [Duration_MicroSec],
      SUM(total_clr_time)  AS [CLR_Time_MicroSec],
      SUM(total_rows)  AS [Rows_Returned],
      SUM(execution_count)  AS [Execution_Count],
      count(*) 'Plan_Count'
    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 [Rank_CPU],
       DatabaseName,
       [CPU_Time_Hr] = convert(decimal(15,2),([CPU_Time_Ms]/1000.0)/3600) ,
        CAST([CPU_Time_Ms] * 1.0 / SUM(case [CPU_Time_Ms] when 0 then 1 else [CPU_Time_Ms] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU_Percent],
       [Duration_Hr] = convert(decimal(15,2),([Duration_MicroSec]/1000000.0)/3600) , 
       CAST([Duration_MicroSec] * 1.0 / SUM(case [Duration_MicroSec] when 0 then 1 else [Duration_MicroSec] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration_Percent],    
       [Logical_Reads],
        CAST([Logical_Reads] * 1.0 / SUM(case [Logical_Reads] when 0 then 1 else [Logical_Reads] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Reads_Percent],      
       [Rows_Returned],
        CAST([Rows_Returned] * 1.0 / SUM(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows_Returned_Percent],
       [Reads_Per_Row_Returned] = [Logical_Reads]/(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end),
       [Execution_Count],
        CAST([Execution_Count] * 1.0 / SUM(case [Execution_Count]  when 0 then 1 else [Execution_Count] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution_Count_Percent],
       [Physical_Reads],
       CAST([Physical_Reads] * 1.0 / SUM(case [Physical_Reads] when 0 then 1 else [Physical_Reads] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal_Reads_Percent], 
       [Logical_Writes],
        CAST([Logical_Writes] * 1.0 / SUM(case [Logical_Writes] when 0 then 1 else [Logical_Writes] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Writes_Percent],
       [Logical_IO],
        CAST([Logical_IO] * 1.0 / SUM(case [Logical_IO] when 0 then 1 else [Logical_IO] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_IO_Percent],
       [CLR_Time_MicroSec],
       CAST([CLR_Time_MicroSec] * 1.0 / SUM(case [CLR_Time_MicroSec] when 0 then 1 else [CLR_Time_MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR_Time_Percent],
       [CPU_Time_Ms],[CPU_Time_Ms]/1000 [CPU_Time_Sec],
       [Duration_MicroSec],[Duration_MicroSec]/1000000 [Duration_Sec]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [Rank_CPU] OPTION (RECOMPILE);

Answer 3

I liked the CPU query from sys.dm_exec_query_stats so much that I extended it. It is still ranked by CPU but I added other totals and percents to get a better server profile. This copies nicely into Excel and with conditional color formatting on the Percent columns, the worst numbers stand out nicely. I used the 'Graded Color Scale' with 3 colors ; a rose color for high values, yellow for middle, green for low.

I added a Label for database id 32676 which is the Internal SQL Resource Database. I convert CPU and Duration time to Hours to get a better sense of time usage.

WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName], 
      SUM(total_worker_time) AS [CPU Time Ms],
      SUM(total_logical_reads)  AS [Logical Reads],
      SUM(total_logical_writes)  AS [Logical Writes],
      SUM(total_logical_reads+total_logical_writes)  AS [Logical IO],
      SUM(total_physical_reads)  AS [Physical Reads],
      SUM(total_elapsed_time)  AS [Duration MicroSec],
      SUM(total_clr_time)  AS [CLR Time MicroSec],
      SUM(total_rows)  AS [Rows Returned],
      SUM(execution_count)  AS [Execution Count],
      count(*) 'Plan Count'

    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 [Rank CPU],
       DatabaseName,
       [CPU Time Hr] = convert(decimal(15,2),([CPU Time Ms]/1000.0)/3600) ,
        CAST([CPU Time Ms] * 1.0 / SUM([CPU Time Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent],
       [Duration Hr] = convert(decimal(15,2),([Duration MicroSec]/1000000.0)/3600) , 
       CAST([Duration MicroSec] * 1.0 / SUM([Duration MicroSec]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration Percent],    
       [Logical Reads],
        CAST([Logical Reads] * 1.0 / SUM([Logical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Reads Percent],      
       [Rows Returned],
        CAST([Rows Returned] * 1.0 / SUM([Rows Returned]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows Returned Percent],
       [Reads Per Row Returned] = [Logical Reads]/[Rows Returned],
       [Execution Count],
        CAST([Execution Count] * 1.0 / SUM([Execution Count]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution Count Percent],
       [Physical Reads],
       CAST([Physical Reads] * 1.0 / SUM([Physical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal Reads Percent], 
       [Logical Writes],
        CAST([Logical Writes] * 1.0 / SUM([Logical Writes]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Writes Percent],
       [Logical IO],
        CAST([Logical IO] * 1.0 / SUM([Logical IO]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical IO Percent],
       [CLR Time MicroSec],
       CAST([CLR Time MicroSec] * 1.0 / SUM(case [CLR Time MicroSec] when 0 then 1 else [CLR Time MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR Time Percent],
       [CPU Time Ms],[CPU Time Ms]/1000 [CPU Time Sec],
       [Duration MicroSec],[Duration MicroSec]/1000000 [Duration Sec]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [Rank CPU] OPTION (RECOMPILE);

Answer 4

We found using Query Store is an excellent starting point in server CPU consumers investigation. In case QS is enabled in all user databases you will get pretty accurate estimation for CPU average distribution among them during some time period.

For instance, the query below returns info on CPU consumers for last week:

DECLARE @DatabaseName NVARCHAR(256);
DECLARE @SQL    NVARCHAR(MAX);

CREATE TABLE #Stats (
    DatabaseName    NVARCHAR(256),
    CPUUsage        FLOAT
);

DECLARE myCursor CURSOR READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE is_query_store_on = 1;

OPEN myCursor;

WHILE 1=1 BEGIN

    FETCH NEXT FROM myCursor
    INTO @DatabaseName;

    IF @@FETCH_STATUS != 0 BREAK;

    SET  @SQL = N'
        USE ' + QUOTENAME(@DatabaseName) +';
        
        INSERT #Stats (DatabaseName, CPUUsage)
        SELECT @DatabaseName,
            SUM(qsrs.count_executions*qsrs.avg_cpu_time)
        FROM [sys].[query_store_runtime_stats] qsrs
            JOIN [sys].[query_store_runtime_stats_interval] qsrsi ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
        WHERE qsrsi.start_time > DATEADD(DAY, -7, GETDATE());
    '
    EXEC sp_executesql @SQL, N'@DatabaseName NVARCHAR(256)', @DatabaseName;

END

CLOSE myCursor;
DEALLOCATE myCursor;

SELECT ROW_NUMBER() OVER(ORDER BY CPUUsage DESC) AS [CPU Rank],
       DatabaseName, 
       ISNULL(CPUUsage , 0)/1000 AS [CPU Time (ms)], 
       CAST(ISNULL(CPUUsage / SUM(CPUUsage) OVER() * 100.0, 0) AS DECIMAL(5, 2)) AS [CPU Percent]
FROM #Stats
ORDER BY [CPU Rank];

DROP TABLE #Stats;