use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();
This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.
However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select x.value(N'@NodeId',N'int') as NodeId
, x.value(N'@PhysicalOp', N'sysname') as PhysicalOp
, x.value(N'@LogicalOp', N'sysname') as LogicalOp
, ox.value(N'@Database',N'sysname') as [Database]
, ox.value(N'@Schema',N'sysname') as [Schema]
, ox.value(N'@Table',N'sysname') as [Table]
, ox.value(N'@Index',N'sysname') as [Index]
, ox.value(N'@IndexKind',N'sysname') as [IndexKind]
, x.value(N'@EstimateRows', N'float') as EstimateRows
, x.value(N'@EstimateIO', N'float') as EstimateIO
, x.value(N'@EstimateCPU', N'float') as EstimateCPU
, x.value(N'@AvgRowSize', N'float') as AvgRowSize
, x.value(N'@TableCardinality', N'float') as TableCardinality
, x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost
, x.value(N'@Parallel', N'tinyint') as DOP
, x.value(N'@EstimateRebinds', N'float') as EstimateRebinds
, x.value(N'@EstimateRewinds', N'float') as EstimateRewinds
, st.*
, pl.query_plan
from sys.dm_exec_query_stats as st
cross apply sys.dm_exec_query_plan (st.plan_handle) as pl
cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x)
cross apply op.x.nodes('./*/Object') as ob(ox)
Worker time is the time the task(s) was effectively active, occupying a scheduler and running code (ie. not suspended). Elapsed time is clock time. On a DOP 1 query the worker time will be at most the elapsed time, less if the task was suspended at any moment during execution (thus the clock time would advance, but the worker time not). For DOP > 1 the worker times aggregate so they can exceed elapsed time, while still subject to suspension.
A significant difference between worker time and elapsed time is indicative of blocking, consider the 682616 worker vs. 11509766 elapsed: this a request that blocked for 11 seconds waiting on something (probably a lock).
Best Answer
The clustered index has 140992 pages, from which only 1410 are page compressed. Page compression is attempted only when it fills up, see When Page Compression Occurs:
It seems that in your case very few pages got compressed. Note that you still benefit from row compression.