6

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.

  • 4
    Honestly, if you want to identify your heaviest databases, just get a monitoring tool, they will typically be able to slice this information for you without you having to learn and/or rewrite scripts. Most of the big ones have trial versions that can probably help you in the short term. – Aaron Bertrand Nov 24 '15 at 16:46
6

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.

  • I would say it's using tempdb, because it's using it to "calculate"? I really don't know. And, I was using this query in the question together with a query that shows expensives queries. So I can merge these informations. There was a heavy query running every second, and the database was in 3 place in berry's query. I fixed it and now it's now even on the list. Thanks again Aaron for helping. – Racer SQL Nov 24 '15 at 16:49
  • @Rafael It's going to report CPU in tempdb because of the USE tempdb; command. If that had been USE master;, the database id in the plan attributes would be the one that correlates to master. – Aaron Bertrand Nov 24 '15 at 16:53
  • I was going to say that haha. But it's good to know. I'll not try to recreate the whell. So, if I use use database x and inside this database, I have only queries pointing to database Y, the cpu will be calculate to the use database ( in this case, x)? – Racer SQL Nov 24 '15 at 16:57
  • Not exactly. What happens is that the plan is associated with database x because of database context, not because of any of the work performed by the query. Glenn's query assumes that if a query is associated with database x due to database context, then the work it performed must have happened in database x. This is probably true often, but not enough to be a given. – Aaron Bertrand Nov 24 '15 at 16:58
5

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.

  • Thank you for the awesome answer @maxVernon. yes we have ` optimize for ad hoc workloads` =1 here. I got 11162 in the first result set, 4426 in the second one, and 17662 in the last one. I just didnt understand the last result set. It means the SQL server can save execution plans for a good time ( based on this results )? this has something with buffer cache right? or doesnt it? – Racer SQL Nov 24 '15 at 17:53
  • 1
    @RafaelPiccinelli - The last result set shows plans that were added to the plan cache while we were waiting for the 1 minute WAITFOR DELAY to complete. This means over a one minute period, you had 17,662 plans cached that were not in the cache at the start of the one minute period. Cached items, such as plans, are stored in the buffer pool. Maximum cache sizes are related to the size of the buffer pool, which is controlled by the max server memory configuration option. – Max Vernon Nov 24 '15 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.