Which SQL Server database is using the most CPU, or consuming the most IO ?

You may have noticed that I haven't posted in quite some time.  I moved during the 1st week of March, and it's been a pretty big transition.  I do apologize for the delay, but I'm back. I have a lot saved in my 'to post' list, and this is the first of many.  :-)

Just a couple quick statements you can use to analyze where your system resources are going. Specifically, your server's CPU utilization and IO file statistics.  


-- Collect CPU usage per database
WITH CPU
AS
(SELECT DatabaseID, DB_Name(DatabaseID)  [Database],SUM(total_worker_time) [CPUTimeMS]
 FROM sys.dm_exec_query_stats dmqs CROSS APPLY (
    SELECT CONVERT(int, value) [DatabaseID] 
    FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
    WHERE attribute = N'dbid') a
    GROUP BY DatabaseID)
 SELECT 
    [Database], 
    [CPUTimeMS], 
CAST([CPUTimeMS] * 1.0 / SUM([CPUTimeMS]) OVER() * 100.0 AS DECIMAL(5, 2))  [CPU%]
 FROM CPU
 ORDER BY [CPU%] DESC;

-- Collect IO consumption per database

WITH IO

AS

(SELECT DB_NAME(database_id) [Database],CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) [IOMB]

 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmios

 GROUP BY database_id)

 SELECT 

[Database],

IOMB [TotalIOMB],

    CAST(IOMB/SUM(IOMB) OVER() * 100.0 AS DECIMAL(5,2)) [IO%]

FROM IO

ORDER BY [IO%] DESC;

See here for more detail from MSFT on either of the two above DMVs:

    https://msdn.microsoft.com/en-us/library/ms189472.aspx

    https://msdn.microsoft.com/en-us/library/ms190326.aspx