SQL Server Database CPU Utilization and Memory Usage

Apart from Query Performance analysis and Schema Objects analysis (indices, statistics etc.), there are two important metrics that are usually sought out for a poorly performing SQL Server instance. These are – CPU usage (grouped by database instance) and memory usage (mainly buffer pool memory) , also grouped by db instance. The following queries do require admin roles to execute – and provide you with the required memory and CPU usage. These are taken from Glen Barry’s post .

Get CPU utilization by database (adapted from Robert Pearl)

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 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 [row_num],
       DatabaseName, [CPU_Time_Ms], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

 

NOTE: It is important to note that this is a SNAPSHOT of the CPU usage per database. To get a time averaged value – this query needs to be run over a period (with the results written to a table of some sort) .

http://www.geniiius.com/blog/which-database-is-using-all-my-cpu

Get total buffer usage by database for current instance

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

— This make take some time to run on a busy instance