How to get CPU usage by database for particular instance? 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 &gt; 4 -- system databases
--AND DatabaseID &lt;&gt; 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?
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;