Hi
We run multiple shared MS SQL Servers and we have sometime the issue, that a single Database on the MS SQL Instance is causing high CPU load. All other DBs are affected.
I haven't found a way to get the cpu time per database with dynatrace. We have the SQL Extension activated, but no such metric is here.
With the following query, we were able to figure it out. Would it be possible to add a metric per MS SQL DB with the cpu Load?
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 -- comment to exclude system databases --AND DatabaseID <> 32767 -- Uncomment to exclude ResourceDB ORDER BY row_num OPTION (RECOMPILE);
