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
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_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.
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
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],
DB_NAME(txt.[dbid]) AS [DatabaseName],
OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
WHEN -1 THEN DATALENGTH(txt.[text])
END - cte.statement_start_offset) / 2
) + 1
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPU DESC;