Doubts in a Glenn Berry Query ( CPU per Database )

by Racer SQL   Last Updated January 12, 2018 20:06 PM

Please, if this is not place to post questions like these, let me know and I will delete it.

Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. This is the query:

-- Get CPU utilization by database (Query 24) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT DatabaseID,
        DB_Name(DatabaseID) AS [Database Name],
        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 [CPU Rank],
       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB 
ORDER BY [CPU Rank] OPTION (RECOMPILE);

And I would like to know, if this is a query to see databases that are now using more CPU, or is this based with past information?

I'm trying to know what is causing my server to have high CPU use:

enter image description here

Well, in this picture the server is pretty good, but almost always we have 90%+ of CPU usage, and using sp_whoisactive I can't find nothing ( obviously I got a lot of queries, but none of them seems to be hammering the server ).And read\write is pretty low ( everytime ). And thats what i'm having problems to understand. how can a low read/write server be using so much CPU? Does I/O have nothing in common with CPU?

I'm trying to know what database is the heaviest one, to migrate it.



Answers 2


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.

Aaron Bertrand
Aaron Bertrand
November 24, 2015 16:42 PM

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.

Max Vernon
Max Vernon
November 24, 2015 16:50 PM

Related Questions


Lower performance on newest servers

Updated August 29, 2017 14:06 PM

SQl code performance

Updated January 15, 2018 16:06 PM


SQL Server 2012 Performance Test

Updated January 15, 2018 16:06 PM