Doubts in a Glenn Berry Query ( CPU per Database )

On first glance this looks to me to approximate CPU per database, over the history of sys.dm_exec_query_stats (so usually since the last restart), but only for plans that are currently in the cache. It also relies on a plan cache attribute, dbid, which means that was the context for the query, but not necessarily that that was the database that caused the work. For example, guess where all the CPU gets reported for this query:

USE tempdb;
GO
SELECT CONVERT(DATETIME, CONVERT(CHAR(10), CONVERT(DATE, 
  CONVERT(DATETIME, o.create_date)), 120))
FROM msdb.sys.all_objects AS o
CROSS APPLY model.sys.all_columns AS c;

I'll give you a hint: It's not msdb or model.

So, it should be used as a ballpark, but there are no guarantees that it reflects 100% of reality 100% of the time. The more often you query it (e.g. have some automated job that stores snapshots of it every n minutes), the more accurate it will be, but unless you have applications that treat each database like impenetrable silos, it will still be influenced by database context rather than the actual source of queries and data.

sys.dm_exec_query_stats 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 implication here is this dynamic management view will only partially fulfill your requirements. As noted by Aaron in his answer, attribution to a particular database is sensitive to the context of the query. Also, if plans are not being cached very long, they may not be in the cache at the time you run this query, so will not be reported. If your system runs dynamic queries you may be flooding the plan cache with single-use plans, that may be severely limiting the reliability of this performance metric.

Do you have 'optimize for ad hoc workloads' turned on? Check that with this query:

SELECT c.name 
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'optimize for ad hoc workloads';

You can use the following query to determine how the plan cache is changing over time:

BEGIN TRY
CREATE TABLE #PC1
(
    refcounts INT
    , usecounts INT
    , size_in_bytes INT
    , memory_object_address varbinary(32)
    , cacheobjtype VARCHAR(255)
    , objtype VARCHAR(255)
    , plan_handle VARBINARY(32)
    , [dbid] INT
    , objectid INT
    , query_plan XML
);
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
CREATE TABLE #PC2
(
    refcounts INT
    , usecounts INT
    , size_in_bytes INT
    , memory_object_address varbinary(32)
    , cacheobjtype VARCHAR(255)
    , objtype VARCHAR(255)
    , plan_handle VARBINARY(32)
    , [dbid] INT
    , objectid INT
    , query_plan XML
);
END TRY
BEGIN CATCH
END CATCH

TRUNCATE TABLE #PC1;
TRUNCATE TABLE #PC2;

INSERT INTO #PC1
(
    refcounts 
    , usecounts 
    , size_in_bytes 
    , memory_object_address 
    , cacheobjtype 
    , objtype 
    , plan_handle 
    , [dbid] 
    , objectid 
    , query_plan 
)
SELECT 
    refcounts 
    , usecounts 
    , size_in_bytes 
    , memory_object_address 
    , cacheobjtype 
    , objtype 
    , plan_handle 
    , [dbid] 
    , objectid 
    , query_plan 
FROM sys.dm_exec_cached_plans decp
    CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) t
ORDER BY decp.usecounts DESC;

WAITFOR DELAY '00:01:00';

INSERT INTO #PC2
(
    refcounts 
    , usecounts 
    , size_in_bytes 
    , memory_object_address 
    , cacheobjtype 
    , objtype 
    , plan_handle 
    , [dbid] 
    , objectid 
    , query_plan 
)
SELECT 
    refcounts 
    , usecounts 
    , size_in_bytes 
    , memory_object_address 
    , cacheobjtype 
    , objtype 
    , plan_handle 
    , [dbid] 
    , objectid 
    , query_plan 
FROM sys.dm_exec_cached_plans decp
    CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) t
ORDER BY decp.usecounts DESC;

SELECT QueryPlan = pc1.query_plan
    , UseCount = pc2.usecounts - pc1.usecounts
    , PlanSize = pc1.size_in_bytes
    , CacheType = pc1.cacheobjtype
    , objType = pc1.objtype
    , DatabaseID = pc1.dbid
FROM #PC1 pc1
    INNER JOIN #PC2 pc2 ON pc1.plan_handle = pc2.plan_handle
WHERE pc2.usecounts - pc1.usecounts > 0
ORDER BY (pc2.usecounts - pc1.usecounts);

SELECT QueryPlan = pc1.query_plan
    , UseCount = pc1.usecounts
    , PlanSize = pc1.size_in_bytes
    , CacheType = pc1.cacheobjtype
    , objType = pc1.objtype
    , DatabaseID = pc1.dbid
FROM #PC1 pc1
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM #PC2 pc2 
    WHERE pc2.plan_handle = pc1.plan_handle
)
ORDER BY (pc1.usecounts);

SELECT QueryPlan = pc2.query_plan
    , UseCount = pc2.usecounts
    , PlanSize = pc2.size_in_bytes
    , CacheType = pc2.cacheobjtype
    , objType = pc2.objtype
    , DatabaseID = pc2.dbid
FROM #PC2 pc2
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM #PC1 pc1 
    WHERE pc1.plan_handle = pc2.plan_handle
)
ORDER BY (pc2.usecounts);

This query looks at the plan cache twice, waiting 1 minute in between. It then returns 3 result sets:

• The first shows plans that remained in the cache between the first and second runs. (Persisted plans)
• The second shows plans that were in the cache during the first run, but were no longer in cache during the second run. (Evicted plans)
• The third shows plans that were in the cache during the second run but not the first run. (New plans)

If the first result set shows a far smaller number of plans than the second result set, that is an indication your server is continuously out of space in the cache; this would obviously indicate Glenn's query will not be as reliable as one might hope.