CODEWITHSUNDEEP
sql-server
Esat Erkec
Esat Erkec

Reputation: 1734

Actual CPU usage per database

I would like to measure the current of the CPU usage for a particular database and I wrote the following query in order to obtain this information. However, I could not be sure about the accuracy of this query. 

DECLARE @Fm INT;
DECLARE @FTime DATETIME;
SELECT @FTime = getdate(),@Fm = SUM(dmqs.total_worker_time) 
FROM sys.dm_exec_query_stats dmqs 
CROSS APPLY 
(SELECT 
CONVERT(INT, value) AS [DatabaseID] 
FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
WHERE attribute = N'dbid') dmpa
where DatabaseID=7
GROUP BY dmpa.DatabaseID
WAITFOR DELAY '00:00:01'
SELECT CAST((SUM(dmqs.total_worker_time) - @Fm)* 1.0  / SUM(dmqs.total_worker_time)  * 100 AS DECIMAL(5, 2))
FROM sys.dm_exec_query_stats dmqs 
 CROSS APPLY 
 (SELECT 
 CONVERT(INT, value) AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
 WHERE attribute = N'dbid') dmpa
 where DatabaseID=7
 GROUP BY dmpa.DatabaseID

Upvotes: 0

Views: 1028

Answers (1)

djh
djh

Reputation: 53

Have a look at Glen Berry's diagnostic queries. He has some CPU related ones including a breakdown of the CPU usage per database in an instance. https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

Upvotes: 2

Related Questions