-- Get CPU utilization by database
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);
-- Helps determine which database is using
the most CPU resources on the instance
-- Get I/O utilization by
database
WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id)
AS [Database Name],
CAST(SUM(num_of_bytes_read +
num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY
database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank],
[Database Name], io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb/
SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM
Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);
--
Helps determine which database is using the most I/O resources on the instance
No comments:
Post a Comment